SQL Change Automation 4

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.

When deploying or creating a Release Artifact using Azure DevOps or Octopus Deploy all environment variables will be converted to SQLCMD variables. If you have defined a SQLCMD variable in your project and an environment variable with same name exists, the environment variable will take precedence. Note that Azure DevOps converts all non-secret pipeline variables to environment variables automatically.

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:

Name

Example

Description/Default value

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.

IsShadowDeployment

0

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.

As these variables are built into SQL Change Automation, you do not need to add definitions to the project settings SQLCMD variables in order to use them in your migrations.

Optional variables in deployment scripts

The following variables can be provided in deployment scripts.

Name	Example	Description/Default value
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.

Name	Example	Description/Default value
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.
DefaultDataPath	D:\MSSQL11\MSSQL\Data\	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.
DefaultLogPath	D:\MSSQL11\MSSQL\Log\
DefaultBackupPath	D:\MSSQL11\MSSQL\Backup\