[진행사항]

Always-on Availability group(AG) 구성의 SQL Server에서 Primary replica가 병렬 Index Rebuild 수행 시 Secondary도 동일한 수량의 병렬 Index Rebuild를 수행하는지를 문의주신 건으로 이해됩니다.

 

AG의 데이터 동기화는 Primary Replica에서 발생한 Transaction log를 Secondary Replica에서 Redo하는 단계로 진행됩니다.

참고로 SQL Server 2016 이후 Parallel redo thread(Instance당 최대 100개, Database당 최대 16개)를 지원합니다.

다만, 이 Parallel redo thread의 개수는 Primary replica에서 Transaction log를 발생시킨 쿼리의 DoP와 관계 없이, 현재 Secondary replica에서 Transaction log를 Redo하는 부하 상황에 따라 달라지게 됩니다.

: SQL Server 2016/2017: Availability group secondary replica redo model and performance - Microsoft Community Hub

 

이 때문에, Primary replica에서 병렬 Index Rebuild 수행 시 Secondary replica에서 병렬로 Redo를 수행할 가능성은 높으나,
Index Rebuild 상황을 Redo하는 Secondary Replica의 Redo Thread 개수는, 일반적으로 Primary replica의 Index Rebuild Thread 개수보다 적게 됩니다.

 

예를 들어 다음과 같이 테스트환경에서 약 10GB 크기의 테이블에 대해, MAXDOP 4의 병렬 Index Rebuild를 수행하여 Secondary Replica에서 Redo thread를 모니터링 하면,
Primary Replica는 DoP 4로 Index Rebuild를 수행하고 있으나, 같은 시점의 Secondary Replica는 3개의 Redo thread로 Transaction을 Redo 하고 있음을 확인할 수 있습니다.

Case Result
Test Scenario
- 약 10GB 크기의 테이블에 Maxdop=4로 Index Rebuild를 수행		 EXEC sys.sp_spaceused 'dbo.tbl_Index_ParallelBuild'
GO        


 
ALTER INDEX UCL__tbl_Index_ParallelBuild__01 ON dbo.tbl_Index_ParallelBuild REBUILD WITH (MAXDOP = 4)
GO
 
Primary Replica
- 첨부드리는 Monitor_IndexRebuild_ParallelRedo.sql 쿼리를 통해
74번 세션에서 수행중인 Index Rebuild 수행 상황을 모니터링 하면,
2개의 Consumer thread를 제외하고, 4개의 Thread로 Parallel Rebuild 수행중임을 확인할 수 있습니다.
Secondary Replica
- 첨부드리는 Monitor_IndexRebuild_ParallelRedo.sql 쿼리를 통해
AG의 Redo 수행 상황을 모니터링 하면, AGDB01 데이터베이스에 대해 3개의 Parallel redo thread가 Index rebuild를 수행중임을 확인할 수 있습니다.
 

 

Monitor_IndexRebuild_ParallelRedo.sql
0.00MB

'Database > AlwaysOn' 카테고리의 다른 글

Log shrink 동기화 지연  (0) 2024.02.14
Alwayson 동기화 지연 원인 파악  (1) 2024.02.14
데이터 동기화 대기 시간에 대한 일반적인 원인 및 문제 해결  (0) 2024.02.14
SQL Server Always On 가용성 그룹에 대한 가용성 복제본 및 데이터베이스를 모니터링하기 위한 동적 관리뷰  (0) 2023.04.05
SQL Server AlwaysOn synchronous-commit 환경에서 동기화 레이턴시 트러블슈팅  (0) 2020.08.27

일반적으로 백업은 DML을 blocking 하지 않으나 백업이 진행중일 때는 database/file을 shrink 할 수 없으며,

Primary에서 Log shrink 를 진행해도 Secondary에도 적용이 되기 때문에 위와 같은 Lock wait이 발생하게 됩니다.

Limitations and restrictions

Ÿ   The database can't be made smaller than the minimum size of the database. The minimum size is the size specified when the database was originally created, or the last explicit size set by using a file-size-changing operation, such as DBCC SHRINKFILE. For example, if a database was originally created with a size of 10 MB and grew to 100 MB, the smallest size the database could be reduced to is 10 MB, even if all the data in the database has been deleted.
Ÿ   You can't shrink a database while the database is being backed up. Conversely, you can't back up a database while a shrink operation on the database is in process.
Ÿ   You cannot shrink files while database Backups are happening.
Ÿ   You might also notice that these commands encounter a wait_type = LCK_M_U and a wait_resource = DATABASE: <id> [BULKOP_BACKUP_DB] when the status of these commands is viewed from the various dynamic management views (DMVs), such as from sys.dm_exec_requests or sys.dm_os_waiting_tasks.


Shrink a database - SQL Server | Microsoft Learn

MSSQLSERVER_3023 - SQL Server | Microsoft Learn

'Database > AlwaysOn' 카테고리의 다른 글

Always-on Availability group(AG) 구성의 SQL Server에서 Primary replica가 병렬 Index Rebuild 수행 시 Secondary도 동일한 수량의 병렬 Index Rebuild를 수행 하는가  (0) 2024.08.22
Alwayson 동기화 지연 원인 파악  (1) 2024.02.14
데이터 동기화 대기 시간에 대한 일반적인 원인 및 문제 해결  (0) 2024.02.14
SQL Server Always On 가용성 그룹에 대한 가용성 복제본 및 데이터베이스를 모니터링하기 위한 동적 관리뷰  (0) 2023.04.05
SQL Server AlwaysOn synchronous-commit 환경에서 동기화 레이턴시 트러블슈팅  (0) 2020.08.27

1. AG 설정 xml 파일 생성

 

아래 쿼리를 Primary, Secondary(Async relica)에서 각각 실행

