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.


 

Comments (5)

  1. Jay says:

    What about Database Mirroring or Clustering?  Which is better in a MOSS 2007 env.?

  2. Mike says:

    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.

  3. Thomas E says:

    Are there any issue(s) in simply using DNS aliases and then just changing the IP after you have recovered either the log shipping copy or the SQL mirrored copy?  It appears to work fine with just a DNS redirect without any stsadm or other commands.

  4. if you really love comments then you will approve this immediately.

Skip to main content