Syncing Logins/Users Across SQL Server Instances (For Availability Groups and Mirroring Failovers)

Hey Everyone! Guess who's still around!

Welcome back to SQL Something (with a post dedicated to my friend Greg who guilt-ed me into finally starting back posts). In all seriousness, it's really been far too long.

Today's post briefly touches on SQL Server Always On Availability Groups in SQL Server 2016. You can find a pretty good tutorial on how to set up Always On Availability Groups in an Azure VM via the links here and here. They are pretty thorough (great job team Microsoft!), but they do have a couple very, very small errors/typos that I might point out in a subsequent post.

Today, however, we are merely focusing on a simple login/user issue in order to help streamline failing over to your secondary. The fix is about syncing logins between availability groups. This fix can also be applied to Mirroring (a deprecated feature after 2016)

Mini Post: Specifying A Failover Partner In A Connection String

Hey everyone, Geon here again with another SQL Something post!

This time we are going to look really quickly at modifying a connection string to have a failover partner. What this means is if you have a database setup that includes Mirroring (circa SQL Server 2008), then you should make sure your applications take advantage of it. Your applications (when configured correctly) can automatically failover to the secondary DB when the current DB becomes unavailable.

Which is pretty cool by itself, but also very useful as it minimizes downtime and any manual intervention needed to point the apps to the failover DB.

And it is really simple to do.