AlwaysOn 구성환경에서 Server Role 체크 후 Job Agent 실행 중지하기

 

·       Version : SQL Server

 

SQL Server에서 AlwaysOn을 구성하였을때, Failover 를 대비하여 Secondary 서버에서도 Primary서버와 동일하게 계정, Job Agent 등이 구성되어 있어야 한다. 그런데 Secondary의 경우 DB가 동기화 되고 있는 대기 서버이기 때문에 Job Agent를 Primary와 동일하게 설정하면 Secondary에서 Job 실행 시 Job Fail이 발생한다. 또한 Secondary에서 일부 Job 의 경우 실행이 되지 말아야 할 것들이 있다. 아래 스크립트는 AlwaysOn 의 role을 확인하여 서버가 Primary일 때만 Job Agent가 수행되도록 한다. 각각의 Job Agent의 첫 번째 단계에 추가하여 Primary Role이 아닐때 Job 수행을 중지하도록 한다.

DECLARE @SERVER_ROLE nvarchar(50)

 

SELECT

    @SERVER_ROLE = A.ROLE_DESC

FROM sys.dm_hadr_availability_replica_states AS A

    INNER JOIN sys.availability_groups AS B ON A.GROUP_ID = B.GROUP_ID

WHERE A.IS_LOCAL = 1

 

IF @SERVER_ROLE <> 'PRIMARY'

       EXEC msdb.dbo.sp_stop_job N'Job Name' ;

 

Secondary에서 Job 이 실행되었을때, Role을 확인하는 부분이 있어 Job이 중지된것을 확인할 수 있다.

 

[참고자료]

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-stop-job-transact-sql?view=sql-server-2017

 

2019-04-10 / Sungwook Kang / http://sungwookkang.com

 

SQL Server, AlwaysOn,  Job Agent, sys.dm_hadr_availability_replica_states, AG Role check

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server 프로토콜과 SQLCMD를 사용한 연결  (0) 2019.04.18
SQL Server Edition 다운그레이드 후 확인사항  (0) 2019.04.12
인덱스 재구성과 통계 업데이트시 발생하는 SQL Server Block  (2) 2019.03.26
SQL Server Scala UDF Inline 기능을 사용한 쿼리 성능 향상  (0) 2019.03.26
SQL Server 가용성 그룹에 데이터베이스 자동으로 추가하기  (0) 2019.03.26

SQL Server 가용성 그룹에 데이터베이스 자동으로 추가하기

 

·         Version : SQL Server

 

SQL Server AlwaysOn Availability Group으로 데이터베이스가 구성된 경우 해당 데이터베이스는 복원할 수 없다. 먼저 데이터베이스를 복원하기 위해서는 가용성 그룹의 SQL Server 데이터베이스를 제거해야 한다. 이번 포스트에서는 SQL Server Job Agent를 사용하여 AlwaysOn 설정이 자동으로 구성되록 하는 방법을 살펴 본다. 각 단계별 스크립트를 Job Agent에 등록하여 사용할 수 있다.

 

1. Primary 서버의 AlwaysOn 가용성 그룹에서 데이터베이스 제거

-- runs on primary server

 

USE master

GO

 

ALTER AVAILABILITY GROUP [<Availability Group>] REMOVE DATABASE [<Database Name>];

GO

 

2. Primary 서버의 데이터베이스 복원

이 단계는 Primary 서버의 데이터베이스를 복원한다. 사용 권한 부여등 복원후 특정 요구사항에 대한 추가 단계를 추가하여 실행 할 수 도 있다. 아래 스크립트는 복원하려는 파일이 네트워크 상의 다른 서버에 있는 경우이다.

-- runs on primary server

 

USE master

GO

 

RESTORE DATABASE [<Database Name>]

FROM DISK='<Shared Network Location>\<Database Name>.bak' WITH REPLACE

GO

 

3. Primary 서버에 복원된 데이터베이스를 전체 복구 모델로 변경

