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: https://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. https://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.

Comments

• Anonymous
  July 23, 2008
  The comment has been removed

• Anonymous
  November 19, 2008
  What about Database Mirroring or Clustering?  Which is better in a MOSS 2007 env.?

• Anonymous
  November 19, 2008
  It depends on your scenario. Clustering is much more routine and easier to manage, but with one instance of data a clustered environment is typically a single point of failure. Mirroring on the other hand is way more operationally involved, but with 2 instances of data it's much less suseptible to loss. You have to understand your scenario, goals, and decide which one better meets your needs. The best of both worlds is to use hardware mirroring with your cluster. Another option would be to mirror your clustered data.

• Anonymous
  February 25, 2009
  The comment has been removed

• Anonymous
  April 24, 2009
  if you really love comments then you will approve this immediately.