SQL Change Automation 4

Variables

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).

NameValue during developmentValue during deployment
DatabaseNameThe name of the development or shadow database being updatedThe name of the database being deployed to
DeployPathThe project folder pathThe 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:

NameExampleDescription/Default value
ForceDeployWithoutBaselineFalseDefault 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.

NameExampleDescription/Default value
ReleaseVersionRelease-1Default 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.

NameExampleDescription/Default value
DefaultFilePrefixAdventureWorksThe 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.
DefaultDataPathD:\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.

DefaultLogPathD:\MSSQL11\MSSQL\Log\
DefaultBackupPathD:\MSSQL11\MSSQL\Backup\

Didn't find what you were looking for?