Requirements and recommendations for database continuous integration and continuous delivery workflows

The base of any database continuous integration workflow starts with a proper source code versioning. 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 create a database continuous integration pipeline in order 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 a database 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 database continuous integration solutions:

Web dashboard – a standalone, web-based database-continuous integration solution. To build, test and deploy solution that does not require 3rd 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 in a database continuous integration, the Custom step is the way to do it.

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

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

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

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

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

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?

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)

I am trying to update a database, but the synchronization drops objects that are currently not located in source control repository, is it possible to change that?

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

 

Related posts:

  1. Configuring Azure DevOps build-release pipelines with ApexSQL DevOps toolkit plugin
  2. Walkthrough example of configuring a SQL Server database continuous integration pipeline with the ApexSQL DevOps toolkit
  3. How to setup continuous integration and continuous delivery tasks using the ApexSQL DevOps toolkit – Azure DevOps Server (TFS) / Azure DevOps Services plugin
  4. How to setup continuous integration and continuous delivery tasks using the ApexSQL DevOps toolkit Bamboo plugin
  5. Quick overview of ApexSQL DevOps toolkit – VSTS/Azure DevOps plugin