Synchronize AlwaysOn Replicas Configuration and Server Level Objects (Logins, Permissions, Jobs….)

26/07/2015

16 comments

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).

Here is a direct download link to AlwaysOnSynchronizer from the sqlserverutilities site.

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.

 

Architecture

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

<WindowsCluster>
<WindowsClusterVirtualNetworkName>You_WSFC_Name</WindowsClusterVirtualNetworkName>
<WindowsClusterAutherntication>true</WindowsClusterAutherntication>
<WindowsClusterUser>sa</WindowsClusterUser>
<WindowsClusterPassword></WindowsClusterPassword>
</WindowsCluster>

<ExecutionOptions>
<PathToLocateSQLScriptFiles>D:\AlwaysOnSyncronizer</PathToLocateSQLScriptFiles>
<ExecuteScriptFiles>True</ExecuteScriptFiles>
</ExecutionOptions>

<ScriptFilesOptions>
<ScriptLogins>True</ScriptLogins>
<ScriptJobs>True</ScriptJobs>
<ScriptLinkedServers>True</ScriptLinkedServers>
<ScriptPermissions>True</ScriptPermissions>
<ScriptConfigurations>True</ScriptConfigurations>
</ScriptFilesOptions>

 

Query used to get all replicas along with their roles

SELECT
cs.replica_server_name
,rs.role_desc
FROM sys.availability_groups_cluster AS ag
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS cs ON cs.group_id = ag.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS rs ON rs.replica_id = cs.replica_id;

 

Execution

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.

SET NOCOUNT ON;

-- Turn on xp_cmdshell if needed
DECLARE @is_config_changed bit = 0;
IF EXISTS(SELECT * FROM sys.configurations where name = 'xp_cmdshell' AND value_in_use = 0)
BEGIN;
IF EXISTS (SELECT * FROM sys.configurations WHERE name = 'show advanced options' AND value_in_use = 0)
BEGIN;
EXEC sys.sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE;
END;

EXEC sys.sp_configure 'xp_cmdshell', 1; RECONFIGURE WITH OVERRIDE;
SELECT @is_config_changed =1;
END;


DECLARE @rc int;
EXEC @rc = xp_cmdshell 'D:\Team\C#\AlwaysOnSyncronizer\bin\Debug\AlwaysOnSyncronizer.exe', no_output;
SELECT @rc;

IF @rc <> 0
BEGIN;
RAISERROR (N'AlwaysOnSyncronizer terminated with a return status of: %d. For additional information check the AlwaysOnSyncronizer.log file.', 16, 1, @rc);
END;


-- Revert back if changed the config
IF @is_config_changed = 1
BEGIN;
EXEC sys.sp_configure 'xp_cmdshell', 0; RECONFIGURE WITH OVERRIDE;
END;

 

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

-Yaniv

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

16 comments

  1. stilgaw20/06/2016 ב 14:46

    Hi Yaniv,
    This tool looks clever and useful.
    It inspires me somehow to write similar one, personalized for my needs 🙂
    If it is not a problem, could you write what was the reason of introducing snapshotting and T-SQL scripts?
    I am thinking about scripting the objects for each execution of the “AlwaysOnSynchronizer” using SQL Server Management Objects.
    For example for jobs:
    1) scripting objects on primary,
    2) scripting objects on secondary
    3) comparison,
    4) introduce the differences on secondary nodes
    Was the performance the reason?
    But on the other hand I was also thinking that introducing the T-SQL scripts for each object type and placing them in the subfolder could be used for generic behaviour.
    Each new script could correspond to new type that we want to sync.

    Thank you in advance.

    Reply
    1. Yanco
      Yanco13/08/2016 ב 16:57

      Sorry, I just noticed your comment now. Please let me know if it is still relevant before I reply

      Reply
  2. Yossi21/09/2016 ב 2:32

    Hi Yaniv,
    This is a great tool, thank you very much for sharing it.
    Is there a way to control the generate script to do execute if not exists (instead of the current version which checking if exists and then drop and create the object)
    It will be nice to have Operators object sync as well ?
    Thanks

    Reply
    1. Yaniv
      Yaniv16/10/2016 ב 10:50

      Hi,
      I plan to take the time and add your 2 requests for an upcoming release.

      -Yaniv

      Reply
      1. Dan15/11/2016 ב 12:57

        version 1.5.0 has a problem:
        a job shedule that was updated at the primary replica would remain unchanged at the secondary replica(s)

        Reply
        1. Yaniv
          Yaniv26/01/2017 ב 16:39

          Thanks Dan, this is true. I have verified this.
          When you remove a schedule the column date_modified at sysjobs is modified but when you add a new schedule or modify an existing schedule this is not reflected at sysjobs and the date_modified column remains unchanged.
          AlwaysOnSyncronizer is built with a concept of maintaining snapshot tables that keep a copy of the data in the online tables and detects a diff based on a comparison done between the snapshot tables against the online tables. Given that the modification in sysschedules is not reflected in sysjobs this is the reason for this bug.
          You can see the procedures used by the program to get the diffs and to populate the snapshot tables at the working dir or at the master database

          Reply
    2. Yaniv
      Yaniv26/01/2017 ב 16:29

      Hi Yossi,
      Operators were added in the latest release 1.6 per your request. See this latest post bellow.

      Regarding the IF EXISTS…. as far as I recall it is only used for the login script. Is that what you were referring to?

      thttp://blogs.microsoft.co.il/yaniv_etrogi/2017/01/26/alwaysonsyncronizer-version-1-6-0-is-now-available-for-download/

      Reply
      1. Amit Arora30/01/2017 ב 2:10

        Hi Yaniv

        Where does the log file get generated? As I am getting the failure code 1 and I want to know why.

        Regards

        Amit

        Reply
        1. Yanco
          Yanco03/02/2017 ב 10:41

          If the defaults were not changed then at the working dir.
          The program uses the log4net logger that have it’s config file (log4net.config) that by default maintains a log file at the folder were the executable runs.

          Reply
      2. Yossi08/04/2017 ב 20:53

        Thank you Yaniv!.
        Yes, the IF EXISTS was related to Logins.

        Reply
  3. YvesA08/03/2017 ב 12:57

    Hello Yaniv,
    The Server.config file included in the ZIP is different as the one specified in your blog.
    Instead the …, there’s a … .

    Can you clarify this point please ?

    Thanks
    Yves

    Reply
    1. Yanco
      Yanco09/03/2017 ב 8:32

      Yes, you are correct. The blog shows the config file of the very first version and it has slightly changed since. However, the keys at the config file are self-descriptive. Is there any specific key you are not sure about?

      Reply
      1. YvesA10/03/2017 ב 11:36

        Hi Yaniv,
        Seems that the answer I posted yesterday was lost somewhere 🙂

        Thanks for your answer!

        It works much better with the correct parameters in the Servers.config file!

        Thanks a lot

        Reply
  4. Julian05/05/2017 ב 18:47

    Hi,

    Is the code open source? or any plans for it?

    Thanks in advance!

    Reply
    1. Yaniv
      Yaniv05/05/2017 ב 22:18

      At the moment it is not open source but I may do so in the future.

      Reply