Installing a local instance of SQL Server Express
Published 24 February 2016
As mentioned in this article on Continuous Integration, we recommend that you use a local instance of SQL Server with gated deployment. This ensures that there are no collisions between build agents (i.e. if you are running multiple simultaneous builds on different build agents).
Installing SQL Server can be quite a long and tedious task, so instead of doing this manually, an alternative is to use Chocolatey. The package that is available from Chocolatey is pre-configured with a set of common default settings.
To install SQL Server 2012 Express Edition using Chocolatey, open a command prompt in administrator mode and run the following (not that this will also retrieve Chocolatey if you don't already have it installed):
@powershell -NoProfile -ExecutionPolicy unrestricted -Command "iex ((new-object net.webclient).DownloadString('http://chocolatey.org/install.ps1'))" && SET PATH=%PATH%;%systemdrive%\chocolatey\bin CALL cinst MsSqlServer2012Express @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