SQL Change Automation allows you to deploy from the same project folder to multiple environments. However, there may be environment specific requirements for each deployment. For instance, you may want to produce more detailed logs when deploying to a User Acceptance Test environment. Instead of hard-coding these variations into your project scripts, it's possible to parameterize your deployments with the help of SQLCMD variables.

You start by adding variable definitions to your SQL Change Automation project within Visual Studio, along with default values, and when it comes to deploy your database outside of the IDE, you can supply values that are appropriate to each of your target environments. These custom values can be provided by a Continuous Integration server like TeamCity, TFS or Bamboo, using a custom installer (e.g. an InstallShield or Wix package), or with a deployment tool like Octopus Deploy with SQL Change Automation Octopus Packages.

Download the sample project (VariableSampleProj.zip, 6kB) used in this article.

Adding a SQLCMD Variable to your project

You can add SQLCMD variables to your SQL Change Automation project within the SQLCMD Variable tab of project properties.

The value you provide in the Default column will be stored in the project file (and therefore shared with other team members) however the Local value is specific to your machine (stored in the non-source controlled .user file). If you leave the Local column blank, the Default will be used when deploying inside Visual Studio.

Using the $(VariableName) notation, reference the variable in a new migration script.

When you build the project, the variable will be substituted with the Default value (or Local value, if it was provided).

Deploying with environment-specific values

When building your SQL Change Automation project outside of Visual Studio, SQL Change Automation can produce a re-usable type of deployment artifact called a SQLCMD package (equivalent to a DACPAC file) which contains all of your project's migrations, SQLCLR assemblies and pre/post-deployment scripts.

This packaging format is designed with the build once, deploy many times approach in mind, meaning that you can use the same package to deploy to your Development, Staging & Production environments. To cater for differences between your environments, you can provide values for variables at the command line, or within the configuration of your Continuous Integration/Deployment server.

To enable package creation as part of your build, firstly enable the SQLCMD package option under Output within project properties:

Build your project to produce the package deployment T-SQL script and accompanying PowerShell script (i.e. VariableSampleProj_Package.sql and VariableSampleProj_DeployPackage.ps1).

To deploy your database, open a Command Prompt session, navigate to the output folder for your project (e.g. bin/debug) and execute the following command, substituting in the relevant values:

@powershell -NoProfile -ExecutionPolicy unrestricted -Command "& { $DatabaseServer='(localdb)\ProjectsV13'; $Environment='STAGING'; .\AdventureWorks_DeployPackage.ps1 }"

Or to deploy directly within PowerShell, use this command:

& { $DatabaseServer='(localdb)\ProjectsV13'; $Environment='STAGING'; .\AdventureWorks_DeployPackage.ps1 }

(Click to enlarge)

Note the list of default values that is emitted during deployment. These values are taken from the Default column in the SQLCMD variable definition, but can be overriden by simply specifying additional PowerShell variables as part of the deploy command. For example, in the above deployment, we provided a value for the Environment variable ("STAGING"). We also provided a value for the built-in DatabaseServer variable ("(local)\A"), which is used to instruct the SqlCmd tool to deploy to a given instance of SQL Server.

However you can also override any of the other built-in variables. For example, you may decide to override DatabaseName, if you want to deploy multiple copies of the database to the same SQL Server instance (MyDb_DEV, MyDb_STG, MyDb_PROD).

System Variables

The following is a list of system variables that you can provide to your package-based deployment from the PowerShell command line or with corresponding Octopus variables.

The following is a list of built-in system variables that can be used in your project scripts using SQLCMD syntax, i.e. $(VariableName). Unlike the above system variables, all of these apply to both the package and patch-based deployment methods.

The values for each of these variables can be overridden at the command line or with corresponding Octopus variables, with the exception of the PackageVersion variable which is set at build-time and thus is read-only.