SQL Server database continuous integration workflow SYNC step – Creating the synchronization/migration script

In this article, the last step of SQL Server database continuous integration (CI) workflow, or the first step of a continuous delivery (CD) workflow) the Sync step.

In the previous article, the Test step described how to create and run unit tests against a database. If all tests are passed, the tested database can be compared to a final QA environment or even Production databases, and a synchronization script will be created to publish the changes.

In this step, we’ll create a synchronization, or migration script, from our QA database to Production (or staging)

There must be a hard break between the Test and the Sync step, to allow for a full manual test of the QA database along with a manual review of the results. As such, you should never fully automate the Sync step as part of a CI workflow in order to avoid updating production with changes that haven’t been thoroughly reviewed.

For the purposes of this article, we are assuming that the user will want to synchronize the schema, but also any static data changes as well. SQL Server database static data in this context are code tables e.g. zip, country codes etc, that generally don’t change, unlike transactional data, which will be created via a test data generation tool.

We showed in the initial Commit step how to load static data into the repository, and in the Build step how to extract it and load it into our QA database.

To implement this step, we are going to perform two comparisons:

1. The entire database schema of our newly created QA database with Production
2. The data in any tables we have selected as static

If there are no changes, the individual job (either Schema changes, data changes or both) will abort and no synchronization script will be created. In theory, if there were no changes, the CI pipeline wouldn’t have been created in the first place, so this won’t be a problem.

If there are changes for schema or static data, we will create synchronization scripts to push our schema changes and any static data changes to production, respectively. We’ll review how to automate this process.

For the purpose of the database schema, we can (and should) compare all objects that exist in the QA database vs Production. But for data, we want to select only our static tables and compare and synchronize changes from those. Otherwise, we’ll be deleting production data and replacing it with synthetic test data from QA or worse, random data input from QA activity.

To see how to implement this process, we’ll refer you to the following articles which will demonstrate setting up the comparison projects and automating them:

Schema

• Automatically comparing and synchronizing SQL Server database schema changes
• Compare SQL Server database schemas automatically

Data

• Automatically comparing and synchronizing data changes in a SQL Server database
• Automatically compare and synchronize SQL Server data

Once you have implemented the final step, you will have fully completed a continuous integration pipeline from Commit to final Synchronization.

You may decide to implement this differently, and rather than synchronize from QA to PROD, instead, go back to the source control repository directly as the source. To implement this change is relatively easy to do with the associated automation scripts.

All articles in this series:

1. SQL Server database continuous integration workflow COMMIT step – Committing SQL database changes to a source control repository
2. SQL Server database continuous integration workflow BUILD step – Building a new SQL QA database from source control
3. SQL Server database continuous integration workflow POPULATE step – Populating the newly built SQL database with data
4. SQL Server database continuous integration workflow TEST step – Running SQL unit tests against the changes
5. SQL Server database continuous delivery workflow SYNC step – Creating the synchronization/migration script
6. SQL Server database continuous integration workflow DOCUMENT step – Creating database documentation

A complete SQL Server DevOps solution

Please see the ApexSQL DevOps toolkit for a complete, end to end SQL Sever CI/CD solution including full, open source PowerShell scripts. Learn more