Database Mirroring 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.
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.
SQL Service Accounts
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 states "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 have to use them.
The solution is simple - use local machine accounts with the same username and password for each SQL instance that will participate in mirroring.
Let's pretend you have two servers named serverA and serverB that will participate in mirroring and the SQL Service on each server uses a local machine account named sqlservice. 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 serverA is going to be the Principal and serverB the Mirror you'll want to put serverB in the Principal and serverA in the Mirror on this screen:
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 serverA on serverA and the login for serverB on serverB. 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 serverA is the Principal the wizard will use the credentials you entered for the Mirror (serverA\sqlservice) to create a login on the Principal (serverA) and the credentials you entered for the Principal (serverB\sqlservice) to create a login on the Mirror (serverB).
The simplified reason this approach works is because when a connection is established from serverA to serverB the credentials supplied from serverA match the local machine account on serverB and access is granted - No certificates required!
Fully Qualified Domain Names
Books Online indicates that while you can use a fully qualified domain name (FQDN) for server network addresses you can also use IP addresses. 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!
Digging around in the Windows event log on the mirror turned up this error:
Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://[SERVERNAME]:5022'.
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.
The solution? Add "fake" FQDN entries to the Windows hosts file (usually located in c:\windows\system32\drivers\etc\) on the principal, mirror, witness, and web servers, then rebuild mirroring using the "fake" FQDNs.
Conclusion
I hope my pain becomes your gain should you ever find yourself having to set up database mirroring in a Windows Workgroup!
