SQL Server Troubleshooting

개 요 :
SQL Server 2005 이후 버전에서는 트랜잭션 복제 운영중에 구독자로의 동기화가 지연될 경우 복제 모니터에서
[추적 프로그램 토큰] (Tracer Token)을 이용해 어떤 구간에 병목이 있는지 확인할 수 있습니다.

사용방법 :

1. 복제 모니터 시작














2. [추적 프로그램 토큰]에서 [추적 프로그램 삽입] Click

















위 화면에서 [추적 프로그램 삽입]을 Click하면 내부적으로 게시자에 Timestamp Transaction이 처리됩니다.
해당 트랜잭션은 LogReader Agent에 의해 배포 데이터베이스로 복사되고 이는 다시 Distribution Agent에
의해 구독자에 전달됩니다. 각 단계에 대한 commit 시간을 기록해 어떤 구간에서 지연이 발생하는지
체크하게 되는 것입니다.

[게시자에서 배포자로 연결]
LogReader Agent에 오류가 있거나 병목이 있는지 체크하게 됩니다.

[배포자에서 구독자로 연결]
Distribution Agent에 오류가 있거나 병목이 있는지 체크하게 됩니다.
또는 구독자 서버에 병목이 있는지도 체크할 필요가 있습니다.

시나리오 :

1. LogReader (로그 판독기) Agent가 중지(실패)된 경우

LogReader Agent가 실패한 상황을 가정하기 위해 해당 서비스를 중지한 상태에서 Tracer Token을 추가하면
보류 중... 상태로 나옵니다. 이는 LogReader Agent에서 게시자의 Timestamp Transaction을 읽지 못하기
떄문에 배포자 및 구독자로 반영이 안되기 떄문입니다.


















LogReader Agent가 다시 시작됐을 때 [게시자에서 배포자로 연결]에서 지연이 발생함을 확인할 수 있습니다.















2. Distribution Agent 가 중지된 경우

이번에는 특정 구독자로의 배포 에이전트(Distribution Agent)를 중지한 후 Tracer Token을 추가해 보았습니다.
 








두개의 구독자에 대해 한쪽 구독자 서버에 대한 배포 에이전트가 중지된 시나리오이며 이에 구독자A의 경우
병목이 없으나 구독자B의 경우 [배포자에서 구독자로 연결]이 보류중 상태임을 확인할 수 있습니다.
이경우 해당 구독자로의 배포 에이전트가 정상인지 체크해 볼 필요가 있으며 또한 구독자B서버에 문제가
없는지 역시 체크해야 될 것입니다.

기타 :
Tracer Token 은 위와 같이 복제 모니터에서 추가할 수 있고 또한 다음의 T-SQL문으로도 추가가 가능합니다.

use replpub -- 게시 데이터베이스
go
EXEC sys.sp_posttracertoken @publication = 'replpub' -- 게시 이름

추가된 Tracer Token 은 다음의 테이블에 저장됩니다.

USE Distribution
go
SELECT * FROM MStracer_tokens
SELECT * FROM MStracer_history

즉, 주기적으로 Tracer Token을 추가(가령, 매 5분마다 추가)해 다음의 쿼리를 수행해 어느 시점에 병목이 있는지
모니터링하실 수 있습니다.

SELECT publication_id, agent_id, t.publisher_commit,
       Datediff(s,t.publisher_commit,t.distributor_commit) as 'Time To Dist (sec)',
       Datediff(s,t.distributor_commit,h.subscriber_commit) as 'Time To Sub (sec)'
FROM MStracer_tokens t
JOIN MStracer_history h
ON t.tracer_id = h.parent_tracer_id

개요 :
Transacional Replication은 SQL Server 복제 유형(스냅샷 복제, 병합복제, ..)중 가장 많이 사용되는 방식으로
초기 스냅샷 동기화 후 게시 데이터베이스의 트랜잭션 로그를 읽어 변경 내용을 구독자로 전달하는 방식입니다.

동작방식 :
Transactional Replication은 다음의 주요 Agent에 의해 동작합니다.

Snapshot Agent
구독자로의 데이터 동기화를 위해 우선 게시자의 초기 스냅샷으로 구독자에 적용해줄 필요가 있습니다.

LogReader Agent
초기 스냅샷이 구독자에 반영되면 이후 LogReader Agent는 게시자 데이터베이스의 트랜잭션 로그에서
변경 내용을 읽어 이를 배포 데이터베이스(Distribution DB)에 저장하게 됩니다.

Distribution Agent
전달된 변경내용을 각 구독자로 전달합니다.

