AlwaysOn Availability Group Read Only Routing Script

,

If your objective is to use your secondary node(s) as readable secondary’s, read on.

While implementing my availability group into higher environments, after having it working quite easily in development, I ran into a few issues. After a long weekend, and making sure all the appropriate firewall ports were open, to make the listener, primary and secondary nodes accessible to all of the other components, I found these issues were important; mainly, because the database servers are in a different domain than the other components.

  1. Not all settings are done via the SSMS UI when setting up the Availability Group
  2. The routing URLs may need to use IP addresses
  3. The routing list is needed for proper read only routing

Set the first six (6) variables for your environment and run the script in text output mode. The commands will be generated to properly configure your read only routing. The select statement at the end will show the key settings once the configuration is applied.

* Note: this script configures the primary and a single secondary node.

USE [master]
GO

SET NOCOUNT ON
GO

DECLARE	  @AGName				VARCHAR(40) = 'myAvailabilityGroup' -- Availability Group Name
		, @PrimaryNodeName		VARCHAR(40) = 'ProdDB01'
		, @SecondaryNodeName	VARCHAR(40) = 'ProdDB02'
		, @PrimaryNodeIP		VARCHAR(40) = '10.5.6.10'
		, @SecondaryNodeIP		VARCHAR(40) = '10.6.6.11'
		, @Domain				VARCHAR(40) = '.prod.net'
		, @RouteUsingIP			TINYINT			= 1 -- 1 for True, 0 for False (1 is recommended)
		, @PrimaryRoutingURL	VARCHAR(40) = '' -- gets set by script
		, @SecondaryRoutingURL	VARCHAR(40) = '' -- gets set by script
		, @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 = READ_ONLY));'
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 = READ_ONLY));'
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

/*
SELECT replica_server_name	,endpoint_url	,availability_mode_desc	,failover_mode_desc
	,session_timeout	,primary_role_allow_connections_desc	,secondary_role_allow_connections_desc
	,create_date	,read_only_routing_url
FROM sys.availability_replicas
*/

Rate

5 (1)

Share

Share

Rate

5 (1)