Words of Caution on SQL Mirroring with SharePoint

Posted on July 2, 2008 by Joel Oleson

I’ve had a lot of conversations with my friend Mike Watson spent a ton of time working on Disaster Recovery strategies in his time working on DR for the Hosted SharePoint and Microsoft IT deployment, as well I’ve gathered a lot of feedback in various conversations with customers, MCS, and other SIs. One thing I’ve gathered from various conversations and in my own experience is there should be some words of caution for those venturing into the SQL mirroring space. These words deal with mirroring for SharePoint in SQL 2005, I think SQL 2008 still has promise or potential. Feel free to comment as I expect this is controversial.

Also check out the latest whitepaper on SQL mirroring, you’ll see the holes are a bit smaller as we all gather more info on this complex operation.

1. It isn’t ready – In my experience SQL mirroring is half there for SharePoint in SQL 2005. The idea is awesome, but since the granularity is in the database I have heard way too many times of failures with unknown reasons.

2. Too complex – with operations folks it is a pretty big jump for real admins to jump from doing clustering (doable) to mirroring (complex). It increases the level of difficulty for supporting the environment by a factor. I find anyone who is new to SharePoint should wait a year to mess with something like SQL mirroring. Unless it’s a SQL team, the SharePoint ops folks will find the complexity just way too high.

3. Doesn’t live up to it’s potential – Without Mirroring really giving you automatic failover what does it give you? Manual failover is complex and even the most experienced SharePoint people will be challenged to properly failover the farm across the databases…. let alone dealing with the config, SSP databases and Index.

4. Consumes too much memory – this may not be a big deal for most of you, but I’ve found the amount of memory that SQL mirroring sucks up is really something to be aware of if not concerned about. The SQL team themselves had a low threshold for mirroring due to memory from what I could glean. In Mike’s own testing he found there really was an upper limit to mirroring since SQL would consume more and more memory with each database that’s added. Please don’t assume that I had mike review this post, since he hasn’t and he may not agree with everything I’m saying here. I’ll let him respond on his blog or refute this.

5. Mirroring over the WAN is obviously a problem too since figuring out the challenges of latency really end up pushing log shipping which is an old TCP/IP netbios basically file sharing type of operation. Sadly inefficient, chatty and continues to still be superior to mirrioring in my opinion. Again I say SQL 2008 and Mirroring step up the challenge.

My 2 cents is to wait for SQL 2008 and pretend like Mirroring doesn’t exist unless you have a top notch SQL team and a top notch SharePoint team. If you do, then you’ll likely be looking at fancy tools that will help you manage the failover and manage the namespace challenges that come with mirroring. I know there are alias workarounds that some have come up with that really reduce the complexity, but the index and SSP challenges don’t completely go away. Don’t sell yourself short with Clustering vs. Mirroring or with Log Shipping vs. Mirroring. In both cases the alternative to mirroring is more simple and does the job in most cases. It is the exceptions which is why we are all looking at mirroring, so let’s not totally write it off, but keep it in your back pocket.