Share via

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

  • Article

To configure an AlwaysOn availability group to support read-only routing in SQL Server 2014, you can use either Transact-SQL or PowerShell. Read-only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an available AlwaysOn readable secondary replica (that is, a replica that is configured to allow read-only workloads when running under the secondary role). To support read-only routing, the availability group must possess an availability group listener. Read-only clients must direct their connection requests to this listener, and the client's connection strings must specify the application intent as "read-only." That is, they must be read-intent connection requests.

Note

For information about how to configure a readable secondary replica, see Configure Read-Only Access on an Availability Replica (SQL Server).

Note

Configuring read-only routing is not supported by SQL Server Management Studio.

Before You Begin

Prerequisites

What Replica Properties Do you Need to Configure to Support Read-Only Routing?

  • For each readable secondary replica that is to support read-only routing, you need to specify a read-only routing URL. This URL takes effect only when the local replica is running under the secondary role. The read-only routing URL must be specified on a replica-by-replica basis, as needed. Each read-only routing URL is used for routing read-intent connection requests to a specific readable secondary replica. Typically, every readable secondary replica is assigned a read-only routing URL.

    For information about calculating the read-only routing URL for an availability replica, see Calculating read_only_routing_url for AlwaysOn.

  • For each availability replica that you want to support read-only routing when it is the primary replica, you need to specify a read-only routing list. A given read-only routing list takes effect only when the local replica is running under the primary role. This list must be specified on a replica-by-replica basis, as needed. Typically, each read-only routing list would contain every read-only routing URL, with the URL of the local replica at the end of the list.

    Note

    Read-intent connection requests are routed to the first available readable secondary on the read-only routing list of the current primary replica. There is no load balancing.

Note

For information about availability group listeners and more information about read-only routing, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).

Security

Permissions

Task Permissions
To configure replicas when creating an availability group Requires membership in the sysadmin fixed server role and either CREATE AVAILABILITY GROUP server permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.
To modify an availability replica Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.

Using Transact-SQL

To Configure read-only routing

Note

For a code example, see Example (Transact-SQL), later in this section.

  1. Connect to the server instance that hosts the primary replica.

  2. If you are specifying a replica for a new availability group, use the CREATE AVAILABILITY GROUPTransact-SQL statement. If you are adding or modifying a replica for an existing availability group, use the ALTER AVAILABILITY GROUPTransact-SQL statement.

    • To configure read-only routing for the secondary role, in the ADD REPLICA or MODIFY REPLICA WITH clause, specify the SECONDARY_ROLE option, as follows:

      SECONDARY_ROLE ( READ_ONLY_ROUTING_URL ='TCP://system-address:port')

      The parameters of the read-only routing URL are as follows:

      system-address
      Is a string, such as a system name, a fully qualified domain name, or an IP address, that unambiguously identifies the destination computer system.

      port
      Is a port number that is used by the Database Engine of the SQL Server instance.

      For example: SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433')

      In a MODIFY REPLICA clause the ALLOW_CONNECTIONS is optional if the replica is already configured to allow read-only connections.

      For more information, see Calculating read_only_routing_url for AlwaysOn.

    • To configure read-only routing for the primary role, in the ADD REPLICA or MODIFY REPLICA WITH clause, specify the PRIMARY_ROLE option, as follows:

      PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST =('server' [ ,...n ] ))

      where, server identifies a server instance that hosts a read-only secondary replica in the availability group.

      For example: PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('Server1','Server2'))

      Note

      You must set the read-only routing URL before configuring the read-only routing list.

Example (Transact-SQL)

The following example modifies two availability replicas of an existing availability group, AG1 to support read-only routing if one of these replicas currently owns the primary role. To identify the server instances that host the availability replica, this example specifies the instance names-COMPUTER01 and COMPUTER02.

ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER01' WITH   
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));  
ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER01' WITH   
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433'));  
  
ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER02' WITH   
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));  
ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER02' WITH   
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433'));  
  
ALTER AVAILABILITY GROUP [AG1]   
MODIFY REPLICA ON  
N'COMPUTER01' WITH   
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01')));  
  
