Overview and Benefits of Read-Only Routing and How to Configure Read-only routing in AlwaysOn SQL Server 2012 and Sample ConnectionStrings for OLEDB, ODBC and SQLNCI providers

SQL Server 2012/2014 AlwaysOn Offloading Read-only Workloads to Secondary Replicas

1)    Overview and benefits of Read-Only Routing.

Note: This document assumes that you are familiar with SQL Server 2012 Always On Availability Groups and how to setup AlwaysOn Database Availability Groups.

 SQL Server 2012 contains an amazing HA/DR improvement over Database Mirroring technology - AlwaysOn. It provides an ability to split application workflows into Writing and Reading and send them to different SQL Server instances thus improve hardware utilization and increase throughput.

 In AlwaysOn, 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 database 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 "readonly." That is, they must be read-intent connection requests.

 By default, applications are considered writing and if no specific property on intent is specified in the connection string the connection property ApplicationIntent is set to ReadWrite automatically by the SQL Server client. To be able to utilize readable Secondary replicas of AlwaysOn application must specify ReadOnly application intent in the connection string. Of course, a DBA needs to configure readable secondary to accept Read-Only or All connections. We will discuss more about this later.

In a nutshell benefit is, Read-Only Routing allows redirecting application workflows that only read data to Secondary replicas using Availability Group Listener and Application Intent. From client application perspective only three requirements need to be fulfilled:-

Application must use SQL Server 2012 or later client (that supports TDS 7.4 or above protocol)

Application's connection string must point to Availability Group listener in "Server" keyword. 
Warning: Pointing application directly to primary replica will bypass read-only routing.

Application must declare its workflow as read-only by adding a connection string keyword "ApplicationIntent" with value "ReadOnly".

 2)      What type of application/workloads will benefit from this feature

·         SQL Server Reporting Services reports. 

·          All reports hosted in SharePoint.

·         Native Mode installation of Report Server can specify read-only intent and can be serviced by your secondary replicas.  Otherwise, it takes the heavy read workload that typically causes SQL Server blocking and consumes memory and CPU from your primary read/write database.

·         After ETL process, Data processed in primary server, we can generate reports from Secondary server, so Read-Write load will be distributed among both servers. 

How does Read-only Routing work?

 Read-only routing uses the following algorithm to locate a readable secondary:-

•       Client connects to an Availability Group listener endpoint.
•       Client specifies ApplicationIntent=ReadOnly in the connection string, this is transmitted to the server during login.
•       On server side, server checks that incoming connection is using an Availability Group listener endpoint. Otherwise, read-only routing is disabled.
•       Server checks the target database and determines if it is part of an availability group.
•       If database is in an availability group, it checks if the read_only_routing_list is set on the primary replica.
•       If list is not set, routing is disabled.
•       If list is set, then routing is enforced
•       SQL Server then enumerates the replicas in the read_only_routing_list and checks each replica in the list.
•       The first replica it finds that is synchronizing and accepts readers (Allow_connections=read_only or All is chosen as the routing target.
•      Server next reads the read_only_routing_url from this replica and sends this response to the client.
•      Client reads routing URL and re-directs to the readable secondary instance.

3   3 )      T-SQL to configure Read-only routing lists and URL

 Definition of No, Yes ad Read-Intent  in Readable Secondary only

·         No option: By default Read-only Secondary Settings remains No.

·         You can choose either Yes or Read-intent only options. You can run the read workload on the secondary replica with either option, but there are few differences:

·         Yes option: Supported TDS clients can connect to the secondary replica explicitly to run the reporting workload. The client is responsible for ensuring that it is connecting to readable secondary, because the roles of replicas can change in case of failover. The key benefit of this option is that older clients can run reporting workloads on the readable secondary.

