SQL Clone is a database provisioning solution that lets you create full copies of SQL Server databases and backups in seconds.

A baseline is a snapshot of your database's schema to provide a starting point for migration development. For more details on how baselines are used in SQL Change Automation projects please check the Baseline concepts page.

A SQL Clone image can be used as a SQL Change Automation baseline since it's a snapshot of your entire database schema and data.

Why and when should I use it?

There are several use cases where using a SQL Clone image as a baseline comes in handy and offers considerable advantages.

Complex databases

Complex databases present significant problems for baseline scripts for some scenarios including objects which do not defer name resolution when created in T-SQL.

• Invalid objects: Objects that reference other objects which are not present in the development environment create problems when a baseline script is executed.
  • For example, a view which references a table which has been dropped is an invalid object. Similarly, a view which references a table in another database which is not present in the environment will cause a failure when a baseline script is executed.
• Circular dependencies across databases: Databases which have mutual dependencies present a problem to a script approach for baselining, as there is no database which may be built from scratch first.
  • For example, DatabaseA contains a view which references a table in DatabaseB, and DatabaseB contains a view which references a table in DatabaseA.

Baseline clones instantiate objects without running “create” statements, and thereby prevent either of these scenarios from blocking progress.

Build performance

When baselining a large database continuous integration builds can take a long time. During each build the baseline scripts must create all the objects in the database, which can be time-consuming.

On the other hand, provisioning a SQL Clone takes seconds regardless of the database size leading to faster builds, as there is significantly less work to be done each time.

The build step can then focus on the development changes rather than on recreating the production environment.

Faster feedback loops

SQL Clone images can be periodically refreshed to get the latest schema and data from your production environments.

When using a SQL Clone image as a baseline you are running migration scripts against this refreshed snapshot, effectively left-shifting production deployments for databases : the clone of the production schema is used as the basis to test incremental migrations (in whatever state production is in).

This ensures that your SQL Change Automation project will deploy to the latest production environment, resulting in a quick rollout of database devops across the enterprise and faster feedback loops.

Data motion

As databases provisioned by SQL Clone contain data, you can test migrations that will update it.

For example, you can do schema refactorings (e.g. column split of the column Name into two distinct columns First Name and Last Name) or data hotfixes (e.g. adjustment of a monetary value).

How does it work?

SQL Clone allows you to create an image of your database (including data) to use as a baseline without the need for T-SQL creation scripts. This means any database that exists in SQL Server can be baselined.

When the baseline is a clone, the virtual snapshot is created at the beginning of each verification. This is like a quick database restore. Scripts for individual object creation need not be run. Incremental migration scripts are then run against the clone as soon as it is present. 

You need a SQL Clone server that you can access, and an image of the database that you would like to use as the baseline. The machine with the SQL Server instance that will be used for creating the database needs to have a SQL Clone Agent installed. You can learn more about SQL Clone set up by checking the requirements.

The following diagram shows a simplified workflow of what happens when you use SQL Clone as a baseline:

How do I set this up?

You can configure a SQL Clone image as a baseline in SSMS or configure a SQL Clone image as a baseline in Visual Studio.

If you are using the Powershell cmdlets Invoke-DatabaseBuild or Invoke-DatabaseTests, please be aware that LocalDB is not supported when using a SQL Clone image as a baseline. Therefore, you must specify a temporary database server (or temporary database) parameter.

For more information on provisioner configuration in SQL Change Automation see here.