HADR, HADRON

HADR is the new hot DR model in SQL 2012.  It looks to answer some of the complaints I had about database mirroring.  I'll adress those complaints as the need arises.  For now, a quick overview of HADR.

HADR = High Availability, Disaster Recovery.  Sometimes it is called HADRON, I guess ON = "Always on".

With a typical active/passive failover cluster, SQL has high availability but no disaster recovery.  This is because there is only a single copy of the database shared by both nodes.  If the active node fails, the passive takes ownership of the database.  The client communicates with the "instance" of SQL that runs on the cluster - it does not need to know that node2 now runs the instance.

SQL 2008 brought database mirroring which was a way to do DR.  The client connection could be changed to connect to both nodes.  If node1 failed, the client could redirect queries to node2.

A "HADR" solution combines High Availability with Disaster Recovery to make a sort of "super" database mirror.

From books online, "Deploying HADR involves configuring one or more availability groups.  Each availability group establishes the context within which a selected set of user databases can fail over as a single unit."  In other words, I can combine a couple of databases in the same availability group so they fail over together.  Note, however, that this high availability solution still works at the database level and not at the instance level.

In my simple scenario involving two nodes, we identify the two nodes as failover partners known as availability replicas.  HADR is different than an active/passive failover cluster because each availability replica houses a copy of the availability group - the set of user databases that we are trying to protect.  In my simple scenario, node1 maintains the primary copy of the user databases and allows users to read/write to them.  Node1 is called the primary replica.  Node2 houses a duplicate set of these databases and is known as the secondary replica.

So how is this different from database mirroring?

For starters, there were no availability groups with database mirroring.  This meant I could run into weird failover situations where some of the user databases were on node1 and others had failed over to node2.  In applications that needed to update both databases (yes, they do exist, and yes, "ick!"), this would cause some problems because the application would not know which server to use.

Another difference is that the secondary replica allows read access so that reports can be run from the secondary.  In the past, this was possible, but we had to set up a database snapshot.  If I understand SQL 2012 correctly, this means we can have reports run from data that is almost up-to-date and we don't have to mess with snapshots which are frozen at whatever time they were created.  I suspect there are performance considerations if we run a lengthy report from the secondary.  For example, what happens if we bog down the secondary so much that it is unable to pull data over from the primary?  If that happens and the primary fails, we have fallen behind in our DR copy of the data.

HADR is also better than database mirroring in that we can now have more replicas.  In the past, we were limited to one; that limit is now increased to four.  I do not know if they can be time delayed.  For example, it might be nice to have a replica that is immediately up-to-date and another that is two hour delayed.  For example, if a developer were to truncate all the tables in a database, those changes would be immediately applied to the up-to-date standby.  Wouldn't it be nice to have a two-hour delayed database available?

HADR claims to do faster client failover and redirection.

Database mirroring had several different availability modes.  In a future article I will take a look at what kind of availability modes are available in HADR.

More to follow.