Provisioning
Published 03 A 2020
SQL Change Automation can use different methods to provision and clean databases for the following scenarios:
- Shadow databases for development purposes.
- Temporary databases for continuous integration builds.
These databases are used in both continuous integration builds and when developing database changes (e.g. generating and applying migrations).
SQL Change automation may also provision databases at deployment time if deploying to a database which does not yet exist.
The available methods to provision them are:
- Create databases using T-SQL scripts.
- Use SQL Clone to create lightweight cloned databases.
Custom scripts provisioning
This is the default for new projects created using SQL Change Automation.
There are two scripts that allow configuration of provisioning databases:
CreateDatabase.sql
- This will be run when a database is provisioned. Running this script must result in a database being created.DropDatabase.sql
- This will be run when a database is cleaned. Running this script should usually result in the database no longer existing on the instance.
These scripts should use the placeholder variable @DatabaseName
which will be provided by SQL Change Automation. SQL Cmd variables defined in SQL Change automation can also be used in these scripts.
SQL Clone provisioning
Instead of creating databases using T-SQL create scripts, you can configure SQL Change Automation to use a SQL Clone server to create clones for the scenarios previously described.
You can learn more about the benefits of using SQL Clone together with SQL Change Automation in using a SQL Clone image as a baseline.
Default scripts provisioning
For projects created using older versions of SQL Change Automation (4.2.20126 and earlier) database creation happens as part of deployment.
To provision the desired database the first pre-deployment script should contain a database creation script.
In this scenario, the sql for dropping temporary databases is hard-coded internally and cannot be altered.
Configuration
You can change the provisioner that your project uses at any time. The configuration for your development source is stored inside the settings file for your project.
When changing your provisioner it is recommended that you manually drop your shadow database. This will ensure it is provisioned again with your new configuration.
Default scripts provisioning
This provisioning method will be used by default if no provisioner is set in the project.
Custom scripts provisioning
To use custom scripts as your provisioning source you must specify:
- Custom scripts as your provisioner
Set custom scripts as the provisioner in the .sqlproj
<PropertyGroup> <Provisioner>CustomScripts</Provisioner> </PropertyGroup>
In addition, you must provide the custom scripts you wish to use in the project folder:
- Add a Provisioning folder to the root of your project
- Add a
CreateDatabase.sql
script to yourProvisioning
folder - Add a
DropDatabase.sql
script to yourProvisioning
folder
SQL Clone provisioning
To use SQL Clone as your provisioning source you must specify:
- SQL Clone as your provisioner
- The SQL Clone server URL
- The baseline SQL Clone image
You should ensure your SQL Clone environment is configured to allow access from the SQL Change Automation machines. (See the SQL Clone requirements)
Set SQL Clone as the provisioner in the .sqlproj
<PropertyGroup> <Provisioner>Clone</Provisioner> <CloneServerUrl>http://localhost:14145</CloneServerUrl> <CloneImageName>MyImage</CloneImageName> </PropertyGroup>