SQL Change Automation allows you to import existing databases into SQL Change Automation projects. When you do this a starting point is created representing the current schema of that database. This starting point is termed the ‘Baseline’.

What is a baseline?

A baseline captures schema changes that have already been deployed to your production database. Future migration scripts will capture “deltas” or incremental changes from this baseline.

When you deploy a SQL Change Automation project to the database from which it was baselined you can have confidence that the deployment will succeed because the migration scripts are built on top of the baseline.

Creating a baseline

We recommend creating an initial baseline when you are beginning a project for an existing database. A baseline script will be generated as a one-off operation as part of the initial setup wizard for a new project. This baseline script will be the first script in the SQL Change Automation project.

Baseline script

The baseline is represented as a baseline script. As each item in the schema must be introduced sequentially it's possible for the script to encounter a 'dependency chain' scenario where dependencies between objects exist in such a way that the script cannot be deployed.

When a baseline script is generated objects involved in the dependency chain will be added to an ‘Unsupported’ folder in your project. See 'Resolving Unsupported Programmable Objects' for more details.

It's possible to manually amend the baseline script. For instance, you may encounter schema level objects that depend on server level objects that don't exist on your development server. Issues like this can be identified by verifying the project.

Deploying a project with a baseline

A project with a baseline can be deployed using the PowerShell automation components. By default, the baseline script will be run if the deployment target is an empty database. It will also run if you configure SQL Change Automation to create an empty database upon deployment. See ‘Deploying SQL Change Automation Projects’ for more information.

Environment drift

If your production environment encounters any drift since it was baselined your deployment will become risky, and the project inconsistent. Refer to Drift to learn more about identifying and resolving such scenarios.

Deploying to other environments

One common customer question is, "What if I generate my baseline from production, and then deploy to another environment, such as QA, that is missing part of that initial baseline?"

In this example, the QA database is not empty, but it does not have the correct baseline applied. The baseline script will be marked as deployed and it will not run. If you have added migration scripts to your project, those will be applied, followed by any programmable objects.

If an error is encountered due to the baseline not being fully present, execution will stop. Providing you are using the default option to deploy your changes within a transaction, the entire deployment would then roll back. There is a potential that the initial deployment might succeed, if none of these changes reference the portions of schema which differ from the baseline, however an error may occur in a later deployment.

It is not recommended to deploy to a database that is inconsistent with the project baseline. You may consider the following alternatives:

• Replace the database with a new, empty database, which you deploy to from SQL Change Automation
• Provision a new database based on a backup which has the correct baseline schema
• Use SQL Compare before the first deployment to ensure that the populated database schema matches the baseline schema, and bring it into alignment if needed