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 as well.
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.
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 even 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.
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 this 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 source 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.
The post Database DevOps Considerations for SQL Server Availability Groups appeared first on Simple Talk.
from Simple Talk https://ift.tt/36I2KdC
via
No comments:
Post a Comment