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.

ERROR: Cannot use the special principal 'sa'. Microsoft SQL Server, Error: 15405

As of May 2012 This is The Most Popular Post For People To Come To This Blog.
People must be having this issue all over the world!

Symptom:

You tried to create or alter a table column (or likewise attributes in a table) with Microsoft SQL Server Management Studio and get the following error.

ERROR: Cannot use the special principal 'sa'. Microsoft SQL Server, Error: 15405

You can get into the SQL server with 'sa' in Mixed Mode authentication. You have even given the sa permissions to all of the databases.

Root Cause:

The database ownership is still not correct when you restored or re-attached database. This happens for example, if you restore a database using an integrated authentication account.


Try This Fix:

Open the SQL Query windows in the Microsoft SQL Server Management Studio, and try something that resemble below by changing. TheNameOfYourDatabase to the database you are using. Do this with every database that you have that you need the 'sa' access. This will change the owner of the database to the account you designate.

use TheNameOfYourDatabase
exec sp_changedbowner 'sa', 'true'

SQL 2005 Find Out Who Are Connected

Problem:

I want to provide a list of currently connected SQL clients on my asp.net based web page, like the version of Activity Monitor that comes in the SQL Management Studio.

Solution:

The information are in the following queriable objects. For me, sp_who is good enough as I just need to list who are connected in my application monitor web app.

• exec sp_who
• exec sp_who2
• select * from master..sysprocesses

and finally this is supposed to give you the same list as what you see in the Activity Monitor (I did not try).

DECLARE @CMD VARCHAR(8000) DECLARE @ID int
SET @ID = @@SPID SET @CMD = '
EXEC sp_MSset_current_activity ' + CAST(@id as varchar) + '
SELECT * FROM ##lockinfo' + CAST(@id as varchar) + '
SELECT * FROM ##procinfo' + CAST(@id as varchar)
EXEC (@CMD)

Time Saved:

This information is either too obvious to DBAs or such that query to Google or Live does not provide an immediate answer. Took me about 30 min to finally found out. The keyword to search is "Activity Monitor"

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.