·         Read-intent-only option: Only connections that have the property ApplicationIntent set to ReadOnly are accepted. The word intent indicates that you want to use the connection as read-only; it does not prevent read/write connections. It is still possible to connect using a read/write application if the ApplicationIntent option is set to Read-only, but the application fails on the first DML or DDL operation. This option allows clients to automatically connect to an available readable secondary, and you can use it to prevent read workloads from running on the primary replica.

The query will print Routing URL and Routing list configured on the Server

Use master go select g.name, r1.replica_server_name, l.routing_priority, r2.replica_server_name, r2.read_only_routing_url  from sys.availability_read_only_routing_lists as l  join sys.availability_replicas as r1 on l.replica_id = r1.replica_id  join sys.availability_replicas as r2 on l.read_only_replica_id = r2.replica_id  join sys.availability_groups as g on r1.group_id = g.group_id

If the output is blank it mean Routing URL and Routing List is not configured on the server.

        How we will set Read-only Routing URL and Read-Only Routing List

1. Read-Only Routing URL Query

 execute this query on primary replica) Below Query Assumes you have 2 node cluster. 

You Need to change Node Name and AG Name accordingly. 

Use Master GO ALTER AVAILABILITY GROUP [SQL00XXXXAG01] MODIFY REPLICA ON N'Win2k8r2-1' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL= 'tcp://Win2k8r2-1.stl.com:1433')) Go ALTER AVAILABILITY GROUP [SQL00XXXXAG01] MODIFY REPLICA ON N'Win2k8r2-2' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'tcp://Win2k8r2-2.stl.com:1433')) Go

Note: N'T tcp://Win2k8r2-2.stl.com:1433  is not an Endpoint URL for AlwaysOn.(Common misconception) and 1433 is Instance Port No. 

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

2. Read-Only Routing List Format-1

Use master go ALTER AVAILABILITY GROUP [SQL00XXXDAG01] MODIFY REPLICA ON N'Win2k8r2-1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=( N'Win2k8r2-2')));  Go ALTER AVAILABILITY GROUP [SQL00XXXDAG01] MODIFY REPLICA ON N'Win2k8r2-2' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=( N'Win2k8r2-1')));

    Or

 Read-Only Routing List Format -2 (Please use this format and execute this query on primary replica) Below Query Assumes you have 2 node cluster.. Kindly change Node Name and AG Name accordingly. 

Use master go ALTER AVAILABILITY GROUP [SQL00XXXDAG01] MODIFY REPLICA ON N'Win2k8r2-1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=( N'Win2k8r2-2', N'Win2k8r2-1')));  Go ALTER AVAILABILITY GROUP [SQL00XXXDAG01] MODIFY REPLICA ON N'Win2k8r2-2' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST= (N’Win2k8r2-1', 'Win2k8r2-2')));

After successful configuration we will get output like this with Above DMVs:-

 1)      Sample connection strings for OLEDB, ODBC and SQLNCI  providers

===========================================================

           SSRS report data source is configured for Read-Only Routing:

 The above screen shot shows the properties of the shared data source in my report.  Notice the use of the Availability Group Listener name SQL1 as the Data Source property and the ApplicationIntent=ReadOnly option.  These are both required for SQL Server to use the Read-Only routing list which we configured earlier.

Another view of the data source properties in SQL Server Data Tools:

 

 Using SQLCMD:- 
==============

By ODBC Database Source:-

 Since ApplicationIntent is ReadWrite so, it is showing TXXXXX3DB

Now will make ODBC connection for ApplicationIntent = Readonly

Now this is pointing to TXXXXX43DA

Determine the correct Routing URL

The format of the URL must follow regular URL specification = <protocol>://<host>:<port>.
SQL Server currently supports 4 ways to specify the target replica in the URL:

a)      Fully qualified domain name.

E.g. à tcp://myreplica.mydomain.com:1433. This is the recommended way.

b)      Relative domain name – E.g. à tcp://myreplica:1433

c)       IP v4 address –                   E.g. à tcp://10.0.2.3:1433