select
AGNode.group_name
,AGNode.replica_server_name
,AGNode.node_name,ReplicaState.role,ReplicaState.role_desc
,ReplicaState.is_local
,DatabaseState.database_id
,db_name(DatabaseState.database_id) as database_name
,DatabaseState.group_database_id
,DatabaseState.is_commit_participant
,DatabaseState.is_primary_replica
,DatabaseState.synchronization_state_desc
,DatabaseState.synchronization_health_desc
,ClusterState.group_id
,ReplicaState.replica_id
from sys.dm_hadr_availability_replica_cluster_nodes AGNode
join sys.dm_hadr_availability_replica_cluster_states ClusterState
on AGNode.replica_server_name = ClusterState.replica_server_name
join sys.dm_hadr_availability_replica_states ReplicaState
on ReplicaState.replica_id = ClusterState.replica_id
join sys.dm_hadr_database_replica_states DatabaseState
on ReplicaState.replica_id=DatabaseState.replica_id
for XML RAW, ROOT('AGInfoRoot')

 

생성되는 링크 클릭

 

각각의 xml output을 primary.xml , secondary.xml 로 저장하여 전달

 

추가 Extended event 수집

문제가 발생하는 시간대를 특정지을 수는 없다고 하셨지만, 지연이벤트가 집중되는 시간대가 확인되시는 경우 해당 시간대에 수집하여 주시기 바랍니다.

아래 수집 경로 및 파일명도 원하시는 대로 변경하실 수 있으며, 서버 사용량에 맞게 file size 및 rollover file 개수를 조정하시기 바랍니다.

보내드리는 스크립트는 500MB 파일이 4개 생성되는 구조입니다.

 

Secondary의 경우 지연 현상에 대해 확인이 필요한 Async 노드에서만 수집하시기 바랍니다.

-- 1.1. On Primary Replica
CREATE EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER
ADD EVENT sqlserver.hadr_apply_log_block,
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_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_worker_pool_task,
 
ADD EVENT sqlserver.hadr_database_flow_control_action,
ADD EVENT sqlserver.hadr_transport_flow_control_action, 
ADD EVENT ucs.ucs_connection_flow_control,
 
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.recovery_unit_harden_log_timestamps
ADD TARGET package0.event_file(SET filename=N'c:\temp\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=OFF)
GO
 
-- 1.2 On Secondary Replica
CREATE EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER
ADD EVENT sqlserver.hadr_apply_log_block,
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_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_undo_of_redo_log_scan,
 
ADD EVENT sqlserver.hadr_database_flow_control_action,
ADD EVENT sqlserver.hadr_transport_flow_control_action, 
ADD EVENT ucs.ucs_connection_flow_control,
 
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.recovery_unit_harden_log_timestamps
ADD TARGET package0.event_file(SET filename=N'c:\temp\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=OFF)
GO
 
 
--2. Start Session
ALTER EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER STATE=START;
 
 
--3. Stop Session
ALTER EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER STATE=STOP;

 

분석에 필요한 event가 SQL Server 2014 SP2 / 2016 SP1 이후 추가되었기 때문에 이전 버전에서는 해당 이벤트 수집이 어려운 점, 참고 부탁드립니다.

KB3173156 - Update adds AlwaysOn extended events and performance counters in SQL Server 2014 or 2016 - Microsoft Support

 

Perfmon 수집

1) cmd를 관리자권한으로 실행한 뒤 아래 명령을 실행하면 성능 수집기가 생성됩니다.

  • C:\sqlperflogs 경로에 파일이 생성되고 5초 간격으로 카운터를 수집하도록 설정되어 있습니다. 옵션을 고객사 환경에 맞게 적절하게 변경하셔도 관계 없습니다.
  • M/d/yyyy 를 다음날로 설정하시면 다음날 00시부터 수집이 되며, 24시간(23시간 59분 59초) 간격으로 새로운 파일 생성이 됩니다.
  • 만약 날짜 상관 없이 수집을 바로 시작하고 24시간 간격으로 수집을 원하실 경우 아래, 명령어를 변경하신 후 start 해 주시기 바랍니다.
  • 로그를 수집한 후 파일 사이즈를 확인하고 문제가 재현되지 않을 경우 디스크 공간을 많이 차지하지 않도록 주기적으로 로그 파일을 수동 제거할 필요가 있습니다.

 

DEFAULT INSTANCE

Logman.exe create counter SQLPerfLog -o "c:\sqlperflogs\PerfLog.csv" -f csv -v mmddhhmm -b M/d/yyyy 12:00:00AM -r -rf 23:59:59 -si 00:00:05   -c "\PhysicalDisk(*)\*" "\LogicalDisk(*)\*" "\Memory\*" "\Processor(_Total)\*" "\Process(*)\*" "\Network Interface(*)\*" "\Paging File(*)\*" "\System\*" "\SQLServer:Access Methods\*" "\SQLServer:Backup Device\*" "\SQLServer:Broker Activation\*" "\SQLServer:Broker Statistics\*" "\SQLServer:Broker TO Statistics\*" "\SQLServer:Broker/DBM Transport\*" "\SQLServer:Buffer Manager\*" "\SQLServer:Buffer Node(*)\*" "\SQLServer:Catalog Metadata(*)\*" "\SQLServer:CLR\*" "\SQLServer:Cursor Manager by Type(*)\*" "\SQLServer:Cursor Manager Total\*" "\SQLServer:Database Mirroring\*" "\SQLServer:Database Replica(*)\*" "\SQLServer:Databases(*)\*" "\SQLServer:Deprecated Features(*)\*" "\SQLServer:Exec Statistics(*)\*" "\SQLServer:General Statistics\*" "\SQLServer:Latches\*" "\SQLServer:Locks(*)\*" "\SQLServer:Memory Manager\*" "\SQLServer:Plan Cache(*)\*" "\SQLServer:Replication Agents\*" "\SQLServer:Replication Dist.\*" "\SQLServer:Replication Logreader\*" "\SQLServer:Replication Merge\*" "\SQLServer:Replication Snapshot\*" "\SQLServer:Resource Pool Stats(*)\*" "\SQLServer:SQL Errors(*)\*" "\SQLServer:SQL Statistics\*"  "\SQLServer:Transactions\*" "\SQLServer:User Settable(*)\*" "\SQLServer:Wait Statistics(*)\*" "\SQLServer:Workload Group Stats(*)\*"

 

NAMED INSTANCE
“MSSQL$NAMEDINS”를 고객사 NAMED INSTANCE이름으로 변경이 필요합니다.
Ex) MSSQL$NAMEDINS à MSSQL$DB2