아래 MSDN문서에 Transaction Replication의 상세 동작 방식을 확인할 수 있습니다.

http://msdn.microsoft.com/ko-kr/library/ms151706(SQL.90).aspx





그림에서 Log Reader Agent는 게시자 데이터베이스의
트랜잭션 로그를 읽어(using sp_replcmds) 이를 배포자
데이터베이스의 MSRepl_Transactions 및
MSRepl_Commands 테이블에 저장(sp_MSadd_replcmds)
하게 됩니다.

이후 Distribution Agent는 Distribution 데이터베이스로
전달된 내용을 읽어 이를 각 구독자에 전달하게 됩니다.
이 시점에 각 구독자에서 Profiler Log를 수집해 보면
sp_MSupd..., sp_MSins..., sp_MSdel... 와 같은
프로시저들이수행되어 변경내용(INS/UPD/DEL)이
반영됨을 확인할 수 있습니다.

Distribution Database내 MSRepl_Transactions 와
MSRepl_Commands 테이블은 게시자의 변경내용을
구독자로 전달할 때 일종의 큐 역할을 하는데
복제 성능에 큰 영향을 줍니다.
즉, 두 테이블이 지나치게 커지게 되면 그만큼 구독자로의
동기화에 지연이 생기게 될 것입니다.
기본적으로 매 10분마다 Clean-up scheduled JOB이
수행되어 구독자로 이미 반영된 내역이나 retention period
이후 레코드를 정리해 줍니다.

이에 만일 Transactional Replication 에 문제가 있을 경우
복제 모니터에서 어떤 Agent에 오류가 있는지 확인하고
Distribution DB내 MSRepl_Transactions 와 MSRepl_Commands 테이블 건수를 체크해볼
필요가 있습니다.

 

개 요 :
가령, 게시자에서 대량의 데이터 Update/Delete (100만건)이 발생하게 되면 각 Row에 대한 sp_MSupd<tablename>
또는 sp_MSdel<tablename> 명령이 구독자로 복제되어 실행됩니다.
이경우 배포 데이터베이스(Distribution)로 대량의 commands가 전파된 후 다시 구독자로 복제되어 성능 이슈 및
Timeout 이 발생할 수 있습니다.
게시장서의 대량 Update/Delete작업을 Stored Procedure로 실행하고 Stored Procedure 실행을 구독자로 복제하게 되면
각각의 Update/Delete 명령이 수행되지 않고 구독자단에서 Stored Procedure가 실행됩니다.

참조 : http://msdn.microsoft.com/ko-kr/library/ms152754(SQL.105).aspx

방 법 :

<게시자>
use replpub
go
-- sample table
create table dbo.table1 (id int primary key, empName varchar(50), hireDate datetime default getdate())
go
-- insert sample data..
insert into dbo.Table1 (id, empName) values (1, 'Scott')
insert into dbo.Table1 (id, empName) values (2, 'James')
insert into dbo.Table1 (id, empName) values (3, 'Jason')
insert into dbo.Table1 (id, empName) values (4, 'Eric')
insert into dbo.Table1 (id, empName) values (5, 'Evan')
go

-- create stored procedure to delete whole data..
create procedure dbo.up_delAll
as
delete from dbo.Table1
go

1. 게시자에서 [새 게시] 생성

아래 아티클 선택창에서 실행을 게시할 저장 프로시저에 대한 [아티클 속성]을 CLICK합니다.





















복제 옵션을 [저장 프로시저 실행]으로 선택합니다.



















2. 스냅샷 초기화 후 구독자에 up_delALL 저장 프로시저가 생성된 것을 확인합니다.
3. 게시자에서 다음의 쿼리 실행

use replpub
go
-- delete a row
delete from Table1 where id = 1
go
-- execute stored procedure to delete all rows..
exec up_delAll
go

4. 배포 데이터베이스(distribution) sp_browsereplcmds 명령을 수행해 전달된 명령 확인

use distribution
go
sp_browsereplcmds

-- 다음의 두 command가 게시자로부터 전파되었음을 확인
/*
{CALL [dbo].[sp_MSdel_dboTable1] (1)}
{call "dbo"."up_delAll" }
*/

5. 구독자 서버에서 Profiler Trace 수집

exec [dbo].[sp_MSdel_dboTable1] 1
exec "dbo"."up_delAll"

[Note] 만일 저장 프로시저 실행을 게시하지 않고 게시자에서 다음의 명령을 직접 실행했을 때

delete from dbo.Table1

