DataGinger.com

Read-only routing refers to the ability of SQL Server to route incoming read-intent connection requests, which are directed to an availability group listener, to an available readable secondary replica. I find this little script very useful when I want to automate and and same time while limiting the human input errors during Read-Only Routing configuration.

Make sure to update the parameters’ value as per your AG and server details

USE [master]
GO
 
SET NOCOUNT ON
GO
 
DECLARE   @AGName               VARCHAR(40) = 'Your AG Name'      
        , @PrimaryNodeName      VARCHAR(40) = 'Your Primary Replica'     
        , @SecondaryNodeName    VARCHAR(40) = 'Your Secondary Replica'     
        , @PrimaryNodeIP        VARCHAR(40) = '10.5.6.10'  -- Not required unless marked 1 for @RouteUsingIP below
        , @SecondaryNodeIP      VARCHAR(40) = '10.6.6.11'  -- Not required unless marker 1 for @RouteUsingIP below
        , @Domain		VARCHAR(40) = '.Domain.com'
        , @RouteUsingIP         TINYINT     = 0            -- 1 for True, 0 for False (1 is recommended)
        , @PrimaryRoutingURL    VARCHAR(40) = ''
        , @SecondaryRoutingURL  VARCHAR(40) = ''
        , @SQLCommand           VARCHAR(2000)
 
 
IF @RouteUsingIP > 0
BEGIN
    SET @PrimaryRoutingURL = @PrimaryNodeIP
    SET @SecondaryRoutingURL = @SecondaryNodeIP
END
ELSE
BEGIN
    SET @PrimaryRoutingURL = @PrimaryNodeName + @Domain
    SET @SecondaryRoutingURL = @SecondaryNodeName + @Domain
END
 
SET @SQLCommand = N'
ALTER AVAILABILITY GROUP ['+@AGName+'] MODIFY REPLICA ON
N'''+@PrimaryNodeName+''' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL));'
PRINT @SQLCommand
 
SET @SQLCommand = N'
ALTER AVAILABILITY GROUP ['+@AGName+'] MODIFY REPLICA ON
N'''+@PrimaryNodeName+''' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N''TCP://'+@PrimaryRoutingURL+':1433''));'
PRINT @SQLCommand
 
SET @SQLCommand = N'
ALTER AVAILABILITY GROUP ['+@AGName+'] MODIFY REPLICA ON
N'''+@SecondaryNodeName+''' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL));'
PRINT @SQLCommand
 
SET @SQLCommand = N'
ALTER AVAILABILITY GROUP ['+@AGName+'] MODIFY REPLICA ON
N'''+@SecondaryNodeName+''' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N''TCP://'+@SecondaryRoutingURL+':1433''));'
PRINT @SQLCommand
 
SET @SQLCommand = N'
ALTER AVAILABILITY GROUP ['+@AGName+'] MODIFY REPLICA ON
N'''+@PrimaryNodeName+''' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('''+@SecondaryNodeName+''','''+@PrimaryNodeName+''')));'
PRINT @SQLCommand
 
SET @SQLCommand = N'
ALTER AVAILABILITY GROUP ['+@AGName+'] MODIFY REPLICA ON
N'''+@SecondaryNodeName+''' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('''+@PrimaryNodeName+''','''+@SecondaryNodeName+''')));'
PRINT @SQLCommand

Read Full Post »