SharePoint with SQL Server Mirroring

While most documentation for implementing Microsoft SharePoint suggests using SQL Server clustering for high availability and failover, database mirroring might be as good or even a better option. If, for instance, you don't have a SAN, you might really appreciate the ability to use database mirroring instead. This post runs through using DB mirroring with SharePoint 2007. There is a ton of information out there about implementing Microsoft Office SharePoint Server 2007 (MOSS), but my experience is that the bulk of them fall into two basic categories: very basic step-by-step instructions that don't go into much detail on the why of it all, and those that focus entirely on the why without talking much, if at all, about the how. So you either end up with a theoretical understanding of the underpinnings of SharePoint (assuming you are familiar with the terminology), or you are able to set a basic MOSS site without knowing if you're following best-practices, or if what you're doing will even give you what you want. It's my hope--as we go through our implementation--to find and post a middle road through all this. By way of starting out, here are a few documents that have been helpful in implementing SharePoint.

• Microsoft SQL Server Mirroring with SharePoint Unfortunately, this document is almost entirely instructions on how to set up database mirroring in SQL Server. If you're a DBA, this is not useful information to you, and if you're not a DBA, you're likely going to use the SQL Server GUI to implement database mirroring, anyway (the white paper gives instructions on mirroring through T-SQL commands). Happily, though, there is a bit on configuring SharePoint to use the mirror database in the event of a failover. In short: you use the SQL Alias feature (through cliconfg.exe) on the front-end web server(s). This is the method we ended up using.

Implementing DB Mirroring with SharePoint

Here's where the rubber hits the road. To be clear: we're talking SQL Server 2005, here. In short, this is what we're doing:

• Mirror the SQL Server databases
• Ensure the SharePoint login accounts can access the mirror DB server
• Configure the SharePoint front-end web server(s) to allow for failover

At the moment, we're not going to go into much detail on setting up the mirroring; that may be added at another time. It's a good idea to set up a witness server (instance, really); that makes testing the failover much easier, and the witness instance can be a SQL Server Express instance, so it won't cost you any more. Do make sure your logins from the primary database instance are copied to the mirror instance. This KB article from Microsoft talks about doing so. Or you can see my summary in this post.

Setting up the Server Alias

Here's the money piece. SQL Server includes a "alias" feature that allows you to create a synonym, stored in the registry, for a SQL Server. This is important, because, for some reason, MOSS 2007 developers decided not to include SQL Server mirror failover capability in the product. <Actually, this decision probably is a function of the way SharePoint handles the database; it really does treat the SQL Server instance as a whole, rather than as several databases. Given that, it does follow that--since mirroring is a database-level (rather than instance-level) high-availability technology--SharePoint mightn't work quite right with mirroring. Still, it'd have been nice for them to have worked out a way for this to function.> To create an alias, run the application cliconfg.exe. This allows you to create an alias for a SQL Server machine, stored in the registry. It should be noted here that there may be two versions of this application if you're running this on a 64-bit server. They are identical except that the 32-bit version stores the alias in the 32-bit compatibility registry key. We've had instances in which we've had to set the alias up in both the standard (64-bit) and compatible (32-bit) keys in order to get SharePoint to use the alias fully. Note that when you create a TCP alias, the port matters. If you're not pointing the alias to a named instance, the port by default is 1433. Additional instances will always have a different port. You can find the port number either in the current SQL Server startup logs or by using the SQL Server configuration utility. The registry key in which the alias is stored is at

\\HKLM\Softare\Microsoft\MSSQLServer\Client\ConnectTo\

While the 32-bit compatible key is at

\\HKLM\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo\

A TCP Alias breaks down something like this: DBMSSOCN,servername,4984 Where DBMSSOCN refers to TCP, servername is the server name, and 4984 is the port number. A Named Pipes Alias, on the other hand: DBNMPNTW,\\servername\PIPE\MSSQL$spstg\sql\query Where DBNMPNTW refers to Named Pipes, servername is the server name, and $spstg is the instance name. Once you've got these registry keys set, it's a simple matter of changing the server name (and port, if necessary) in the alias after a failover. This will point SharePoint to the mirror DB server instead of to the primary. If you're comfortable doing this, changing the registry directly is quite sufficient. If not, you can edit the alias with the cliconfg.exe application. After changing the alias, it's prudent to go ahead and reset IIS. You can simply run the IISRESET command from the command line to do this. It should be pointed out again that, while SQL Server DB mirroring is on a database-level (that is, a single database within an instance can fail over to the mirror), SharePoint, for the most part, treats the SQL Server instance as a unit. While it's possible to use STSADM to rename a server for a particular database, this seems like more work than is necessary. To do this failover automatically requires another step, and it's one that we're still working on. I'll post that as we move closer to that goal.