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 are some graphic representations of the recommended workflows for SQL database CI CD best practices
The general concept of the overall SQL database CI CD system is represented with this design:
This particular article on CI will provide a description for continuous integration recommended workflow to automate integration of database changes
Continuous integration
In SQL database continuous integration, we commit database changes from development, process them with continuous integration by building the new database version, test it, audit sensitive tables if needed, document changes and prepare package for deployment.
Deployment of package goes through continuous delivery workflow where provisioning of databases is done then synchronization of changes with the provisioned databases or with the production through final deployment action.
At any moment the comparison between production database and development on Source control is possible in order to ensure that developers are working with the latest version of the database.
In detail the continuous integration workflow will look like this:
At the beginning we will use committed code to Source control from database development as source for continuous integration with the following steps:
- Build step
This is the first step in the workflow where temporary test database is built. We use ApexSQL Build for the execution of the step.
More details about the mechanics of the Build step can be found in the article: SQL Server database continuous integration workflow BUILD step – Building a SQL database from a source control repository
- Populate step
This is the next step in line and the first step of the testing phase and it is used to fill in the empty tables with synthetic data. ApexSQL Generate will perform this task
More details about how the Populate step works can be found in the article: SQL Server database continuous integration workflow POPULATE step – Populating the newly built SQL database with data
- Audit step
Along with testing steps this step is available to do some database pre-deployment work “on-the-fly”. It can be used to implement triggers on new tables that are needed in production. ApexSQL Trigger is used for this step.
More details about the trigger based auditing can be found in the article: Auditing triggers in SQL Server databases
- Test step
This step is next is line for the testing phase. It is used to perform unit tests against the objects in test database. To design unit tests and install test framework ApexSQL Unit Test is used and it comes with SQLCop compilation of unit tests.
More details about how the Test step works can be found in the article: SQL Server database continuous integration workflow TEST step – Running SQL unit tests against the changes
- Review step
In this step we run a rulebase designed and executed by ApexSQL Enforce against the test database and check if database satisfies criteria defined in a rulebase file for best practices.
More information about rulebase and database review can be found in the article: How to execute an unattended best practices review against a database
After the testing phase which provides feedback for developers there are few finishing steps available.
- Document step
The step is executed using ApexSQL Doc to document entire database or delta between different versions.
More details about documenting a database can be found in the article: SQL Server database continuous integration workflow DOCUMENT step – Creating database documentation
- Package step
It prepares a package for deployment by creating database script folder with ApexSQL Script and creates a NuGet package which is used as source for deployment. In the package should be stored outputs from all previously executed steps like reports and summaries for later review.
More details about expected package inventory can be found in the article: Inventory of the CI/CD pipeline output files
- Publish step
Optional step that can be used to push the newly created NuGet package to a designated NuGet feed.
- Notify step
It is the final step in the continuous integration workflow to send the information about success/failure status of the workflow to a QA person who can open a NuGet package and review all reports, summaries and documentation and troubleshoot the process if needed.
ApexSQL DevOps toolkit
In order to design a continuous integration workflow like the one described and automate it in a single or multiple pipelines ApexSQL DevOps toolkit is required. This is a free automation layer that works with your existing ApexSQL tools.
The ApexSQL DevOps toolkit is available as a standalone solution with PowerShell cmdlets or Web dashboard application or as plugin for the popular Atlassian Bamboo and JetBrains TeamCity continuous integration servers.
November 21, 2018
