SQL

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

• 높은 네트워크 대기 시간 / 낮은 네트워크 처리량   ----> 1차와 발급된 2차 간의 네트워크 전송이 느리거나 불안정한 경우 로그 전송 속도가 느려지고 2차로 전송되는 데이터의 양에 영향을 미칩니다(문서 https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2012/dn135335(v=sql.110)?redirectedfrom=MSDN )  

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

• 차단된 REDO 스레드   ---- 보조 복제본에 다른 문제나 성능 병목 현상이 있는 경우 REDO 스레드가 차단되어 데이터 동기화 대기 시간이 발생할 수 있습니다. (문서 https://techcommunity.microsoft.com/t5/sql-server-blog/alwayson-minimizing-blocking-of-redo-thread-when-running/ba-p/383963 )

• 공유 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

USE [master]
GO
CREATE FUNCTION [dbo].[ufn_LogicalDiskDrives]()
RETURNS @DriveList Table
  (
   [DriveLetter]  CHAR(1)
  ,[VolumeName]  VARCHAR(255)
  ,[FileSystem]  VARCHAR(50)
  ,[TotalSize] BIGINT
  ,[AvailableSpace]   BIGINT
  ,[FreeSpace] BIGINT 
  )
AS
BEGIN
--Written by Percy Reyes
  DECLARE @DriveLetter_ASCII_Code  INT
  DECLARE @FileSystemInstance  INT
  DECLARE @DriveCount  INT
  DECLARE @DriveCollection  INT
  DECLARE @Drive  INT
  DECLARE @Property  NVARCHAR(100)    
  DECLARE @DriveLetter  VARCHAR(1)  
  DECLARE @TotalSize BIGINT
  DECLARE @AvailableSpace  BIGINT
  DECLARE @FreeSpace  BIGINT  
  DECLARE @FileSystem VARCHAR(128)
  DECLARE @VolumeName VARCHAR(128)
  DECLARE @IsReady VARCHAR(5)
  
  --Creating a File System Object for getting files or disk info.
  exec sp_OACreate 'Scripting.FileSystemObject', @FileSystemInstance OUT
  --Getting the collection of drives
  exec sp_OAGetProperty @FileSystemInstance,'Drives', @DriveCollection OUT
  --Getting the count of drives from collection
  exec sp_OAGetProperty @DriveCollection,'Count', @DriveCount OUT

  --starting from Drive "A" (ASCII 65) 
  SET @DriveLetter_ASCII_Code = 65
  --to "Z" (ASCII 90)
  WHILE @DriveLetter_ASCII_Code <= 90
  BEGIN
  ---Creating the instance drive from Drive Collection 
        SET @Property = 'item("'+CHAR(@DriveLetter_ASCII_Code)+'")'
        exec sp_OAGetProperty @DriveCollection,@Property, @Drive OUT
  -- Getting the drive letter property
        exec sp_OAGetProperty @Drive,'DriveLetter', @DriveLetter OUT

  IF @DriveLetter = CHAR(@DriveLetter_ASCII_Code)
        BEGIN   
        -- Getting more properties from each drive   
     exec sp_OAGetProperty @Drive,'VolumeName', @VolumeName OUT 
     exec sp_OAGetProperty @Drive,'FileSystem', @FileSystem OUT
     exec sp_OAGetProperty @Drive,'TotalSize', @TotalSize OUT 
              exec sp_OAGetProperty @Drive,'AvailableSpace', @AvailableSpace OUT
              exec sp_OAGetProperty @Drive,'FreeSpace', @FreeSpace OUT          
              exec sp_OAGetProperty @Drive,'IsReady'  , @IsReady OUT; 
            
   IF @IsReady='True'
     INSERT INTO @DriveList ( [DriveLetter],[TotalSize], [AvailableSpace],[FreeSpace],[FileSystem] ,[VolumeName] )
     VALUES( @DriveLetter,@TotalSize,@AvailableSpace,@FreeSpace,@FileSystem,@VolumeName)
                             
            END
   -- forward next drive
            SET @DriveLetter_ASCII_Code = @DriveLetter_ASCII_Code +1
  END 
  
  EXEC sp_OADestroy @Drive 
  EXEC sp_OADestroy @DriveCollection

  RETURN
END

새 기능을 사용할 수 있도록 "Ole 자동화 절차" 구성 설정을 활성화해야 합니다. 그렇지 않으면 실패합니다.

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'Ole Automation Procedures', 1
RECONFIGURE WITH OVERRIDE
GO

ufn_LogicalDiskDrives 테스트

SELECT * FROM [dbo].[ufn_LogicalDiskDrives]() 

새 저장 프로시저 sp_fixeddrives는 모든 데이터베이스에서 호출할 수 있도록 시스템 개체로 표시됩니다. 이 저장 프로시저는 ufn_LogicalDiskDrives 함수에서 반환된 결과를 사용하고 총 데이터 파일 크기 및 총 로그 파일 크기 정보를 최종 결과에 추가합니다. 다음은 T-SQL 생성 코드입니다.

select ISNULL(DF.DriveLetter, LF.DriveLetter) DriveLetter,  DF.DataSize_GB, LF.LogSize_GB from  (
   select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024 AS DECIMAL(10,2)) DataSize_GB
   from sys.master_files
   where type_desc='ROWS'
   group by left(physical_name, 1), type_desc 
   ) DF
  full outer join 
  (
   select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024  AS DECIMAL(10,2)) LogSize_GB
   from sys.master_files 
   where type_desc='LOG'
   group by left(physical_name, 1), type_desc
   ) LF
  on DF.DriveLetter=LF.DriveLetter

