ag readonly
http://www.overtop.co.kr/136
가용성 그룹에 대한 읽기 전용 라우팅 구성(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"
[참고문서]
Modifying AlwaysOn Read Only Routing Lists
http://blogs.msdn.com/b/alwaysonpro/archive/2014/01/22/modifying-alwayson-read-only-routing-lists.aspx
192.168.137.5에 연결하는 중...
메시지 41158, 수준 16, 상태 3, 줄 65
로컬 가용성 복제본을 가용성 그룹 'poc_ag'에 조인하지 못했습니다. 작업 중 SQL Server 오류 41106이(가) 발생하여 작업이 롤백되었습니다. 자세한 내용은 SQL Server 오류 로그를 확인하십시오. 나중에 오류 원인을 해결한 후 ALTER AVAILABILITY GROUP JOIN 명령을 다시 시도하십시오.
192.168.137.5에서 연결을 끊는 중...
쿼럼 구성
클라이언트가 ad 도메인에 없을경우
클라이언트의 host 에 도메인 이름을 명시적으로 쓰던지
아래와 같이 리스너 구성을 명시적으로 ip 를 기술한다.
ALTER AVAILABILITY GROUP poc_ag
MODIFY REPLICA ON N'mhv1' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))
ALTER AVAILABILITY GROUP poc_ag
MODIFY REPLICA ON N'mhv2' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))
ALTER AVAILABILITY GROUP poc_ag
MODIFY REPLICA ON N'chv1' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))
ALTER AVAILABILITY GROUP poc_ag
MODIFY REPLICA ON
N'mhv1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://192.168.137.4:1433'));
ALTER AVAILABILITY GROUP poc_ag
MODIFY REPLICA ON
N'mhv2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://192.168.137.5:1433'));
ALTER AVAILABILITY GROUP poc_ag
MODIFY REPLICA ON
N'chv1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://192.168.137.6:1433'));
ALTER AVAILABILITY GROUP poc_ag
MODIFY REPLICA ON
N'mhv1' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('chv1','mhv2','mhv1')));
-- AG1 이 주 복제본일경우에 라우팅은 AG3 로 연결되며, AG3 가 접속이 안되는 경우에는 AG2 로 자동연결 된다.
ALTER AVAILABILITY GROUP poc_ag
MODIFY REPLICA ON
N'mhv2' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('chv1','mhv1','mhv2')));
ALTER AVAILABILITY GROUP poc_ag
MODIFY REPLICA ON
N'chv1' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('mhv2','mhv1','chv1')));
GO
jdbc test
package com.company;
import java.sql.*;
public class Main {
public static void RunQuery() throws ClassNotFoundException, SQLException, InterruptedException
{
String url = "jdbc:sqlserver://192.168.137.44;DatabaseName=cafedb;ApplicationIntent=ReadOnly";
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection(url, "test", "1234!@#$");
stmt = conn.createStatement();
rs = stmt.executeQuery("select @@servername ServerName");
rs.next();
System.out.println(rs.getString("ServerName"));
Thread.sleep(500);
rs.close();
stmt.close();
conn.close();
}
public static void main(String[] args)
{
Integer i = 0;
while (1 == 1)
{
i++;
try
{
Main.RunQuery();
System.out.println(i);
}
catch(Exception e)
{
System.out.println(e.getMessage());
}
}
}
}