다음 단계에서는 Datastream과 함께 사용할 자체 관리형 SQL Server 데이터베이스를 구성하는 방법을 설명합니다.
소스 데이터베이스에 CDC를 사용 설정합니다. 이렇게 하려면 데이터베이스에 연결하고 SQL 프롬프트 또는 터미널에서 다음 명령어를 실행합니다.
USE [DATABASE_NAME] GO EXEC sys.sp_cdc_enable_db GO
DATABASE_NAME
을 소스 데이터베이스의 이름으로 바꿉니다.변경사항을 캡처해야 하는 테이블에서 CDC를 사용 설정합니다.
USE [DATABASE_NAME] EXEC sys.sp_cdc_enable_table @source_schema = N'SCHEMA_NAME', @source_name = N'TABLE_NAME', @role_name = NULL GO
다음을 바꿉니다.
DATABASE_NAME
: 소스 데이터베이스의 이름SCHEMA_NAME
: 테이블이 속한 스키마의 이름TABLE_NAME
: CDC를 사용 설정할 테이블의 이름
SQL Server 에이전트를 시작하고 항상 실행되는지 확인합니다. SQL Server 에이전트가 장시간 작동 중지 상태로 유지되면 로그가 잘릴 수 있으며 Datastream에서 읽지 않은 변경 데이터가 영구적으로 손실될 수 있습니다.
SQL Server 에이전트 실행에 대한 자세한 내용은 SQL Server 에이전트 인스턴스 시작, 중지, 다시 시작을 참조하세요.
Datastream 사용자를 만듭니다.
소스 데이터베이스에 연결하고 다음 명령어를 입력합니다.
USE DATABASE_NAME;
Datastream에서 연결 프로필을 설정할 때 사용할 로그인을 만듭니다.
CREATE LOGIN YOUR_LOGIN WITH PASSWORD = 'PASSWORD';
사용자를 만들고
db_owner
및db_denydatawriter
역할을 할당합니다.CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
EXEC sp_addrolemember 'db_owner', 'USER_NAME'; EXEC sp_addrolemember 'db_denydatawriter', 'USER_NAME';
이 사용자를
master
데이터베이스에 추가합니다.USE master; CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
트랜잭션 로그 CDC 메서드에 필요한 추가 단계
이 섹션에서 설명하는 단계는 트랜잭션 로그 CDC 메서드에 사용하도록 소스 SQL Server 데이터베이스를 구성하는 경우에만 필요합니다.
sys.fn_dblog
함수에SELECT
권한을 부여합니다.USE master; GRANT SELECT ON sys.fn_dblog TO USER_NAME;
사용자를 msdb 데이터베이스에 추가하고 다음 권한을 사용자에게 할당합니다.
USE msdb; CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN; GRANT SELECT ON dbo.sysjobs TO USER_NAME;
master
데이터베이스에서 사용자에게 다음 권한을 할당합니다.USE master; GRANT VIEW SERVER STATE TO YOUR_LOGIN;
소스에 변경사항을 적용할 보관 기간을 설정합니다.
USE [DATABASE_NAME] EXEC sys.sp_cdc_change_job @job_type = 'capture' , @pollinginterval = 86399 EXEC sp_cdc_stop_job 'capture' EXEC sp_cdc_start_job 'capture'
@pollinginterval
매개변수는 초 단위로 측정되며 권장 값은86399
로 설정됩니다. 즉, 트랜잭션 로그는 86,399초(1일) 동안 변경사항을 보관합니다.sp_cdc_start_job 'capture
프로시저를 실행하면 설정이 시작됩니다.데이터베이스에서 실행 중인 정리 또는 캡처 작업이 있으면 중지합니다. 자세한 내용은 변경 데이터 캡처 관리 및 모니터링을 참조하세요.
로그 잘림 방지를 설정합니다.
CDC 리더가 로그를 읽을 수 있는 충분한 시간을 확보하는 동시에 로그 자르기를 허용하여 저장 공간을 사용하지 않도록 하려면 로그 잘림 방지를 설정할 수 있습니다.
- SQL Server 클라이언트를 사용하여 데이터베이스에 연결합니다.
로그 잘림을 방지하기 위해 지정한 기간 동안 활성 트랜잭션을 실행하는 저장 프러시저를 만듭니다.
CREATE PROCEDURE dbo.DatastreamLogTruncationSafeguard @transaction_logs_retention_time INT AS BEGIN DECLARE @transactionLog TABLE (beginLSN BINARY(10), endLSN BINARY(10)) INSERT @transactionLog EXEC sp_repltrans DECLARE @currentDateTime DATETIME = GETDATE() DECLARE @cutoffDateTime DATETIME = DATEADD(MINUTE, -@transaction_logs_retention_time, @currentDateTime) DECLARE @firstValidLSN BINARY(10) = NULL DECLARE @lastValidLSN BINARY(10) = NULL DECLARE @firstTxnTime DATETIME = NULL DECLARE @lastTxnTime DATETIME = NULL SELECT TOP 1 @lastTxnTime = t.logStartTime, @lastValidLSN = t.beginLSN FROM ( SELECT beginLSN AS beginLSN, (SELECT TOP 1 [begin time] FROM fn_dblog(stuff(stuff(CONVERT(CHAR(24), beginLSN, 1), 19, 0, ':'), 11, 0, ':'), DEFAULT)) AS logStartTime FROM @transactionLog ) t ORDER BY t.beginLSN DESC -- If all transactions are before cutoff, clear everything IF (@lastTxnTime < @cutoffDateTime) BEGIN EXEC sp_repldone NULL, NULL, 0, 0, 1 END ELSE BEGIN -- Find the earliest transaction SELECT TOP 1 @firstTxnTime = t.logStartTime, @firstValidLSN = ISNULL(@firstValidLSN, t.beginLSN) FROM ( SELECT beginLSN AS beginLSN, (SELECT TOP 1 [begin time] FROM fn_dblog(stuff(stuff(CONVERT(CHAR(24), beginLSN, 1), 19, 0, ':'), 11, 0, ':'), DEFAULT)) AS logStartTime FROM @transactionLog ) t ORDER BY t.beginLSN ASC IF (@firstTxnTime < @cutoffDateTime) BEGIN -- Identify the earliest and latest LSNs within VLogs before cutoff SELECT @firstValidLSN = SUBSTRING(MAX(t.lsnMarkers), 1, 10), @lastValidLSN = SUBSTRING(MAX(t.lsnMarkers), 11, 10) FROM ( SELECT MIN(beginLSN + endLSN) AS lsnMarkers FROM @transactionLog GROUP BY SUBSTRING(beginLSN, 1, 4) ) t WHERE ( SELECT TOP 1 [begin time] FROM fn_dblog(stuff(stuff(CONVERT(CHAR(24), t.lsnMarkers, 1), 19, 0, ':'), 11, 0, ':'), DEFAULT) WHERE Operation = 'LOP_BEGIN_XACT' ) < @cutoffDateTime EXEC sp_repldone @firstValidLSN, @lastValidLSN, 0, 0, 0 END END END;
다른 저장 프러시저를 만듭니다. 이번에는 지정된 주기에 따라 이전 단계에서 만든 저장 프러시저를 실행하는 작업을 만듭니다.
CREATE PROCEDURE [dbo].[SetUpDatastreamJob] @transaction_logs_retention_time INT AS BEGIN DECLARE @database_name VARCHAR(MAX) SET @database_name = (SELECT DB_NAME());; DECLARE @command_str VARCHAR(MAX); SET @command_str = CONCAT('Use ', @database_name,'; exec dbo.DatastreamLogTruncationSafeguard @transaction_logs_retention_time = ' + CAST(@transaction_logs_retention_time AS VARCHAR(10))); DECLARE @job_name VARCHAR(MAX); SET @job_name = CONCAT(@database_name, '_', 'DatastreamLogTruncationSafeguardJob1') DECLARE @current_time INT = CAST(FORMAT(GETDATE(), 'HHmmss') AS INT); -- Schedule the procedure to run after every 5 minutes. IF NOT EXISTS ( SELECT * FROM msdb.dbo.sysjobs WHERE name = @job_name ) BEGIN EXEC msdb.dbo.sp_add_job @job_name = @job_name, @enabled = 1, @description = N'Execute the procedure every 5 minutes.' ; EXEC msdb.dbo.sp_add_jobstep @job_name = @job_name, @step_name = N'Execute_DatastreamLogTruncationSafeguard', @subsystem = N'TSQL', @command = @command_str; DECLARE @schedule_name_1 VARCHAR(MAX); SET @schedule_name_1 = CONCAT(@database_name, '_', 'DatastreamEveryFiveMinutesSchedule') EXEC msdb.dbo.sp_add_schedule @schedule_name = @schedule_name_1, @freq_type = 4, -- daily start @freq_subday_type = 4, -- every X minutes daily @freq_interval = 1, @freq_subday_interval = 5, @active_start_time = @current_time; EXEC msdb.dbo.sp_attach_schedule @job_name = @job_name, @schedule_name = @schedule_name_1 ; -- Add a schedule that runs the stored procedure on the SQL Server Agent startup. DECLARE @schedule_name_agent_startup VARCHAR(MAX); SET @schedule_name_agent_startup = CONCAT(@database_name, '_', 'DatastreamSqlServerAgentStartupSchedule') EXEC msdb.dbo.sp_add_schedule @schedule_name = @schedule_name_agent_startup, @freq_type = 64, -- start on SQL Server Agent startup @active_start_time = @current_time; EXEC msdb.dbo.sp_attach_schedule @job_name = @job_name, @schedule_name = @schedule_name_agent_startup ; EXEC msdb.dbo.sp_add_jobserver @job_name = @job_name, @server_name = @@servername ; END END;
Datastream 작업을 만드는 저장 프러시저를 실행합니다.
DECLARE @transaction_logs_retention_time INT = (INT) EXEC [dbo].[SetUpDatastreamJob] @transaction_logs_retention_time
INT를 로그를 보관하려는 시간(분)으로 바꿉니다. 예를 들면 다음과 같습니다.
60
값은 보관 기간을 1시간으로 설정합니다.24 * 60
값은 보관 기간을 1일로 설정합니다.3 * 24 * 60
값은 보관 기간을 3일로 설정합니다.
다음 단계
- Datastream의 SQL Server 소스 작동 방법 자세히 알아보기