ApexSQL database DevOps continuous delivery workflow – visual representation

This article will represent the recommended working model for database DevOps to create SQL Server database continuous integration and continuous delivery pipelines with a set of SQL developer tools.

Here is a graphic representations of the recommended workflows for SQL database CI CD best practices

Continuous delivery

This particular article on CD will provide a description for continuous delivery recommended workflow to automate database provisioning and deployment of changes as shown on diagram below:

The following steps are available for this workflow:

1. Provision step

   What this step does is to create necessary database clones of production database to testing environment. PSDatabaseClone utility is used for this process.

2. Mask step

   When provisioned databases are present in the testing environment this step can provide masking/obfuscation of sensitive data to prevent exposure to testers. ApexSQL Mask can perform this task.

   With the NuGet package prepared in continuous integration process we will use it as the source to synchronize changes to production and/or to test environment. Following steps are available for this task:

3. Sync step

   This step creates schema synchronization script after comparing the source in the package which contains database changes with the target database awaiting the update. ApexSQL Diff will execute the comparison and generate the synchronization script.

4. Sync (data) step

   In case there is some static data from development in the package this step will create data synchronization script after comparing the tables in the source from the package which contains new static data with the tables in the target database where data should be stored. ApexSQL Data Diff will execute the data comparison and generate the synchronization script.

   More details about the mechanics of the Sync and Sync (data) steps can be found in the article: SQL Server database continuous integration workflow SYNC step – Creating the synchronization/migration script

   Schema and data sync steps can be used to update the test environment and then additional testing can be performed, for example a client application can be run with the new database version to validate compatibility with changes.

   With the QA feedback from testing area to the release manager deployment approval is expected to proceed with the following steps:

5. Deploy step

   This step will execute created schema and data synchronization scripts against the target database. Before execution Deploy step provides the Backup feature for production database and the Validation feature to check if some changes were introduced in the meantime on the target database.

6. Notify step

   If validation checks out the deployment can proceed and with the Notify step at the end release manager can get information about success status of the deployment.

ApexSQL DevOps toolkit

This continuous delivery workflow can be designed using ApexSQL DevOps toolkit solutions automate it in a single or multiple pipelines. The ApexSQL DevOps toolkit is available as a standalone solution with PowerShell cmdlets or Web dashboard application or as selection of plugins for the most popular continuous integration servers.