In this article we will try to understand differences of SQL Server Always On Availability Groups vs. Read Scale Availability Groups.
Designing HA/DR Solution and Customer’s Question:
Recently, we were working on designing SQL Server HA/DR database solution for one of our customer. After considering customer’s requirement, we suggested a multi subnet three node Always On Cluster. Customer was fine with solution. However when we started talking about setting it up, other thought came from customer. Windows Failover cluster is prerequisite for setting up Always On setup. Customer wanted to know if it is possible to design availability group solution without creating windows cluster.
Can we design HA/DR Solution without cluster?
SQL server 2012 introduced always on Concept. Till version 2016, failover cluster is prerequisite for always on. However SQL 2017 allows to setup availability groups even without a windows cluster through Read Scale Availability Groups.
There is less overhead of managing cluster in Read Scale Availability Group. However it comes with limited functionalities in comparison to Always On Availability Group.
Read Scale vs. Always On Groups:
Let us try to understand the differences so that right solution could be designed.
High Availability :
- Health detection and automatic failover requires Windows cluster. Read Scale Availability Group is not a HA solution. High availability is possible only through Always On availability groups as it is built on top of cluster.
- Only Manual failover functionality is possible in Read Scale Availability Group. Always ON groups provide both manual and automatic failover options.
Disaster Recovery :
- DR solution is possible with Read Scale groups. Both synchronous as well as asynchronous commit mode are available in Read Scale Availability Group. In synchronous commit mode, read scale Availability Group provides RPO of zero.
Read Replicas & Connectivity:
- Read Only Replicas : Like Always On Groups, Read scale availability groups could be used to create one or more secondary replicas for read purpose. As the name suggests, this is the primary purpose of providing read scale groups as a new feature in SQL 2017. Analytics/Reporting application can directly connect to read replicas. Reporting applications can also connect to primary replica and read routing rules need to be created to direct read traffic to secondary replicas. We can also create listener as mentioned below.
- SQL Listener: This is very important to understand this difference from application connectivity perspective. In Always On groups, we create listener which takes care of all the connectivity irrespective of whichever node is primary. This provides HA functionality as well. In read scale groups also , we can create create listener but at the time of creation of this listener, this is pointed to primary instance IP and port. In case of failover, we need to drop and recreate listener with new primary replica IP and port. Read routing rules are required to route traffic to read replicas if reporting applications connect through listener.
So just to summarize, read scale availability group is an amazing feature if high availability is not the need. This feature does not require Failover cluster . If requirement is only to offload read workload to one or more secondary replicas then it is a good option. This feature can help in performance optimization by segregating OLTP and OLAP workloads.
So this was all about Always On Availability Groups vs. Read Scale Availability Groups.
Reference articles:
Happy Learning !