SQL Server DELAYED_DURABILITY 옵션으로 트랜잭션 로그 쓰기 성능 개선 하기

 

l  Version : SQL Server

 

SQL Server 데이터베이스에는 모든 트랜잭션과 트랜잭션에 의해 적용된 데이터베이스 수정 내용을 기록하는 트랜잭션 로그가 있다. 트랜잭션 로그는 데이터베이스의 매우 중요한 요소로 시스템 오류가 발생한 경우 데이터베이스를 일관된 상태로 다시 전환하려면 로그가 필요하다.

l  트랜잭션 로그 아키텍처 : https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver16

 

데이터베이스에 대량의 쓰기 작업이 발생하면 트랜잭션 로그에는 변경사항을 기록하기 위해 오버헤드가 발생하면서 로그 쓰기 작업(Write Log) 지연되는 경우가 발생한다. 로그 모니터링을 통하여 Write Log 지연이 발생할 경우 DELAYED_DURABILITY 옵션을 사용하여 해당 문제를 개선할 있다. 옵션을 사용할 경우 트랜잭션 로그의 커밋을 기다리지 않기 때문에 워크로드의 대기 시간이 줄어들어 성능 개선은 있지만 트랜잭션 데이터를 소실할 있기 때문에 주의해서 사용해야 한다. 이번 포스트에서는 DELAYED_DURABILITY 옵션에 대한 특징을 살펴보고, 어떠한 경우에 사용할 있는지 살펴본다.

 

SQL Server 트랜잭션 로그 파일에 데이터를 저장해야 트랜잭션 로그 파일이 저장된 디스크에 직접 데이터를 기록하지 않는다. 대신 모든 데이터는 인메모리 구조인 로그 캐시(로그 버퍼, 또는 로그 블록 이라고 불리기도 한다.) 직렬로 기록된다. 또한 SQL Server OS ACID 원칙을 준수해야 하므로 전체 로그 캐시를 디스크 하위 시스템에 저장하거나 필요한 경우 롤백 되는 트랜잭션 로그 파일로 플러시 한다. 로그 캐시의 크기는 512B ~ 64KB이다.

 

로그 캐시는 특정 조건에서 디스크로 플러시 된다.

l  트랜잭션이 커밋될

l  로그 캐시가 가득 차서 60KB 도달했을

l  Sys.sp_flush_log 실행될

l  CHECKPOINT 프로세스가 완료 되었을

 

SQL Server 로그 캐시가 트랜잭션 로그 파일로 플러시 되기 시작하는 순간 WRITELOG 대기 유형에 등록되고 로그 캐시가 메모리에서 디스크 드라이브의 파일로 데이터 플러시를 완료 때까지 해당 시간이 누적된다. 누적시간이 낮을수록 트랜잭션 로그파일의 쓰기 대기 시간이 낮아 진다.

 

SQL Server에서는 기본적으로 동기 트랜잭션 커밋을 사용하기 때문에 트랜잭션에 대한 로그 레코드가 디스크에 기록된 후에만 커밋을 성공으로 보고하고 클라이언트에 컨트롤을 반환한다. 따라서 이러한 커밋이 느릴수록 클라이언트에서는 느린 응답을 받을 수밖에 없다. 동기 커밋은 아래와 같은 완전 내구성이 있는 환경의 경우 필수적으로 사용해야한다.

l  시스템에서 데이터 손실을 허용할 없는 경우

l  병목 현상의 원인이 트랜잭션 로그 쓰기 대기 시간이 아닌 경우

 

DELAYED_DURABILITY 옵션을 활성화 경우, 비동기 트랜잭션 커밋을 사용하기 때문에 트랜잭션에 대한 로그 레코드가 디스크에 기록되기 전에 커밋 성공으로 클라이언트에 컨트롤을 반환하기 때문에 클라이언트에서는 빠른 응답을 얻을 있다. 또한 동시 트랜잭션의 로그 I/O 경합 가능성이 낮아지고, 청크 구성으로 디스크에 플러시하여 경합을 줄이고 처리 속도를 높일 있다. 비동기 커밋의 경우 아래와 같은 환경에서 사용할 있다.

l  약간의 데이터 손실을 허용하는 경우

l  트랜잭션 로그 쓰기 중에 병목 현상이 발생하는 경우

l  작업의 경합률이 높은 경우

 