Logman.exe create counter SQLPerfLog -o "c:\sqlperflogs\PerfLog.csv" -f csv -v mmddhhmm -b M/d/yyyy 12:00:00AM  -r -rf 23:59:59 -si 00:00:05  -c  "\PhysicalDisk(*)\*" "\LogicalDisk(*)\*" "\Memory\*" "\Processor(_Total)\*" "\Process(*)\*" "\Network Interface(*)\*" "\Paging File(*)\*" "\System\*" "\MSSQL$NAMEDINS:Backup Device\*" "\MSSQL$NAMEDINS:Broker Activation\*" "\MSSQL$NAMEDINS:Broker Statistics\*" "\MSSQL$NAMEDINS:Broker TO Statistics\*" "\MSSQL$NAMEDINS:Broker/DBM Transport\*" "\MSSQL$NAMEDINS:Buffer Manager\*" "\MSSQL$NAMEDINS:Buffer Node(*)\*" "\MSSQL$NAMEDINS:Catalog Metadata(*)\*" "\MSSQL$NAMEDINS:CLR\*" "\MSSQL$NAMEDINS:Cursor Manager by Type(*)\*" "\MSSQL$NAMEDINS:Cursor Manager Total\*" "\MSSQL$NAMEDINS:Database Mirroring\*" "\MSSQL$NAMEDINS:Database Replica(*)\*" "\MSSQL$NAMEDINS:Databases(*)\*" "\MSSQL$NAMEDINS:Deprecated Features(*)\*" "\MSSQL$NAMEDINS:Exec Statistics(*)\*" "\MSSQL$NAMEDINS:General Statistics\*" "\MSSQL$NAMEDINS:Latches\*" "\MSSQL$NAMEDINS:Locks(*)\*" "\MSSQL$NAMEDINS:Memory Manager\*" "\MSSQL$NAMEDINS:Plan Cache(*)\*" "\MSSQL$NAMEDINS:Replication Agents\*" "\MSSQL$NAMEDINS:Replication Dist.\*" "\MSSQL$NAMEDINS:Replication Logreader\*" "\MSSQL$NAMEDINS:Replication Merge\*" "\MSSQL$NAMEDINS:Replication Snapshot\*" "\MSSQL$NAMEDINS:Resource Pool Stats(*)\*" "\MSSQL$NAMEDINS:SQL Errors(*)\*" "\MSSQL$NAMEDINS:SQL Statistics\*"  "\MSSQL$NAMEDINS:Transactions\*" "\MSSQL$NAMEDINS:User Settable(*)\*" "\MSSQL$NAMEDINS:Wait Statistics(*)\*" "\MSSQL$NAMEDINS:Workload Group Stats(*)\*" "\SQLAgent$NAMEDINS:Alerts\*" "\SQLAgent$NAMEDINS:Jobs(*)\*" "\SQLAgent$NAMEDINS:JobSteps(*)\*"

 

 

-- Database Mirroring 항목 추가

 

DEFAULT INSTANCE

Logman.exe update counter [수집중인Perflog명]  -c "\SQLServer:Availability Replica\*" "\PhysicalDisk(*)\*" "\LogicalDisk(*)\*" "\Memory\*" "\Processor(_Total)\*" "\Process(*)\*" "\Network Interface(*)\*" "\Paging File(*)\*" "\System\*" "\SQLServer:Access Methods\*" "\SQLServer:Backup Device\*" "\SQLServer:Broker Activation\*" "\SQLServer:Broker Statistics\*" "\SQLServer:Broker TO Statistics\*" "\SQLServer:Broker/DBM Transport\*" "\SQLServer:Buffer Manager\*" "\SQLServer:Buffer Node(*)\*" "\SQLServer:Catalog Metadata(*)\*" "\SQLServer:CLR\*" "\SQLServer:Cursor Manager by Type(*)\*" "\SQLServer:Cursor Manager Total\*" "\SQLServer:Database Mirroring\*" "\SQLServer:Database Replica(*)\*" "\SQLServer:Databases(*)\*" "\SQLServer:Deprecated Features(*)\*" "\SQLServer:Exec Statistics(*)\*" "\SQLServer:General Statistics\*" "\SQLServer:Latches\*" "\SQLServer:Locks(*)\*" "\SQLServer:Memory Manager\*" "\SQLServer:Plan Cache(*)\*" "\SQLServer:Replication Agents\*" "\SQLServer:Replication Dist.\*" "\SQLServer:Replication Logreader\*" "\SQLServer:Replication Merge\*" "\SQLServer:Replication Snapshot\*" "\SQLServer:Resource Pool Stats(*)\*" "\SQLServer:SQL Errors(*)\*" "\SQLServer:SQL Statistics\*"  "\SQLServer:Transactions\*" "\SQLServer:User Settable(*)\*" "\SQLServer:Wait Statistics(*)\*" "\SQLServer:Workload Group Stats(*)\*"

 

NAMED INSTANCE

“MSSQL$NAMEDINS”를 고객사 NAMED INSTANCE이름으로 변경이 필요합니다.

Ex) MSSQL$NAMEDINS à MSSQL$DB2