배포 에이전트로 전파된 command를 확인해 보면 다음과 같습니다.

{CALL [dbo].[sp_MSdel_dboTable1] (1)} 1
{CALL [dbo].[sp_MSdel_dboTable1] (2)} 2
{CALL [dbo].[sp_MSdel_dboTable1] (3)} 3
{CALL [dbo].[sp_MSdel_dboTable1] (4)} 4
{CALL [dbo].[sp_MSdel_dboTable1] (5)} 5

위의 경우 테이블에 5건만 존재하지만 만일 100만건의 데이터가 존재한다면 100만개의 command가 생성될 것입니다.
이로 인해 구독자에서 Profiler를 잡아보면 아래와 같이 각각의 delete문이 실행될 것입니다.

개요 :
SQL Server 2000/2005에서 Transactional Replication을 구성할 때 스냅샷 초기화하지 않고 게시 데이터베이스 Backup을
구독자에 Restore해 초기화하는 방법이 있습니다.

테스트 환경)
replpub - 게시 데이터베이스
replsub - 구독자 데이터베이스
table1   - 아티클

use replpub
go
create table dbo.Table1 (id int primary key, empName varchar(100), hireDate datetime default getdate())
go
insert into dbo.Table1(id, empName) values(1, 'Peter')
insert into dbo.Table1(id, empName) values(2, 'John')
insert into dbo.Table1(id, empName) values(3, 'Sam')

방법 :

STEP 1) 게시 생성 (이미 게시가 존재할 경우 SKIP함)

Management Studio의 [로컬 게시] > [새 게시]를 click해 [게시 마법사]를 이용해 새 게시를 생성합니다.
이때, 아래 [스냅샷 에이전트] 단계에서 스냅샷을 생성하지 않게 uncheck합니다.














STEP 2) 게시 속성의 [구독 옵션]에서 [백업 파일로 초기화 허용] 옵션을 True로 변경함(Default : False)














STEP 3) 게시자 데이터베이스에 대한 전체 백업을 받음

backup database replpub to disk = 'C:\Temp\replpub_full.bak' with init

이후 게시자에서 발생한 트랜잭션은 로그판독기(Log Reader) 에이전트가 읽어 Distribution DB에 저장한 다음 각 구독자에
전달될 것입니다.

insert into dbo.Table1(id, empName) values(4, 'Scott')
insert into dbo.Table1(id, empName) values(5, 'Eric')

STEP 4) 구독자에서 위 데이터베이스 Backup을 RESTORE 함

use master
go
restore database replsub from disk = 'C:\Temp\replpub_full.bak' with
move 'replTest'  to 'C:\SQL2008\MSSQL10.MSSQLSERVER\MSSQL\DATA\replsub.mdf',
move 'replTest_log' to 'C:\SQL2008\MSSQL10.MSSQLSERVER\MSSQL\DATA\replsub_log.ldf'

이 시점에 구독자 테이블에는 3 row만이 존재합니다. (id 4,5는 Backup 이후 발생한 트랜잭션)

STEP 5) T-SQL Script를 이용해 백업 파일로 초기화되는 구독을 생성함


use [replpub]
go
exec sp_addsubscription @publication = N'backup_initialize_test', @subscriber = N'<구독자서버>', @destination_db = N'replsub',
@subscription_type = N'Push', @article = N'all', @update_mode = N'read only', @subscriber_type = 0,
@sync_type = N'initialize with backup',
@backupdevicetype='Disk',
@backupdevicename='C:\Temp\replpub_full.bak'

exec sp_addpushsubscription_agent @publication = N'backup_initialize_test', @subscriber = N'<구독자서버>', @subscriber_db = N'replsub', @job_login = null, @job_password = null, @subscriber_security_mode = 1,
@frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0,
@active_end_time_of_day = 235959, @active_start_date = 20100319, @active_end_date = 99991231,
@enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO

위 SCRIPT는 아래와 같이 해당 게시에서 [새 구독]을 Click해 [구독 마법사]를 실행해 구독자를 설정해 준 다음













아래와 같이 마지막 단계에서 [구독 만들기] 대신 스크립트 파일 생성을 체크해 주시면 됩니다.
















이렇게 생성된 Script에서 다음 부분을 수정 및 추가해 주시면 됩니다.

@sync_type = N'initialize with backup',
@backupdevicetype='Disk',
@backupdevicename='C:\Temp\replpub_full.bak'

이후 게시자에서 Backup 이후 발생한 트랜잭션 역시 구독자에 반영됨을 확인할 수 있습니다. (배포 에이전트 수행 후)

