가용성 그룹 모니터링(Transact-SQL)

  • 아티클

적용 대상:SQL Server

Transact-SQL에서는 Always On 가용성 그룹을 사용하여 가용성 그룹, 복제본 및 연결된 데이터베이스를 모니터링할 수 있도록 여러 카탈로그 및 동적 관리 뷰와 서버 속성을 제공합니다. Transact-SQL SELECT 문을 사용하여 뷰를 통해 가용성 그룹과 해당 복제본 및 데이터베이스를 모니터링할 수 있습니다. 지정된 가용성 그룹에 대해 반환되는 정보는 주 복제본(replica) 호스팅하는 SQL Server 인스턴스에 연결되어 있는지 또는 보조 복제본(replica) 연결되었는지에 따라 달라집니다.

팁

이러한 뷰의 대부분은 ID 열을 사용해서 단일 쿼리에서 여러 뷰에서 정보를 반환할 수 있습니다.

사용 권한

Always On 가용성 그룹 카탈로그 뷰에는 서버 인스턴스에 대한 VIEW ANY DEFINITION 권한이 필요합니다. Always On 가용성 동적 관리 뷰에는 서버에 대한 VIEW SERVER STATE 권한이 필요합니다.

서버 인스턴스의 Always On 가용성 그룹 기능 모니터링

서버 인스턴스에서 Always On 가용성 그룹 기능을 모니터링하기 위해서는 다음 기본 제공 함수를 사용합니다:

SERVERPROPERTY 함수
Always On 가용성 그룹을 사용할 수 있는지와 사용할 경우에 서버 인스턴스에서 시작되었는지 여부에 대한 서버 속성 정보를 반환합니다.

열 이름: IsHadrEnabled, HadrManagerStatus

WSFC 클러스터에서 가용성 그룹 모니터링

Always On 가용성 그룹에 대해 활성화된 로컬 서버 인스턴스를 호스트하는 WSFC(Windows Server 장애 조치(failover) 클러스터링) 클러스터를 모니터링 하기 위해 다음 보기를 사용합니다:

sys.dm_hadr_cluster
Always On 가용성 그룹이 활성화된 SQL Server 인스턴스를 호스팅하는 WSFC(Windows Server 장애 조치(failover) 클러스터링) 노드에 WSFC 쿼럼 이 있는 경우 sys.dm_hadr_cluster 클러스터 이름과 쿼럼에 대한 정보를 노출하는 행을 반환합니다. WSFC 노드에 쿼럼이 없는 경우 행이 반환되지 않습니다.

열 이름: cluster_name, quorum_type, quorum_type_desc, quorum_state, quorum_state_desc

sys.dm_hadr_cluster_members
SQL Server의 로컬 Always On 사용 인스턴스를 호스트하는 WSFC 노드에 WSFC 쿼럼이 있는 경우, 쿼럼 및 각 멤버의 상태를 만드는 각 멤버에 대한 행을 반환합니다.

열 이름: member_name, member_type, member_type_desc, member_state, member_state_desc, number_of_quorum_votes

sys.dm_hadr_cluster_networks
가용성 그룹의 서브넷을 만드는 것에 참여하는 모든 멤버에 대한 행을 반환합니다. 이 동적 관리 뷰를 사용하여 각 가용성 복제본에 대해 만들어진 네트워크 가상 IP의 유효성을 확인할 수 있습니다.

열 이름: member_name, network_subnet_ip, network_subnet_ipv4_mask, network_subnet_prefix_length, is_public, is_ipv4

기본 키: member_name + network_subnet_IP + network_subnet_prefix_length

sys.dm_hadr_instance_node_map
Always On 가용성 그룹에 조인된 가용성 복제본(replica) 호스트하는 SQL Server의 모든 인스턴스에 관해 서버 인스턴스를 호스트하는 WSFC(Windows Server 장애 조치(failover) 클러스터링) 노드의 이름을 반환합니다. 이 동적 관리 뷰의 용도는 다음의 사항과 같습니다:

  • 이 동적 관리 뷰는 동일한 WSFC 노드에서 호스팅되는 여러 개의 가용성 복제본이 있는 가용성 그룹을 검색하는 데 유용하며, 이는 가용성 그룹이 잘못 만들어진 경우 FCI 장애 조치(failover) 후에 발생할 수 있는 지원되지 않는 구성입니다.

  • 여러 SQL Server 인스턴스가 동일한 WSFC 노드에 호스팅이 될 때 리소스 DLL이 이 동적 관리 뷰를 사용해서 연결할 SQL Server의 인스턴스를 확인합니다.

