AlwaysOn Availability Groups and User Accounts

I recently built a AlwaysOn Availablity Group for my companies database server on SQL Server 2012. With three databases for mission critical applications running on the database server, we needed a little more protection than a weekly backup that we can restore.  So I took two fresh servers, installed Windows Server 2012 Standard and SQL Server 2012 Enterprise on each and found this link, which is a great place to start when building an AlwaysOn Availability group.  There's a couple points that were either missing or noted so briefly that I missed them, but it pretty much covers everything from end to end.  There's also an ebook called "High Availability Solutions" downloadable from Microsoft with a lot more information and detail.

With a few tweaks, I got it up and running fairly easily.  The database synchronized between the two servers and I could fail-over from one being the primary to the other being the primary and back without data loss.  However, there was a real issue here.  A critical application that the majority of our company runs on would not operate when the second server was running as the primary. The problem turned out to be a user account that the application created on the database that was only created on the first server. I didn't find this at first, because the log files listed the fact that the user account did not exist on the second server as only a warning and I passed over it a number of times.  It seems that in an AlwaysOn Availability Group, only the database(s) are synchronized, not the user accounts.  And our application vendor was of no help, this user account was created on the fly (and completely randomly) when the application was first installed and there is no record of what the password for the account is. A hash of the password is hard-coded in somewhere, so changing it isn't an option. And since this was an unsupported setup for their application, they were unwilling to provide any assistance.

After a long period of head scratching and repeated searching on Google, I came across a great utility that is a free download away from solving this problem.   There is a blog called SQLSkills that everyone who deals with SQL Server should know about. They provide the utility here called "SQL Server 2012 Availability Group Add-in" that will synchronize "Uncontained Objects" such as user accounts that do not get synchronized between servers in the group.  This tool is exactly what the doctor ordered.

I downloaded the utility and installed it on both servers in the group.  It brings up a pretty simple and self-explanatory wizard that will synchronize any or all uncontained objects. The user account synchronized correctly in just a few seconds, and I can now fail-over the group, or completely shut off the first server with only a brief interruption in database services that lasts for a second or two. And because the application generates a new user account with each upgrade, I can run it again later and synchronize any new objects.

I am not in any way affiliated with the SQLSkills site, I'm just someone who is grateful for their freely available tool.  It really bailed me out.