Calculating read_only_routing_url for AlwaysOn

Article
04/25/2012

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.

I 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 an 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 script v.2012.1.24.1'

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, exiting 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

Comments

Anonymous
April 26, 2012
Also, if you test this script and find any errors, please let me know!
Anonymous
July 01, 2013
Very cool and handy script. thanks Matt
Anonymous
August 11, 2013
Super helpful! Thanks a lot!
Anonymous
May 02, 2014
Was this setup in Azure?
Anonymous
July 13, 2014
Hi Matt, That's great thanks. One note, generally string concatenation via SELECT @var = @var + column etc. isn't considered safe. Better perhaps to replace that chunk of code with: set @list_of_ports = (select cast(port as varchar(max)) + ', ' as 'data()' 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 ip_address not in (select ip_address from sys.availability_group_listener_ip_addresses agls) group by port order by port asc for xml path('')); if len(@list_of_ports) > 0 SET @list_of_ports = substring(@list_of_ports,1,len(@list_of_ports) - 1); Regards, Greg
Anonymous
July 13, 2014
Also, instead of using the code like this to get the FQDN and then having to extract the domain name from it: exec xp_getnetname @instance_fqdn output, 1 Couldn't you have avoided that by calling: exec xp_getnetname @server_domain output, 2 (To just retrieve the domain part)
Anonymous
November 26, 2015
Isn't this url not just the same as the Endpoint URL, except with a different port number?
Anonymous
December 29, 2015
Great script!!!! Perfect is the enemy of good.
Anonymous
January 19, 2016
Can you please put your script in a standard script format, copy/paste is messing up the script.

Share via

Calculating read_only_routing_url for AlwaysOn

Comments

Additional resources