High Availability And Disaster Recovery In SQL Server - A Quick Note

Hardware and Software are the backbones of most of businesses today. To minimize the downtime and impact to the business, we need a high availability (HA) or disaster recovery (DR) plan in place. You should first understand the difference between High Availability and Disaster Recovery, so that you can explain it to your management and get all approval for procuring the perfect system in place

High Availability

Implementing something that gives you the ability to keep running the business in the event of some catastrophic failure is like a rocket having multiple engines so if one of the engines breaks down then the other engines can take it to orbit.

Disaster Recovery

Implementing something that gives you the ability to overcome data loss after disaster. A highly available system only makes your data available but they do not control whether the data is correct. Imagine a scenario in which a DBA or Developer forgets to add a WHERE clause to his or her UPDATE statement, and all of sudden the table is empty! High availability solutions do not help in a scenario like that. In fact, they just make the problem worse because they will duplicate the bad data to multiple nodes/sites. We should always have a updated fully documented disaster recovery plan.

Following are different ways we can achieve HA and DR. Each has its own pros and cons

  • Clustering
  • Database snapshots
  • Log Shipping
  • Database Mirroring
  • Replication
  • Physical Redundancy
  • Backups
You might have used log shipping, replication, clustering or mirroring to attain high availability and disaster recovery. Now, SQL Server alwayOn has also joined the list and it is next improved version of mirroring. Database mirroring has its own disadvantages. I mean, it cannot have more than one secondary database. Also, we cannot read the secondary database. So these drawbacks of database mirroring identified and resolved in SQL Server alwaysOn.

Here are a few limitaitons of prior technologies which become motivation to develop SQL Server Always On!

Clustering

In clustering, we are covered for server failure but not for SAN failure. When SAN goes down then cluster goes down. So we cannot use clustering for Disaster recovery.

Replication

In replication, we cannot have complete copy of the database. We can publish only a few types of objects like tables, views and other objects. So the secondary server is not an exact copy of the primary. There is always a delay in response. So this is also not a high availability solution.

Log shipping

Like clustering and replication, log shipping also has primary and secondary databases. There will be log backups running in a scheduled manner from primary to secondary. (For example: Every 3 minutes) But when Server goes down within a three minute period, then you will start losing the data. It can be used as warm stand by. Log shipping is used for disaster recovery purpose because if Primary Server goes down, then we can use the Secondary Server.

Database mirroring

We have to set up mirroring at the database level. We can have only one copy of the primary database. If we have more than that, we are unable to read from secondary server. The database in secondary server will be in recovery mode.

So what is the enhancement done in alwaysOn?

You have no need to have monitoring server in alwaysOn. AlwaysOn has an inbuilt technology to switch the automatic failover from primary to secondary. Behind the scenes, it uses Windows clustering and quorum.
  1. You can have more than one secondary server.
  2. You can read the secondary database
  3. So, alwaysOn has resolved most of the mirroring limitations.