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
- Going to the mirror side
- Enter "ALTER YourDatabaseName SET PARTNER OFF"