열 이름: ag_resource_id, instance_name, node_name

sys.dm_hadr_name_id_map
SQL Server의 현재 인스턴스는 세 가지 고유 ID인 가용성 그룹 ID, WSFC 리소스 ID 및 WSFC 그룹 ID에 조인한 Always On 가용성 그룹의 매핑을 보여줍니다. 이 매핑의 목적은 WSFC 리소스/그룹의 이름이 바뀐 시나리오를 처리하는 것입니다.

열 이름: ag_name, ag_id, ag_resource_id, ag_group_id

참고

또한 모니터링 가용성 복제본 섹션의 sys.dm_hadr_availability_복제본(replica)_cluster_nodes 및 sys.dm_hadr_availability_복제본(replica)_cluster_states,모니터링 가용성 데이터베이스의 sys.availability_databases_cluster 및 sys.dm_hadr_database_복제본(replica)_cluster_states 을(를) 참조해 주세요. 이 항목의 뒷부분에 있는 모니터링 가용성 데이터베이스 섹션의 을(를) 참조해 주세요.

WSFC 클러스터 및 AlwaysOn 가용성 그룹에 대한 자세한 내용은 SQL Server의 WSFC(Windows Server 장애 조치(failover) 클러스터링) 및 장애 조치(failover) 클러스터링 및 Always On 가용성 그룹(SQL Server)을 참조하세요.

가용성 그룹 모니터링

서버 인스턴스가 가용성 복제본(replica) 호스트하는 가용성 그룹을 모니터링하려면 다음 보기를 사용합니다:

sys.availability_groups
SQL Server의 로컬 인스턴스에서 가용성 복제본(replica)을 호스트하는 각 가용성 그룹에 대해 하나의 행을 반환합니다. 각 행에는 가용성 그룹 메타데이터의 캐시된 복사본이 포함됩니다.

열 이름: group_id, name, resource_id, resource_group_id, failure_condition_level, health_check_timeout, automated_backup_preference, automated_backup_preference_desc

sys.availability_groups_cluster
WSFC 클러스터의 각 가용성 그룹에 대해 하나의 행을 반환합니다. 각 행에는 WSFC(Windows Server 장애 조치(Failover) 클러스터링) 클러스터의 가용성 그룹 메타데이터가 포함됩니다.

열 이름: group_id, name, resource_id, resource_group_id, failure_condition_level, health_check_timeout, automated_backup_preference, automated_backup_preference_desc

sys.dm_hadr_availability_group_states
SQL Server의 로컬 인스턴스에서 가용성 복제본(replica)을 가지고 있는 각 가용성 그룹에 대해 행을 반환합니다. 각 행에는 지정된 가용성 그룹의 상태를 정의하는 상태가 표시됩니다.

열 이름: group_id, primary_replica, primary_recovery_health, primary_recovery_health_desc, secondary_recovery_health, secondary_recovery_health_desc, synchronization_health, synchronization_health_desc

가용성 복제본(replica) 모니터링

가용성 복제본(replica) 모니터링하려면 다음 보기 및 시스템 함수를 사용합니다:

sys.availability_replicas
SQL Server의 로컬 인스턴스에서 가용성 복제본(replica)을 호스팅하는 각 가용성 그룹의 모든 가용성 복제본에 대해 행을 반환합니다.

열 이름: 복제본(replica)_id, group_id, 복제본(replica)_metadata_id, 복제본(replica)_server_name, owner_sid, endpoint_url, availability_mode, availability_mode_desc, failover_mode, failover_mode_desc, session_timeout, primary_role_allow_connections, primary_role_allow_connections_desc, secondary_role_allow_connections, secondary_role_allow_connections_desc, create_date, modify_date, backup_priority, read_only_routing_url

