Posted by: Denny Cherry
SQL Server, SQL Server 2012
When setting up AlwaysOn Availability Groups you may receive Error 41158 which references error 41006 when you attempt to join the the replica to the Availability Group. What these errors in a nut shell mean is that it ain’t going to work with your current configuration.
Assuming that you ran through your SQL Server installation and went next, next, next through the install this result is to be expected. The reason for this is that your SQL services are all running under local accounts which don’t have the ability to log into each other. There’s two solutions to this problem at this point. 1 is supported, the second isn’t.
Option #1 – aka. The Supported Option
Reconfigure the SQL Services which will be hosting the Availability Group Replicas to run under a single domain account. Restart the services. Give the SQL Account that the services are running under sysadmin rights. The replicas should sync up automatically at this point. If they don’t you can use the ALTER AVAILABILITY GROUP command to join the AG.
Option #2 – aka. Totally Unsupported, but works great for a demo
Add the domain computer account for each of the nodes of the cluster to each others SQL instance so that they can log in. For example the four computers which I use for my demo are called ALWAYSON1, ALWAYSON2, ALWAYSON3, and ALWAYSON4. So on machine ALWAYSON1 I added the domain accounts BACON\ALWAYSON2$, BACON\ALWAYSON3$, and BACON\ALWAYSON4$ as members of the sysadmin fixed server role (again this is for my demo lab so I’m going for working not secure). On machine ALWAYSON2 I add BACON\ALWAYSON1$, BACON\ALWAYSON3$, and BACON\ALWAYSON4$ and so on for machines 3 and 4. Once that was done the replication should being syncing up automatically. If they don’t either use ALTER AVAILABILITY GROUP or use the UI to force retrying.