
High availability in SQL Server 2019
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.