SQL Server 2016 가용성 그룹- 로드밸런싱 읽기 전용 라우팅 다양하게 구성하는 방법

로드밸런싱 그룹핑을 통해서 부하분산 확장을 할 수 있다.

그룹핑을 수정을 하고 싶을 때 어떻게 할까?

간단하다. 기존 구성은 그대로 두고 그룹핑하는 쿼리만으로 수정이 가능하다.

   

현재구성은 아래와 같이 주서버 + 보조서버 3개를 모두 그룹핑 하였다.

-- 보조 복제본 2,3,4를 모두 LB 구성, 3개 모두 복제복이 비정상일 경우에는 1번으로 연결한다는 정의

   

ALTER AVAILABILITY GROUP [AG-GroupNameTSQL]

MODIFY REPLICA ON N'AG2016-S1\AG1'

WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('AG2016-S4\AG4','AG2016-S3\AG3', 'AG2016-S2\AG2'), 'AG2016-S1\AG1')));

   

아래와 같이 변경해보자.

[ 2번 3번으로 로드밸런싱 구성하기]

-- 구성 리셋

-- 라우팅 제거할때는 아래와 같이 해주면 끝.

ALTER AVAILABILITY GROUP [AG-GroupNameTSQL]

MODIFY REPLICA ON N'AG2016-S3\AG3'

WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=NONE));

   

-- 그룹은 괄호() 로 묶는 방식이다. 나머지는 정의 하든 안하든 정책에 따라

-- 테스트 시나리오 : 2,3 만 라우팅 하고 2, 3번이 죽으면 접근이 안된다.

ALTER AVAILABILITY GROUP [AG-GroupNameTSQL]

MODIFY REPLICA ON N'AG2016-S1\AG1'

WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('AG2016-S2\AG2','AG2016-S3\AG3'))));

   

   

3번만 서비스 시작을 했다.

   

   

위 처럼 다양하게 보조복제본 읽기전용으로 활용할 수 있다.

   

-- 주서버가 AG2016-S1\AG1 일때 읽기전용은 AG2016-S4\AG4 으로만 접근 하고, 만약 AG2016-S4\AG4 장애가 발생하면 2번 3번으로 로드밸런싱 해라

ALTER AVAILABILITY GROUP [AG-GroupNameTSQL]

MODIFY REPLICA ON N'AG2016-S1\AG1'

WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('AG2016-S4\AG4', ('AG2016-S2\AG2','AG2016-S3\AG3'))));

   

   

AG2016-S4\AG4 서비스를 중지를 하게 되면 4번으로 접근이 차단이 되며 자동으로 2번 3번 양쪽으로 접속하게 된다.

   

[점검쿼리]

--라우팅쿼리

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,

ars.synchronization_health_desc

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_replicas ar3 on rl.read_only_replica_id = ar3.replica_id

INNER JOIN sys.availability_groups ag on ar.group_id = ag.group_id

INNER JOIN sys.dm_hadr_availability_replica_states ars on ar3.replica_id=ars.replica_id

--WHERE ag.name = 'AG-Seeding'

ORDER BY ag.name, ar.replica_server_name, rl.routing_priority

GO

-- Routing Priority 필드의 값이 1, 2 로 설정이 되어 있다. 2014에서는 1,2 3 순차적으로 설정이 된다.

--SELECT * FROM sys.availability_read_only_routing_lists