Logman.exe update counter [수집중인Perflog명]  -c "\MSSQL$NAMEDINS:Availability Replica\*"   "\PhysicalDisk(*)\*" "\LogicalDisk(*)\*" "\Memory\*" "\Processor(_Total)\*" "\Process(*)\*" "\Network Interface(*)\*" "\Paging File(*)\*" "\System\*" "\MSSQL$NAMEDINS:Backup Device\*" "\MSSQL$NAMEDINS:Broker Activation\*" "\MSSQL$NAMEDINS:Broker Statistics\*" "\MSSQL$NAMEDINS:Broker TO Statistics\*" "\MSSQL$NAMEDINS:Broker/DBM Transport\*" "\MSSQL$NAMEDINS:Buffer Manager\*" "\MSSQL$NAMEDINS:Buffer Node(*)\*" "\MSSQL$NAMEDINS:Catalog Metadata(*)\*" "\MSSQL$NAMEDINS:CLR\*" "\MSSQL$NAMEDINS:Cursor Manager by Type(*)\*" "\MSSQL$NAMEDINS:Cursor Manager Total\*" "\MSSQL$NAMEDINS:Database Mirroring\*" "\MSSQL$NAMEDINS:Database Replica(*)\*" "\MSSQL$NAMEDINS:Databases(*)\*" "\MSSQL$NAMEDINS:Deprecated Features(*)\*" "\MSSQL$NAMEDINS:Exec Statistics(*)\*" "\MSSQL$NAMEDINS:General Statistics\*" "\MSSQL$NAMEDINS:Latches\*" "\MSSQL$NAMEDINS:Locks(*)\*" "\MSSQL$NAMEDINS:Memory Manager\*" "\MSSQL$NAMEDINS:Plan Cache(*)\*" "\MSSQL$NAMEDINS:Replication Agents\*" "\MSSQL$NAMEDINS:Replication Dist.\*" "\MSSQL$NAMEDINS:Replication Logreader\*" "\MSSQL$NAMEDINS:Replication Merge\*" "\MSSQL$NAMEDINS:Replication Snapshot\*" "\MSSQL$NAMEDINS:Resource Pool Stats(*)\*" "\MSSQL$NAMEDINS:SQL Errors(*)\*" "\MSSQL$NAMEDINS:SQL Statistics\*"  "\MSSQL$NAMEDINS:Transactions\*" "\MSSQL$NAMEDINS:User Settable(*)\*" "\MSSQL$NAMEDINS:Wait Statistics(*)\*" "\MSSQL$NAMEDINS:Workload Group Stats(*)\*" "\SQLAgent$NAMEDINS:Alerts\*" "\SQLAgent$NAMEDINS:Jobs(*)\*" "\SQLAgent$NAMEDINS:JobSteps(*)\*"

 

2) 성능 수집을 중지하려면 아래 명령어를 입력합니다.

Logman.exe stop SQLPerfLog

 

파라미터 변경이 필요하신 경우 아래 링크 참고 부탁 드립니다. 

logman create counter | Microsoft Learn

 

위 logman 명령은 perfmon (성능 모니터)에서 로그 수집기의 중지 조건이나 스케줄을 GUI로도 편집하실 수 있습니다.

 

 

 

(optional) lsn 정보 수집

아래 내용을 수집 기간 동안 분 단위로 수집하여 주시면 분석에 도움이 될 수 있습니다.

다만 서버에 부하가 갈 수 있을 경우 수집하지 않으셔도 됩니다.

select GETDATE(), database_id, recovery_lsn, truncation_lsn, last_hardened_lsn, last_sent_lsn, last_received_lsn,  
   last_redone_lsn, last_sent_time, last_received_time, last_redone_time 
from sys.dm_hadr_database_replica_states

 

'Database > AlwaysOn' 카테고리의 다른 글

