当在 AlwaysOn secondary role 配置 read-only routing 时候，需要配置 read-only routing URL, 使用下面的脚本可以生成这个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 '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

 

 

结果如下：

This SQL Server instance version is [13.0.4001.0]

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

This SQL Server instance is enabled for AlwaysOn.

This SQL Server instance is NOT a Sql Azure instance.

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

This SQL Server instance is listening to 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 'efld.io'

This SQL Server instance FQDN (Fully Qualified Domain Name) is 'server1.consoto.com'

This SQL Server instance port is 1433

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

The read_only_routing_url for this SQL Server instance is 'tcp://server1.consoto.com:1433'

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