DELAYED_DURABILITY 옵션은 데이터베이스 수준에서 트랜잭션 내구성 수준을 제어할 있다. 아래 스크립트는 DELAYED_DURABILITY 옵션을 데이터베이스 수준에서 변경한다.

ALTER DATABASE ... SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }

 

l  DISABLED : 기본값으로 커밋 수준 설정 (DELAYED_DURABILITY={ON|OFF}) 상관없이 데이터베이스 커밋된 모든 트랜잭션이 완전 내구성을 가진다. 저장 프로시저를 변경하고 다시 컴파일할 필요가 없다. 지연된 내구성으로 인해 데이터가 위험에 노출되지 않는다.

l  ALLOWED : 트랜잭션의 내구성이 트랜잭션 수준에서 결정된다. 커밋 수준 설정 (DELAYED_DURABILITY={ON|OFF}) 의해 결정된다.

l  FORCED : 데이터베이스에 커밋되는 모든 트랜잭션이 지연된 내구성을 가진다. 설정은 지연된 트랜잭션 내구성이 데이터베이스에 유용하고 어플리케이션 코드를 변경하지 않으려는 경우에 유용하다.

 

 

아래 스크립트는 커밋 수준을 설정한다.

DELAYED_DURABILITY = { OFF | ON }

 

l  OFF : 기본값으로 DELAYED_DURABILITY = FORCED 데이터베이스 옵션을 적용하여 커밋이 비동기적이고 지연된 내구성을 갖는 경우를 제외하고 트랜잭션은 완전 내구성을 가진다.

l  ON : DELAYED_DURABILITY = FORCED 데이터베이스 옵션을 적용하여 커밋이 동기적으로 완전 내구성있는 경우를 제외하고 트랜잭션은 지연된 내구성을 가진다.

 

아래 스크립트는 저장 프로시저에 커밋 수준을 적용한 예시이다.

CREATE PROCEDURE [<procedureName>] /* parameters go here */
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER   
AS BEGIN ATOMIC WITH    
(   
    DELAYED_DURABILITY = ON,   
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,   
    LANGUAGE = N'English'   
)   
/* procedure body goes here */
END

 

 

지연된 트랜잭션 내구성을 강제로 적용할 있도록 COMMIT 구문으로도 확장할 있는데, 데이터베이스 수준에서 DELAYED_DURABILITY DISABLED 또는 FORCED 경우 COMMIT 옵션은 무시된다.

COMMIT [ { TRAN | TRANSACTION } ] [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]

 

l  OFF : 기본값으로 DELAYED_DURABILITY = FORCED 데이터베이스 옵션을 적용하여 CIMMIT 비동기 적으로 지연된 내구성을 갖는 경우를 제외하고 COMMIT 트랜잭션은 완전 내구성을 가진다.

l  ON : DELAYED_DURABILITY = DISABLED 데이터베이스 옵션을 적용하여 COMMIT 동기적이고 완전 내구성 있는 경우를 제외하고 COMMIT 트랜잭션은 지연된 내구성을 가진다.

 

 

지연된 내구성을 사용할 경우 특정 상황에서 데이터를 손실 가능성이 있기 때문에 반드시 해당 비즈니스의 목적과 데이터베이스 성능 등을 고려하여 사용 여부를 결정할 있도록 한다.

 

[참고자료]

l  Control Transaction Durability : https://learn.microsoft.com/en-us/sql/relational-databases/logs/control-transaction-durability?view=sql-server-ver16

l  Measure Delayed Durability impact in SQL Server 2016 and later : https://www.mssqltips.com/sqlservertip/6355/measure-delayed-durability-impact-in-sql-server-2016-and-later/

l  Get SQL Server Delayed Durability Advantages Without Configuration Changes : https://www.mssqltips.com/sqlservertip/6324/get-sql-server-delayed-durability-advantages-without-configuration-changes/

l  How to handle the SQL Server WRITELOG wait type : https://www.sqlshack.com/how-to-handle-the-sql-server-writelog-wait-type/

l  Improve SQL Server transaction log performance with Delayed Durability : https://www.sqlshack.com/improve-sql-server-transaction-log-performance-with-delayed-durability/

l  The Transaction Log (SQL Server) : https://learn.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-ver16

l  SQL Server Transaction Log Architecture and Management Guide : https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver16

 

 

 

2022-10-30/ Sungwook Kang / http://sungwookkang.com

 

 

 