-- runs on primary server

 

USE master

GO

 

ALTER DATABASE [<Database Name>] SET RECOVERY FULL WITH NO_WAIT

GO

 

4. Primary 서버에서 데이터베이스 전체 백업 및 로그 백업 진행

복원 작업을 쉽게 수행하기 위해 Primary 서버보다 Secondary 서버가 액세스할 수 있는 네트워크 공유에 백업파일을 만드는것이 좋다. 이렇게 하면 Secondary로 백업을 복사하지 않아도 되며 복사단계를 생략하여 시간을 절약할 수 있다.

-- runs on primary server

 

BACKUP DATABASE [<database Name>]

TO DISK='<Shared Network Location>\<Database Name>.bak' WITH FORMAT, INIT, COMPRESSION

GO

 

BACKUP LOG [<database Name>]

TO DISK='<Shared Network Location>\<Database Name>.trn' WITH FORMAT, INIT, COMPRESSION 

GO

 

5. Primary 서버에서 가용성 그룹에 데이터베이스를 추가

-- runs on primary server

 

USE master

GO

 

ALTER AVAILABILITY GROUP [<Availability Group>] ADD DATABASE [<Database Name>]; 

GO

 

6. Secondary 서버에서 데이터베이스 복원

Secondary에서 가요성 그룹에 추가할 데이터베이스를 전체 백업 및 로그 백업 파일을 이용해서 복원한다.

-- runs on secondary server

 

USE master

GO

 

RESTORE DATABASE [<database Name>]

FROM DISK='<Shared Network Location>\<Database Name>.bak' WITH FILE=1, REPLACE, NORECOVERY

GO

 

RESTORE LOG [<database Name>]

FROM DISK='<Shared Network Location>\<Database Name>.trn' WITH FILE=1, REPLACE, NORECOVERY 

GO

 

7. 데이터베이스 상태를 확인한 다음 가용성 그룹에 추가

-- runs on secondary server

 

-- Wait for the replica to start communicating

