SQL Change Automation (SCA) can support a development team while it develops the skills and resources required to deliver changes and services to a database application more rapidly and effectively. SCA supports all major aspects of rapid delivery, including Build and Deployment, Test and Verification and Information and Reporting.

Build and Deployment

At its simplest, you can use SCA to build a database straight from source, without any verifications or checks. However, a database development of any scale will require that teams acquire the skills and resources to use source control, regular builds, and the automation of the more tedious tasks such as unit testing, checking code for obvious mistakes and documentation.

SCA's build process creates a build artifact, which is just a snapshot of the source code and static data that is stored in a way that can't be changed subsequently. This means that the test team can be confident that what's being released to each SQL Server instance is the same and that there is no version-creep. They can inspect the source without needing access to source control. As an assistance to developers in debugging, it allows old builds to be archived quickly, for example to allow debugging with old versions, and to answer the question "when did the bug appear?".

It will support a more advanced build process that runs on every new code version and provide documentation that allows rapid code reviews, and checks on code quality, so that any major issues can be discovered rapidly. It can provide automatic updates to all the versions of the database required, including any remote sites.

Finally, SCA is designed to support continuous release of a database application, within the constraints of the business and of required sign offs, by introducing a structured, and automated, database build-and-release pipeline, where the team can:

1. Always be sure of exactly what was released, for each version and be confident that all required assets are in source control
2. Perform quality checks before creating the build, or before creating the release, so that they can find errors as early as possible.
3. Make as much as possible of the process visible to others for inspection, including any generated scripts, warnings, test results and a summary of the changes. In this way problems, such as potential security issues with a proposed change, can be spotted very early in the process, before they become expensive to fix.

Test and Verification

Although unit tests are the preserve of developers and should be developed before the code is checked in, there are a range of tests that can be performed during each build and release.

SCA's automated build process assists the team with the automation of the tasks such as providing every development and test server each new version of the database and application. SCA can automatically load any necessary static data, and can work with other toolbelt components, such as SQL Data Generator, to load the required test data set for each database. This can be particularly useful where different test instances need databases with varying datasets.

SCA can then run automatically many of the required integration tests, using the tSQLt test framework.

Information and Reporting

The build and release process for a database can be mysterious, because of the plethora of messages that are created when things go awry. SCA reports every release with a separate HTML-based report. If, for example, ten development versions of the database(s) must be updated to the current version, there will be ten reports of what happened, one for each 'release'. This gives as much information as possible about the synchronization process and the changes attempted. It will even include information on code quality issues gleaned from SQL Code Guard. This is all done to allow the process to be modified in the light of experience, even when the build process is done overnight.