SQL Server, MS SQL, DELAYED_DURABILITY, 트랜잭션 로그 비동기 커밋, WAL, Write Log

SQL Server 에서 AWS S3 직접 백업하기

 

l  Version : SQL Server 2022

 

SQL Server 2022부터는 데이터 플랫폼에 통합 오브젝트 스토리지를 도입하여 Azure Storage외에도 AWS S3 호환 오브젝트 스토리지도 사용할 있다. , SQL Server에서 AWS S3 직접 백업을 있게 되었다.

기존 SQL Server 2019 경우 Microsoft Azure 저장소만 가능 하였다. SQL Server 2022 부터 도입된 S3 REST API SQL Server에서 AWS S3 직접 백업, 복원을 있어, 백업 이동에 따른 프로세스 단축 대용량 백업을 진행할 경우에도 로컬 공간이 부족하여도 백업을 진행할 있게 되었다.

 

지원되는 백업 종류

l  전체 백업

l  차등 백업

l  트랜잭션 로그 백업

l  복사 전용 백업 (COPY ONLY)

 

지원되지 않는 백업 종류

l  스냅샷 백업 (FILE_SNAPSHOT)

l  미러 백업 (MIRROR)

 

AWS S3버킷으로 백업 복원을 수행 하기위해서는 아래 정보가 필요 하다.

l  S3 버킷이름

l  S3 URI

l  S3 URL

l  IAM 액세스 ID

l  IAM 액세스

 

AWS IAM(Idnetity Access Management) 정책은 사용자의 권한 또는 특정 서비스에 대해 수행할 있는 역할을 정의한다. SQL Server 2022에서 S3 버킷으로 직접 데이터베이스 백업을 수행하려면 ListBucket PutObject 권한이 필요하다.

l  ListBucket : ListBucket 액세스는 요청의 인증된 발신자가 소유한 모든 버킷 목록을 반환한다.

l  PutObject : PutObject 액세스를 통해 버킷에 객체를 작성하고 추가할 있다.

 

SQL Server에서 AWS S3 백업을 하기 위해 우선 자격증명을 만들어야 한다. 아래는 자격증명을 만드는 스크립트이다.

