자체 관리형 SQL Server 데이터베이스 구성

다음 단계에서는 Datastream과 함께 사용할 자체 관리형 SQL Server 데이터베이스를 구성하는 방법을 설명합니다.

  1. 소스 데이터베이스에 CDC를 사용 설정합니다. 이렇게 하려면 데이터베이스에 연결하고 SQL 프롬프트 또는 터미널에서 다음 명령어를 실행합니다.

    USE [DATABASE_NAME]
GO
EXEC sys.sp_cdc_enable_db
GO

    DATABASE_NAME을 소스 데이터베이스의 이름으로 바꿉니다.

  2. 변경사항을 캡처해야 하는 테이블에서 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를 사용 설정할 테이블의 이름

  3. SQL Server 에이전트를 시작하고 항상 실행되는지 확인합니다. SQL Server 에이전트가 장시간 작동 중지 상태로 유지되면 로그가 잘릴 수 있으며 Datastream에서 읽지 않은 변경 데이터가 영구적으로 손실될 수 있습니다.

    SQL Server 에이전트 실행에 대한 자세한 내용은 SQL Server 에이전트 인스턴스 시작, 중지, 다시 시작을 참조하세요.

  4. Datastream 사용자를 만듭니다.

    1. 소스 데이터베이스에 연결하고 다음 명령어를 입력합니다.

      USE DATABASE_NAME;

    2. Datastream에서 연결 프로필을 설정할 때 사용할 로그인을 만듭니다.

      CREATE LOGIN YOUR_LOGIN WITH PASSWORD = 'PASSWORD';

    3. 사용자를 만들고 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';

    4. 이 사용자를 master 데이터베이스에 추가합니다.

      USE master;
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;

트랜잭션 로그 CDC 메서드에 필요한 추가 단계

이 섹션에서 설명하는 단계는 트랜잭션 로그 CDC 메서드에 사용하도록 소스 SQL Server 데이터베이스를 구성하는 경우에만 필요합니다.

  1. sys.fn_dblog 함수에 SELECT 권한을 부여합니다.

    USE master;
GRANT SELECT ON sys.fn_dblog TO USER_NAME;

  2. 사용자를 msdb 데이터베이스에 추가하고 다음 권한을 사용자에게 할당합니다.

    USE msdb;
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
GRANT SELECT ON dbo.sysjobs TO USER_NAME;

  3. master 데이터베이스에서 사용자에게 다음 권한을 할당합니다. 

      USE master;
  GRANT VIEW SERVER STATE TO YOUR_LOGIN;

  4. 소스에 변경사항을 적용할 보관 기간을 설정합니다.

    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 프로시저를 실행하면 설정이 시작됩니다.

  5. 데이터베이스에서 실행 중인 정리 또는 캡처 작업이 있으면 중지합니다. 자세한 내용은 변경 데이터 캡처 관리 및 모니터링을 참조하세요.

  6. 로그 잘림 방지를 설정합니다.

    CDC 리더가 로그를 읽을 수 있는 충분한 시간을 확보하는 동시에 로그 자르기를 허용하여 저장 공간을 사용하지 않도록 하려면 로그 잘림 방지를 설정할 수 있습니다.

    1. SQL Server 클라이언트를 사용하여 데이터베이스에 연결합니다.

    2. 데이터베이스에 더미 테이블을 만듭니다.

      USE [DATABASE_NAME];
CREATE TABLE dbo.gcp_datastream_truncation_safeguard (
  [id] INT IDENTITY(1,1) PRIMARY KEY,
  CreatedDate DATETIME DEFAULT GETDATE(),
  [char_column] CHAR(8)
  );

    3. 로그 잘림을 방지하기 위해 지정한 기간 동안 활성 트랜잭션을 실행하는 저장 프러시저를 만듭니다.

      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;

    4. 다른 저장 프러시저를 만듭니다. 이번에는 지정된 주기에 따라 이전 단계에서 만든 저장 프러시저를 실행하는 작업을 만듭니다.

      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;

    5. 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일로 설정합니다.