sys.availability_read_only_routing_lists
WSFC 장애 조치(Failover) 클러스터의 Always On 가용성 그룹에 있는 각 가용성 복제본의 읽기 전용 라우팅 목록에 대한 행을 반환합니다.

열 이름: 복제본(replica)_id, routing_priority, read_only_복제본(replica)_id

sys.dm_hadr_availability_replica_cluster_nodes
WSFC(Windows Server 장애 조치(Failover) 클러스터링) 클러스터에 있는 Always On 가용성 그룹의 모든 가용성 복제본(조인 상태에 상관없음)에 대해 하나의 행을 반환합니다.

열 이름: group_name, 복제본(replica)_server_name, node_name

sys.dm_hadr_availability_복제본(replica)_cluster_states
WSFC(Windows Server 장애 조치(Failover) 클러스터링) 클러스터에 있는 모든 Always On 가용성 그룹(복제본 위치에 상관없음)의 각 복제본(조인 상태에 상관없음)에 대해 하나의 행을 반환합니다.

열 이름: 복제본(replica)_id, 복제본(replica)_server_name, group_id, join_state, join_state_desc

sys.dm_hadr_availability_replica_states
각 로컬 가용성 복제본(replica) 상태를 보여주는 행과 동일한 가용성 그룹의 각 원격 가용성 복제본(replica) 대한 행을 반환합니다.

열 이름: replica_id, group_id, is_local, role, role_desc, operational_state, operational_state_desc, connected_state, connected_state_desc, recovery_health, recovery_health_desc, synchronization_health, synchronization_health_desc, last_connect_error_number, last_connect_error_description, and last_connect_error_timestamp

sys.fn_hadr_backup_is_preferred_복제본(replica)
현재 복제본(replica)이 기본 백업 복제본(replica) 여부를 결정합니다.

참고

가용성 복제본의 성능 카운터(SQLServer:가용성 복제본 성능 개체)에 대한 자세한 내용은 SQL Server, 가용성 복제본을 참조해 주세요.

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

가용성 데이터베이스를 모니터링하려면 다음 뷰를 사용합니다:

sys.availability_databases_cluster
로컬 복사본 데이터베이스가 아직도 가용성 그룹에 조인되어 있는지에 상관없이 클러스터의 모든 Always On 가용성 그룹의 일부인 SQL Server 인스턴스의 데이터베이스별로 하나의 행을 포함합니다.

참고

데이터베이스가 가용성 그룹에 추가되면 주 데이터베이스가 자동으로 그룹에 조인됩니다. 가용성 그룹에 조인하기 이전에 각각의 보조 복제본(replica)에서 각각의 보조 데이터베이스를 준비해야만 합니다.

열 이름: group_id, group_database_id, database_name

sys.databases
SQL Server 인스턴스의 데이터베이스당 하나의 행을 포함합니다. 데이터베이스가 가용성 복제본(replica) 속하는 경우 해당 데이터베이스의 행에는 복제본(replica) GUID와 가용성 그룹 내의 데이터베이스 고유 식별자가 표시됩니다.

Always On 가용성 그룹 열 이름: 복제본(replica)_id, group_database_id

sys.dm_hadr_auto_page_repair
서버 인스턴스에서 의하여 가용성 그룹에 대해 호스트 되는 가용성 복제본(replica) 가용성 데이터베이스에 대한 모든 자동 페이지 복구 시도에 대한 행을 반환합니다. 이 보기에는 데이터베이스당 최대 100개의 행과 함께 지정된 주 데이터베이스 또는 보조 데이터베이스에서의 최신 자동 페이지 복구 시도에 대한 행이 포함되어 있습니다. 데이터베이스가 최대값에 도달하는 즉시 다음 자동 페이지 복구 시도에 대한 행이 기존 항목 중 하나를 대체합니다.

열 이름: database_id, file_id, page_id, error_type, page_상태, modification_time

sys.dm_hadr_database_replica_states
SQL Server의 로컬 인스턴스가 가용성 복제본을 호스팅하는 가용성 그룹에 참여하는 각 데이터베이스에 대한 행을 반환합니다.

