블로그 이미지
SQL Server VS. 석이 minsouk@hotmail.com MSSQL 쿼리성능 관련해 궁금한 사항이 있다면 언제나 누구나 TeamViewer + Line (네이버 japan 메신저) 에 minsouk1 추가 후 연락주세요~ 010-9967-0955 보미아빠

카테고리

보미아빠, 석이 (433)
밥벌이 (16)
싸이클 (1)
일상 (1)
Total185,809
Today895
Yesterday74

달력

« » 2017.08
    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31    

공지사항

ag readonly

분류없음 / 2016.03.22 10:03


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 aon 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에서 연결을 끊는 중...

주서버에서 transaction log backup 후 보조서버에서 norecovery 로 복구 후 조인하면 된다. 


쿼럼 구성



클라이언트가 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());
}
}
}
}



저작자 표시 비영리 변경 금지
신고
Posted by 보미아빠

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

티스토리 툴바