In SQL Server 2019, High availability options have been enhanced by adding support to different platforms which includes

i) Enabling high availability configurations for SQL Server running in containers,now kubernetes can be used as an orchestration layer while configuring highly available systems with Always On Availability Groups.

ii) SQL Server 2019 increases the limit for synchronous replica pairs from 3 to 5. Users can now configure up to 5 synchronous replicas with automatic failover between these replicas.


Automatic redirection of connections based on read/write intent—

Configuring an Always On Availability can be challenging for a number of reasons, including:

• In SQL Server 2017, an administrator must configure the Availability Group listener (and the corresponding
cluster resource) to direct SQL Server traffic to the primary replica to ensure that clients are transparently
reconnected to the active primary node upon failover; however, there are cluster technologies that support
SQL Server Availability Groups that do not offer a listener-like capability.

• In a multi-subnet floating IP address in an availability group using pacemaker,configurations become complex, prone to errors and difficult to troubleshoot due to multiple
components involved.

• When the availability group is configured for read scale-out or DR and cluster type is NONE, there is no
straightforward mechanism to ensure transparent reconnection upon manual failover.


To address these challenges, SQL Server 2019 adds a new feature for Availability Groups:
secondary to primary replica connection redirection. With this feature, client applications can connect to any of the replicas of the
Availability Group and the connection will be redirected to the primary replica, according to the Availability Group
configuration and the connection intent (read only or read/write) specified in the connection string.