Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
On 2008, failover clustering and synchronous mirroring w/witness are the only automatic failover options. Both have advantages and disadvantages. –  Jon Seigel Jun 6 '13 at 19:06
1  
If/when you move to SQL Server 2012, you can consider using Windows Server Core, which will reduce a lot of your downtime requirements (since there are far fewer patches that are required for the non-UI portions of the OS). This won't eliminate all downtime, especially if you always install all the patches made available every patch Tuesday, but it gives you a chance to skip some of those windows altogether. –  Aaron Bertrand Jun 6 '13 at 19:24
add comment

2 Answers

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.

We currently only have one production SQL Server 2008 server.

You will need a cluster (WSFC), and you will benefit from multiple servers/nodes to ensure maximum uptime.

We are looking for a solution that will automatically switch to a failover server so we can apply rolling updates.

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.

share|improve this answer
add comment

One fairly easy solution (and easy to configure), provided you have the hardware resources, is database mirroring with replication failover.

http://msdn.microsoft.com/en-us/library/ms151799.aspx

As for the best high availability solution? There's clustering, log shipping, syncing, and other options but they might all require more resources. This solution might be a very good option given that you have one production database server.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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