Synchronize Availability Group Logins and Jobs

An important part of ensuring application functionality with Availability Groups is manually maintaining any uncontained objects across each of the replicas in the Availability Group. As more of our clients upgrade to SQL Server 2012 and implement Availability Groups, we’ve had to face the challenges of maintaining uncontained objects across replicas multiple times and there hasn’t been a good solution to the problem. No one solution is a perfect fit for every client, and one of the challenges is the difference in change control policies that might exist.  Some environments might be agreeable to an SSIS package that runs nightly to copy all logins and jobs, where others require a script be generated to accomplish the same tasks. 

To solve this problem, we developed the SQL Server 2012 Availability Group Add-in for SQL Server 2012 Management Studio specifically targeted at enhancing the UIs for Availability Groups.  The add-in creates an additional menu in Object Explorer for the Availability Group node for easy access.

SQL Server 2012 AG Add in Menu SQL Server 2012 Availability Group Add in

By clicking on the menu item, the Availability Group Synchronization UI form will open allowing you to easily step through the configuration of the different objects to synchronize and how.  Currently the add-in supports synchronizing:

  • User-defined Server Roles
  • Server Logins and Permissions
  • SQL Server Agent Jobs

The current output is a SQLCMD mode script in a new query window to allow an administrator to review all of the changes prior to changing SQL Server Management Studio to SQLCMD mode to execute the script.  This requires an additional step, but also fulfills the requirements of many change control processes.  We may evaluate based on feedback enabling the ability to automatically synchronize instead of generating a script in a future update to the add-in.

image

This add-in is under continuous development to add additional objects to synchronize and additional reporting for configuration reviews across replicas. The latest build of the add-in can be downloaded from the SQL Server 2012 Availability Group Add-in page on our site.

40 thoughts on “Synchronize Availability Group Logins and Jobs

  1. Jonathan,

    We are implementing AlwaysOn with Availability Groups and welcome this utility.
    I have found a couple of issues with using the current version. In setting up a server I have created the ‘A’ node and as we use SQL logins (I know not a good idea) the logins are members in msdb and master roles. I had created the same situation on both nodes and when I use your utility it told me that the sid’s differed so I re-created the ones on the ‘B’ node from the ones on the ‘A’ node using your script. Unfortunatly it just dropped the login and did not repair the connections to the role membership in msdb and master. I see the user in masb and master but it does not match the SQL login sid.
    My second issue is with scripting out SQL Agent jobs. It pulls out the schedule uid which should not exist on the other node. When I used the utility I just dropped the schedule uid to be applied to the other ndoe.

    Thanks for this utility as it helps lots.

    Chris

  2. Jonathan,

    We are currently reviewing/writing failover documentation as we are about to upgrade to SQL 2012 and use Always on availability groups.

    I stumbled upon this post and thought I would give it a try.

    The particular install that i have, the first page says that SSIS packages would be synched but when i actually tried neither did it give me an option to select SSIS packages that i want synched nor did it automatically push all SSIS packages from my Primary to my Selected Replicas.Not sure what I am doing wrong

    ALso if you could add scripting of linked servers to this utility it would be great.

    Thanks & Regards,
    Mani

    1. The synchronization of SSIS packages is not a part of the initial release of the Addin. It will be added at some point in the future if I can determine a safe way of doing it through scripts (which I don’t think is possible due to package security).

  3. We also found a situation with a login and check_policy. Your script wanted us to change the password but it failed because of check_policy. We just added an ALTER LOGIN XXX with CHECK_POLICY = OFF before your scripts change and added an ALTER LOGIN XXX with CHECK_POLICY = ON afterwards.

    Chris

  4. This is strange. I had to add securityadmin permission to a couple of logins. I did this on the primary node. Now if I run your script from the primary to the secondary it creates the addsrvrolemember and then creates a dropsrvrolemember. IE

    — Add login to the securityadmin role
    EXECUTE sp_addsrvrolemember @loginame = N’XXX_YYYY_ZZZ’, @rolename = N’securityadmin’;
    GO

    — Drop login from the securityadmin role
    EXECUTE sp_dropsrvrolemember @loginame = N’XXX_YYYY_ZZZ’, @rolename = N’securityadmin’;
    GO

    I have never seen this before so perhaps its just the securityadmin that has this glitch.

    Chris

        1. Hi, I have the same problem.
          The setup creates a directory with two files one DLL and one dll.config and thats it.
          C:\Program Files\SQLskills.com\Availability Group Addin\

          DO I have to move these two files to another directory? If so which one?

  5. i am trying to replicate logins to secondary replica in the availability group. however i get below error.

    Msg 102, Level 15, State 1, Line 16
    Incorrect syntax near ‘:’.

    when i remove ‘:’ before connect statement i get below error

    Msg 2812, Level 16, State 62, Line 1
    Could not find stored procedure ‘Connect’.

  6. It would be great to have this tool for Sql Server Management Studio 2014! is there a way to use it now? any plan to implement it in the near future?

  7. Great application, but we ran into a problem when our SQL install was on a instance and non-default port. The application would not generate the script set to use. Is there a way around this?

    1. Hey Chris,

      There haven’t been any changes to this since the first release. I have a list of requested features and functionality that I will eventually have the time to implement, but so far nothing has changed yet.

  8. Any idea when you may release a new version Jonathan? I have been waiting since last August. Such a good idea that just needs a few extra touches.

    Thanks

    Chris

  9. I was wondering if anybody had tested this in a Microsoft Dynamics GP environment. Currently we have sql server 2012 clustered with high availability for the gp databases. When the primary server fails the users can’t log into GP because the password on the fail over server isn’t the same as the password on the primary server. It sounds like this would fix that issue but I don’t have a testing environment to try this in.

  10. Hey Jonathan,

    This is a great tool.would you advise please how it is possible manually to add respective into management studio as after installing no changing occurred and nothing add in management studio.
    thanks in advance for your support.

  11. Hi Jonathan,

    That’s really handy for the logins etc. We’ve been using master/target (MSX/TSX) for SQL Agent for a while now and we find that very useful in synchronising our jobs up. I’m not sure it’s that widely used, but it’s a great tool for this purpose.

    This tool is then very good for dealing with the logins and server roles issue.

    Thanks,
    Dan

  12. Hey there,

    Great idea! Any plans for 2014 support? Also, any thoughts on adding support to schedule the functionality?

    Thanks

  13. Hi Jonathan,
    It was nice seeing you in our offices and holding the Immersion training!
    We are also planning to upgrade to SQL 2014 and was wondering if this SQL 2012 AG add-in will work with SQL 2014 SSMS?
    Thanks.
    Drew

  14. Is it possible to download the actual program scripts, T-SQL, powershell etc. to sync logins and SQL Agent Jobs? My client does not allow download and/or installing executables.

  15. Hi Jonathan,

    Is there any other way in which we can sync these unconatined objects? Some thing like a trigger that can replicate these objects when ever there is a change made to these jobs.Something apart from using a ssis package…

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.