SharePoint and Mirroring

Rather than deal with issues we've run into with the Microsoft Clustering service, it was decided that we try SQL database mirroring...and in the process opened up another can of worms.

Careful planning and thorough design are the keys to success in any IT project, this article will show some of the things to consider before going with mirroring or clustering for you high availability solution.

The differences between SQL mirroring and clustering:

1. A SQL cluster uses a virtual server instance that the active server uses to host the Server...the entire server.
   A SQL mirror mirrors individual databases between two servers. By mirroring I mean it passes the transaction logs between servers.
2. SQL cluster shares a single storage location for databases
   With mirroring each server manages it's own databases, so there isn't a single point of failure.
3. A mirror can have a 3rd server that acts as a witness...it receives and passes on the transactions to ensure no transactions are lost.

The big advantage to clustering is that it is completely transparent to outside servers since no matter which server is active the servers will always connect to the virtual server which never changes. SharePoint doesn't have any method natively of detecting or handling a database failover. So this leads me to ask, how do we handle a failover?

There are two ways of managing this, the first is through stsadm. Run the commands to change the database server and reconnect the databases...I'll look those up and get back to you...This method seemed cumbersome to me, so of course there's more than one way to skin a SharePoint instance.
Another alternative is to use SQL client aliases. A SQL alias is basically a local nickname for a SQL server. You can configure Aliases by running "cliconfg.exe" and going to the alias tab. Add one, give it a logical name, select tcp/ip as the protocol and put in the SQL server FQDN.


Repeat for each of the front end and application servers. If the DB server goes down, or the databases fail over, just change this value on each of these. Sharepoint will refresh the connection within about 60 seconds and your users will just assume it was a blip in the system.

Now the next question: Do we really have to do this manually? on every one of the servers? What a pain! Yes, you will have to do it manually and there's no "Microsoft" way of doing it...I also couldn't find any third party tools for making it happen either. On the advice of another blogger I wrote my own asp.net windows service to take care of this. The logic is pretty simple, connect to the witness server, query the sys.mirroring_databases view and check the principal server of one of the SharePoint databases (I usually have it check the config DB) and change the SQL client alias information accordingly. You do this in this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo
There is a seperate value for each alias in this registry key.

So...what happens if a single database fails over and not the entire server? That's right, more SharePoint Instances to be skinned. One thing you can do is add a SQL server alert to catch WMI event changes to the DATABASE_MIRRORING_STATE_CHANGE object which then tells all the databases to fail over. A good way of doing this is documented on this page: http://www.mssqltips.com/tip.asp?tip=1564.
We ran into a problem when our server lost connectivity briefly and started failing databases over. When it came back online the remaining databases stayed Primary but the others stayed mirrored. I haven't investigated entirely to see if in that situation if the server would register a WMI event for a fail over. In the event that it doesn't, you're stuck with databases scattered on two different servers and your SharePoint server doesn't know where to find it's data. My recent idea was to make a separate alias for each database and tell the mirror watch service which database belongs to which alias. Now it doesn't matter where your databases are because SharePoint will be able to find them. This method will increase initial administrative overhead when creating new databases and installing initially, and will make SharePoint's server list in CA look huge. I'll have to do more research into any other side effects this may have on SharePoint as a whole.

A couple closing notes...and precautions about SQL mirroring with SharePoint.

1. Be very careful how many databases you put into a mirrored set. Unless you have a real beefy SQL server, you will get very strange behavior from your mirrored databases, 10 per server is one recommendation that I heard, but it's possible to have thousands, provided you have the processing power and RAM.
2. When designing your maintenance plans, keep in mind that the databases won't always be live on the same server. If you are planning to have databases live on different servers, you might want to write a T-SQL script that only backs up databases that are currently principals on both databases. If possible, make sure they write to the same backup location also to prevent confusion.

There are a few different whitepapers from Microsoft out there detailing the steps to take to get database mirroring functioning, the purpose of this article was to detail to you some further concerns you might want to consider when designing your SharePoint archicture. With SharePoint sometimes it's not so easy to make a second go of it.

To sum up, when considering an SQL high availability solution, some things to consider: how will the application fail over? How will you maintain the databases? How many databases are you planning to host? What are you looking for your solution to achieve? After considering these points you'll be more prepared to proceed successfully with your deployment.