Always-on Availability group(AG) 구성의 SQL Server에서 Primary replica가 병렬 Index Rebuild 수행 시 Secondary도 동일한 수량의 병렬 Index Rebuild를 수행 하는가  (0) 2024.08.22
Log shrink 동기화 지연  (0) 2024.02.14
데이터 동기화 대기 시간에 대한 일반적인 원인 및 문제 해결  (0) 2024.02.14
SQL Server Always On 가용성 그룹에 대한 가용성 복제본 및 데이터베이스를 모니터링하기 위한 동적 관리뷰  (0) 2023.04.05
SQL Server AlwaysOn synchronous-commit 환경에서 동기화 레이턴시 트러블슈팅  (0) 2020.08.27

 

  • 읽기 전용 보조 복제본의 장기 실행 활성 트랜잭션 ---- > 읽기 가능한 보조 복제본  에 장기 실행 트랜잭션이 있는 경우 동일한 데이터에 액세스할 때 보조 복제본의 로컬 다시 실행 스레드가 차단 될 수 있습니다 . 

 

 

  • 흐름 제어로 인해 높은 로그 전송 큐 크기 및 낮은 로그 전송 속도 ---- >   SQL AG의 성능이 특정 SQL AG 데이터베이스 또는 SQL AG 복제본 수준에서 흐름 제어 게이트를 트리거하면 로그 데이터 전송이 일시적으로 종료되고 제한됩니다. 초등부터 중등까지. 흐름 제어는 일반적으로 서버 과부하 또는 느린 네트워크로 인해 발생할 수 있습니다. AG에서 흐름 제어 게이트에 진입하는 것을 지속적으로 관찰한 경우 흐름 제어를 유발하는 환경 조건을 해결해야 합니다. 아래 성능 모니터 카운터를 확인하여 SQL AG 데이터베이스 또는 SQL AG 복제본이 흐름 제어 게이트를 트리거했는지 확인할 수 있습니다. 

 

 

  • 공유 REDO 대상   ----- 이 문제는 보조 복제본이 여러 개 있는 경우에만 발생합니다. 하나의 보조 복제본이 다시 실행 프로세스를 완료하는 데 매우 느린 경우 다시 실행 프로세스를 통해 허용되는 최대 LSN에서 다른 보조 복제본이 더 이상 진행되지 않도록 제한합니다. 추적 플래그 9559를 사용하여 문제를 완화할 수 있습니다.  (문서 https://learn.microsoft.com/en-us/archive/blogs/alwaysonpro/recovery-on-secondary-lagging-shared-redo-target )

 

  • 보조 복제본의 병렬 다시 실행 스레드 부족  --- 보조 복제본에 병렬 다시 실행 스레드가 부족하면 TF3478을 사용하여 총 CPU 수에 따라 병렬 다시 실행 스레드의 최대 수가 증가하도록 허용할 수 있습니다. 기본적으로 SQL Server 인스턴스는 보조 복제본에 대한 병렬 다시 실행을 위해 최대 100개의 스레드를 사용합니다. 각 데이터베이스는 총 CPU 코어 수의 최대 절반을 사용하지만 데이터베이스당 스레드는 16개를 초과할 수 없습니다. 단일 인스턴스에 필요한 총 스레드 수가 100을 초과하면 SQL Server는 나머지 모든 데이터베이스에 대해 단일 다시 실행 스레드를 사용합니다. 

 

  • 디스크 또는 I/O 하위 시스템 대기 시간 --> 보조 복제본의 디스크 또는 I/O 하위 시스템에 심각한 병목 현상이 있는 경우 다시 실행 속도가 영향을 받으며 보조 복제본에서는 매우 작습니다. 보조 복제본을 읽을 수 없고 SQL 데이터베이스 파일 스토리지에 대해 기본 및 이 SQL AG의 사용 전용 하드웨어와 유사한 하드웨어가 있는 경우 기본 복제본에 도달하기 전에 보조 복제본에서 이를 볼 가능성이 적습니다. 그러나 보조 복제본에 읽기 전용 워크로드의 양이 많은 경우 IO 하위 시스템에 추가 IO 오버헤드가 발생할 수 있습니다. 빈번한 데이터베이스 백업 및 VM 스냅샷은 추가 I/O 오버
  • 헤드의 또 다른 잠재적인 원인이 될 수 있습니다. 

 

 

 

 

https://techcommunity.microsoft.com/t5/sql-server-support-blog/common-causes-and-troubleshooting-solutions-for-sql-ag-data/ba-p/2963083

 

Common Causes and Troubleshooting Solutions for SQL AG Data Synchronization Latency

This article summarizes the common causes , solutions and troubleshooting mechanism for SQL Availability Group (AG) data synchronization latency between..

techcommunity.microsoft.com

 

'Database > AlwaysOn' 카테고리의 다른 글

Log shrink 동기화 지연  (0) 2024.02.14
Alwayson 동기화 지연 원인 파악  (1) 2024.02.14
SQL Server Always On 가용성 그룹에 대한 가용성 복제본 및 데이터베이스를 모니터링하기 위한 동적 관리뷰  (0) 2023.04.05
SQL Server AlwaysOn synchronous-commit 환경에서 동기화 레이턴시 트러블슈팅  (0) 2020.08.27
AlwaysOn 패치 적용  (0) 2020.08.27

가용성 복제본 모니터링

sys.availability_replicas

각 SQL Server Always On 가용성 그룹에 대한 기존 가용성 복제본에 대한 정보를 얻습니다. 예를 들어 내 랩 환경에는 [demoag] 및 [SQLAGDemo]라는 두 개의 가용성 그룹이 있습니다. 각 가용성 그룹에는 2개의 복제본이 있으므로 출력에 4개의 행이 표시됩니다.

1
2
3
select replica_server_name,endpoint_url, availability_mode_desc,
failover_mode_desc,secondary_role_allow_connections_desc,create_date,
backup_priority,read_only_routing_url,seeding_mode_desc,read_write_routing_url   from sys.availability_replicas

이 DMV 출력에 유용한 열은 다음과 같습니다.

  • endpoint_url: SQL Server Always On 가용성 그룹은 기본 및 보조 복제본 통신에 데이터베이스 미러링 엔드포인트를 사용합니다. SQL Server는 가용성 그룹을 정의하면 엔드포인트를 자동으로 구성합니다. 이 열에서 엔드포인트 URL을 얻습니다.
  • availability_mode 및 availability_mode_desc: AG가 동기 또는 비동기 모드로 구성되는지 여부를 지정합니다.
    • 0: 비동기 커밋
    • 1: 동기 커밋
    • 4: 구성 전용: 이 모드에서는 기본 복제본이 보조 복제본으로 데이터를 보내지 않습니다. AG 구성 메타데이터만 동기화합니다.
  • failover_mode 및 failover_mode_desc: 가용성 복제본의 장애 조치 모드는 사용하는 가용성 모드에 따라 다릅니다.
    • 동기식 모드: 자동 및 수동 장애 조치
    • 비동기식 모드: 수동 장애 조치
    이 DMV에서 장애 조치 모드에 대해 다음 값을 얻습니다.
    • 0: 자동 장애 복구
    • 1: 수동 장애 조치
  • secondary_role_allow_connections: 읽기 전용 워크로드에 보조 복제본을 사용할 수 있습니다. 이 열에 대해 다음 값을 볼 수 있습니다.
    • 0: 연결 없음
    • 1: 읽기 전용
    • 2: 읽기 전용 액세스를 위한 모든 연결
  • create_date: AG 복제본의 생성된 데이터입니다.
  • backup_priority: 가용성 그룹 구성에서 복제본의 백업 우선 순위를 구성할 수 있습니다. 기본적으로 각 복제본에는 50개의 백업 우선 순위가 있습니다. 0에서 100까지의 값을 사용할 수 있습니다.

 

 

Understanding backups on AlwaysOn Availability Groups - Part 1

In this blog post, which is a first article in series, we will provide you a basic overview of Understanding backups on AlwaysOn Availability Groups

www.sqlshack.com

 

  • read_only_routing_url: 가용성 그룹에 대한 읽기 라우팅 URL을 구성한 경우 이 열에서 해당 URL을 가져옵니다. 제 경우에는 구성하지 않았습니다. 따라서 NULL 값을 보여줍니다.

 

 

How to Configure Read-Only Routing for an Availability Group in SQL Server 2016

In this article we will try to explain how to configure read-only routing for an Availability Group in SQL Server 2016 and how we should work with them.

www.sqlshack.com

 

  • seeding_mode: 직접 시드의 경우 자동 값을 표시하고 그렇지 않으면 수동 시드를 얻습니다.
  • read_write_routing_url: SQL Server 2019에서는 모든 읽기-쓰기 연결에 대해 보조 복제본에서 주 복제본으로 연결을 리디렉션할 수 있습니다. 사용자가 보조 복제본에도 연결하는 경우 내부 연결이 기본 복제본으로 다시 라우팅되기 때문에 오류 메시지가 표시되지 않습니다.

 

  • 읽기-쓰기 URL을 구성하지 않았습니다. 따라서 출력에 NULL 값을 제공합니다.

자세한 내용은 Microsoft 문서 를 참조하십시오.

 

Redirect read/write connections to primary replica - SQL Server Always On

Learn how to redirect read/write connections to the primary replica of an Always On availability group regardless of the server specified in the connection string.

learn.microsoft.com

sys.dm_hadr_availability_replica_cluster_nodess

WSFC에서 AG의 가용성 복제본에 대한 정보를 제공합니다. 복제본 상태에 관계없이 가용성 복제본에 대한 정보를 얻습니다.

출력에서 복제본 서버와 인스턴스 이름을 볼 수 있습니다.

sys.dm_hadr_availability_replica_states

로컬 복제본, 원격 복제본 및 해당 동기화 상태에 대한 세부 정보를 얻는 데 유용한 DMV입니다. 다음은 이 DMV의 중요한 열입니다.

  • role 및 role_desc: 이 열에서 SQL Server Always On 가용성 그룹의 복제본 역할을 얻습니다.
    • 0= 해결 중 - 해결 중 상태에서 Windows Server 장애 조치 클러스터는 장애 조치 중간 또는 실패 상태일 수 있습니다. WSFC에 쿼럼이 없으면 AG도 상태 확인에 표시됩니다.
    • 1: 기본(AG 기본 복제본)
    • 2: 보조( AG 보조 복제본)
  • Operational_state 및 Operational_state_desc : 이 열에서 복제본의 현재 작동 상태를 가져옵니다. 이 열에 다음 값을 가질 수 있습니다.
    • 0- 보류 중인 장애 조치
    • 1- 보류 중:
    • 2- 온라인
    • 3- 오프라인
    • 4-실패
    • 5- No Quorum으로 인한 실패

    • 기본: 온라인, 보류 중, 실패
    • 보조: 온라인, 실패 및 NULL
    • 해결 중: 오프라인, Pending_failover, 실패 및 쿼럼 없음으로 실패
  • recovery_health 및 recovery_health_desc: 이 DMV에 대한 ONLINE_IN_PROGRESS 및 ONLINE 값을 가져옵니다. DMV sys.dm_hadr_database_replica_states의 database_state 열에 대한 롤업입니다.
  • synchronization_health 및 synchronization_health_desc: 동기 또는 비동기 커밋에서 모든 가용성 그룹 데이터베이스의 동기화 상태를 가져옵니다. 이러한 열에 가능한 값은 다음과 같습니다.
    • 0: 건강하지 않음
    • 1: 부분적으로 건강함
    • 2: 건강한

가용성 데이터베이스 모니터링

이 범주에서는 SQL Server Always On 가용성 그룹의 가용성 데이터베이스와 관련된 동적 관리 보기를 살펴봅니다.

sys.dm_hadr_auto_page_repair

SQL Server Always On 가용성 그룹은 자동 페이지 복구 기능을 제공합니다. 손상된 페이지의 경우 아래 이미지와 같이 다른 복제본에서 페이지를 수신합니다.

 

Automatic Page Repair in SQL Server Always On Availability Groups

This article gives an overview of Automatic Page Repair in SQL Server Always On Availability Groups.

www.sqlshack.com

sys.dm_hadr_database_replica_states

기본 및 보조 복제본 동기화, 로그 시퀀스 번호, 데이터베이스 상태, suspend\resume에 대한 정보를 얻는 데에도 유용한 DMV입니다. AG 대시보드에서 기본, 보조 복제본 상태에 대한 정보를 볼 수 있습니다. 대시보드는 이 DMV에서 정보를 가져와 그래픽 형식으로 표시합니다.

기본 복제본과 보조 복제본 모두에서 AG 대시보드를 실행할 수 있습니다. 기본 복제본의 AG 대시보드에는 모든 복제본에 대한 정보가 표시됩니다. 그러나 보조 복제본에서 시작하면 연결된 보조 복제본에 대한 정보만 가져옵니다.

아래 이미지에서 AG 대시보드와 DMV 출력을 볼 수 있습니다.

  • 기본 복제본의 AG 대시보드:
  • 보조 복제본의 AG 대시보드:
1
2
3
4
select db_name(database_id) as [Database],is_primary_replica,
synchronization_state_desc,database_state_desc,is_suspended,suspend_reason_desc,
recovery_lsn,truncation_lsn,last_sent_lsn,last_sent_time,last_received_lsn,last_received_time,last_hardened_lsn,log_send_queue_size,log_send_rate,redo_queue_size,
redo_rate,end_of_log_lsn,last_commit_lsn,last_commit_time,secondary_lag_seconds from sys.dm_hadr_database_replica_states

  • database_id: AG 데이터베이스의 데이터베이스 ID입니다. db_name() 함수를 사용하여 데이터베이스 이름을 얻을 수 있습니다.
  • is_primary_replica: 기본 복제본에 대해 값 1을 반환하고 보조 복제본에 대해 0을 반환합니다.
  • synchronization_state 및 synchronization_state_desc: AG 데이터베이스의 데이터 이동 상태를 나타냅니다.
    • 0: 동기화 안 함 - 주 복제본이 해당 보조 복제본으로 트랜잭션을 보내지 않으면 상태가 동기화 안 됨으로 표시됩니다. 연결 문제, 일시 중단된 데이터 이동, 장애 조치 때문일 수 있습니다.
    • 1: 동기화 중 – 비동기 데이터 커밋에서 AG 데이터베이스의 상태를 동기화 중으로 가져옵니다.
    • 2: 동기화됨: 기본 복제본과 보조 복제본이 동기 데이터 커밋을 위해 동기화된 경우 동기화됨 값을 얻습니다.
    • 3: 되돌리기: 보조 데이터베이스가 복구의 Redo 단계에 있음을 보여줍니다.
    • 4: 초기화 중: 보조 데이터베이스 복구의 실행 취소 단계를 나타냅니다.
  • database_state_desc: 이 열에서 sys.databases와 유사한 데이터베이스의 상태를 얻습니다. 다음 값을 가질 수 있습니다.
    • 온라인
    • 복원 중
    • 복구 중
    • 복구 대기 중
    • Suspect
    • Emergency
    • 오프라인
    • NULL
  • is_suspended: 기본 복제본과 연결된 보조 복제본 간에 데이터베이스 이동이 일시 중단되면 플래그를 1로 변경합니다.
    • 0: 재개
    • 1: 일시 중단됨
  • Suspend_reason : 여기에서 데이터 이동을 중지한 이유를 업데이트합니다. 사용자가 시작했거나 강제 장애 조치 시나리오로 인한 것일 수 있습니다.
    • 0: 사용자가 시작한 정지 활동(SUSPEND_FROM_USER)
    • 1: 강제 장애 조치로 인해(SUSPEND_FROM_PARTNER)
    • 2: Redo: Redo 단계의 오류로 인해(SUSPEND_FROM_REDO)
    • 3: 캡처: 기본 복제본 로그 캡처 프로세스의 오류로 인해.
    • 4:적용: 로그 작성 오류로 인해(SUSPEND_FROM_APPLY)
    • 5: 재시작: 데이터베이스 재시작으로 인해(SUSPEND_FROM_RESTART)
    • 6:Undo: Undo 단계의 오류로 인해(SUSPEND_FROM_Undo)
    • 7: 재검증: 로그 변경의 불일치로 인해(SUSPEND_FROM_REVALIDATION)
    • 8: 오류: 보조 복제본 LNS 지점의 오류로 인해(SUSPEND_FROM_XRF_UPDATE)
  • secondary_lag_seconds: 이 열을 사용하여 주 복제본과 보조 복제본 간의 지연을 확인할 수 있습니다.
  • DMV를 사용하여 동기화 지연을 계산하는 가용성 그룹 동기화 지연 측정 문서를 참조할 수 있습니다.

이 외에도 AG 대시보드와 유사한 기본 복제본과 보조 복제본 간의 데이터 전송 각 단계에 대한 LSN 정보를 제공합니다. 복제본 간의 동기화 문제를 해결하는 데 유용합니다.

SQL Server Always On 가용성 그룹을 모니터링하기 위한 충분한 정보를 얻기 위해 여러 DMV를 결합할 수 있습니다.

아래 쿼리는 다음 DMV를 조인합니다.

  • sys.dm_hadr_database_replica_states,
  • sys.availability_databases_cluster
  • sys.availability_groups
  • sys.availability_groups
  • sys.availability_replicas
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
32
33
34
35
36
37
SELECT
    ar.replica_server_name,
    adc.database_name,
    ag.name AS ag_name,
    case HDRS.is_primary_replica
    when 1 then 'Primary Replica'
    else 'Secondary Replica'
    end as Replica,
    HDRS.synchronization_state_desc,
    HDRS.synchronization_health_desc,
    HDRS.recovery_lsn,
    HDRS.truncation_lsn,
    HDRS.last_sent_lsn,
    HDRS.last_sent_time,
    HDRS.last_received_lsn,
    HDRS.last_received_time,
    HDRS.last_hardened_lsn,
    HDRS.last_hardened_time,
    HDRS.last_redone_lsn,
    HDRS.last_redone_time,
    HDRS.log_send_queue_size,
    HDRS.log_send_rate,
    HDRS.redo_queue_size,
    HDRS.redo_rate,
    HDRS.filestream_send_rate,
    HDRS.end_of_log_lsn,
    HDRS.last_commit_lsn,
    HDRS.last_commit_time
FROM sys.dm_hadr_database_replica_states AS HDRS
INNER JOIN sys.availability_databases_cluster AS adc
    ON HDRS.group_id = adc.group_id AND
    HDRS.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
    ON ag.group_id = HDRS.group_id
INNER JOIN sys.availability_replicas AS ar
    ON HDRS.group_id = ar.group_id AND
    HDRS.replica_id = ar.replica_id

 

'Database > AlwaysOn' 카테고리의 다른 글

Alwayson 동기화 지연 원인 파악  (1) 2024.02.14
데이터 동기화 대기 시간에 대한 일반적인 원인 및 문제 해결  (0) 2024.02.14
SQL Server AlwaysOn synchronous-commit 환경에서 동기화 레이턴시 트러블슈팅  (0) 2020.08.27
AlwaysOn 패치 적용  (0) 2020.08.27
가용성 그룹의 일부인 경우 SQL Server 업그레이드  (0) 2020.08.27

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 DelaySQL 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 추적이 캡처되면 로그 블록 이동의 각 단계에서 중요한 시점을 알 수 있으므로 트랜잭션 대기 시간의 정확한 위치를 알 수 있다. 일반적으로 지연은 세 부분으로 나누어진다.

Primary 서버에서 log harden 지속 시간 Log_flush_start(step 2) 및 Log_flush_complete(step 3)의 델타 시간은 동일하다.

Replica 서버에서 log harden 지속 시간 Log_flush_start(step 10) 및 Log_flush_complete(step 11)의 델타 시간은 동일하다.

네트워크 트래픽의 지속 시간 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.494Primary Log harden : 0.663Secondary 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.2167393Primary: hadr_log_block_group_commit: 2018-03-06 16:56:33.3732847Total 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/

'Database > AlwaysOn' 카테고리의 다른 글

Alwayson 동기화 지연 원인 파악  (1) 2024.02.14
데이터 동기화 대기 시간에 대한 일반적인 원인 및 문제 해결  (0) 2024.02.14
SQL Server Always On 가용성 그룹에 대한 가용성 복제본 및 데이터베이스를 모니터링하기 위한 동적 관리뷰  (0) 2023.04.05
AlwaysOn 패치 적용  (0) 2020.08.27
가용성 그룹의 일부인 경우 SQL Server 업그레이드  (0) 2020.08.27

AlwaysOn 가용성 그룹 구성에서 패치를 적용하는 방법?

 

보조 복제본이 하나 인 가용성 그룹에 대해 단계별로 선택하겠습니다.

 

  1. 시스템 상태 (또는 SQL 서비스가 중지 된 VMware 스냅 샷)를 사용하여 최신 OS 백업, 모든 데이터베이스를 최근에 백업하고 기본 노드에서 checkdb를 성공적으로 완료했는지 확인하십시오. {이것은 필수는 아니지만 "Ouch"순간을 피하기 위해}

  2. 기본 복제본 (SQL1)으로 작동하는 노드에서 장애 조치 모드를 수동으로 변경하십시오.

  3. 보조 복제본 (SQL2)에서 영향을받는 데이터베이스를 새로 고치고 대시 보드에서 모든 것이 녹색인지 확인하십시오.

  4. SQL2에 패치 (CU의 서비스 팩)를 적용하십시오.

  5. 사용 가능한 모든 패치가 적용될 때까지 Windows Update 및 / 또는 소프트웨어 업데이트를 반복하십시오. 모든 패치와 패치 후 재부팅 및 구성 작업이 완료 될 때까지 패치 단계를 진행하지 마십시오.

  6. 패치가 적용되었고 클러스터가 정상이며 AlwaysOn 가용성 그룹이 작동하는지 다시 확인하십시오.

  7. 동기화 상태가 SYNCHRONIZED인지 확인하십시오.

  8. 가용성 그룹을 보조 복제본으로 장애 조치 (SQL2)하십시오.

  9. 동기화 상태가 동기화 될 때까지 보조 복제본 (이전 기본 = SQL1)에서 영향을받는 데이터베이스를 새로 고칩니다.

  10. SQL1에 패치 (CU의 서비스 팩)를 적용하십시오.

  11. 사용 가능한 모든 패치가 적용될 때까지 Windows Update 및 / 또는 소프트웨어 업데이트를 반복하십시오. 모든 패치와 패치 후 재부팅 및 구성 작업이 완료 될 때까지 패치 단계를 진행하지 마십시오.

  12. 패치가 적용되었고 클러스터가 정상이며 AlwaysOn 가용성 그룹이 작동하는지 다시 확인하십시오.

  13. 동기화 상태가 SYNCHRONIZED인지 확인하십시오.

  14. 가용성 그룹을 기본 노드로 페일 오버합니다 (SQL1로 돌아 가기).

  15. 장애 조치 모드를 지금 자동으로 변경하십시오 (b 단계에서 변경됨).

계획대로 진행되지 않으면 a) 단계를 수행 했으므로 수행해야 할 작업을 알 수 있습니다.

