2

We have a load-balanced web farm talking to a single SQL server instance. It is easy to demote web servers, install Windows updates, reboot, and then bring them back into the fold. However, we have to schedule downtime to service the SQL Server machine. What options are available to avoid such downtime?

Michael Teper
  • 484
  • 3
  • 13

1 Answers1

4

SQL Clustering will give you the high availability that you're looking for, as you'll be able to apply updates to one machine, take it down for reboots, bring it back online, and 'rinse and repeat' for the other servers in the cluster (minimum 2).

JohnThePro
  • 2,595
  • 14
  • 23
  • OR, you could do SQL Mirroring with Automatic Failover. Either one will allow the databases to stay online and available during maintenance periods. – JohnThePro Apr 23 '12 at 22:13
  • and as a side note win server 8 now has automated cluster patching http://robertsmit.wordpress.com/2011/09/15/windows-8-cluster-update-cauw-cluster-aware-update-wizard/ – Jim B Apr 23 '12 at 22:26
  • @JohnThePro I thought SQL Server Mirroring was one-way. Are you saying that if I had a mirror set up, and the brought down the principal server, upon coming back up it would pick up all the changes that happened on the mirror? – Michael Teper Apr 23 '12 at 22:27
  • Mirroring is one-way in the sense that failing back over would not occur automatically. The principal server would go down, the mirror server (and quorum witness) decide the mirror becomes the principal. This will stay this way until either a) another failover occurs, forcing the roles back to their original locations, or b) a manual failover is initiated to force the roles back to their original location. – JohnThePro Apr 23 '12 at 22:29
  • But for your purposes, it would allow the DB to continue to be served while taking down the primary server, and then once it is back online, you can re-initiate the failover, and perform your updates to the mirror server. – JohnThePro Apr 23 '12 at 22:29
  • 1
    Mirroring does require using an SQL gateway that supports mirroring for this to be painless though. We have some software that does *not* support mirroring. Combined with NLB it works well (the NLB shifts over at the same time as mirroring in the case of a real failure), but there *is* a 10-second break in connectivity and all sessions are terminated. – Mark Henderson Apr 23 '12 at 23:01