Is there any way to provide 100% uptime on SQL Server 2008 R2?

From my experience, the downtimes for the different replication methods are:

  • Log Shipping: Lots (for DR only)
  • Mirroring w. NLB: ~ 45 seconds
  • Clustering: ~ 5-15 seconds

And all of these solutions involve all of the connections being dropped from the source, so if the downtime is too long or the app's gateway doesn't support reconnection in the middle of task, then you're out of luck.

The only way I can think to get around this is to abstract the clustering a level (by virtualising and then enabling VMWare FT. Yuck. Good luck getting this to work on a quad-socket, 32-core system anyway.).

Is there any other way of providing 100% uptime of SQL Server?


No. 100% is not possible with SQL Server. Several nines, yes.

As for clustering: only one individual SQL Server install can "own" the MDFs at any point in time. So you have the rollforward/rollback cycle as another instance takes over and connections are broken. This decides how long for the cluster to failover: typically it can be under 10 seconds.

You have the same issue with mirroring too: there only ever is one owning/controlling SQL Server instance

You can code the client to retry on timeouts/deadlocks then this mitigates the failover time. Not everyone agrees with this, but we've found it useful on occasion.

When we've had failover (touch wood, not for a year or so) then stateless clients (eg web services) have not noticed unless one call fails at the wrong time. See my previous statement.

  • I suspected as much. Unfortunately we're dealing with software that uses a legacy SQL connection where everything is stateful, so we need to even restart the website brokers when the failover happens. Thanks for the clarification though. – Mark Henderson Feb 7 '11 at 5:01
  • 6
    there isn't an IT architecture on earth which provides 100% uptime. However clustering is going to provide the most nines. Rearchitect the state problem if it's interfering with implementing the best DB HA solution. – Scott Herbert Feb 7 '11 at 5:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.