'Database > AlwaysOn' 카테고리의 다른 글

Alwayson 동기화 지연 원인 파악  (1) 2024.02.14
데이터 동기화 대기 시간에 대한 일반적인 원인 및 문제 해결  (0) 2024.02.14
SQL Server Always On 가용성 그룹에 대한 가용성 복제본 및 데이터베이스를 모니터링하기 위한 동적 관리뷰  (0) 2023.04.05
SQL Server AlwaysOn synchronous-commit 환경에서 동기화 레이턴시 트러블슈팅  (0) 2020.08.27
가용성 그룹의 일부인 경우 SQL Server 업그레이드  (0) 2020.08.27

Always On 가용성 그룹

 

고객이 엔지니어로서 미션 크리티컬 애플리케이션을 사용하는 경우 항상 HA (고 가용성) 및 (DR) 재해 복구에 중점을 둡니다. 따라서 "Always On"은 고 가용성 및 재해 복구를 달성하기위한 단순화 된 통합 솔루션입니다. 가용성 그룹은 여러 다른 기능과 함께 여러 활성 보조 노드뿐만 아니라 여러 데이터베이스 장애 조치를 지원하는 새로운 개념입니다.

 

 

 

 

이미지 출처 : https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server

 

간단히 말해서 고객 데이터를 보호하기 위해 여러 개의 SQL Server와 동기화 된 데이터베이스가 있습니다. 우리는 이미 소프트웨어가 요구 사항 및 새로운 기능 추가와 일치하기 위해 항상 업그레이드 및 패치가 필요하다는 것을 알고 있으며, 프로덕션 환경에서도 독립형 SQL Server를 쉽게 업그레이드 할 수 있습니다. 가용성 그룹의 경우 까다 롭습니다. 따라서 중단 시간없이 SQL Server 인스턴스를 업그레이드하는 데 도움이되는 단계를 제공하려고 생각했습니다.

 