begin try

   declare @conn bit

   declare @count int

   declare @replica_id uniqueidentifier

   declare @group_id uniqueidentifier

   set @conn = 0

   set @count = 30 -- wait for 5 minutes

 

   if (serverproperty('IsHadrEnabled') = 1)

      and (isnull((select member_state

                   from master.sys.dm_hadr_cluster_members

                   where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)

      and (isnull((select state

                   from master.sys.database_mirroring_endpoints), 1) = 0)

   begin

      select @group_id = ags.group_id

      from master.sys.availability_groups as ags

      where name = N'<Availability Group>'

 

      select @replica_id = replicas.replica_id

      from master.sys.availability_replicas as replicas

      where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id

 

      while @conn <> 1 and @count > 0

      begin

         set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)

         if @conn = 1

         begin

            -- exit loop when the replica is connected, or if the query cannot find the replica status

            break

         end

         waitfor delay '00:00:10'

         set @count = @count - 1

      end

   end

end try

 

begin catch

   -- If the wait loop fails, do not stop execution of the alter database statement

end catch

 

ALTER DATABASE [<Database Name>] SET HADR AVAILABILITY GROUP = [<Availability Group>];

GO

 

지금까지의 위 단계를 각 Primary 서버 및 Secondary 서버에 Job Agent로 추가한다. 순서를 정리하면 아래 표와 같다.

 

Primary

Secondary

1

가용성 그룹에서 데이터베이스 제거

 

2

데이터베이스 복원

 

3

데이터베이스 복구 모델을 FULL로 설정

 

4

가용성 그룹에 데이터베이스 추가

 

5

데이터베이스 전체 백업 및 로그 백업 생성

 

6

(sp_start_job을 사용하여 보저 서버의 작업을 호출)

 

7

 

데이터베이스 백업 및 로그 복원

8

 

가용성 그룹에 추가

 

Primary 서버에서 Secondary 서버의 작업을 호출하지 않는 경우 Primary 작업이 완료 된 후 Secondary 서버의 작업이 시작되도록 시간을 설정해야 한다.

 

[참고자료]

https://www.mssqltips.com/sqlservertip/5194/automate-refresh-of-a-sql-server-database-that-is-part-of-an-availability-group/

 

 

2018-11-07 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, MSSQL, AlwaysOn, SQL Server Availability Group, 가용성 그룹

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

인덱스 재구성과 통계 업데이트시 발생하는 SQL Server Block  (2) 2019.03.26
SQL Server Scala UDF Inline 기능을 사용한 쿼리 성능 향상  (0) 2019.03.26
SQL Server MySQL PostgreSQL 비교  (0) 2019.03.26
SQL Server 복잡한 쿼리가 옵티마이저에 미치는 영향과 옵티마이저 timeout  (0) 2019.03.26
SQL Server 2019 에서 문자열 잘림에 대한 향상된 에러 메시지 반환  (0) 2019.03.26

VM환경에서 AG를 구성하였을때VSS 백업 동작 변경

 

·         Version : SQL Server 2016, SQL Server 2017

 

SQL Server 2016 Standard Edition(또는 이후 버전)을 사용하여 가상 컴퓨터 (Virtual Machine) 환경에서 기본 가용성 그룹( AG)을 구성하였을때 AG의 보조 복제본을 호스팅하는 VM환경에서 백업이 실패한 것으로 나타날 수 있다. 이러한 이유는 Volume Shadow Copy Service(VSS)가 VM환경의 보조 복제본에서호스트되는 모든  SQL Server 데이터베이스를 일관된 방식으로 백업하기 때문이다.

·         Volume Shadow Copy Service : https://docs.microsoft.com/ko-kr/windows/desktop/VSS/about-the-volume-shadow-copy-service

기본 가용성 그룹에서는 보조 데이터베이스의 백업이 지원되지 않으므로 백업중인 데이터베이스의 하위 집합과 함께 VM 백업이 성공할 수 있도록 허용하지 않아 전체 VM백업이 실패한다. 이전 버전의 SQL Server에서  Database Mirroring은 VM에서 호스트되는 미러된 데이터베이스를 스킵하면서 VSS 백업을 완료할 수 있었다. SQL Server 2016 SP2 CU2 및 SQL Server 2017 CU9에서는 기본 AG의 VSS 백업의 기본 동작을 데이터베이스 미러링과 같이 변경하여 기본 AG 보조 복제본에서 호스트되는 보조 데이터베이스를 스킵한다. 즉, VM환경에서 백업은 성공하지만 백업 당시에는 2차 상태였던 데이터베이스는 포함되지 않는다.

 

SQL Writer Service는 SQL Server에서 VSS백업을 수행하는 서비스이다. 이 서비스는 SQL Server엔진과 별개이며 다른 버전의 SQL Server에서 공유된다.

·         SQL Writer Service : https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-writer-service?view=sql-server-2017

동일한 서버 또는 VM에 SQL Server인스턴스가 여러 개 설치되어있는 경우 이러한 인스턴스는 모두 SQL Writer Service의 동일한 인스턴스를 공유한다. 동일한 서비스가 버전간에 공유되더라도 각 SQL Server버전과 함께 제공되는 파일에는 함께 제공되는 인스턴스와 동일한 버전 번호가 표시된다. 즉 SQL Server 2016 SP2 CU2를 서버에 적용하면 새로운 기능을 갖춘 새 SQL Writer Service가 제공되지만 이후에 해당 인스턴스를 SQL Server 2017RTM으로 업그레이드 하거나 SQL Server 2017의 별도 인스턴스를 설치하는 경우 RTM을 동일한 서버에 설치하면 새로운 SQL Writer Service 바이너리는 새 버전 번호를 갖기 때문에 이전 버전으로 바뀐다. 이 때문에 SQL Server 2016 SP2 CU2로 패치된 시스템에 SQL Server 2017인스턴스를 업그레이드 하거나 설치하는 경우 최신 CU를 적옹해야 한다.

 

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/behavior-change-for-vss-backups-on-vms-with-basic-availability-groups/

 

 

2018-08-28 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, MSSQL, AlwaysOn,가용성 그룹, SQL 가용성 그룹, 가용성 그룹 백업, VSS, Volume Shadow Copy Service, SQL Writer Service

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server 2016부터 도입된 USE HINT를 사용한 추적 플래그 활성화  (0) 2019.03.26
In-memory optimized table에 사용되는 Hash Index  (0) 2019.03.26
SQL Server 2016 향상된 가용성 그룹 – 데이터베이스 수준의 상태 탐지 장애조치  (0) 2019.03.25
SQL Server 2016 대용량 데이터 로드시 최소 로깅(minimal logging) 과Batch Size  (0) 2019.03.25
SQL Server 설치시 발생하는 1638 오류  (0) 2019.03.25

SQL Server 2016 향상된 가용성 그룹 – 데이터베이스 수준의 상태 탐지 장애조치

 

·         Version : SQL Server 2016, SQL Server 2017

 

SQL Server2016에서 도입된 가용성 그룹(Availability Group)에 대한 데이터베이스 수준 상태 탐지 장애 조치 (Database Level Failover) 옵션은 가용성 그룹에 있는 하나 이상의 데이터베이스에 문제가 있을 경우 가용성 그룹에 장애 조치 메커니즘을 실행시키기 위해 도입되었다. 이 기능을 사용하면 데이터베이스의 고가용성을 보장할 수 있으며 업무상 중요한 데이터베이스가 있는 모든 가용성 그룹에 권장되는 최상의 방법이다.

데이터베이스 수준 상태 탐지 장애조치의 초기 구현에서는 가용성 그룹의 주 복제본에서 다음 조건을 검사 하도록 설계 되었다.

·         DB Status는 온라인 상태인가?

·         데이터베이스의 트랜잭션 로그 파일에 트랜잭션 쓰기를 사용할 수 있는가?

위 두 조건중 하나라도 true가 아닌 경우 데이터베이스를 호스팅하는 가용성 그룹은 사용 가능한 동기(동기화) 보조 노드 중 하나에 장애조치가 된다.

 

과거에는 사용자가 하드웨어 문제 때문에 발생하는 오류와 같은 추가 검사를 진행하였으나 최신 서비스 릴리즈에서는 데이터베이스 수준의 상태 확인 검사의 일부로 포함될수 있다. SQL Server의 최신 서비스 릴리스는 아래 링크를 참고한다.

·         SQL2017RTM CU9

 https://support.microsoft.com/en-us/help/4341265/cumulative-update-9-for-sql-server-2017

·         SQL2016SP1 CU10

 https://support.microsoft.com/en-us/help/4341569/cumulative-update-10-for-sql-server-2016-sp1

·         SQL2016SP2 CU2

 https://support.microsoft.com/en-us/help/4340355/cumulative-update-2-for-sql-server-2016-sp2

 

만약 릴리즈 적용이후 이전 상태로 되돌릴경우 사용자는 TF9576을 시작 매개변수로 사용하거나 DBCC TRACEON 명령을 사용한다. 글을 쓰는 현재 시점에는 윈도우에서만 가능하지만 추후 Linux 버전의  SQL Server 2017에도 적용될 예정이다.

 

기존 검사외에 새로운 구현에는 다음과 같은 추가 검사가 있다.

1.       데이터베이스 상태 정보를  스냅샷으로 저장하고 사용하여 AG가 오류 상태로 표시되어야 하는지 여부를 결정한다. 상태 검사 루틴은 데이터베이스 상태 및 관련 오류 정보를 마지막 세 번의 실행 결과를 캐시한 다음 현재 상태 검사 루틴의 실행 상태 정보와 비교한다. 상태 탐지 루틴의 네 번의 연속 실행중에 동일한 오류 조건(아래 언급된 오류코드)이 존재하면 장애조치가 시작된다.

2.       아래 목록의 추가 오류를 확인한다. 이러한 오류의 대부분은 서버의 하드웨어 문제를 나타낸다. 아래 목록이 데이터베이스 가용성에 영향을 줄 수 있는 모든 오류 목록은 아니다.

Error

Cause

Documentation

605

페이지 또는 할당 손상

https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-605-database-engine-error?view=sql-server-2017

823

검사점 실패

https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-823-database-engine-error?view=sql-server-2017

829

디스크 손상

 

832

하드웨어 또는 메모리 손상

 

1101

파일 그룹에 사용가능한 디스크 공간 부족

https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-1101-database-engine-error?view=sql-server-2017

1105

파일 그룹에 사용 가능한 디스크 공간부족

https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-1105-database-engine-error?view=sql-server-2017

5102

누락된 파일 그룹 ID 요청

 

5108

잘못된 파일 ID 요청

 

5515

 

https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-5515-database-engine-error?view=sql-server-2017

5534

FILESTREAM 작업 로그 레코드로 인해 로그가 손상됨

 

5535

FILESTREAM 데이터 컨테이너가 손상됨

 

9004

로그 손상

https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-9004-database-engine-error?view=sql-server-2017

 

 

 

 

2018-08-27 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, MSSQL, AlwaysOn, SQL Failover, SQL Server Availability Group, 가용성 그룹, SQL 가용성 그룹, 장애조치, 향상된 가용성 그룹


저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

In-memory optimized table에 사용되는 Hash Index  (0) 2019.03.26
VM환경에서 AG를 구성하였을때VSS 백업 동작 변경  (0) 2019.03.25
SQL Server 2016 대용량 데이터 로드시 최소 로깅(minimal logging) 과Batch Size  (0) 2019.03.25
SQL Server 설치시 발생하는 1638 오류  (0) 2019.03.25
SQL Server 666코드의 고유 식별자 오류  (0) 2019.03.25

SQL Server AlwaysOn synchronous-commit 환경에서 동기화 레이턴시 트러블슈팅

 

·         Version : SQL Server

 

SQL Server AlwaysOn synchronous-commit(동기 커밋) 환경에서HADR_SYNC_COMMIT 대기 정보를 이용해서 레이턴시 상태를 확인할 수 있다. HADR_SYNC_COMMIT대기는 SQL Server가 원격 복제본의 신호가 트랜잭션을 커밋하기를 기다리고 있음을 나타낸다. 트래잭션 커밋 대기 시간에 대한 자세한 정보는 아래 링크를 확인한다.

·         Troubleshooting High HADR_SYNC_COMMIT wait type with Always On Availability Groups :  https://blogs.msdn.microsoft.com/sql_server_team/troubleshooting-high-hadr_sync_commit-wait-type-with-always-on-availability-groups/

·         SQL Server 2012 AlwaysOn – Part 12 – Performance Aspects and Performance Monitoring II : https://blogs.msdn.microsoft.com/saponsqlserver/2013/04/24/sql-server-2012-alwayson-part-12-performance-aspects-and-performance-monitoring-ii/

 

위 두 링크를 살펴보면 트랜잭션 지연은 다음 두 성능 카운터로 평가하는 것을 알 수 있다.

·         SQL Server:Database Replica –> Transaction Delay

·         SQL Server:Database Replica –> Mirrored Write Transactions/sec

 

예를 들어 AG에서 노드 성능이 좋지 않아 “SQL Server:Database Replica –> Transaction Delay = 1000ms” 이고 “SQL Server:Database Replica –> Mirrored Write Transactions/sec  = 50”이라고 가정하면 평균적으로 트랜잭션 지연시간은 1000ms/50= 20ms 이다.  동기 커밋에 대한 자세한 정보는 아래 링크를 확인한다.

·         AlwaysON – HADRON Learning Series: – How does AlwaysON Process a Synchronous Commit Request : https://blogs.msdn.microsoft.com/psssql/2011/04/01/alwayson-hadron-learning-series-how-does-alwayson-process-a-synchronous-commit-request/

·         Update adds AlwaysOn extended events and performance counters in SQL Server 2014 or 2016 : https://support.microsoft.com/en-us/help/3173156/update-adds-alwayson-extended-events-and-performance-counters-in-sql-s

 

아래 그림은 동기 커밋 모드에서 로그 블록이 Replica 서버에 커밋되는 각 단계마다 XEvent가 캡쳐되는 흐름을 보여준다.

 


위 그림에서 살펴보면 XEvent 추적이 캡처되면 로그 블록 이동의 각 단계에서 중요한 시점을 알 수있으므로 트랜잭션 대기 시간의 정확한 위치를 알 수 있다. 일반적으로 지연은 세 부분으로 나누어진다.

1.       Primary 서버에서 log harden 지속 시간

·         Log_flush_start(step 2) 및 Log_flush_complete(step 3)의 델타 시간은 동일하다.

2.       Replica 서버에서 log harden 지속 시간

·         Log_flush_start(step 10) 및 Log_flush_complete(step 11)의 델타 시간은 동일하다.

3.       네트워크 트래픽의 지속 시간

·         Primary : hadr_log_block_send_complete ->secondary:hadr_transport_receive_log_block_message (step 6-7)

·         Secondary : hadr_lsn_send_complete->primary:hadr_receive_harden_lsn_message (step 12-13)

 

아래 스크립트는XEvent를 사용하여 각 단계에 소요되는 시간을 캡처할 수 있다.

/* Note: this trace could generate very large amount of data very quickly, depends on the actual transaction rate. On a busy server it can grow several GB per minute, so do not run the script too long to avoid the impact to the production server.  */

 

CREATE EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER

ADD EVENT sqlserver.file_write_completed,

ADD EVENT sqlserver.file_write_enqueued,

ADD EVENT sqlserver.hadr_apply_log_block,

ADD EVENT sqlserver.hadr_apply_vlfheader,

ADD EVENT sqlserver.hadr_capture_compressed_log_cache,

ADD EVENT sqlserver.hadr_capture_filestream_wait,

ADD EVENT sqlserver.hadr_capture_log_block,

ADD EVENT sqlserver.hadr_capture_vlfheader,

ADD EVENT sqlserver.hadr_db_commit_mgr_harden,

ADD EVENT sqlserver.hadr_db_commit_mgr_harden_still_waiting,

ADD EVENT sqlserver.hadr_db_commit_mgr_update_harden,

ADD EVENT sqlserver.hadr_filestream_processed_block,

ADD EVENT sqlserver.hadr_log_block_compression,

ADD EVENT sqlserver.hadr_log_block_decompression,

ADD EVENT sqlserver.hadr_log_block_group_commit ,

ADD EVENT sqlserver.hadr_log_block_send_complete,

ADD EVENT sqlserver.hadr_lsn_send_complete,

ADD EVENT sqlserver.hadr_receive_harden_lsn_message,

ADD EVENT sqlserver.hadr_send_harden_lsn_message,

ADD EVENT sqlserver.hadr_transport_flow_control_action,

ADD EVENT sqlserver.hadr_transport_receive_log_block_message,

ADD EVENT sqlserver.log_block_pushed_to_logpool,

ADD EVENT sqlserver.log_flush_complete ,

ADD EVENT sqlserver.log_flush_start,

ADD EVENT sqlserver.recovery_unit_harden_log_timestamps

ADD TARGET package0.event_file(SET filename=N'c:\mslog\AlwaysOn_Data_Movement_Tracing.xel',max_file_size=(500),max_rollover_files=(4))

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

 

GO

 

아래 그림은 XEvent를 실행하여 캡처한 결과이다.

[Primary]

 

[Second synchronous replica]

 


참고로 hadr_receive_harden_lsn_message의 log_block_id(14602889512)가 다른 ID(146028889488)와 동일하지 않는데,  그이유는 항상 다음의 harden log block의ID를 즉시 반환하기 때문이다. hadr_db_commit_mgr_update_harden xevent를 사용하여 XEvent를 상호연관 시킬 수 있다.

 

위의 XEvent 로그를 사용하여 캡처한 데이터를 사용하여 아래 표처럼 만들어서 트랜잭션 커밋의 상세한 지연시간을 확인할 수 있다. 이 리스트는 네트워크 및 log harden process의 시간 델타(latency)를 나열한 것이며 로그 블록의 압축이나 해제 등  다른 시간이 발생할 수도 있다.

 

From

To

Lantency

Network:

Primary->Secon

Primary:

hadr_log_block_send_complete

 

2018-03-06 16:56:28.2174613

Secondary:

hadr_transport_receive_log_block_message

 

2018-03-06 16:56:32.1241242

3.907 seconds

Network:

Secondary->Primary

Secondary:hadr_lsn_send_complete

2018-03-06 16:56:32.7863432

Primary:hadr_receive_harden_lsn_message

2018-03-06 16:56:33.3732126

0.587 seconds

LogHarden(Primary)

log_flush_start

2018-03-06 16:56:28.2168580

log_flush_complete

2018-03-06 16:56:28.8785928

0.663 seconds

Log Harden(Secondary)

Log_flush_start

2018-03-06 16:56:32.1242499

Log_flush_complete

2018-03-06 16:56:32.7861231

0.663 seconds

 

위에서 언급했든이  주로 발생하는 대기시간은 다음 세 부분에서 발생한다.

·         복제본간의 네트워크 대기시간 : 3.907 + 0.857 = 4.494

·         Primary Log harden : 0.663

·         Secondary Log harden : 0.663

 

총 트랜잭션 지연시간을 얻기에는Primary 로그 플러시와 네트워크 전송이 동시에 발생하기 때문에 합산할 수 없어 쉽지 않다. 네트워크에서 4.494초가 발생하였고,  Primary가 복제본으로부터 컨펌(hadr_receive_harden_lsn_message:2018-03-06 16:56:33.3732126)을 받고 완료한 시간을 (log_flush_complete : 2018-03-06 16 : 56 : 28.8785928) 시간을 계산할때 타임스탬프를 수동으로 계산할 필요가 없다. XEvent는  두 개의 hadr_log_block_group_commit사이의 델타시간을 알 수 있기 때문이다. 아래 예시를 살펴보자.

·         Primary: hadr_log_block_group_commit: 2018-03-06 16:56:28.2167393

·         Primary: hadr_log_block_group_commit: 2018-03-06 16:56:33.3732847

·         Total time to commit=delta of above two timestamps= 5.157 seconds

 

이 숫자는 네트워크 전송 시간과Secondary서버의 log harden을 더한것과 같다. 그 이유는Secondary 서버의log harden 가 네트워크를 사용할 수 있기를 기다려야하므 Primary와 마찬가지로 동시에 log harden를 할 수 없기 때문이다.

 

지금까지 AlwaysOn 동기-커밋 모드에서 복제본간 로그 블록 이동에 대해서 살펴보았다. 그런데 Primary 서버의 XEvent에서 “hadr_db_commit_mgr_harden_still_waiting”가 가끔 발생하는 것을 볼수 있는데 이 이벤트는 Primary 서버가  Secondary 복제본의 확인 메시지를 기다리고 있을때 2초 간격으로 발생(2초로 하드코딩 되어있음)한다. 2초 내에 Ack이 돌아오면 XEvent에 표시되지 않는다.

 

[참고자료]

https://blogs.msdn.microsoft.com/psssql/2018/04/05/troubleshooting-data-movement-latency-between-synchronous-commit-always-on-availability-groups/

 

 

2018-07-05 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, AG, AlwaysON, delay HADR_SYNC_COMMIT latency Performance transaction

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server 설치시 발생하는 1638 오류  (0) 2019.03.25
SQL Server 666코드의 고유 식별자 오류  (0) 2019.03.25
SQL Server Scheduling and Yielding 트러블슈팅  (0) 2019.03.25
SQL Server 2016 Tempdb 경합(contention) 최적화  (0) 2019.03.25
XEvent를 사용하여 Auto tuning 작업 모니터링  (0) 2019.03.25

.NET 4.6.1에 변경된 Multisubnet 기본 수신기 동작

  • Version : SQL Server 2012, 2014, 2016

 

SQL Server에서 AlwaysOn을 구성하여 사용할 때 리스너는 여러 개의 IP 주소를 감지하여 가용성 그룹에 정의되어 있는 IP주소에 연결을 시도하는데 간헐적으로 연결시간 초과가 발생할 수 있다. SQL 클라이언트의 기본 동작은 DNS에서 반환된 모든 IP 주소에 연결을 시도하는데 DNS 구성에 의존하기 때문에 몇 가지 문제가 발생할 수 있다. 가장 빈번하게 발생하는 문제가 정확하지 않은 IP 반환 또는 온라인되어 있지 않은 IP 반환이다. 기본적으로 TCP 연결 시도에 대한 기본 제한시간은 21초이며 IP 주소가 온라인 상태가 아닌 경우 다음 IP를 시도하기까지 21초를 기다려야 한다.

 

.NET 4.6.1에 업데이트된 내용은 SQL 클라이언트 제공자의 기본값이 MultiSubnetFailover=True 이며 이 동작은 모둔 IP 주소를 검색하고 병렬로 모든 연결을 시도한다. IP가 온라인 상태이면 성공적으로 연결이 되며 장애조치의 경우 가용성 그룹에 다시 연결하는 최적의 방법으로 동작한다.

 

자세한 업데이트 내용 및 .NET 패키지 다운로드는 아래 링크를 참고 한다.

http://blogs.msdn.com/b/dotnet/archive/2015/11/30/net-framework-4-6-1-is-now-available.aspx

 

새로운 릴리즈를 적용하지 않아도 이전의 SQL Server 2012 가용성 그룹 수신기 또는 장애조치 클러스터 인스턴스 연결을 사용할 때 MultiSubnetFailover=True를 지정하면 장애조치 클러스터 인스턴스 또는 모든 가용성 그룹에 대해 병렬로 연결을 시도하여 서브넷 장애조치가 시행 되는동안 신속하게 TCP 연결을 다시 시도한다.

 

[참고자료]

http://blogs.msdn.com/b/alwaysonpro/archive/2015/12/01/improved-multisubnet-listener-behavior-with-newly-released-sql-client-provider-in-net-4-6-1.aspx

 

2015-12-04 / 강성욱 / http://sqlmvp.kr

 

 

SQL Server, AlwaysOn, 가용성 그룹, Multisibnet, Failover Cluster, 장애조치, 고가용성, 멀티서브넷, SQL 클라이언트 공급자

'SQL Server > SQL Server Tip' 카테고리의 다른 글

DeadLock(교착상태) 모니터 하기  (0) 2016.01.11
Spool 연산자와 추적 플래그 8690  (0) 2016.01.11
SQL Server 2014 온라인 인덱스 리빌드와 잠금 우선순위 옵션  (0) 2015.11.26
함수 통계 정보 확인 (sys.dm_exec_function_stats)  (0) 2015.11.24
NULL 데이터가 포함된 데이터 사용 시 주의점  (0) 2015.11.20

+ Recent posts

티스토리툴바