CREATE CREDENTIAL   [s3://<endpoint>:<port>/<bucket>]
WITH   
        IDENTITY    = 'S3 Access Key',
        SECRET      = '<AccessKeyID>:<SecretKeyID>';

 

l  [s3://<endpoint>:<port>/<bucket>] : 자격 증명 이름에는 S3 버킷 이름이 포함되어야 한다. S3:// 시작하고 접두사 HTTPS:// 제거한 S3 버킷 URL 따른다.

l  IDENTITY : S3 Access Key 값으로, 자격 증명에서 S3 커넥터를 사용하고 있음을 나타낸다.

l  SECRET : 접근키 ID 비밀 키를 콜론으로 구분하여 입력한다. 콜론 기호는 구분자 역할을 한다.  

 

 

 

백업 명령은 기존에 사용하던 백업문과 거의 동일하다. 다만 백업 위치가 DISK 아닌 URL 사용한다.

BACKUP DATABASE [DatabaseName] TO URL = 'CredentialName\Backupfilename.bak'

 

 

백업 완료 S3 확인해보면 백업파일이 생성된 것을 확인할 있다.

 

아래 스크립트는 백업하려는 데이터베이스를 여러 스트라이프된 백업 파일로 오브젝트 스토리지 엔드포인트를 사용하여 백업을 수행한다.

BACKUP DATABASE <db_name>
TO      URL = 's3://<endpoint>:<port>/<bucket>/<database>_01.bak'
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_02.bak'
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_03.bak'
--
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_64.bak'
WITH    FORMAT -- overwrite
,       STATS               = 10
,       COMPRESSION;

 

 

 

아래 스크립트는 오브젝트 스토리지 엔드포인트 위치에서 데이터베이스 복원을 수행한다.

RESTORE DATABASE <db_name>
FROM    URL = 's3://<endpoint>:<port>/<bucket>/<database>_01.bak'
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_02.bak'
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_03.bak'
--
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_64.bak'
WITH    REPLACE -- overwrite
,       STATS = 10;

 

 

데이터를 저장하려면 S3 호환 오브젝트 스토리지 공급자는 파트(Part) 불리는 여러 블록으로 파일을 분할해야 한다. 파일은 최대 10,000개의 파트로 분할 있으며, 파트의 크기는 5MB ~ 20MB이다. 범위는 매개변수 MAXTRANSFERSIZE 통해 T-SQL BACKUP 명령으로 제어할 있다. MAXTRANSFERSIZE 기본값은 10MB 이므로 파트의 기본 크기는 10MB이다.  , 단일 백업 파일의 크기는 MAXTRANSFERSIZE 기본값으로 설정되어 있을 경우 100,000MB이다. 백업 스트라이프가 100,000MB 초과할 경우 아래와 같은 오류 메시지를 반환한다.

Msg 3202, Level 16, State 1, Line 161
Write on 's3://<endpoint>:<port>/<bucket>/<path>/<db_name>.bak' failed: 87(The parameter is incorrect.)
Msg 3013, Level 16, State 1, Line 161
BACKUP DATABASE is terminating abnormally.

 

단일 백업 파일의 크기가 100,000MB 보다 파일을 백업 해야 경우 최대 64개의 URL 스트라이프 백업을 지원한다. , 가능한 최대 백업 파일 크기는 10,000part * MAXTRANSFERSIZE * URL이다. 압축 백업을 사용할 경우 백업 파일 사이즈가 현저히 많이 줄어들기 때문에 백업 압축 옵션을 적극 활용할 있도록 한다.

아래 예제는 MAXTRANSFERSIZE 사용하여 20MB으로 설정하고, 백업 압축 암호화한다.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = <password>;
GO
 
CREATE CERTIFICATE AdventureWorks2019Cert
    WITH SUBJECT = 'AdventureWorks2019 Backup Certificate';
GO
-- Backup database
BACKUP DATABASE AdventureWorks2019
TO URL = 's3://<endpoint>:<port>/<bucket>/AdventureWorks2019_Encrypt.bak'
WITH FORMAT, MAXTRANSFERSIZE = 20971520, COMPRESSION,
ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = AdventureWorks2019Cert)
GO

 

백업 복원 엔진에서 URL 길이는 259바이트로 제한되어 있기 때문에 s3:// 제외하고 사용자의 경로 길이(호스트 이름 + 개체키) 259 - 5 = 254글자까지 입력할 있다. 만약 URL 길이를 초과할 경우 아래와 같은 오류가 반환된다.

SQL Server has a maximum limit of 259 characters for a backup device name. The BACKUP TO URL consumes 36 characters for the required elements used to specify the URL - 'https://.blob.core.windows.net//.bak', leaving 223 characters for account, container, and blob names put together'

 

S3 스토리지는 SQL 호스트와 S3 서버간의 시간 차이가 15분을 초과할 마다 연결을 거부하고 “InvalidSignatureException”오류를 SQL Server 보낸다. SQL Server에서는 아래와 같은 오류로 반환된다.

Msg 3201, Level 16, State 1, Line 28
Cannot open backup device '<path>'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 28
BACKUP DATABASE is terminating abnormally.

 

 

[참고자료]

l  SQL Server backup to URL for S3-compatible object storage : https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url-s3-compatible-object-storage?view=sql-server-ver16

l  SQL Server back up to URL for S3-compatible object storage best practices and troubleshooting : https://learn.microsoft.com/ko-kr/sql/relational-databases/backup-restore/sql-server-backup-to-url-s3-compatible-object-storage-best-practices-and-troubleshooting?view=sql-server-ver16

l  https://www.mssqltips.com/sqlservertip/7301/sql-server-2022-backup-restore-aws-s3-storage/

l  https://www.mssqltips.com/sqlservertip/7302/backup-sql-server-2022-database-aws-s3-storage/

 

 

 

2022-10-28/ Sungwook Kang / http://sungwookkang.com

 

 

 

SQL Server, MS SQL, AWS S3 backup, S3 백업, 클라우드 백업, cloud backup

SSRS 리포트에서 이미지 추가하기

 

·       Version : SSRS

 

SSRS에서 이미지를 출력하는 방법에는 3가지가 있다.

·       Embedded  : SSRS 서버에 있는 이미지로 리포트에 표시

·       Database : 데이터베이스에 저장된 이미지를 표시

·       External : 외부 URL 이미지를 표시

 

이번 포스트에서는 외부 URL 이미지를 출력하는 방법에 대해서 살펴본다. 외부 URL 이미지를 표시하기 위해서는 이미지 주소를 포함하고 있는 데이터셋이 필요하다. 필자의 경우 검색 사이트에서 검색한 로고 이미지의 주소를 사용하여 실습용 데이터셋을 만들었다.

select 'Microsoft' as Name, 'https://cdn.arstechnica.net/wp-content/uploads/2012/08/microsoft-metro-tall.png' as Image_URL

union all

select 'LinkedIn' as Name, 'https://nonesnotes.files.wordpress.com/2019/09/lil.jpg' as Image_URL

union all

select 'Google' as Name, 'https://www.adweek.com/wp-content/uploads/files/blogs/google-logo-hed-2014.jpg' as Image_URL

union all

select 'FaceBook' as Name, 'https://cdn0.tnwcdn.com/wp-content/blogs.dir/1/files/2015/07/sdffdsafsdf-1200x604.png' as Image_URL

 

 

 

리포트에서 테이블을 추가하고 컬럼에 [Image] 추가하였다. 이미지를 추가하게 되면 속성창이 나타나며, 외부 URL 이미지를 사용할 경우 “External’ 선택 한다. 그리고 이미지 URL 데이터 소스를 선택한다.

 

 

보고서 미리보기를 통하여 정상적으로 이미지가 출력되는지 확인한다.

 

 

[참고자료]

https://docs.microsoft.com/ko-kr/sql/reporting-services/report-design/add-an-external-image-report-builder-and-ssrs?view=sql-server-ver15

 

 

 

 

 

 

 

2020-04-16 / Sungwook Kang / http://sungwookkang.com

 

 

 

 

 

SSRS, SQL Server, 리포팅 서비스, 대시보드, 이미지 출력, 대시보드 이미지 삽입, SSRS이미지 출력, 보고서 이미지 포함하기

SSRS 보고서 실행 로그 감사(Audit)

 

·         Version : SSRS

 

SSRS에서 보고서가 얼마나 자주 실행되는지, 누가 또는 어떤 프로세스가 보고서를 실행하는지, 실행하는데 걸리는 시간, 보고서 매개변수에 대한 통계, 사용되지 않는 보고서등에 대한 정보가 필요할 SSRS Execution Logging 기능을 사용하여 해당 정보를 얻을 있다.

 

로깅 작동 방식은, Reporting Services 서버에서 로깅 기능을 활성화 하여 사용할 있다. 로깅이 활성화 되면 ReportServer 데이터베이스의 dbo.ExectionLogStorage 테이블에 행위가 기록 된다. 감사 데이터에 액세스하려면 ReportServer 데이터베이스의 ExecutionLog, ExecutionLog2, ExecutionLog3 뷰테이블  하나를 사용하면 된다.

 

SSRS에서 로깅을 활성화 화기 위해서는 SSMS 사용하여 SSRS 서버에 접속한다. 이때 접속하는 Server Type 주의한다.

 

SSRS 서버에 접속 되었으면 서버에서 마우스 오른쪽을 클릭하여 [Properties] 선택한다.

 

속성창이 나타면 [Logging] – [Enable report execution logging] 체크하여 활성화 한다.

 

[Advanced] 탭에서 동일한 속성을 설정할 있다.

 

로깅을 구성하는 특성을 정의할 있는데, ExecutionLevel 따라 로깅레벨이 달라진다. Normal Verbose 로깅 2가지가 제공되며, Verbose 로깅은 데이터 소스 데이터 세트에 대한 추가 감사 데이터가 수집된다.

 

로깅 설정이 완료 되었으면 SSMS에서 SQL Server 접속하여 dbo. ExectionLogStorage테이블에 보고서 실행에 대한 로깅 정보를 확인한다. 테이블에서 마지막열인 AdditionalInfo컬럼은데이터 소스 데이터 세트에 대한 정보가 XML 기록된다. 로그를 좀더 쉽게 확읺기 위해ExecutionLog, ExecutionLog2, ExecutionLog3 뷰가 제공된다.

 

 

[참고자료]

https://docs.microsoft.com/en-us/sql/reporting-services/report-server/report-server-executionlog-and-the-executionlog3-view?view=sql-server-ver15

 

 

2020-04-07 / Sungwook Kang / http://sungwookkang.com

 

 

 

 

 

SSRS, SQL Server, 리포팅 서비스, 보고서 만들기, SQL 보고서, SQL Server Reporting Services, SSRS Logging, SSRS Audit, 보고서 감사, 보고서 실행 로그

+ Recent posts