
-- Configure the READ_ONLY_ROUTING_URL
ALTER AVAILABILITY GROUP [SQL2K16-AG1] 
MODIFY REPLICA ON N'SQL2K16-AG01' 
WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'TCP://SQL2K16-AG01:1433'))
GO

ALTER AVAILABILITY GROUP [SQL2K16-AG1] 
MODIFY REPLICA ON N'SQL2K16-AG02' 
WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'TCP://SQL2K16-AG02:1433'))
GO

ALTER AVAILABILITY GROUP [SQL2K16-AG1] 
MODIFY REPLICA ON N'SQL2K16-AG03' 
WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'TCP://SQL2K16-AG03:1433'))
GO


-- Configure the READ_ONLY_ROUTING_LIST (T-SQL)
ALTER AVAILABILITY GROUP [SQL2K16-AG1] 
MODIFY REPLICA ON N'SQL2K16-AG01' 
WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N'SQL2K16-AG02', N'SQL2K16-AG03')))

ALTER AVAILABILITY GROUP [SQL2K16-AG1] 
MODIFY REPLICA ON N'SQL2K16-AG02' 
WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N'SQL2K16-AG03', N'SQL2K16-AG01')))
GO

ALTER AVAILABILITY GROUP [SQL2K16-AG1] 
MODIFY REPLICA ON N'SQL2K16-AG03' 
WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N'SQL2K16-AG01', N'SQL2K16-AG02')))
GO



SELECT ar.replica_server_name,
		arl.routing_priority, 
		ar2.replica_server_name,
		ar2.read_only_routing_url
FROM sys.availability_read_only_routing_lists AS arl 
INNER JOIN sys.availability_replicas AS ar
	ON arl.replica_id = ar.replica_id
INNER JOIN sys.availability_replicas AS ar2
	ON arl.read_only_replica_id = ar2.replica_id
ORDER BY ar.replica_server_name asc, 
		arl.routing_priority asc

-- Now Configure Load Balancing
:CONNECT SQL2K16-AGL1

ALTER AVAILABILITY GROUP [SQL2K16-AG1] 
MODIFY REPLICA ON N'SQL2K16-AG01' 
WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=((N'SQL2K16-AG02', N'SQL2K16-AG03'))));
GO



SELECT 
	ar.replica_server_name,
	arl.routing_priority, 
	ar2.replica_server_name,
	ar2.read_only_routing_url
FROM sys.availability_read_only_routing_lists AS arl 
INNER JOIN sys.availability_replicas AS ar
	ON arl.replica_id = ar.replica_id
INNER JOIN sys.availability_replicas AS ar2
	ON arl.read_only_replica_id = ar2.replica_id
ORDER BY ar.replica_server_name, 
		arl.routing_priority
GO
