SQL Change Automation is designed to build or alter a database so that it is the same as the version in source control. The DDL source code to create the specific version of the database is the only source of truth for the database. SCA then works to automatically "make your database look like what's in source control".

The first step is to establish the source for the database version you wish to deploy. This source can consist of a set of migration scripts describing the sequence of changes required to move the existing database from one version to another, such as provided by the SCA Development tools. Alternatively, it can take the form of a directory containing a set of scripts describing the current state of each database object, such as provided by SQL Source Control, or even a single script describing the current state all objects, such as provided by SQL Server Management Studio.

Next, we supply the source as an input to SCA Deployment, a set of PowerShell cmdlets, which will deploy it so that the target database matches exactly the version described in the source. This happens as part of a structured, automated delivery pipeline where outputs are packaged, versioned, immutable, and can be exported for inspection, at each step.

In the build phase of deployment, SCA Deployment will take the supplied input, containing the source, verify that it can be used to build a database, and create a deployment package called a build artifact, which can also contain database documentation, static data, and pre- and post-deployment scripts.

The package containing the build artifact can be used to modify databases, without needing access to the DDL source. It can also be sent securely to team members, published and inspected. This lessens the potential confusion about exactly what is being assessed, checked, tested and documented.

If SCA Deployment can deploy over a network connection to the target database, it can use this build artifact directly as a source to update a target database and bring it to the same version. However, there are benefits from using the build artifact to create a release artifact for each specific target.

When SCA creates the release artifact, it imports into it the schema of your intended target database so it can check for any subsequent modifications in the target, 'database drift', which would invalidate the synchronization script. There will always be one build artifact per database version, but you will need a separate release artifact for each target, unless some or all are identical. SCA allows the release artifact to be exported, as an immutable package, for inspection and verification, prior to use. It will also contain an HTML report of the release, detailing all the changes, and a report of any code issues.

SCA then uses the release artifact to update the target database and bring it to the same version as the source.

Migration-based deployments

SCA Development tools adopt the migrations approach. During development, either in Visual Studio or Management Studio, they create in source control a SQL Change Automation project file, which represents a database as a set of SQL scripts that describe how to migrate a database from one state to another.

The project stores the object scripts only for the initial database version, and then a team of developers make and test changes on their local instances and commit them to source. On each commit, SCA Development verifies that the new database version will build, and then saves the committed change to the project, as a migration script. The result is a set of migration scripts that describe using ALTER statements the sequence of changes required to move the existing database from one version to another.

The team can then use this project file as input to the deployment process, using SCA Deployment. During deployment, SCA will either run just the correct sequence of migration (ALTER) scripts, if the initial database version already exists on the target, or do a full build otherwise, by creating the objects for the initial database version, and then running the migration scripts.

Static, or state-based, deployments

SCA is not a traditional build server, which relies on executing a set of scripts and files in the right order. It can accommodate several different approaches to database development. When preparing a new build, or to update a database to a new version, SCA can also take its input from a source control directory containing one or more build scripts describing the current state of the objects, or a database.

SCA reads and analyses the source to build up a logical 'model' of the database, which can be more easily compared with another database. It produces a single synchronization script that will automatically modify the target so that it matches the source, and allows for safe usage, with rollback, if anything goes wrong. If the target is an empty database, then the synchronization looks like the traditional single-file build script, but it isn't. If your source contains extra executable code that isn't part of an object definition, such as a script for modifying data during the synchronization, it won't be included.