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.

SQL Server Database Mirroing Cannot Establish A Witness

Problem:

I spent about two days on this issue. I could establish a Principal and Mirror pair with the SQL Server 2005, but when an Witness server was added in the mix, the witness could not establish.

I have gotten errors like "Cannot find the server at mirror.xyz.com" and "Alter Database Failed..."

Answer:

You should go through the article on MSDN http://msdn2.microsoft.com/en-us/library/ms189127.aspx to make sure that you did not botch up any other stuff.

The key to this are the following,

• The mirroring operation is very very sensitive to DNS, if there is anything wrong with it, it won't work.
  
• The fact that you can "talk" to the mirror and the principal using all aliases is not a sufficient test. Go to each machine (e.g., the mirror and the witness) involved and make sure that all full and DNS alias names can be resolvable to the (identical) IP address and that you can ping the servers with ALL of the known names. If this does not work then you should "hard wire" all the names and addresses in the local machine's C:\Windows\System32\Drivers\Etc\Hosts file.

What Made This Difficult:

In my specific sitaution, there were couple of DNS aliases for all of the servers, and the host name appeared in all different variety of names while confugiring the mirror. As it turns out that having various names is not actually an issue, but the DNS configuration on the TCP/IP control panel was all slightly different causing one server for not being able to find the server name.

Total Time Saved:

You will save at least one day or more of your time by debugging the DNS first.