AlwaysOnSynchronizer is a free .NET console application that is designed to be run by an SQL Server Agent job and address the issue of synchronizing “server level objects” from the primary replica to the secondary replica(s).
The utility comes in to address the need where environments that have implemented AlwaysOn Availability Groups need to synchronize “server level objects” that are beyond the scope of the Availability Databases that actually synchronize the databases only but do not synchronize objects outside the scope of the database. This leaves objects that are crucial for the ongoing operation such as logins and jobs not synchronized/ updated on the secondary replica(s) and in the event of a failover things stop working and effect the business operation.
I actually coded this tool for a customer that had faced the situation described above and due to lack of a login and a login having it’s password out of date on the secondary replica, applications stopped working following a failover, resulting in an unplanned downtime.
The utility currently synchronizes Logins, Permissions, Linked Servers, Jobs and the Server Wide Configuration (sp_configure) but the existing infrastructure allows me to easily add any additional object that may be needed so if you need to add any object not yet included let me know and I will do that.
Here is the Microsoft documentation showing all objects that should be taken into consideration, and as you can see there is still some way to go. I did not want to invest my time supporting all these objects simply because my current implementation does not require it but as I mentioned I will add support for any object that you will need that is not yet supported.
AlwaysOnSynchronizer maintains a copy of the tables being synchronized at the master database. For example the table dbo.server_principals_snapshot is a copy of the sys.server_principals DMV. Based on the snapshot tables the utility figures out the diff. This is done by comparing the current state against the snapshot table. The detected differences are scripted and saved to script files, a script file per object type. The saved script files are then read from disk and get executed at each secondary replica.
Setup and configuration
Having downloaded the AlwaysOnSynchronizer_Setup.rar file from the link at the top of this post just extract the rar file and locate it at your preferred location.
There is a single configuration file to configure called Servers.config located under the Configuration folder.
Edit the WindowsCluster xml node and type in your WSFC name (the windows cluster name). AlwaysOnSynchronizer uses the query in the bellow snippet to retrieve the replicas belonging to the cluster along with their role.
In the ExecutionOptions xml node edit the attribute PathToLocateSQLScriptFiles and type in the location where you want the generated synchronization script files to be saved. This can be a local path or a UNC path just make sure the account running the utility has read/write access to that location.
The Boolean attribute ExecuteScriptFiles allows you to control if the generated synchronization script files that have been saved will be executed on the secondary replica(s) or not.
Having done that you are all set and ready to go. All other attributes belonging to the ScriptFilesOptions xml node just allow you to control what objects you want to synchronize. Typically you will stay with the defaults but the flexibility exists.
Servers.config configuration file
Query used to get all replicas along with their roles
I use SQL Server Agent job to run the AlwaysOnSynchronizer executable wrapped up in T-SQL code that uses xp_cmdshell and capture the exit code in order to raise an error if the returned exit code is not 0 and fail the job step
The available exit codes are:
0 – Success
1 – Failure
2 – The number of servers found in the Windows Cluster is smaller than 2 which is unexpected and there is nothing to sync.
You can also check out this related post which is an add on to SSMS for manual synchronization
20170203 – Update: Check out the latest release here