업계에는 여러 유형의 가용성 그룹 토폴로지가 있습니다. 다음은 몇 가지 예입니다.

 

  • 원격 보조 복제본이있는 가용성 그룹

  • 장애 조치 클러스터 인스턴스 노드가있는 가용성 그룹

  • 보조 복제본이 여러 개인 가용성 그룹 (복제본 중 하나에 대한 비동기 커밋)

 

간단한 토폴로지를 보자. 이 다이어그램은 고객 설정을 설명합니다.

 

 

 

업그레이드 단계

 

  • SQL Server에서 AG 속성으로 이동

     

     

     

     

 

자동 장애 조치를 피하려면 가용성 모드를 "비동기"로 변경하십시오.

 

이러한 단계는 SSMS (SQL Management Studio)에 연결하여 기본 복제본 (노드)에서 수행해야합니다.

 

  • 두 번째 단계는 보조 복제본을 업그레이드하는 것입니다. [2014 년에서 2016 년으로 업그레이드 할 수 있습니다]

  • 이제 기본 모드에서 가용성 모드를 "동기"로 다시 변경하십시오.

  • AG의 대시 보드에서 동기화 상태가 "동기화"로 표시 될 때까지 기다리십시오.

  • 기본 노드를 교환하려면 장애 조치를 수행하십시오.

  • 현재 보조 노드를 업그레이드하십시오.

  • 아래의 resume 명령을 실행하십시오.

 

ALTER DATABASE  database_name  SET HADR 이력서

 

유용한 링크

 

'Database > AlwaysOn' 카테고리의 다른 글

Alwayson 동기화 지연 원인 파악  (1) 2024.02.14
데이터 동기화 대기 시간에 대한 일반적인 원인 및 문제 해결  (0) 2024.02.14
SQL Server Always On 가용성 그룹에 대한 가용성 복제본 및 데이터베이스를 모니터링하기 위한 동적 관리뷰  (0) 2023.04.05
SQL Server AlwaysOn synchronous-commit 환경에서 동기화 레이턴시 트러블슈팅  (0) 2020.08.27
AlwaysOn 패치 적용  (0) 2020.08.27

+ Recent posts

티스토리툴바