We currently only have one production SQL Server 2008 server. Anytime we need to perform maintenance or install patches, it causes downtime as the server reboots. In addition, if the server ever fails, that will obviously cause a great deal of downtime.
What is the best solution to avoid this maintenance downtime? We are looking for a solution that will automatically switch to a failover server so we can apply rolling updates.
Best Answer
Have you considered a Failover Cluster Instance? It'll give you the high availability of the entire instance. And provided you have more than one node in the FCI, you would be able to failover while patching the other server.
There are quite a few details regarding failover clusters, but please see this MSDN reference to get an overview and other how-to topics.
You will need a cluster (WSFC), and you will benefit from multiple servers/nodes to ensure maximum uptime.
You can have automatic failover of the FCI to a different node in the cluster. When are you patching a SQL Server instance, you will basically want to shuffle current and possible owners and apply the patch to the passive node. This will give you a solution of minimized downtime for updates.
The real question you need to answer is, at what level are you looking for redundancy and high availability? The instance? A database? Database objects? By your question, I'm guessing at the instance level, which is why I recommend an FCI. But if a database, you could consider database mirroring.