d)      IP v6 address –                   E.g. à tcp://[2001:4898:f0:f020:79ee:9e2f:4319:60b6]:1433

The easiest way to determine the correct read-only routing URL is to run the below script on the SQL Server instance that is being configured for read-only routing. You have to run this on Primary and all secondary instances.

Calculating read_only_routing_url for AlwaysOn 

When setting up read-only routing for SQL Server AlwaysOn, you have to configure each availability replica with a read-only routing URL.
The read-only routing URL is just a “pointer” to the replica and determines what the replica will report back to the client driver when routing to the replica.
Suppose you have an AlwaysOn cluster with 3 cluster nodes, Node1, Node2, and Node3.   On each of these nodes you will have a SQL Server instance running.   For very simple configurations, there will be one default SQL Server instance listening on tcp port 1433 on each node.  With a more complex configuration you may have clustered SQL Server instances on these nodes.

Read-only routing uses the following algorithm to locate a readable secondary:

• Client connects to an Availability Group listener endpoint.
• Note this endpoint always points to the primary replica for the availability group
• Client specifies ApplicationIntent=ReadOnly in the connection string, this is transmitted to the server during login
• On server side, server checks that incoming connection is using an Availability Group listener endpoint
• Otherwise, read-only routing is disabled
• Server checks the target database and determines if it is in an availability group
• If database is in an availability group, we check if the read_only_routing_list is set on the primary replica
• If list is not set, routing is disabled
• If list is set, then routing is enforced
• Server then enumerates the replicas in the read_only_routing_list and checks each replica in the list
• First replica it finds that is synchronizing and accepts readers (allow_connections=read_only or all) is the routing target
• Server next reads the read_only_routing_url from this replica and sends this response to the client
• Client reads routing URL and re-directs to the readable secondary instance

Given above, you need to ensure that the read_only_routing_url is properly set for each replica when enabling read-only routing, otherwise the client will be redirected to the wrong instance.

Below created a T-SQL script that you can run against any instance and it will tell you what the appropriate read_only_routing_url is for the instance.

The script will detect if the instance is clustered or not then generate appropriate URL.
 Script is included below, enjoy!  =>

 ---Read-only routing URL generation script.

 ---Connect to each replica in your AlwaysOn cluster and run this script to get the read_only_routing_url for the replica.

-- Then set this to the read_only_routing_url for the availability group replica =>

--   Alter availability group MyAvailabilityGroup modify replica on N'ThisReplica' with (secondary_role(read_only_routing_url=N'<url>'))

print 'Read-only-routing url Generation script'
Print '**************************************************************'

print 'This SQL Server instance version is [' + cast(serverproperty('ProductVersion') as varchar(256)) + ']'
if (ServerProperty('IsClustered') = 1)
begin
    print 'This SQL Server instance is a clustered SQL Server instance.'
end
else
begin
    print 'This SQL Server instance is a standard (not clustered) SQL Server instance.'
end
if (ServerProperty('IsHadrEnabled') = 1)
begin
    print 'This SQL Server instance is enabled for AlwaysOn.'
end
else
begin
    print 'This SQL Server instance is NOT enabled for AlwaysOn.'
end
-- Detect SQL Azure instance.
declare @is_sql_azure bit
set @is_sql_azure = 0
begin try
    set @is_sql_azure = 1
    exec('declare @i int set @i = sql_connection_mode()')
    print 'This SQL Server instance is a Sql Azure instance.'
end try
begin catch
    set @is_sql_azure = 0
    print 'This SQL Server instance is NOT a Sql Azure instance.'
end catch
-- Check that this is SQL 11 or later, otherwise fail fast.
if (@@microsoftversion / 0x01000000 < 11 or @is_sql_azure > 0)
begin
    print 'This SQL Server instance does not support read-only routing, existing script.'
end

