I love blog comments - SharePoint and SQL failovers.
I love blog comments. They keep you honest and earnest. In response to my blog post Database Mirroring and Log Shipping. Which is Better? Andrew Woodward noted that my post did not address the fact that SharePoint is not mirroring aware. While my intent was to simply compare the two technologies (thereby keeping the scope small) as the owner of a SharePoint blog it’s my duty to talk about the SharePoint aspect.
Specifically in reference to mirroring, no… SharePoint is not mirroring aware, but that is not a major hurdle. Out of the box, SharePoint does allow you to use the STSADM –O RENAMESERVER command to rename any server in your topology including your SQL server. The problem, however, is that for some reason, the query server does not honor the command. You’ll notice that you lose the query after failover. Once your fail back over, query is fine.
There are better solutions. I’m just going to list them for now, but will expand on these in the future. Note: Each solution involves configuring SharePoint to use an arbitrary SQL cluster name for all its database connections.
SQL Aliasing – See this article for more information: http://blogs.msdn.com/sql_protocols/archive/2007/01/07/connection-alias.aspx
Windows Load Balancing – Yes, you can front your SQL server with NLB. It works pretty slick. This article explains NLB and SQL Server 2000, but it still works in 2005. http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog04.mspx
Hosts file – You can call you SQL server anything you want in your hosts file as long as the IP points to the principal server.
WINS/DNS – Same as hosts file, but is more complex. I don’t know any SharePoint admins that also have rights to manage WINS/DNS.
