Installing a local instance of SQL Server Express
Published 24 February 2016
As mentioned in Continuous Integration, it's recommended to use a local instance of SQL Server with gated deployment. This ensures that there are no collisions between build agents (e.g. if you are running multiple simultaneous builds on different build agents).
It's to use Microsoft SQL Server 2017 Express. You can install it manually from the Microsoft website, or use Chocolatey.
Chocolately is recommended as it simplifies the installation process. The package that is available from Chocolatey is pre-configured with a set of common default settings.
To install Microsoft SQL Server 2017 Express using Chocolatey, open a command prompt in administrator mode and run the following script. It will do the following:
- Install Chocolately and add it to %PATH%
- Install Microsoft SQL Server 207 Express
- Add your Windows user to the SQL Server Express instance to system administrator
@powershell -NoProfile -ExecutionPolicy unrestricted -Command "iex ((new-object net.webclient).DownloadString('http://chocolatey.org/install.ps1'))" && SET PATH=%PATH%;%systemdrive%\chocolatey\bin CALL cinst sql-server-express @ECHO Adding [BUILTIN\Users] to sysadmin role on local SQL instance "%ProgramFiles%\Microsoft SQL Server\110\Tools\Binn\sqlcmd.exe" -S . -E -Q "ALTER SERVER ROLE [sysadmin] ADD MEMBER [BUILTIN\Users];"
These commands will take a while to run, but you shouldn't have to intervene or confirm any dialog boxes, and a restart should not be required.
Microsoft Licensing Terms
ALTER SERVER ROLE