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.
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.
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.
42 thoughts on “Synchronize Availability Group Logins and Jobs”
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
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
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).
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
Hey Chris,
Thanks for the feedback. We’ll look at implementing fixes for these items in the next build of the Addin.
Great utility, looking forward to the automated version !
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
Jonathan,
Can you add alerts in the next version too please?
Thanks
Chris
Very nice Tool !
Could you please add synchronizing Linked Server to the plugin, too ?
Hey Sascha,
I’ll add this to the list of requested features for the next release.
Thanks for the feedback.
Hi
How do i install this i just ran it and restarted sql but still i cant see the addin.
Was this question ever answered? I’m having the same issue.
Hey Rob,
Can you send me an email with more details about your specific installation
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?
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’.
Hey Jay,
You have the run the script in SQLCMD mode in SSMS, as stated in the comments at the top of the script.
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?
Hey Marco,
I haven’t looked at what is required to get a Addin into SSMS 2014 at all, so this may be something I’ll look at doing in the future.
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?
Hey Don,
I’ll add this into the changes needed for the next version of the Addin when I get some time to be able to work on it again.
Is there a change log or something so we can see what features have been added since release?
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.
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
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.
Does this work on SQL 2014 as well?
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.
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
Here’s a link for anyone else interested in Master/Target SQL Agent – https://msdn.microsoft.com/en-us/library/ms180992.aspx
Hey there,
Great idea! Any plans for 2014 support? Also, any thoughts on adding support to schedule the functionality?
Thanks
A 2014 version is coming out soon so watch my blog for an update on this in the coming weeks.
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
No but a 2014 version is coming out soon so watch my blog for an update on this in the coming weeks.
Hi..Waiting on SQL2014 version..
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.
No, only the addin is available as provided.
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…
There aren’t triggers for everything that is uncontained usage unfortunately and you’d still risk having differences without manually managing it in my experience.
thanks a lot
Excuse me, is there a version that can be used on SQL Server 2017?
No there isn’t a version that supports the newer releases of SSMS unfortunately.
Excuse me, is there a version that can be used on SQL Server 2017?
Would really help to get a 2017 version 🙂 as this was epic!