SQL Server Always On 가용성 그룹에 대한 가용성 복제본 및 데이터베이스를 모니터링하기 위한 동적 관리뷰

가용성 복제본 모니터링

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까지의 값을 사용할 수 있습니다.

 

• 자세한 내용은 Always On 가용성 그룹의 백업 이해 문서를 참조하세요.

Understanding backups on AlwaysOn Availability Groups - Part 1

 

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

 

• 읽기 전용 라우팅 에 대한 자세한 내용은 SQL Server 2016에서 가용성 그룹에 대한 읽기 전용 라우팅을 구성하는 방법 문서를 참조하십시오.

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

 

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

 

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

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

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

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 가용성 그룹은 자동 페이지 복구 기능을 제공합니다. 손상된 페이지의 경우 아래 이미지와 같이 다른 복제본에서 페이지를 수신합니다.

• 자세한 내용은 SQL Server Always On 가용성 그룹의 자동 페이지 복구 문서를 참조하세요.

Automatic Page Repair in SQL Server Always On Availability Groups

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