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.

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

<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 your Provisioning folder
• Add a DropDatabase.sql script to your Provisioning 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)

<PropertyGroup>
    <Provisioner>Clone</Provisioner>
    <CloneServerUrl>http://localhost:14145</CloneServerUrl>
    <CloneImageName>MyImage</CloneImageName>
</PropertyGroup>