AlwaysOn: High-Availability and reads Scale-Out
November 11th, 2010Along with SQLPASS Summit 2010 announcements on SQL Server “Denali” features the MSDN site has published preliminary content on some of these features. Going over the “HADR” Overview (SQL Server) content we can get an early idea about this feature. This post summarizes the AlwaysOn technology, and compares it with its predecessor and close cousin, Database Mirroring. For brevity, I am intentionally omitting a lot of details.
The AlwaysOn technology in SQL Server “Denali”, also known by the project name “HADR” and often called Hadron, it is a huge improvement over its predecessor, Database Mirroring. Like Mirroring, AlwaysOn is also based on physical replication of database by shipping over the transaction log. In fact, it is not only similar to Database Mirroring but actually using the DBM technologies to replicate the database. The steps to set up AlwaysOn contain the steps to set up a Mirroring sessions, and the mirroring endpoints, catalog views and DMVs are still used to set up and monitor AlwaysOn. But AlwaysOn brings three more Aces to the table to make an unbeatable play:
- Availability Groups
- Databases with dependencies on one another fail over together, as a group.
- Multiple Secondaries
- AlwaysOn will allow for multiple standby replicas for each availability group.
- Readable Secondaries
- The standby replicas are accessible for read-only operations.
The following table shows the main differences between AlwaysOn and Database Mirroring:
Feature | Mirrroring | AlwaysOn |
---|---|---|
Unit of failover | Single Database | Availability Group |
Secondary Access | Database Snapshots | Read-Only access |
Quorum and failover | Witness | Windows Server Clustering |
Number of secondaries | Exactly one | Number of nodes in the cluster |
Availability Groups
DBM considers each mirrored database as an individual entity:
But often application are deployed on several databases contained in an instance and there are tight dependencies between them (eg. cross database queries and procedures). With DBM one had to set up complicated logic to force failover of all related databases if one individual database was occurring a failover event. AlwaysOn introduces Availability Groups that allows explicit declaration of such dependencies. A failover occurs always as an entire group, all databases in the group fail over together become available on the new primary host.
The individual databases inside an Availability Group are still replicated using the Database Mirroring technology:
Multiple Secondaries
Database Mirroring is a private affair between two hosts. A mirroring session can only have on Principal and one Mirror, and they can switch roles. From a High Availability point of view the risk involved in losing one of the partner was fairly serious. Until the operations were set in place to either add a new partner or bring back the old partner online, the system would run at the risk of loosing availability on any further incident. With AlwaysOn there can be multiple stand-by secondary availability groups, on multiple hosts. This allows for a much safer operations, where multiple failures could be survived without loss of availability. This reduces the cost of achieving ‘five nines’ availability numbers, and eases planning, deployment and operating of mission critical systems.
The figure bellow show a possible AlwaysOn deployment on a 4 node cluster:
- Availability Group 1 contains 2 databases and 3 nodes of the cluster have joined this availability group. The SQL Server instance on node A is the the primary replica and the ones nodes B and C each host an availability replica.
- Availability Group 2 contains one database, it has the primary availability group running on the SQL Server instance on node B of the cluster and an availability secondary replica on the instance on node A of the cluster.
- Availability Group 3 contains five databases, it has the primary availability group running on the SQL Server instance on node D of the cluster and an availability secondary replica on the instance on node C of the cluster.
- Availability Group 4 contains two databases, it has the primary availability group running on the SQL Server instance on node B of the cluster and an availability secondary replica on the instance on node D of the cluster.
Readable Secondaries
Secondary replicas are readable in real-time. All read-only operations are allowed on databases in the secondary availability groups. Coupled with the capability to have multiple secondaries, this gives a very nice solution for Scale Out reads. Unlike Database Mirroring database snapshot solution, that was giving a point-in-time snapshot view of the database, readable secondaries allow for real query access to the content of the secondary database, in real-time for up-to-date changes. Access is read-only, no updates are permitted, and all queries run automatically under snapshot isolation model (lock hints and explicitly set isolation levels are ignored). The queries always get a transactionally consistent view of the data, as fresh as allowed by the underlying mirroring session that continuously updates the database. With synchronous mirroring this means that the scale-out achieves the all elusive golden standard of reads that are always perfectly consistent with the last committed writes *on any replica*, with no lag! So far this was impossible to achieve with any other scale-out technology [Correction 11/22: As Gopal points out bellow, this is still not achievable because synchronous mirroring only requires the shipped log to be hardened to disk, not redone. You can get pretty close, but but the application still needs to be prepared to handle stale reads].
The following image shows a possible AlwaysOn reads Scale-Out deployed on a 3 node cluster to serve a web farm. Read requests can be server by any of the availability replicas, including the Primary Replica. Write requests have to be all routed to the Primary Replica:
What gives
Database Mirroring has always been a viable choice for small businesses. Available in Standard Edition, running on commodity hardware and with minimal licensing cost requirements, it was basically dirt cheap to implement. AlwaysOn is on a different league. Its availability is no longer based on quorum decided by a witness, as in Database Mirroring, but instead is based on Windows Server Failover Clustering. WSFC is a premium feature available only on Enterprise and DataCenter editions of the Windows Server family, and is supported only on hardware that has passed the Validate a Configuration Wizard. Small businesses can still deploy basic Database Mirroring, but they will have to support a significantly higher entry cost to enjoy the advantages of the AlwaysOn technology.
[…] This post was mentioned on Twitter by Adrian Hills and Remus Rusanu, Amit Banerjee. Amit Banerjee said: RT @rusanu: [Blog] AlwaysOn: High-Availability and reads Scale-Out http://bit.ly/9bznxX #sqlblog […]
Great write-up Remus!!
Thanks for the write up. I just wanted to make a quick clarification on the below
“The queries always get a transactionally consistent view of the data, as fresh as allowed by the underlying mirroring session that continuously updates the database. With synchronous mirroring this means that the scale-out achieves the all elusive golden standard of reads that are always perfectly consistent with the last committed writes *on any replica*, with no lag! So far this was impossible to achieve with any other scale-out technology.”
Even with synchronous availability mode, the consistency of the data will still lag a little bit and is not in perfect synchronization with the primary. This is because the synchronous commit only require the logs to be hardened synchronously. Once the log is hardened there is an asyncronous process that does the redo of the pages using the hardened log to keep the pages consistent. For readable secondary the freshness of the data is reflected only after the redo. Having said that on a well tuned system the redo process is very close to real time. So the freshness of data will also be very close to real time.
Remus,
Unbelievable Article, Thanks for posting this together. I was looking for test scenarios for my test environment. Your write is so descriptive, I got answers to most of my questions.
I took, screen shots during setting up Availability Groups. Readers can take a quick looks at http://goo.gl/RhnMS to get answer about
– Infrastructure Required To test the HADR / High Availability Group
– How to implement Availability Groups in SQL Server Denali.
http://sqlserver-training.com/how-to-implement-hadr-a-high-availability-and-disaster-recovery-solution-in-sql-server-step-by-step-guide-with-screenshots/-
Thanks,
DBATAG
I just added a reference to your site, to understand the HADR concept
DBATAG
[…] Unsure, What this is and how this works, Check Here with Details […]