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).

[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).

[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.

[Top]

Related Tasks

• Configure Read-Only Access on an Availability Replica (SQL Server)

• Configure Read-Only Routing for an Availability Group (SQL Server)

• Monitor Availability Groups (Transact-SQL)

• View Availability Replica Properties (SQL Server)

• Use the New Availability Group Dialog Box (SQL Server Management Studio)

[Top]

Related Content

• Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery

• SQL Server AlwaysOn Team Blog: The official SQL Server AlwaysOn Team Blog

[Top]

See Also

Concepts

Overview of AlwaysOn Availability Groups (SQL Server)

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

Statistics