Published 31 July 2019
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 project, along with default values, and when it comes to deploying your database, you can supply values that are appropriate to each of your environments. These custom values can be provided by a continuous integration server like TeamCity, Azure DevOps or Bamboo, using a custom installer (for example, an InstallShield or Wix package), or with a deployment tool like Octopus Deploy. Azure DevOps and Octopus Deploy will populate these SQLCMD variables from server environment variables.
Variables populated by SQL Change Automation when performing deployments
The following is a list of built-in variables that can be used in your project scripts using SQLCMD syntax, that is $(VariableName).
|Name||Value during development||Value during deployment|
|DatabaseName||The name of the development or shadow database being updated||The name of the database being deployed to|
|DeployPath||The project folder path||The release folder path|
Defining your own variables
You can add SQLCMD variables to your SQL Change Automation project within the SQL Change Automation project file:
<ItemGroup> <SqlCmdVariable Include="RELEASE_EnvironmentName"> <DefaultValue>Not-specified</DefaultValue> <Value>$(SqlCmdVar__1)</Value> </SqlCmdVariable> </ItemGroup>
In Visual Studio you can do this via the UI. Learn more
Variables hard coded in the deployment scripts
The following variables are hard-coded in the deployment scripts:
|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.|
Read-only. Indicates that the currently executing deployment is to the shadow database. Possible values are 0 or 1.
This can be used to exclude statements from the verification process.
Optional variables in deployment scripts
The following variables can be provided in deployment scripts.
|ReleaseVersion||Release-1||Default is empty. The value is used in the MigrationLog table.|
Legacy hard coded variables
The following variables are still defined in project deployment scripts, but they are not used by the patch or package scripts. We recommend defining your own variables for these properties if desired, rather than using these variables directly.
These variables may be removed in a future version of SQL Change Automation.
|DefaultFilePrefix||AdventureWorks||The filename prefix for the MDF/LDF files for your database storage. The default value for this variable is the same as the name of the database the script is generated against.|
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.