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:
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:
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.
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
"
