A week ago we completed the migration of SharePoint implementation to new infrastructure for a client of ours. The non-functional requirements included high-availability which we met with SQL Server 2008 Database Mirroring. Below are some of the architecture decisions from this project.
Fig 1: Content DB is running as principal on SQL2 and mirror on SQL1, while all the others are running as principal SQL1, and mirror on SQL2.
Asynchronous vs. Synchronous database mirroring
With asynchronous mirroring (high-performance mode), the transaction is completed on principal server before it is applied on mirrored server. This means that if fail down occurs, there is possibility of data loss.
With synchronous mirroring, transaction on the principal is committed after it has been completed on the mirror server, thus causing a performance delays. Synchronous mirroring requires a high-performance network infrastructure with low latency.
Note that asynchronous is available only in Enterprise version of SQL Server.
Using SQL Aliases
SharePoint is sensitive (meaning lots of stsadm work required) to changing the database server for Configuration database. Using SQL aliases, you can trick SharePoint to “think” that it is connecting to the same principal database, while in reality it is using the mirrored database (when principal is down).
You shall be setting SQL aliases on front end servers, where you don’t have SQL Server installed. Then, you shall use SQL Client Configuration utility cliconfig, located here: C:\Windows\System32\cliconfg.exe.
Here is the location to the key that this tool sets. This key can be directly modified in registry to change the alias.
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo]SQL -> DBMSSOCN, SQL1
“Active-active” Database mirroring
By definition, Database mirroring implies that one server is the principal, and the other is the mirror. With this configuration, each of the servers is running as principal for some of the databases, and mirror for the others, and vice versa.
The purpose is to distribute the load between the two servers and to utilize the memory of both of the servers. If you have read operation, than only the principal server of the database will register disk IO. Of course, if you have write operation, than both the principal and the mirror will carry out the operation.
Also, with SharePoint, you can put site collections in separate content databases. That means that you can have half of the site collections running on SQL 1 as principal, and half of them running on SQL 2 as principal.
Note that if you use “active-active” type of DB Mirroring, you will have to license the both of the SQL Servers. If one server is acting as only mirror instance, then you will have to have license only one SQL Server – the principal server.
Manual vs. Automatic Failover
With manual failover you will have to run the following scripts:
- T-SQL Script on the active server to force manual failover on the mirror server (with data loss if you use asynchronous mirroring)
- Registry update script on each of the front-end servers to update the SQL Aliases. Also, you can use remote registry in order to run the script remotely on each of the servers in the farm.
SharePoint with does not support truly automatic failover. However, what you can do is you can use a specific event that is triggered when DB mirroring failver has occured. The event will initiate a SQL server job, which in turn will update the SQL aliases.
Once the other server is up and running, you will have to revert the databases to their, and update the SQL aliases as well.
Our implementation:
We implemented asynchronous active-active mirroring, with fully-scripted manual failover without witness server.
Sincerely,Rossen Zhivkov