SQL Server 2008 R2 100% availability
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?
Solution 1:
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.