열 이름: database_id, group_id, 복제본(replica)_id, group_database_id, is_local, synchronization_state, synchronization_state_desc, is_commit_participant, synchronization_health, synchronization_health_desc, database_state, database_state_desc, is_suspended, suspend_reason, suspend_reason_desc, recovery_lsn, truncation_lsn, last_sent_lsn, last_sent_time, last_received_lsn, last_received_time, last_hardened_lsn, last_hardened_time, last_redone_lsn, last_redone_time, log_send_queue_size, log_send_rate, redo_queue_size, redo_rate, filestream_send_rate, end_of_log_lsn, last_commit_lsn, last_commit_time, low_water_mark_for_ghosts

sys.availability_databases_cluster
WSFC(Windows Server 장애 조치(Failover) 클러스터링) 클러스터링의 각 가용성 그룹에 가용성 데이터베이스의 인사이트를 제공하기 위하여 의도한 정보가 들어 있는 행을 반환합니다. 이 동적 관리 뷰는 장애 조치(failover)를 계획 또는 응답하거나 가용성 그룹의 보조 복제본(replica) 지정된 주 데이터베이스에서 로그 잘림을 보유하는 것을 검색할 때 유용합니다.

열 이름: replica_id, group_database_id, database_name, is_failover_ready, is_pending_secondary_suspend, is_database_joined, recovery_lsn, truncation_lsn

참고

기본 복제본(replica) 위치는 가용성 그룹에 대한 신뢰할 수 있는 원본입니다.

참고

가용성 데이터베이스(SQLServer:Database 복제본 성능 개체)의 성능 카운터에 대한 자세한 내용은 SQL Server, 데이터베이스 복제본을 참조해 주세요. 또한 가용성 데이터베이스에서 트랜잭션 로그 작업을 모니터링하려면 SQLServer:Databases 성능 개체의 다음 카운터를 사용해 주세요. 로그 플러시 쓰기 시간(ms), Log Flushes/sec, Log Pool Cache Misses/sec, Log Pool Disk Reads/sec, Log Pool Requests/sec입니다. 자세한 내용은 SQL Server, 데이터베이스 개체를 참조해 주세요.

가용성 그룹 수신기 모니터링

WSFC 클러스터의 서브넷에서 가용성 그룹 수신기를 모니터링하려면 다음 뷰를 사용합니다:

sys.availability_group_listener_ip_addresses
가용성 그룹 수신기에 대해 현재 온라인 상태인 모든 준수 가상 IP 주소에 대한 행을 반환합니다.

열 이름: listener_id, ip_address, ip_subnet_mask, is_dhcp, network_subnet_ip, network_subnet_prefix_length, network_subnet_ipv4_mask, 상태, state_desc

sys.availability_group_listeners
지정된 가용성 그룹에 대해 0개의 행을 반환하여 가용성 그룹에 연결된 네트워크 이름이 없음을 나타내거나 WSFC 클러스터의 각 가용성 그룹 수신기 구성에 대해 하나의 행을 반환합니다.

열 이름: group_id, listener_id, dns_name, 포트, is_conformant, ip_configuration_string_from_cluster

sys.dm_tcp_listener_states
각 TCP 수신기에 대한 동적 상태 정보가 포함된 행을 반환합니다.

열 이름: listener_id, ip_address, is_ipv4, 포트트, 형식, type_desc, 상태, state_desc, start_time

기본 키: listener_id

가용성 그룹 수신기에 대한 자세한 내용은 가용성 그룹 수신기, 클라이언트 연결 및 애플리케이션 장애 조치(failover)(SQL Server)를 참조하세요.

관련 작업

Always On 가용성 그룹 모니터링 작업:

Always On 가용성 그룹 모니터링 참조(Transact-SQL):

Always On 성능 카운터:

Always On 가용성 그룹에 대한 정책 기반 관리

항목을 참고해 주세요

Always On 가용성 그룹(SQL Server)
Always On 가용성 그룹 개요(SQL Server)
가용성 그룹 모니터링(SQL Server)