SQL Server Ver




























Always on Availability group is one of the high availability and disaster recovery solution in SQL Server. Always on Feature have introduced in SQL Server 2012 Version.

SQL Server 2012

2012 Limits: 1 primary replica, 4 secondary replicas, 2 of the replicas can be synchronous.

Only two automatic failovers will take place in In SQL Server 2012.

Three synchronous mode replicas available in SQL server 2012.

SQL Server 2014

2014 Limits: 1 primary replica, 8 secondary replicas, 2 of the replicas can be synchronous.

Two automatic failovers will take place in SQL server 2014.

Three synchronous replicas are there in SQL server 2014.

Read intent was introduced in SQL Server 2014.

Read intent routing command




In this read intent routing the read request will direct based on the order, which you have given in above command. Based on the above routing list all read request will direct to the ‘WIN2012R2-VM2\GeoPITS_SR1’ which is give in first in above routing list in case if these secondary replica is next available the read requests will direst to next replica which is second(WIN2012R2-VM3\GeoPITS_SR2) in routing list. In both first and second secondary replicas not available read requests will go next secondary replica based on routing list.


SQL server 2016

2016 Limits: 1 primary replica, 8 secondary replicas, 3 of the replicas can be synchronous.

The Enhancements of ALWAYS ON in SQL server 2016 given below

-> Round-robin Load-balanced readable secondary’s.

-> Direct seeding of new DB replicas.

-> supports Azure integration.

-> Always on Supports TDE.

->Support for Distributed Transactions (DTC).

-> SQL Server Standard Edition support always on Availability group(Basic Availability Group).

->‘ALWAYSON’ renamed as ‘ALWAYS ON’.



Round-robin load balancing in readable secondary’s:

In SQL server 2016 round-robin load balancing concept was introduced. Before SQL server 2016 there is no round-robin load balancing. Main use of the round-robin read requests will distributes equality to all secondary replicas as show in below figure.


Prior of SQL server 2016 the read request will connect to the secondary replica which will responds quickly for listener.

Automatic Seeding:

Automatic seeding is new enhancement of alwayson in SQL server 2016.

SQL Server performs a full database backup using Microsoft SQL Server Virtual Device Interface (VDI) full database.

This VDI database backup is streamed through the network to all available secondary replicas.

Secondary replica restores this streamed backup.

Once the database restoration is complete, it is added into the availability group.

GRANT CREATE ANY DATABASE permission needed.


Supports Azure:

In SQL Server 2016 Always on support AZURE for Secondary replica.

Recommended is secondary replica in Asynchronous mode.

Always on Supports TDE:

Now always on supports for Encrypted Databases.

However authentication requires when adding encrypted database to Availability Group.

Support for Distributed Transactions (DTC)

Always on supports the Distributed Transaction in SQL server 2016, now we register a resource manager per availability database. The resource manager works with DTC services to track distributed Transactions. Because of this now we can guarantee for integrity of a distributed Transaction.

To use DTC with Always on we require Windows Server 2012 with KB3090973.

We need to create Availability Group with the WITH DTC_SUPPORT =PER_DB.

Database level health detection failover:

In SQL server 2016 always on one of the new enhancement is DATABSE level Health detection failover was introduced. in prior SQL server 2016 only Availability group failover takes place when the instance fails. From SQL server 2016 availability group failover takes place even on database fails or not available for long time.

We can enable this feature using below commands.



We can see how to enable Database level Health detection for Availability group in below pictures for SQL server 2016 when creating new group.

 SQL Server 2017

Read-scale Availability Group in SQL server 2017 and later we can create the availability group without windows failover cluster.

But it will not provides the high availability.

Basic Availability Group

Basic Availability group feature was introduced in SQL server 2016, which was supports SQL Server 2016 standard edition. Basic availability behaves like mirroring feature.

In below picture shows how to enable Basic availability in SQL Server 2016 and higher SQL server Standard edition.


Limitations of Basic Availability Group:

Secondary replica not allows read operations

No backup in secondary replica

Basic availability group supports only in standard editions in 2016 and later

Basic availability group supports failover environment for a single database.

In Basic availability group we have only on secondary replica.

·        Cross database transactions are now supported among all databases that are part of an Always On Availability Group, including databases that are part of same instance.

·        New Availability Groups functionality includes read-scale support without a cluster, Minimum Replica Commit Availability Groups setting, and Windows-Linux cross-OS migrations and testing. (CTP 1.3)

SQL Server 2019

2019 Limits: 1 primary replica, 8 secondary replicas, 5 of the replicas can be synchronous (one primary and up to four secondary replicas with automatic failover between these replicas).

  • Up to 5 synchronous commits to replicas in a SQL server 2019 availability group. This means that you can have a 4 + 1 setup, where there are 1 primary server and 4 synchronous commit replicas. Since all databases are in synchronous availability group that forms a quorum, you can safely setup automatic failover with zero-data-loss.
  • Availability Groups now supports Kubernetes as an orchestration layer in the 2019 edition
  • You can now connect to any available replicas and would be automatically redirected to available replicas based on a read or write request. This would obviously depend on the configuration that you need to set up at the time of creation.

SQL Server 2016 is making some significant improvements to the Always On Availability Groups set of features. There are a number of features, such as:

  • Round-robin load balancing in readable secondaries
  • Increased number of auto-failover targets
  • Enhanced log replication throughput and redo speed
  • Support for group-managed service accounts
  • Support for Distributed Transactions (DTC)
  • Basic HA in Standard edition
  • Direct seeding of new database replicas

SQL Server 2017 – Availability Groups Features

SQL Server 2019 Availability Groups Features

  • Cross-database transactions support across availability groups
  • Cluster less support
  • Minimum Replica Commit Availability Groups setting
  • Windows-Linux cross-OS migrations
  • AG support for Kubernetes
  • Five Sync Replica support (4+1)
  • Automatic Read/Write routing to Primary


Standard edition support here.  With limited

2019 Limits: 1 primary replica, 8 secondary replicas, 5 of the replicas can be synchronous (one primary and up to four secondary replicas with automatic failover between these replicas).10 AG Groups can be setup

SQL Server 2022 Availability

New feature or update


Link to Azure SQL Managed Instance

Connect your SQL Server instance to Azure SQL Managed Instance. See Link feature for Azure SQL Managed Instance.

Contained availability group

Create an Always On availability group that:
- Manages its own metadata objects (users, logins, permissions, SQL Agent jobs etc.) at the availability group level in addition to the instance level.
- Includes specialized contained system databases within the availability group. For more information, see What is a contained availability group?

Distributed availability group

- Now using multiple TCP connections for better network bandwidth utilization across a remote link with long tcp latencies.

Improved backup metadata

backupset system table returns last valid restore time. See backupset (Transact-SQL).

No comments:

Post a Comment

Popular Posts