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

다음 단계에서는 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
```
참고: CDC를 사용 설정하려는 각 테이블에 대해 명령어를 실행해야 합니다.

다음을 바꿉니다.
- DATABASE_NAME: 소스 데이터베이스의 이름
- SCHEMA_NAME: 테이블이 속한 스키마의 이름
- TABLE_NAME: CDC를 사용 설정할 테이블의 이름
SQL Server 에이전트를 시작하고 항상 실행되는지 확인합니다. SQL Server 에이전트가 장시간 작동 중지 상태로 유지되면 로그가 잘릴 수 있으며 Datastream에서 읽지 않은 변경 데이터가 영구적으로 손실될 수 있습니다.

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

참고: SQL Server 에이전트가 장시간 다운되지 않도록 SQL Server 에이전트 서비스에 자동 다시 시작을 설정하는 것이 좋습니다.
스냅샷 격리를 사용 설정합니다.

SQL Server 데이터베이스에서 데이터를 백필할 때는 스냅샷 일관성을 확인하는 것이 중요합니다. 이 섹션에서 설명하는 설정을 적용하지 않을 경우 백필 프로세스 중에 데이터베이스가 변경되어 특별히 기본 키가 없는 테이블에 대해 중복 또는 잘못된 결과가 발생할 수 있습니다.

스냅샷 격리를 사용 설정하면 백필 프로세스 시작 시 데이터베이스의 임시 보기가 생성됩니다. 이렇게 하면 다른 사용자가 라이브 테이블을 동시에 변경하더라도 복사되는 데이터가 일관되게 유지됩니다. 스냅샷 격리를 사용 설정하면 성능에 약간 영향을 미칠 수 있으나 안정적인 데이터 추출을 위해 필수입니다.

스냅샷 격리를 사용 설정하려면 다음 안내를 따르세요.
1. SQL Server 클라이언트를 사용하여 데이터베이스에 연결합니다.
2. 다음 명령어를 실행합니다.
```
ALTER DATABASE DATABASE_NAME SET ALLOW_SNAPSHOT_ISOLATION ON;
```
DATABASE_NAME을 데이터베이스 이름으로 바꿉니다.
Datastream 사용자를 만듭니다.
1. 소스 데이터베이스에 연결하고 다음 명령어를 입력합니다.
```
USE DATABASE_NAME;
```
2. Datastream에서 연결 프로필을 설정할 때 사용할 로그인을 만듭니다.
```
CREATE LOGIN YOUR_LOGIN WITH PASSWORD = 'PASSWORD';
```
3. 사용자를 만듭니다.
```
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
```
4. 사용자에게 db_datareader 역할을 할당합니다.
```
EXEC sp_addrolemember 'db_datareader', 'USER_NAME';
```
5. 사용자에게 VIEW DATABASE STATE 권한을 부여합니다.
```
GRANT VIEW DATABASE STATE TO USER_NAME;
```
6. 이 사용자를 master 데이터베이스에 추가합니다.
```
USE master;
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
```

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

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

소스 데이터베이스에 연결하고 사용자에게 db_owner 및 db_denydatawriter 역할을 할당합니다.

USE DATABASE_NAME;
EXEC sp_addrolemember 'db_owner', 'USER_NAME';
EXEC sp_addrolemember 'db_denydatawriter', 'USER_NAME';

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 소스 작동 방법 자세히 알아보기

자체 관리형 SQL Server 데이터베이스 구성 컬렉션을 사용해 정리하기 내 환경설정을 기준으로 콘텐츠를 저장하고 분류하세요.

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

다음 단계

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