About Client Connection Access to Availability Replicas (SQL Server)

In an AlwaysOn availability group, you can configure one or more availability replicas to allow read-only connections when running under the secondary role (that is, when running as a secondary replica). You can also configure each availability replica to allow or exclude read-only connections when running under the primary role (that is, when running as the primary replica).

To facilitate client access to primary or secondary databases of a given availability group, you should define an availability group listener. By default, the availability group listener directs incoming connections to the primary replica. However, you can configure an availability group to support read-only routing, which enables its availability group listener to redirect the connection requests of read-intent applications to a readable secondary replica. For more information, see Configure Read-Only Routing for an Availability Group (SQL Server).

During a failover, a secondary replica transitions to the primary role and the former primary replica transitions to the secondary role. During the failover process, all client connections to both the primary replica and secondary replicas are terminated. After the failover, when a client reconnects to the availability group listener, the listener reconnects the client to the new primary replica, except for a read-intent connect request. If read-only routing is configured on the client and on the server instances that hosts the new primary replica and on at least one readable secondary replica, read-intent connection requests are re-routed to a secondary replica that supports the type of connection access that the client requires. To ensure a graceful client experience after a failover, it is important to configure connection access for both the secondary and primary roles of every availability replica.

Note

For information about the availability group listener, which handles client connection requests, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).

In This Topic:

  • Types of Connection Access Supported by the Secondary Role

  • Types of Connection Access Supported by the Primary Role

  • How the Connection Access Configuration Affects Client Connectivity

  • Related Tasks

  • Related Content

Types of Connection Access Supported by the Secondary Role

The secondary role supports three alternatives for client connections, as follows:

  • No connections
    No user connections are allowed. Secondary databases are not available for read access. This is the default behavior in the secondary role.

  • Only read-intent connections
    The secondary database(s) are available only for connection for which the Application Intent connection property is set to ReadOnly (read-intent connections).

    For information about this connection property, see SQL Server Native Client Support for High Availability, Disaster Recovery.

  • Allow any read-only connection
    The secondary database(s) are all available for read access connections. This option allows lower versioned clients to connect.

For more information, see Configure Read-Only Access on an Availability Replica (SQL Server).

Arrow icon used with Back to Top link[Top]

Types of Connection Access Supported by the Primary Role

The primary role supports two alternatives for client connections, as follows:

  • All connections are allowed
    Both read-write and read-only connections are allowed to primary databases. This is the default behavior for the primary role.

  • Allow only read-write connections
    When the Application Intent connection property is set to ReadWrite or is not set, the connection is allowed. Connections for which the Application Intent connection string keyword is set to ReadOnly are not allowed. Allowing only read-write connections can help prevent your customers from connecting a read-intent work load to the primary replica by mistake.

    For information about this connection property, see Using Connection String Keywords with SQL Server Native Client.

For more information, see Configure Read-Only Access on an Availability Replica (SQL Server).

Arrow icon used with Back to Top link[Top]

How the Connection Access Configuration Affects Client Connectivity

The connection access settings of a replica determine whether a connection attempt fails or succeeds. The following table summarizes whether a given connection attempt succeeds or fails for each the connection-access setting.

Replica Role

Connection Access Supported on Replica

Connection Intent

Connection-Attempt Result

Secondary

All

Read-intent, read-write, or no connection intent specified

Success

Secondary

None (This is the default secondary behavior.)

Read-intent, read-write, or no connection intent specified

Failure

Secondary

Read-intent only

Read-intent

Success

Secondary

Read-intent only

Read-write or no connection intent specified

Failure

Primary

All (This is the default primary behavior.)

Read-only, read-write, or no connection intent specified

Success

Primary

Read-write

Read-intent only

Failure

Primary

Read-write

Read-write or no connection intent specified

Success

For information about configuring an availability group to accept client connections to its replicas, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).

Example Connection-Access Configuration

Depending on how different availability replicas are configured for connection access, support for client connections might change after an availability group fails over. For example, consider an availability group for which reporting is performed on remote asynchronous-commit secondary replicas. All of the read-only applications for the databases in this availability group set their Application Intent connection property to ReadOnly, so that all read-only connections are read-intent connections.

This example availability group possesses two synchronous-commit replicas at the main computing center and two asynchronous-commit replicas at a satellite site. For the primary role, all the replicas are configured for read-write access, which prevents read-intent connections to the primary replica in all situations. The synchronous commit secondary role uses the default connection-access configuration ("none"), which prevents all client connections under the secondary role. In contrast, the asynchronous commit replicas are configured to permit read-intent connections under the secondary role. The following table summarize this example configuration:

Replica

Commit Mode

Initial Role

Connection Access for Secondary Role

Connection Access for Primary Role

Replica1

Synchronous

Primary

None

Read-write

Replica2

Synchronous

Secondary

None

Read-write

Replica3

Asynchronous

Secondary

Read-intentonly

Read-write

Replica4

Asynchronous

Secondary

Read-intent only

Read-write

Typically, in this example scenario, failovers occur only between the synchronous-commit replicas, and immediately after the failover, read-intent applications are able to reconnect to one of the asynchronous-commit secondary replicas. However, when a disaster occurs at the main computing center both synchronous-commit replicas are lost. The database administrator at the satellite site responds by performing a forced manual failover to an asynchronous-commit secondary replica. The secondary databases on the remaining secondary replica are suspended by the forced failover, making them unavailable for read-only workloads. The new primary replica, which is configured for read-write connections, prevents the read-intent workload from competing with the read-write workload. This means that until the database administrator resumes the secondary databases on the remaining asynchronous-commit secondary replica, read-intent clients cannot connect to any availability replica.

Arrow icon used with Back to Top link[Top]

Arrow icon used with Back to Top link[Top]

Arrow icon used with Back to Top link[Top]

See Also

Concepts

Overview of AlwaysOn Availability Groups (SQL Server)

Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server)

Statistics