If you need a database engine to use with your application on the windows platform, SQL Express is probably the easiest option. Today I will cover how to install SQL Express manually. Then I will cover a more automated way to install SQL Express.
What is SQL Express?
Before we begin, lets talk about what SQL Express is. SQL Express is a limited version of MS SQL. You get all of the basic functionality, but none of the cost. You don’t get access to SQL agent, which you would use for scheduling jobs, you have a memory and CPU limit, which varies by version. You have a database size limit of 10 GB. And generally you don’t get replication, because a lot of the forms of replication rely on the SQL agent. That said, it is possible to replicate data with a SQL Express…it just takes more work.
Most of the time, the limitation you will run into first is the database size limit. It is hard to max out the CPU and memory with a 10GB database.
Manually installing SQL Express
You can download SQL Express from Microsoft here. After you have finished downloading the installer, double-click on it to start the installation wizard. Follow the prompts on the screen to install SQL. If you used all of the default settings, you can connect to sql using the default location of:
Scripted Install of SQL Express
Microsoft has instructions with all of the command line parameters here. But here is a condensed version. One thing to note is during any scripted installation, you still have to accept the EULA. That can be done with this command line argument: /IACCEPTSQLSERVERLICENSETERMS. You also have to combine any Quiet install with the /action=install argument.
Here is an example of the most basic installation command that will install SQL using the default instance name:
Setup.exe /q /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="<DomainName\UserName>" /SQLSVCPASSWORD="<StrongPassword>" /SQLSYSADMINACCOUNTS="<DomainName\UserName>" /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /IACCEPTSQLSERVERLICENSETERMS
As you can see, there are a few arguments you will need to fill in. They are:
- SQL Service Account
- SQL Service Account Password
- SQL sysadmin Accounts
After you fill in the three parameters above you are ready to run your command
Open an elevated command prompt, navigate to the folder with the setup.exe file, and enter the installation command above.
Install SQL Express using Chocolatey
My favorite method of installing SQL Express is by using Chocolatey. Chocolatey is a pakage manager for windows. Similar to Apt-get and yum on Linux. Chocolatey is based on Nuget packages, which if you are a software developer, you may be familiar with those.
First, you need to install Chocolatey.
Next, you just have to run the following from an elevated powershell prompt:
choco install sql-server-express -y
When the above command finishes running, you will have SQL Express installed. I would also recommend installing SQL Management studio:
choco install sql-server-management-studio -y
You now know how to install SQL express on your workstation or server. The configuration and the SQL language for SQL express is the same as the full version of MS SQL.