Technical Article

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  @AGNameVARCHAR(40) = 'myAvailabilityGroup' -- Availability Group Name
, @PrimaryNodeNameVARCHAR(40) = 'ProdDB01'
, @SecondaryNodeNameVARCHAR(40) = 'ProdDB02'
, @PrimaryNodeIPVARCHAR(40) = '10.5.6.10'
, @SecondaryNodeIPVARCHAR(40) = '10.6.6.11'
, @DomainVARCHAR(40) = '.prod.net'
, @RouteUsingIPTINYINT= 1 -- 1 for True, 0 for False (1 is recommended)
, @PrimaryRoutingURLVARCHAR(40) = '' -- gets set by script
, @SecondaryRoutingURLVARCHAR(40) = '' -- gets set by script
, @SQLCommandVARCHAR(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)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (1)

You rated this post out of 5. Change rating

Related content

External Article

Network Configuration of SQL Server Always On Availability Groups in Azure

Implementing SQL Server Failover Clustering in Azure virtual machines differs in several aspects from its on-premises implementations. These differences reflect some of the unique characteristics of the storage and network infrastructure services in the Microsoft cloud environment. In this article, Marcin Policht looks at the networking aspects of clustered deployments of SQL Server 2016 in Azure.

2017-10-11

2,735 reads

External Article

Configure SQL Server AlwaysOn Availability Group on a Multi-Subnet Cluster

A “multi-subnet” environment is defined when the OS cluster used as the backbone for AlwaysOn has server nodes that are located in multiple/different subnets. Deploying SQL Server AlwaysOn Availability Groups requires a Windows Server Failover Clustering (WSFC) cluster and each availability replica of a given availability group must reside on a different node of the same WSFC cluster. This post from Manvendra Singh will help you in configuring an HA and DR solution for SQL Server AlwaysOn in multi-subnet networks.

2017-01-13

3,309 reads