Last week I bragged about an amazing PowerShell module I’ve recently fallen in love with. Today I’m going to talk about the script that, in a roundabout way, made me discover that tool. This is a great script from an extremely knowledgeable and helpful member of the SQL Server family, Robert Davis.
Free SQL Server Tool of the Week
This is the second post in this Free DBA Tools series. I want to make a quick post each week, some longer than others, talking about a tool that I’ve used or use in my day to day world as a SQL Server Consultant. This is one such tool. Robert wrote a book about SQL Server mirroring. It’s a good book, with good tips. Inside that book he mentions a script for synchronizing logins between servers. Well on his site he has a copy of that script, with a couple improvements from the one released with the book.
Synchronize Logins
This is a pretty common problem. You see, Microsoft did a lot with Mirroring back in the day, and they’ve done a lot with Always On Availability Groups. Somewhere somehow along the way though, they forgot to make certain tasks easy. In a nutshell, these technologies are great for synchronizing databases between servers. They can be used for local high available, synchronous pairs or remote and asynchronous DR pairs. I implement a lot of these, more AGs these days, for clients who are looking to take advantage of the features. They provide some additional protections, sometimes a faster failover than a failover cluster instance and some additional options like off-loading read-only workloads when appropriate. Great technology for the right client.
But Microsoft sort of didn’t give you a simple way to synchronize certain objects that are more at the SQL Server instance level, since Mirroring and AGs operate at the database level. A login added to one node, will not be on all nodes. Nor will a job. Or a Linked Server (but please tell me you aren’t relying too heavily on Linked Servers in 2016, if you are let’s talk about that 😉 )
So there are often a myriad of approaches taken. From use the GUI and type things in twice and use one of the orphaned login cleanup tactics to fix mismatched User/Login SIDs for SQL authenticated users, to folks rolling their own scripts, to using the PowerShell module I described last week in this series. But this script from Robert fits in nicely there.
For the longest time (his book came out for SQL Server 2008 Mirroring) and the script has been on the web here on his blog in some form for about 6 years, Robert’s script is what I used.
It still works great. Check it out here in his blog post. Even if you don’t use his script to do this, it still will teach out a bit about some of the objects involved and they can help you out. Thank Robert for his great contributions to the SQL community on Twitter (The guy is prolific on the #SQLHelp hashtag on twitter 🙂 ) or at his Blog. And enjoy your SQLing!
