Database Mirroring In A Windows Workgroup

<div><p><a href="http://msdn.microsoft.com/en-us/library/ms189852.aspx" target="_blank">Database Mirroring</a> is a popular high availability solution and low-cost alternative to clustering, especially for smaller shops who can't afford the costs associated with SANs and Windows Server Enterprise or for smaller installations where clustering is simply overkill.</p> <p>I recently set up mirroring for servers in a Windows workgroup and I had a few struggles getting it to work correctly. I did eventually get it running and wanted to share two things in particular that you might find helpful if you find yourself in a similar situation.</p> <h5>SQL Service Accounts</h5> <p>Because a workgroup isn't part of a domain any account that SQL is run as on a machine in a workgroup is a non-domain account. Books Online <a href="http://msdn.microsoft.com/en-us/library/ms366346.aspx" target="_blank">states</a> "If you run SQL Server under a non-domain account, you must use certificates" for configuring mirroring. However, that's not entirely accurate - unless you have a specific need for certificates you don't <em>have </em>to use them.</p> <p>The solution is simple - use local machine accounts with the same username and password for each SQL instance that will participate in mirroring.</p> <p>Let's pretend you have two servers named <strong>serverA</strong> and <strong>serverB</strong> that will participate in mirroring and the SQL Service on each server uses a local machine account named <strong>sqlservice</strong>. When you configure mirroring and use the Database Mirroring Security Wizard you'll want to make sure to pay attention to the part where you specify the service accounts. If <strong>serverA</strong> is going to be the Principal and <strong>serverB</strong> the Mirror you'll want to put <strong>serverB</strong> in the Principal and <strong>serverA</strong> in the Mirror on this screen:</p> <p><img alt="image" border="0" height="444" src="../../-iJqc5gdvWvk/TtgIm68RVRI/AAAAAAAACHA/JiROgO1VEXI/image-255B5-255D_imgmax-800.png" style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto; padding-top: 0px" title="image" width="494"/></p> <p>Although this seems backwards from what you might think at first don't worry - when the wizard completes it will actually create the login for <strong>serverA</strong> on <strong>serverA</strong> and the login for <strong>serverB</strong> on <strong>serverB</strong>. This is because the wizard creates mirroring endpoints on each server and grants CONNECT access to the service account from the other partner. In this example since <strong>serverA</strong> is the Principal the wizard will use the credentials you entered for the Mirror (<strong>serverA\sqlservice</strong>) to create a login on the Principal (<strong>serverA</strong>) and the credentials you entered for the Principal (<strong>serverB\sqlservice</strong>) to create a login on the Mirror (<strong>serverB</strong>).</p> <p>The simplified reason this approach works is because when a connection is established from <strong>serverA</strong> to <strong>serverB</strong> the credentials supplied from <strong>serverA</strong> match the local machine account on <strong>serverB</strong> and access is granted - No certificates required!</p> <h5>Fully Qualified Domain Names</h5> <p>Books Online indicates that while you can use a fully qualified domain name (FQDN) for server network addresses <a href="http://msdn.microsoft.com/en-us/library/ms189921.aspx" target="_blank">you can also use IP addresses</a>. Windows workgroups don't have the concept of a FQDN so I went with IP addresses and everything appeared to work…until I tested failover. Failover to the mirror worked great except that the apps on the web servers using the SQL native client (configured with the right connection string parameters) wouldn't talk to the mirror. On top of that, after failover the mirror would lose connectivity to the witness and if I rebooted the principal all the databases on the mirror would go into recovery mode. Talk about a demo that didn't go over so well!</p> <p>Digging around in the Windows event log on the mirror turned up this error:</p> <blockquote> <p>Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://[SERVERNAME]:5022'.</p> </blockquote> <p>where [SERVERNAME] is the name of the mirror. It turns out Adam Machanic (Blog | Twitter) had the same issue and fixed it by switching to FQDN. The problem? In a workgroup there are no FQDNs and trying to enter just a machine name in the mirroring wizard results in an error. </p> <p>The solution? Add "fake" FQDN entries to the Windows <a href="http://en.wikipedia.org/wiki/Hosts_file" target="_blank">hosts file</a> (usually located in c:\windows\system32\drivers\etc\) on the principal, mirror, witness, and web servers, then rebuild mirroring using the "fake" FQDNs.</p> <h5>Conclusion</h5> <p>I hope my pain becomes your gain should you ever find yourself having to set up database mirroring in a Windows Workgroup!</p> </div>