Derik Hammer

Synchronizing SQL Server Instance Objects in an Availability Group

September 8, 2017 by

Availability groups can no longer be considered new. The feature was released in SQL Server 2012 but I often notice that SQL Server Failover Cluster Instances (FCIs) are much better understood. For a very long time, when people thought about SQL Server they thought about the entire instance as a unit. Microsoft has been implementing new features and services which begin to segment the concept of an instance from the concept of a database.

With an FCI, which most seem to be comfortable with, your entire SQL Server instance gains high-availability. With an Availability Group (AG), that is not the case.

An availability group supports a replicated environment for a discrete set of USER databases, known as availability databases.” (emphasis is mine) – MSDN

Since AGs only replicate user databases, that means that all data stored in master, model, and msdb will not be handled. Replication of your SQL Agent jobs, linked servers, logins, credentials, and all over instance objects, will not be handled for you. This means that you have to implement a process to do keep all of your cluster nodes synchronized.

Options

I wrote about this same topic about two years ago. In that article, I expressed a release based method which would be hooked into your database lifecycle management (DLM) process. Over the years, I found that very few people adopted that way of dealing with their server objects. Shifting-left something like security and other instance level objects did not appeal to many and the method was highly specialized to development shops that used desired state configuration tools to manage their DLM.

This article is focusing on a new method that I wrote which is much more generic and fitting for a strong separation of duties between the production DBAs and the development / DevOps teams.

The process

The solution that you see below is a PowerShell script which is executed by a SQL Agent job on the primary replica of the AG. While it only executes on the primary replica, the job must exist on all nodes of the cluster and be smart enough to know which node is allowed to execute and which are secondary replicas.

The core functionality all resides within the PowerShell script. In the diagram, I show the script in a Microsoft Azure File Share. This is definitely not a requirement. If you prefer, the script could reside on an on-premises file server, locally on each node of your cluster, or even pasted into the SQL Agent job step itself. When I developed this solution, my servers were Azure VMs and I wanted a single piece of code to manage, rather than ensuring that the script was updated on all nodes of the cluster at all times.

The PowerShell script relies on the community project dbatools and follows this process flow.

A notable limitation of this process is that it does not update existing objects. Jobs which already exist but were updated, will not be altered. I chose to omit that functionality because it presents merge complications and problems. For example, the cleanest way to handle the process would be to drop and create the object each time the synchronization runs. If that happened, however, there would be gaps when logins didn’t exist and applications would fail to connect, SQL Agent jobs would lose history, and/or the processing of a job would fail because it was dropped part way through executing.

Prerequisites and community code

For this script to work, there are two prerequisites. The prerequisites are required to be installed on all nodes of the cluster.

What is dbatools?

Dbatools is a community developed, open-source, PowerShell module. It contains over 200 cmdlets designed to help the DBA with SQL Server administration, migrations, and best practices.

I chose to use this tool over rolling my own script because this is a supported platform with a team of experts constantly fixing bugs and adding new features. The long-term maintenance of this entire solution is nearly zero because the dbatools contributors will handle all of the tedium for you. They will update the cmdlets for new versions of SQL Server and they have hundreds of automated unit tests to stress this code base with every change.

Even outside of this one-use case, I highly recommend to look into what dbatools has to offer and how it can help optimize your day.

Installation

You can download and install the prerequisites yourself. I prefer scripting out installs, however. For that I recommend reviewing this article by Mike Robbins on scripting out the installation of PowerShell version 5.0 and you can use the below script to install dbatools by running a PowerShell console window as administrator.

The code

Full PowerShell script and SQL Agent job T-SQL script available for download here as a zip file.

SQL Agent job

You will need to create a SQL Agent job to run the PowerShell script. In the downloadable content, there is the full T-SQL required to create the job, but edits are required. As mentioned above, I chose to use an Azure File Share to store my PowerShell script. If this is the direction that you will go, then you will need to update the script with:

  • The storage account name.
  • The share name.
  • The storage account’s primary or secondary key.

If you choose to use an on-premises file server or local directories, you will need to adjust the script accordingly. No matter which direction you take, you will need to create a proxy account with access to each node of your cluster and update the scripts:

  • Domain name.
  • Port number.
  • Proxy name.

sp_add_jobstep parameters which need review / editing:

Downloads

References

Appendix A – The PowerShell script


Derik Hammer

Derik Hammer

Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability, disaster recovery, continuous integration, and automated maintenance. His experience has spanned database administration, consulting, and entrepreneurial ventures.

Derik thanks our #sqlfamily for plugging the gaps in his knowledge over the years and actively continues the cycle of learning by sharing his knowledge with all and volunteering as a PASS User Group leader.

View all posts by Derik Hammer
Derik Hammer
SQL Server maintenance

About Derik Hammer

Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability, disaster recovery, continuous integration, and automated maintenance. His experience has spanned database administration, consulting, and entrepreneurial ventures. Derik thanks our #sqlfamily for plugging the gaps in his knowledge over the years and actively continues the cycle of learning by sharing his knowledge with all and volunteering as a PASS User Group leader. View all posts by Derik Hammer

321 Views
  • SQLUndercover

    We’ve been playing around with various ways of doing this recently. I love how tidy your approach looks, I’ll be trying it out soon, thanks for sharing 🙂