AlwaysON 복제본 읽기 전용 연결

USE master
GO

-- 보조 복제본 읽기 전용 연결 허용 (UI로 변경 가능)
ALTER AVAILABILITY GROUP [ALG]
MODIFY REPLICA ON N'WS16-AOSVR1' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL));
 
-- 읽기 전용 라우팅 URL 지정 
ALTER AVAILABILITY GROUP [ALG]
MODIFY REPLICA ON N'WS16-AOSVR1' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://WS16-AOSVR1:1433'));
 ALTER AVAILABILITY GROUP [ALG]
MODIFY REPLICA ON N'WS16-AOSVR2' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL));
 ALTER AVAILABILITY GROUP [ALG]
MODIFY REPLICA ON N'WS16-AOSVR2' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://WS16-AOSVR2:1433'));
 
-- 라우팅 우선 순위
ALTER AVAILABILITY GROUP [ALG]
MODIFY REPLICA ON N'WS16-AOSVR1' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('WS16-AOSVR2','WS16-AOSVR1')));
 ALTER AVAILABILITY GROUP [ALG]
MODIFY REPLICA ON N'WS16-AOSVR2' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('WS16-AOSVR1','WS16-AOSVR2')));
 
-- 설정확인
SELECT ag.name as "Availability Group", ar.replica_server_name as "When Primary Replica Is",
rl.routing_priority as "Routing Priority", ar2.replica_server_name as "RO Routed To",
ar.secondary_role_allow_connections_desc, ar2.read_only_routing_url
FROM sys.availability_read_only_routing_lists rl
inner join sys.availability_replicas ar on rl.replica_id = ar.replica_id
inner join sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id
inner join sys.availability_groups ag on ar.group_id = ag.group_id
ORDER BY ag.name, ar.replica_server_name, rl.routing_priority