SQL Change Automation 3

Installing a local instance of SQL Server Express

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.

Chocolately installation

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

Please note that your acceptance of the Microsoft EULA’s is implied when you run these commands (legal information here).


Regarding the last command above: this ensures that your build agent can access the newly-installed SQLEXPRESS instance (which installs as the default SQL Server instance on the machine). You might choose to make this access more restrictive if you know which user your build agent will run-as.

Didn't find what you were looking for?