Testing AlwaysOn routing URL using SQLCMD

Article
09/03/2014

You have created a listener and have configured routing URL for the replicas in the AlwaysOn group. When you are trying to check if the routing URL is working as expected by trying to connect from SQLCMD using the below command:

sqlcmd -S Listenername,portnumber -d databasename -K ReadOnly

you are getting the below error message :

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Two or more redirections have occurred. Only one redirection per login is allowed.

The things which you should be checking are :

1. Make sure that you are able to make connection using the listener name.

2. Make sure that the port on which listener is listening is open in Windows firewall.

3. Use the below query to find the routing URL :

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

4. Update the routing URL for each of the servers participating in AlwaysOn:

--Set ROR URL’s

ALTER AVAILABILITY GROUP [GAG]

Modify Replica on

N'GGaurav-N01' with

(Secondary_Role (READ_ONLY_ROUTING_URL = N'tcp://GGaurav-N01.ft.cp.mt.com:1433'))

ALTER AVAILABILITY GROUP [GAG]

Modify Replica on

N'GGaurav-N03' with

(Secondary_Role (READ_ONLY_ROUTING_URL = N'tcp://ggaurav-n03.ft.cp.mt.com:1433'))

ALTER AVAILABILITY GROUP [GAG]

Modify Replica on

N'GGaurav-N01' with

(Primary_Role (READ_ONLY_ROUTING_LIST =('GGaurav-N03','GGaurav-N01')))

5. The below query should have the same routing URL as we got in step 3

SELECT ag.name as "Availability Group", ar.replica_server_name as "When Primary Replica Is",

rl.routing_priority as "Routing Priority", ar2.replica_server_name as "RO Routed To",

ar.secondary_role_allow_connections_desc, ar2.read_only_routing_url

FROM sys.availability_read_only_routing_lists rl

inner join sys.availability_replicas ar on rl.replica_id = ar.replica_id

inner join sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id

inner join sys.availability_groups ag on ar.group_id = ag.group_id

ORDER BY ag.name, ar.replica_server_name, rl.routing_priority

6. Check if the routing is working using SQLCMD :

sqlcmd -S Listenername,portnumber -d databasename -K ReadOnly

Happy Learning & Troubleshooting!!

Disclaimer: Everything here, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.

Testing AlwaysOn routing URL using SQLCMD

Additional resources