결과는 다음과 같습니다.

마지막으로 저장 프로시저 sp_fixeddrives의 T-SQL 생성 코드는 다음과 같습니다.

USE [master]
GO
CREATE PROC dbo.sp_fixeddrives
WITH ENCRYPTION
AS
BEGIN
 SET NOCOUNT ON 
 -----Reporting Drive details ....
 select LDD.DriveLetter ,[VolumeName] ,[FileSystem]  
 ,CAST(TotalSize/1024.0/1024/1024.0 AS DECIMAL(10,2)) [Capacity_GB]
 ,CAST(FreeSpace/1024.0/1024/1024.0 AS DECIMAL(10,2)) [FreeSpace_GB]
 ,CAST((CAST(FreeSpace/1024.0/1024/1024.0 AS DECIMAL(10,2))/CAST(TotalSize/1024.0/1024/1024.0 AS DECIMAL(10,2)) ) *100 AS DECIMAL(10,2)) [Free %]
 , ISNULL( DBFiles.DataSize_GB,0) DataSize_GB,ISNULL( DBFiles.LogSize_GB,0) LogSize_GB
 from master.[dbo].[ufn_LogicalDiskDrives]() LDD
 full outer join 
 (
 select ISNULL(DF.DriveLetter, LF.DriveLetter) DriveLetter,  DF.DataSize_GB, LF.LogSize_GB from
  (
   select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024 AS DECIMAL(10,2)) DataSize_GB
   from sys.master_files
   where type_desc='ROWS'
   group by left(physical_name, 1), type_desc 
   ) DF
  full outer join 
  (
   select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024  AS DECIMAL(10,2)) LogSize_GB
   from sys.master_files 
   where type_desc='LOG'
   group by left(physical_name, 1), type_desc
   ) LF
  on DF.DriveLetter=LF.DriveLetter
  
 )  DBFiles
 ON DBFiles.DriveLetter =LDD.DriveLetter
 SET NOCOUNT OFF
END

저장 프로시저를 시스템 개체로 표시합니다.

USE [master]
GO
EXEC sys.sp_MS_marksystemobject 'sp_fixeddrives'

다음은 코드를 실행한 후의 출력입니다.

EXEC sp_fixeddrives

가용성 복제본 모니터링

sys.availability_replicas

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

이 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 가용성 그룹의 백업 이해 문서를 참조하세요.

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

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

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

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

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

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 가용성 그룹의 자동 페이지 복구 문서를 참조하세요.

sys.dm_hadr_database_replica_states

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

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

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

• 기본 복제본의 AG 대시보드:

• 

• 보조 복제본의 AG 대시보드:
• 

• 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