else
begin -- if server supports read-only routing
    -- Fetch the dedicated admin connection (dac) port.
    -- Normally it's always port 1434, but to be safe here we fetch it from the instance.
    -- We use this later to exclude the admin port from read_only_routing_url.
    declare @dac_port int
    declare @reg_value varchar(255)
    exec xp_instance_regread
        N'HKEY_LOCAL_MACHINE',
        N'SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp',
        N'TcpDynamicPorts',
        @reg_value output
    set @dac_port = cast(@reg_value as int)
    print 'This SQL Server instance DAC (dedicated admin) port is ' + cast(@dac_port as varchar(255))
    if (@dac_port = 0)
    begin
        print 'Note a DAC port of zero means the dedicated admin port is not enabled.'
    end
    -- Fetch ListenOnAllIPs value.
    -- If set to 1, this means the instance is listening to all IP addresses.
    -- If set to 0, this means the instance is listening to specific IP addresses.
    declare @listen_all int
    exec xp_instance_regread
        N'HKEY_LOCAL_MACHINE',
        N'SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\SuperSocketNetLib\Tcp',
        N'ListenOnAllIPs',
        @listen_all output
    if (@listen_all = 1)
    begin
        print 'This SQL Server instance is listening to all IP addresses (default mode).'
    end
    else
    begin
        print 'This SQL Server instance is listening to specific IP addresses (ListenOnAllIPs is disabled).'
    end
    -- Check for dynamic port configuration, not recommended with read-only routing.
    declare @tcp_dynamic_ports varchar(255)
    exec xp_instance_regread
        N'HKEY_LOCAL_MACHINE',
        N'SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\SuperSocketNetLib\Tcp\IPAll',
        N'TcpDynamicPorts',
        @tcp_dynamic_ports output
    if (@tcp_dynamic_ports = '0')
    begin
        print 'This SQL Server instance is listening on a dynamic tcp port, this is NOT A RECOMMENDED CONFIGURATION when using read-only routing, because the instance port can change each time the instance is restarted.'
    end
    else
    begin
        print 'This SQL Server instance is listening on fixed tcp port(s) (it is not configured for dynamic ports), this is a recommended configuration when using read-only routing.'
    end
    -- Calculate the server domain and instance FQDN.
    -- We use @server_domain later to build the FQDN to the clustered instance.
    declare @instance_fqdn varchar(255)
    declare @server_domain varchar(255)
    -- Get the instance FQDN using the xp_getnetname API
    -- Note all cluster nodes must be in same domain, so this works for calculating cluster FQDN.
    set @instance_fqdn = ''
    exec xp_getnetname @instance_fqdn output, 1
    -- Remove embedded null character at end if found.
    declare @terminator int
    set @terminator = charindex(char(0), @instance_fqdn) - 1
    if (@terminator > 0)
    begin
        set @instance_fqdn = substring(@instance_fqdn, 1, @terminator)
    end
    -- Build @server_domain using @instance_fqdn.
    set @server_domain = @instance_fqdn
    -- Remove trailing portion to extract domain name.
    set @terminator = charindex('.', @server_domain)
    if (@terminator > 0)
    begin
        set @server_domain = substring(@server_domain, @terminator+1, datalength(@server_domain))
    end
    print 'This SQL Server instance resides in domain ''' +  @server_domain + ''''
    if (ServerProperty('IsClustered') = 1)
    begin
        -- Fetch machine name, which for a clustered SQL instance returns the network name of the virtual server.
        -- Append @server_domain to build the FQDN.
        set @instance_fqdn = cast(serverproperty('MachineName') as varchar(255)) + '.' + @server_domain
    end
    declare @ror_url varchar(255)
    declare @instance_port int
    set @ror_url = ''
    -- Get first available port for instance.
    select
    top 1    -- Select first matching port
    @instance_port = port
    from sys.dm_tcp_listener_states
    where
    type=0 -- Type 0 = TSQL (to avoid mirroring endpoint)
    and
    state=0    --  State 0 is online
    and
    port <> @dac_port -- Avoid DAC port (admin port)
    and
    -- Avoid availability group listeners
    ip_address not in (select ip_address from sys.availability_group_listener_ip_addresses agls)
    group by port 
    order by port asc  -- Pick first port in ascending order
    -- Check if there are multiple ports and warn if this is the case.
    declare @list_of_ports varchar(max)
    set @list_of_ports = ''
    select
    @list_of_ports = @list_of_ports +
        case datalength(@list_of_ports)
        when 0 then cast(port as varchar(max))
        else ',' +  cast(port as varchar(max))
        end
    from sys.dm_tcp_listener_states
    where
    type=0    --     Type 0 = TSQL (to avoid mirroring endpoint)
    and
    state=0    --  State 0 is online
    and
    port <> @dac_port -- Avoid DAC port (admin port)
    and
    -- Avoid availability group listeners
    ip_address not in (select ip_address from sys.availability_group_listener_ip_addresses agls)
    group by port 
    order by port asc
    print 'This SQL Server instance FQDN (Fully Qualified Domain Name) is ''' + @instance_fqdn + ''''
    print 'This SQL Server instance port is ' + cast(@instance_port as varchar(10))
    set @ror_url = 'tcp://' + @instance_fqdn + ':' + cast(@instance_port as varchar(10))
    print '****************************************************************************************************************'
    print 'The read_only_routing_url for this SQL Server instance is ''' + @ror_url + ''''
    print '****************************************************************************************************************'
    -- If there is more than one instance port (unusual) list them out just in case.
    if (charindex(',', @list_of_ports) > 0)
    begin
        print 'Note there is more than one instance port, the list of available instance ports for read_only_routing_url is (' + @list_of_ports + ')'
        print 'The above URL just uses the first port in the list, but you can use any of these available ports.'
    end
end -- if server supports read-only routing
go

Output from PRIMARY

# Read-Only-Routing URL Generation Script #

*******************************************

This SQL Server instance version is [11.0.2100.60]

This SQL Server instance is a standard (not clustered) SQL Server instance.

This SQL Server instance is enabled for AlwaysON.

This SQL Server instance DAC (dedicated admin) port is 1434

This SQL Server instance is listening on all IP addresses (default mode).

This SQL Server instance is listening on fixed tcp port(s) (it is NOT configured for dynamic ports), this is a recommended configuration when using read-only routing.

This SQL Server instance resides in domain 'hq.target.com'

This SQL Server instance FQDN (Fully Qualified Domain Name) is 'TCSQL0043DA.hq.target.com'

This SQL Server instance port is 1433

**********************************************************************************************

The read_only_routing_url for this SQL Server instance is 'tcp://TCSQL00XXDA.XX.XXXX.com:1433'

**********************************************************************************************

Output from SECONDARY

# Read-Only-Routing URL Generation Script #

*******************************************

This SQL Server instance version is [11.0.2100.60]

This SQL Server instance is a standard (not clustered) SQL Server instance.

This SQL Server instance is enabled for AlwaysON.

This SQL Server instance DAC (dedicated admin) port is 1434

This SQL Server instance is listening on all IP addresses (default mode).

This SQL Server instance is listening on fixed tcp port(s) (it is NOT configured for dynamic ports), this is a recommended configuration when using read-only routing.

This SQL Server instance resides in domain 'hq.target.com'

This SQL Server instance FQDN (Fully Qualified Domain Name) is 'TESQL0043DB.hq.target.com'

This SQL Server instance port is 1433

**********************************************************************************************

The read_only_routing_url for this SQL Server instance is 'tcp://TESQL00XX3DB.XX.XXXXX.com:1433'

**********************************************************************************************

Make note of the highlighted output from all the replica servers. Now we need to set Read-only Routing URL and Read-Only Routing List.

Inherited from 

http://blogs.msdn.com/b/mattn/archive/2012/04/25/calculating-read-only-routing-url-for-alwayson.aspx