ALTER AVAILABILITY GROUP [AG1]   
MODIFY REPLICA ON  
N'COMPUTER02' WITH   
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));  
GO

Using PowerShell

To Configure read-only routing

Note

For a code example, see Example (PowerShell), later in this section.

  1. Set default (cd) to the server instance that hosts the primary replica.

  2. When adding an availability replica to an availability group, use the New-SqlAvailabilityReplica cmdlet. When modifying an existing availability replica, use the Set-SqlAvailabilityReplica cmdlet. The relevant parameters are as follows:

    • To configure read-only routing for the secondary role, specify the ReadonlyRoutingConnectionUrl"url" parameter.

      where, url is the connectivity fully-qualified domain name (FQDN) and port to use when routing to the replica for read-only connections. For example: -ReadonlyRoutingConnectionUrl "TCP://DBSERVER8.manufacturing.Adventure-Works.com:7024"

      For more information, see Calculating read_only_routing_url for AlwaysOn.

    • To configure connection access for the primary role, specify ReadonlyRoutingList"server" [ ,...n ], where server identifies a server instance that hosts a read-only secondary replica in the availability group. For example: -ReadOnlyRoutingList "SecondaryServer","PrimaryServer"

      Note

      You must set the read-only routing URL of a replica before configuring its read-only routing list.

    Note

    To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL Server PowerShell environment. For more information, see Get Help SQL Server PowerShell.

To set up and use the SQL Server PowerShell provider, see SQL Server PowerShell Provider and Get Help SQL Server PowerShell.

Example (PowerShell)

The following example configures the primary replica and one secondary replica in an availability group for read-only routing. First, the example assigns a read-only routing URL to each replica. Then it sets the read-only routing list on the primary replica. Connections with the "ReadOnly" property set in the connection string will be redirected to the secondary replica. If this secondary replica is not readable (as determined by the ConnectionModeInSecondaryRole setting), the connection will be directed back to the primary replica.

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg  
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"  
$secondaryReplica = Get-Item "AvailabilityReplicas\SecondaryServer"  
  
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://PrimaryServer.domain.com:1433" -InputObject $primaryReplica  
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://SecondaryServer.domain.com:1433" -InputObject $secondaryReplica  
Set-SqlAvailabilityReplica -ReadOnlyRoutingList "SecondaryServer","PrimaryServer" -InputObject $primaryReplica

Follow Up: After Configuring Read-Only Routing

Once the current primary replica and the readable secondary replicas are configured to support read-only routing in both roles, the readable secondary replicas can receive read read-intent connection requests from clients that connect via the availability group listener.

Tip

When using the bcp Utility or sqlcmd Utility, you can specify read-only access to any secondary replica that is enabled for read-only access by specifying the -K ReadOnly switch.

Requirements and Recommendations for Client Connection-Strings

For a client application to use read-only routing, its connection string must satisfy the following requirements:

  • Use the TCP protocol.

  • Set the application intent attribute/property to readonly.

  • Reference the listener of an availability group that is configured to support read-only routing.

  • Reference a database in that availability group.

In addition, we recommend that connection strings enable multi-subnet failover, which supports a parallel client thread for each replica on each subnet. This minimizes client reconnection time after a failover.

The syntax for a connection string depends on the SQL Server provider an application is using. The following example connection string for the .NET Framework Data Provider 4.0.2 for SQL Server illustrates the parts of a connection string that are required and recommended to work for read-only routing.

Server=tcp:MyAgListener,1433;Database=Db1;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True

For more information about read-only application intent and read-only routing, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).

If Read-Only Routing is Not Working Correctly

For information about troubleshooting a read-only routing configuration, see Read-Only Routing is Not Working Correctly.

Related Tasks

To view read-only routing configurations

To configure client connection access

To use connection strings in applications

Related Content

See Also

Overview of AlwaysOn Availability Groups (SQL Server)
Overview of AlwaysOn Availability Groups (SQL Server)
Active Secondaries: Readable Secondary Replicas (AlwaysOn Availability Groups)
About Client Connection Access to Availability Replicas (SQL Server)
Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server)