Variables
Published 10 F 2016
If you need handle subtle differences betwen your Dev, Staging & Production environments, or if you are deploying to customer sites with variations that you don't want to hard-code into your project scripts, it can be useful to provide certain values from an external source. ReadyRoll allows parameterization of your deployments with the help of SQLCMD variables.
You start by adding variable definitions to your ReadyRoll database 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.
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 ReadyRoll database 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 you just created in a new migration script.
Variable qualifiers
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 using a variable within an object or database name, the variable must be enclosed by [] square brackets, e.g. SELECT * FROM [$(MyObjectNameVariable)]
- When using a variable within a string, the variable must be enclosed by quotation marks, either with the '' single-quote or "" double-quote characters, e.g. PRINT '$(HelloWorldVariable)'
- When using a variable that contains a numeric value, the variable must be treated like a string (as above) and cast as a numeric data type before it can be used. For example DECLARE @myNumber int = CAST('$(MyNumberVariable)' AS int)
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 database project outside of Visual Studio, ReadyRoll 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:
Octopus Deploy
ReadyRoll 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 ReadyRoll projects with Octopus Deploy.
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 and execute the following command:
@powershell -NoProfile -ExecutionPolicy unrestricted -Command "& { $DatabaseServer='(local)\SQLEXPRESS'; $Environment='STAGING'; .\VariableSampleProj_DeployPackage.ps1 }"
Or to deploy directly within PowerShell, use this command:
& { $DatabaseServer='(local)\SQLEXPRESS'; $Environment='STAGING'; .\VariableSampleProj_DeployPackage.ps1 }
(Click the following screenshot 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.
Name | Example | Description |
---|---|---|
DatabaseServer | MYDBSERVER\INSTANCE | Required. Name of the SQL Server machine\instance to deploy to. |
DatabaseUserName | sa | 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. |
DatabasePassword | ******** | |
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.
Name | Example | Description/Default value |
---|---|---|
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 ReadyRoll database project itself. |
ReleaseVersion | 1.0.0-MyRelease | 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. The exception to this is when deploying with Octopus, in which case the value is automatically sourced from the Octopus.Release.Number system variable. |
PackageVersion | 1.0.123-MyRelease | 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, 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 ReadyRoll database project.. |
ForceDeployWithoutBaseline | False | Default is False. If you are attempting to deploy to an existing database, ReadyRoll 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\1.0.0.123\. 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. |
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\ | |
IsShadowDeployment | 1 | 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. |