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.
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 using variables in your scripts, qualifiers must be used in order for your scripts to be successfully parsed and validated by the build engine.
When you build the project, the variable will be substituted with the Default value (or Local value, if it was provided).
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:
SQL Change Automation has first-class support for Octopus Deploy: any variable that you define on the SQLCMD Variable tab will automatically be sourced from your Octopus project variables at deploy-time.
Additionally, if you intend to use Octopus to orchestrate your database deployments, you do not need to enable the above option. For more details, see our guide on setting up SQL Change Automation projects with Octopus Deploy with SQL Change Automation Octopus Packages.
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:
Or to deploy directly within PowerShell, use this command:
(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).
|DatabaseServer||MYDBSERVER\INSTANCE||Required. Name of the SQL Server machine\instance to deploy to.|
Optional. Provide a value for these variables if you would like to use SQL Server Authentication to connect to your database server, otherwise Windows Integrated Authentication will be used.
|UseSqlCmdVariableDefaults||True||Optional. Set to False if you require that values for all non-system SqlCmd variables be passed in explicitly, rather than using the default values as set in the project file.|
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.
|DatabaseName||AdventureWorks||The name of the database being deployed. This value is sourced from the TargetDatabase MSBuild property, and if that has not been specified, the name of the SQL Change Automation project itself.|
Release number to store against deployed migrations within the [dbo].[__MigrationLog] table. There is no default value for this variable; a warning will be emitted during deployment if it has not been set.
Read-only. Package version to store against deployed migrations within the [dbo].[__MigrationLog] table. If Semantic Versioning has been enabled, it will use the current SemVer for the project. Otherwise, it will be set to (undefined).
However, if using Octopus Deploy with SQL Change Automation Octopus Packages, this value will be set at build-time either from the OctoPackPackageVersion MSBuild property, or if that has not been provided, from the AssemblyInfo.cs file within the SQL Change Automation project.
|ForceDeployWithoutBaseline||False||Default is False. If you are attempting to deploy to an existing database, SQL Change Automation performs a check to ensure that a baseline has been set. This is done to ensure that no objects are accidentally dropped or overwritten during deployment. To force the deployment to continue without a baseline, set this to True.|
|DeployPath||c:\Source\MyProject\||Provides the full path to the currently deploying project/package, useful when needing to refer to packaged files from your scripts. In Visual Studio, this will be the root of the project (i.e. where the .sqlproj file is located), and in Octopus Deploy this will be the path that the package is extracted to, e.g. C:\Octopus\Applications\MyProject\AdventureWorks.Database\188.8.131.52\. For more information, see Seed Data.|
|DefaultFilePrefix||AdventureWorks||The filename prefix for the MDF/LDF files for your database storage. The default value for this variable is the same as DatabaseName.|
The default directory paths for the MDF/LDF/BKP files, which can be used to determine where your database should be stored (provided that you have consumed these variables in the CREATE statement of your Pre-Deployment\01_Create-Database.sql project file).
These variables are sourced from the instance-specific branch of the SQL Server's system registry. In the event that no default paths have been set for that SQL Server instance, the directory paths to the [master] database's files will be used instead.
Read-only. Indicates that the currently executing deployment is to the shadow database. Possible values are 0 or 1.
This can be used to gate whether to include a set of statements as part of the script verification process or not.