One question which comes up periodically from our Microsoft Data Platform customers is whether there is anything special that they need to do when implementing version control, continuous integration, and automated deployments for a production database which is a member of a SQL Server Availability Group. 

The good news is that deployments are quite straightforward. You’ve most likely already configured a listener for your applications to connect to the Availability Group and automatically find the writeable database. To do a deployment, you simply need to connect to the listener and update the writeable databases.

There are a few other considerations which are helpful to think about when building your approach to database DevOps, however.

A migrations approach gives you needed flexibility for Availability Groups

Managing Availability Groups can be quite tricky. This is especially true if you have large databases, or if you sometimes experience latency to some of your secondary databases.

For example, operations like creating an index may generate a large amount of log data which needs to be transferred to your secondaries. If you need to do this in a deployment, you may wish to customize the deployment by adding a command to wait for a period of time after doing the operation before you move on to the next thing you’d like to change in that database.

A ‘migrations’ approach to database deployment — for example what we have in Redgate Deploy’s SQL Change Automation, which automatically generates code for you at the time of development — gives you the ability to easily customize your deployment scripts in any way you like:

• You might choose to write a wait for statement which uses SQL Server’s dynamic management views to check log queue sizes for your secondaries and act accordingly
• You might also choose to customize your index create scripts with a MAXDOP hint to control the creation speed to ease the pain on your transaction log (thanks to Michael J Swart for this real-world tip)

As a contrast, with a ‘state’ based approach to database deployment — for example what we have in SQL Source Control — schema changes are automatically generated by an automated comparison process at the time of deployment. While this is convenient, it doesn’t have the flexibility which is helpful for more complex environments such as Availability Groups. With a state approach, you will likely want to isolate longer running commands into their own deployments. 

SQL Agent jobs need special handling

If you have SQL Agent jobs related to your database, you can add these into version control if you wish. 

SQL Agent jobs are a bit special, as the code for these jobs doesn’t reside in user databases. Instead, the code for all the jobs resides in the msdb system database. 

In an Availability Group scenario, typically there is a desire to have SQL Server Agent Jobs configured on all members of the Availability Group, and to use conditional logic within the job to determine if the job should run or not.

If this is the case for you, it probably means that you want to deploy code for your jobs to all nodes in the Availability Group cluster — not only to a single “writeable” primary database.

I think the easiest way to manage this is to create and version control a user database for administrative purposes, perhaps named Admin or DBA. This database and repository can contain code for both instance level objects (SQL Agent Jobs, Linked Servers, etc.) as well as other helpful objects that you may use for administration on the instance, such as the free community sp_WhoIsActive procedure.

If you are using the ‘state’ based approach (SQL Source Control or a similar solution), you will need to store the code for instance-level objects in a post-deployment script, as the code needs to run in another database. This is an optional choice with the migrations-based approach of SQL Change Automation, but it is convenient there as well.

Consider having a staging environment with Availability Groups in your pipeline

In my experience, Availability Groups are complex enough that it makes sense to have a staging environment for them. This means having full-sized production like datasets, configured in the same AG topology that you have in production. 

This is valuable as it can help you identify changes which make take a long time to execute and/or generate significant amounts of log. 

This also means that you don’t necessarily have to configure all your development environments as Availability Groups. Because most features of AGs tend to be transparent, I’ve found that having AGs in all environments hampers development more than helps it.