가용성 그룹에 대한 읽기 전용 라우팅 구성

가용성 그룹에 대한 읽기 전용 라우팅 구성(SQL Server)
Configure Read-Only Routing for an Availability Group
https://msdn.microsoft.com/library/hh710054.aspx

읽기전용 라우팅을 언제 사용해야 적절할까는 고민할 부분이다.

 

[환경]

AG1㈜, AG2(보조), AG3(보조) 의 복제복이 존재한다.

[Action Plan]

A. 필수 구성 요소

가용성 그룹 수신기 있어야 한다.

읽기전용 보조 복제복이 있어야 한다.

B. 적용은 T-SQL 또는 PowerShell 로만 적용이 가능하다.

 

 

 

-- 1. 읽기전용 라우팅 설정 하기

/*

읽기전용이란 이름으로 정의하였기 때문에 보조복제본의 연결은 모두 ALL 이 아닌 READ_ONLY 이어야 한다.

아래 설정변경은 주복제본에서만 수행이 된다.

*/

use master

go

 

ALTER AVAILABILITY GROUP AGName

MODIFY REPLICA ON N'AG1' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))

 

ALTER AVAILABILITY GROUP AGName

MODIFY REPLICA ON N'AG2' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))

 

ALTER AVAILABILITY GROUP AGName

MODIFY REPLICA ON N'AG3' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))

 

ALTER AVAILABILITY GROUP AGName

MODIFY REPLICA ON

N'AG1' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://AG1.overtop.local:1433'));

 

ALTER AVAILABILITY GROUP AGName

MODIFY REPLICA ON

N'AG2' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://AG2.overtop.local:1433'));

 

ALTER AVAILABILITY GROUP AGName

MODIFY REPLICA ON

N'AG3' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://AG3.overtop.local:1433'));

 

 

ALTER AVAILABILITY GROUP AGName

MODIFY REPLICA ON

N'AG1' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('AG2','AG3','AG1')));

-- AG1 이 주 복제본일경우에 라우팅은 AG3 로 연결되며, AG3 가 접속이 안되는 경우에는 AG2 로 자동연결 된다.

 

ALTER AVAILABILITY GROUP AGName

MODIFY REPLICA ON

N'AG2' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('AG3','AG1','AG2')));

 

ALTER AVAILABILITY GROUP AGName

MODIFY REPLICA ON

N'AG3' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('AG1','AG2','AG3')));

GO

 

 

-- 2. 설정 확인하기.

SELECT * FROM sys.availability_read_only_routing_lists

GO

 

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

 

 

C. 라우팅 연결 테스트

-K , -d 옵션을 필수 입력해야 확인이 가능하다.
주 복제본에 따라 라우팅 순서를 정의할 수 있다. 위 코드 상에서 AG2가 주 복제복일경우에는 AG3 리턴한다.

sqlcmd -S Aglistener.overtop.local,62000 -E -K ReadOnly -d AGDB1 -Q "select @@servername"

 

 

SQLcmd 에서 멀티서브넷을 설정하려면 : -M

logintimeout 설정 : -l (소문자 L)

[참고문서]

Modifying AlwaysOn Read Only Routing Lists

http://blogs.msdn.com/b/alwaysonpro/archive/2014/01/22/modifying-alwayson-read-only-routing-lists.aspx

변경됨 : https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/22/modifying-alwayson-read-only-routing-lists/

키워드 : 리스너, 라우팅