select * from replpub.dbo.table1
select * from replsub.dbo.table1

개 요 :
데이터 및 로그 파일에 대한 실시간 I/O 암호화 및 암호 해독을 수행합니다.
데이터베이스 파일 암호화는 페이지 수준에서 수행되며, 암호화된 데이터베이스의 페이지는 암호화된 후 디스크에 저장되고
메모리로 읽어 들일 때 암호가 해독됩니다.
기존 SQL Server 2005의 Encryption/Decryption 함수가 column Level의 암호화 기능을 제공한다면 TDE는 Database 전체
암호화 기법입니다. 암호/복호화가 IO Level에서 이뤄지므로 사용자 및 Application에 Transparent해 기존 code를 변경할
필요가 없습니다.

아키텍처 :




































TDE setup Sample :

위 TDE 아키텍처에 따라 수행하면 됩니다. 즉, DATABASE Encryption에 사용되는 DEK(Database Encryption Key)를
암호화하는데 필요한 Certificate, 그리고 Certificate를 암호화하는 데 필요한 Database Master Key를 Master 데이터베이스에
미리 생성해줘야 합니다.

1) master Database에 Database Master Key 생성

USE master;
GO
IF NOT EXISTS
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'dsalfjkadsf!@!#';
go

2) master DB에 인증서 생성 (DEK를 암호화하는데 필요)

CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate'
Go

3) DEK (Database Encryption Key) 생성

Create Database TDETest
GO

USE TDETest
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert
GO

-- Insert sample data ..
Create Table dbo.Encrypt ( Col1 int, Col2 varchar(20) )
Go
Declare @i int
Set @i = 0
While (@i < 200)
Begin
 Insert into Encrypt values (@i, 'SQLSERVER2008')
 Set @i = @i+1
End

아직 Database Encryption이 setting되지 않은 상태입니다. (아래 DMV에서 is_encrypted 값이 0 임을 확인)

select name , is_encrypted from sys.databases where name = 'TDETest'

4) SETUP Database Encryption

ALTER DATABASE TDETest
SET ENCRYPTION ON
GO

5) sys.dm_database_encryption_keys 내 encryption_state 로 암호화 상태 확인

0 = 데이터베이스 암호화 키가 없고 암호화되지 않음
1 = 암호화되지 않음
2 = 암호화 진행 중
3 = 암호화됨
4 = 키 변경 진행 중
5 = 해독 진행 중

<주의사항>

TDE가 설정된 데이터베이스 Backup을 Restore하기 위해선 DEK 암호화에 사용된 Certificate(인증서)가 존재해야 합니다.
따라서, 다른 서버에 백업본을 Restore하려면 인증서 역시 Backup받아 이를 대상서버에 생성해줘야 합니다.
대상 서버에 인증서가 없을 경우 RESTORE는 다음의 오류가 발생하며 실패하게 됩니다.

메시지 33111, 수준 16, 상태 3, 줄 1
지문이 '0xCFD230F9187D236D5A39C82C9ABB028823D3A962'인 서버 인증서을(를) 찾을 수 없습니다.
메시지 3013, 수준 16, 상태 1, 줄 1
RESTORE DATABASE이(가) 비정상적으로 종료됩니다.

1) 원본 서버에서 Certificate를 Backup 받음

BACKUP CERTIFICATE MyServerCert TO FILE = 'c:\temp\MyServerCert2'
    WITH PRIVATE KEY ( FILE = 'c:\temp\Myserverkey' ,
    ENCRYPTION BY PASSWORD = 'adfadsfasdfas123123' );
GO

인증서를 Backup 받을 때 Private Key까지 같이 받아야 합니다. private key 포함하지 않을 경우
대상서버에 인증서를 복원한 후 RESTORE DATABASE 명령 수행시 다음의 에러가 발생하게 됩니다.

메시지 15507, 수준 16, 상태 1, 줄 1
이 작업에 필요한 키가 손상된 것 같습니다.
메시지 3013, 수준 16, 상태 1, 줄 1
RESTORE DATABASE이(가) 비정상적으로 종료됩니다.

2) 대상 서버에서 위의 인증서 백업으로부터 Certificate 생성

CREATE CERTIFICATE
MyServerCert
FROM FILE = 'c:\temp\MyServerCert2'
WITH PRIVATE KEY
(
FILE = 'c:\temp\Myserverkey',
DECRYPTION BY PASSWORD = 'adfadsfasdfas123123'
);

3) RESTORE DATABASE ..