Requirements and recommendations for database continuous integration and continuous delivery workflows (4212751)

At the beginning of establishing a database DevOps implementation it is recommended to use ApexSQL Source Control to link a database to a source control repository, track changes from database development and commit them to the repository.

After checking the SQL code to source control for the deployment of changes to stage/production environment the ApexSQL DevOps toolkit will provide necessary means. With this solution it is possible to build a test database from source control repository and perform some test on it - population tests, unit tests, custom rulebase checkup, install some table field level auditing triggers and document entire database or changes only. With this process needed reports are generated for QA management review. The testing phase can be finished with a deployment package that can be used to deploy changes to staging/production environment.

ApexSQL DevOps toolkit is a solution that is used to design automated continuous integration and delivery process. In order for the process to work a set of ApexSQL developer tools that needs to be installed on premise. In summary, it is possible to create a dedicated build machine with mentioned tools.

ApexSQL developer tools

Regarding the set of ApexSQL developer tools, the usability of ApexSQL DevOps toolkit expands with every tool present on the premise:

• ApexSQL Build – for the Build step to build temporary test database from a source control repository, test the buildability of changes and validate dependencies
• ApexSQL Generate – for the Populate step to populate empty tables with synthetic static data for testing tables
• ApexSQL Unit Test – for the Test step to perform unit tests on a test database
• ApexSQL Doc – for the Document step to create database documentation or document database changes only
• ApexSQL Trigger – for the Audit step to install triggers upon newly introduced tables for auditing table changes
• ApexSQL Enforce – for the Review step to automatically review the database to help enforce best practices by applying custom rulebase checkup
• ApexSQL Script – for the Package step to create a database package ready for deployment
• For deployment process it is necessary to have ApexSQL Diff – for the Sync step to create database schema sync script containing changeset that will be deployed to production
• ApexSQL Data Diff – for the Sync data step to create static data sync script containing static data changes that will be deployed to production
• ApexSQL Refactor – an optional functionality of the Package step will help to apply some SQL code formatting or obfuscation on-the-fly
• ApexSQL Mask – for the Mask step to mask sensitive data in staging/development/QA environments

Solutions

ApexSQL DevOps toolkit comes in form of several solutions:

Web dashboard – a standalone, web-based database build, test and deploy solution that does not require 3^rd party host to work. Major advantage is that it does not depend on sourcing setup that various CI and Build servers require for creating pipelines and limitations that comes with them. Compared to other CI and CD solutions, the downside is that it does not have capability for non-database related projects to be deployed. This limitation can be somewhat avoided with the available Custom step, which allows execution of PowerShell scripts during a database project pipeline. If non-database related deployment projects can be constructed and executed through a PowerShell script, the Custom step is the way to do it.

• For more information about pipeline configuration see: Walkthrough example of configuring a SQL Server database continuous integration pipeline with the ApexSQL DevOps toolkit

Jenkins plugin – Provides workflow designs through Jenkins server native user interface

• For more information about pipeline configuration see: How to implement SQL continuous integration (CI) using the ApexSQL DevOps toolkit – Jenkins plugin

TeamCity plugin – Provides workflow designs through TeamCity server native user interface

• For more information about pipeline configuration see:

• An introduction to SQL Server database continuous integration with the ApexSQL DevOps toolkit – TeamCity plugin
• An introduction to SQL Server database continuous delivery with the ApexSQL DevOps toolkit – TeamCity plugin

Bamboo plugin - Provides workflow designs through Bamboo server native user interface

• For more information about pipeline configuration see: How to setup continuous integration and continuous delivery tasks using the ApexSQL DevOps toolkit Bamboo plugin

Azure DevOps Services/ Azure DevOps Server (TFS) plugin - Provides workflow designs for build and release pipelines on Azure DevOps cloud and on premises solution

• For more information about pipeline configuration see: How to setup continuous integration and continuous delivery tasks using the ApexSQL DevOps toolkit – Azure DevOps Server (TFS) / Azure DevOps Services plugin

Octopus deploy templates – Provide step templates for Octopus Deploy server for deployment against Octopus tentacles

• For more information about pipeline configuration see: How to setup Octopus Deploy server for SQL Server continuous delivery with the ApexSQL DevOps toolkit

FAQ

I have several development branches that correlate different environments. Can I control which changeset goes to which environment?

Yes, it is necessary to create a pipeline per environment and using BuildPackageSyncDeploy workflow will allow to choose which changeset goes as input and which environment will be targeted at output.

I want to accumulate changes from different sources to staging environment and then update production from staging database, can I do that?

Yes, the workflow can be simply represented like this:

Pipeline 1: Source1 → Build → Package → Sync → Deploy → Staging

Pipeline 2: Source2 → Build → Package → Sync → Deploy → Staging

…

Pipeline N: SourceN → Build → Package → Sync → Deploy → Staging

Pipeline final: Staging → Tests → Package → Sync → Deploy → Production                                      (optional)

I don’t need to build or test database I just need automated database updates?

• See the following article on how to setup such workflow: Automating database synchronization using a continuous integration solution

I have several tables containing some static data in source control repository, but I need to push only specific ones to production. How can I do that?

The Sync data step is used to update static data in target environment and by default it will update all static data present in source. Using the object filtering options available in ApexSQL Data Diff, saving them as project file and importing into the step can be possible to determine which table records are going to be considered for update. As a shortcut the switch /include used as step’s additional parameter has the function to include only specific objects in processing. Objects can be specified by first specifying a bitwise set and a regular expression. For example: /include:8:”Address$” (includes all tables that end with Address)

• More details on filtering options can be found in following articles:
  Using the Row filter option in ApexSQL Data Diff How to filter compared objects using the Inc/Exc switch with the ApexSQL Data Diff CLI

Yes, the Sync step creates synchronization script with statements to drop target objects by default if they are not present in the source. Using the object filtering options available in ApexSQL Diff, saving them as project file and importing into the step can be possible to determine which objects are going to be considered for update. As a shortcut the switch /objects_to_synchronize with its parameters - Missing [m] – sync (create) objects only found in source, Additional [a] - sync (drop) objects only found in source, Different [d] – sync (update) objects found in source and destination – can be used as step’s additional parameter to set the behavior. For example /objects_to_synchronize:m d will not drop objects in target database

• More details on filtering options can be found here: How to narrow schema comparison and synchronization to affected objects only How to change SQL object selections for automated processes/jobs