SQL  Server Mirroring Tips

A few days ago I had to fix yet another SQL Server mirroring issue.

So here are some practices I have adoped on how to make it work right. Before diving into this, though you should be very keenly aware that Mirroring uses host name based encryption just like the SSL on a web server. If the hostnames do not match against its' certificates then you will not have a connection.

Don’t Use The Servers’ Hostnames

This absolutely sounds not intuitive, but here is the deal. If you are multi-homing your server, or having a Hyper-V running on your machine, chances are you have unintentionally left the “Auto Register DNS” mode in your TCP/IP control panel. If you do not know what I am talking about then, it is very likely that you will run into this situation, since “Auto Register DNS” seems to be on by default.

Don’t bother even thinking about it. No need to understand it and no need for a full time network engineer looking out for your situation.

The deal is that the IP address to Hostname mapping can quickly go wrong as people change the IP addresses on the control panel etc. If you are running Hyper-V or multi-homing then there are more than one IP addresses assigned to a host name.

Unfortunately, the Mirroring works basically only with a pure Fully Qualified Domain Name (FQDN). Which means that a single IP address must be mapped to a single host name.

So don’t let the OS determine this for you. Ask your IT folks and create Dedicated Hostnames for your SQL Servers and have them enter those statically into their DNS, and just for an extra measure edit your on C:\Windows\System32\Drivers\Etc\Hosts file and put the FQDN and addresses of the hostnames involved in mirroring (i.e., primary, secondary and witness – note I did not say Principal, Mirror and Witness, I said primary, secondary and witness since any of primary and secondary can be a principal at any given time.)

For example you have a primary and secondary as MyServer1.mydomain.edu and MyServer2.mydomain.edu then I’d separately create MyServerSQL1.mydomain.edu and MyServerSQL2.mydomain.edu. And have those entered manually into your domain’s DNS.

Now You Have Defined the Dedicated Host Names…

Connect with the SQL Server using the FQDN of the newly defined server using the FQDN string. For example, if you have an instance called TOMORROW, connect the database as MyServer1.mydomain.edu\TOMORROW

This will ensure that subsequent mirroring configurations will use the FQDN.

Configuring for Mirroring -- Security End-Point  Setup Step

Use the newly created FQDN when establishing the security end-points to connect to other database servers. Do not connect to the original server names.

Even if you take good care in doing this, the SQL Server still decides to change the FQDN of the endpoints, and that's the very reason it breaks or mirroring not establishing right.

After the end-points are set up, DO NOT start mirroring immediately. Instead check the end-point strings to have the FQDN. Typically SQL server reverts all or some of the string to the original host name, and that’ exactly not what you want.

Half-Established Mirror Databases -- Recovery Technique

Somethings the mirroring begins to establish and fails. The mirror side goes into the "mirrored" but disconnected mode. This can happen when you may have not typed in the FQDN correctly, and after realizing that you made a mistake, you tried to re-configure the end points, then you are only to get a Database Instance is Not Online or Available message.

You may recover from this condition by

1. Going to the mirror side
2. Enter "ALTER YourDatabaseName SET PARTNER OFF"

If the database goes into the "Restoring" mode, and if you try to re-mirror by entering the correct FQDN and also checking the correct FQDN before starting the mirroring.