High availability and disaster recovery on Linux and macOS
The ODBC drivers for Linux and macOS support Always On availability groups. For more information about Always On availability groups, see:
You can specify the availability group listener of a particular availability group in the connection string. If an ODBC application on Linux or macOS is connected to a database in an availability group that fails over, the original connection is broken. The application must open a new connection to continue work after the failover.
The ODBC drivers on Linux and macOS iterate sequentially through all IP addresses associated with a DNS hostname, if you aren't connecting to an availability group listener. If the DNS server's first returned IP address isn't connectable, these iterations can be time consuming.
When you're connecting to an availability group listener, the driver attempts to establish connections to all IP addresses in parallel. If a connection attempt succeeds, the driver discards any pending connection attempts.
Because a connection can fail due to an availability group failover, you should implement connection retry logic. Retry a failed connection until it reconnects. Increasing the connection timeout and implementing connection retry logic increases the chance of connecting to an availability group.
Connect with MultiSubnetFailover
MultiSubnetFailover=Yes when you're connecting to a SQL Server 2012 (11.x) availability group listener or SQL Server 2012 (11.x) failover cluster instance.
MultiSubnetFailover enables faster failover for all availability groups and failover cluster instances in SQL Server 2012 (11.x).
This connection property also significantly reduces failover time for single and multi-subnet Always On topologies. During a multi-subnet failover, the client attempts connections in parallel. During a subnet failover, the driver aggressively retries the TCP connection.
MultiSubnetFailover connection property indicates that the application is being deployed in an availability group or failover cluster instance. The driver tries to connect to the database on the primary SQL Server instance by trying to connect to all the IP addresses.
When you connect with
MultiSubnetFailover=Yes, the client retries TCP connection attempts faster than the operating system's default TCP retransmit intervals.
MultiSubnetFailover=Yes enables faster reconnection after failover of either an Always On availability group, or an Always On failover cluster instance.
MultiSubnetFailover=Yes applies to both single- and multi-subnet availability groups and failover cluster instances.
MultiSubnetFailover=Yes when you're connecting to an availability group listener or failover cluster instance. Otherwise, your application's performance can be negatively affected.
When you're connecting to a server in an availability group or failover cluster instance:
MultiSubnetFailover=Yesto improve performance when you're connecting to a single subnet or multi-subnet availability group.
Specify the availability group listener of the availability group as the server in your connection string.
You can't connect to a SQL Server instance configured with more than 64 IP addresses.
Both SQL Server Authentication or Kerberos Authentication can be used with
MultiSubnetFailover=Yes, without affecting the behavior of the application.
You can increase the value of
loginTimeoutto accommodate for failover time and reduce the application's connection retry attempts.
Distributed transactions aren't supported.
If read-only routing isn't in effect, connecting to a secondary replica location in an availability group fails in the following situations:
If the secondary replica location isn't configured to accept connections.
If an application uses
ApplicationIntent=ReadWriteand the secondary replica location is configured for read-only access.
A connection fails if a primary replica is configured to reject read-only workloads, and the connection string contains
Specify application intent
You can specify the keyword
ApplicationIntent in your connection string. The assignable values are
ReadWrite (the default) or
When you set
ApplicationIntent=ReadOnly, the client requests a read workload when connecting. The server enforces the intent at connection time, and during a
USE database statement.
ApplicationIntent keyword doesn't work with legacy read-only databases.
Targets of ReadOnly
When a connection chooses
ReadOnly, the connection is assigned to any of the following special configurations that might exist for the database:
Always On. A database can allow or disallow read workloads on the targeted availability group database. This choice is controlled by using the
ALLOW_CONNECTIONSclause of the
If none of those special targets are available, the regular database is read from.
ApplicationIntent keyword enables read-only routing.
Read-only routing is a feature that can ensure the availability of a read-only replica of a database. To enable read-only routing, all of the following apply:
You must connect to an Always On availability group listener.
ApplicationIntentconnection string keyword must be set to
The database administrator must configure the availability group to enable read-only routing.
Multiple connections that each use read-only routing might not all connect to the same read-only replica. Changes in database synchronization or changes in the server's routing configuration can result in client connections to different read-only replicas.
You can ensure that all read-only requests connect to the same read-only replica by not passing an availability group listener to the
Server connection string keyword. Instead, specify the name of the read-only instance.
Read-only routing might take longer than connecting to the primary. This is because read-only routing first connects to the primary, and then looks for the best available readable secondary. Due to these multiple steps, you should increase your
login timeout to at least 30 seconds.
Two ODBC connection string keywords support Always On availability groups:
For more information about ODBC connection string keywords, see Using connection string keywords with SQL Server Native Client.
The equivalent connection attributes are:
For more information about ODBC connection attributes, see SQLSetConnectAttr.
An ODBC application that uses Always On availability groups can use one of two functions to make the connection: