SQL Server용 Cloud SQL 데이터베이스 구성

다음 단계에서는 Datastream와 함께 사용할 SQL 서버용 Cloud SQL 데이터베이스를 구성하는 방법을 설명합니다.

Cloud SQL 인스턴스에 연결합니다. Cloud Shell 프롬프트에서 gcloud sql connect 명령어를 사용하여 수행할 수 있습니다.
다음 명령어를 실행하여 데이터베이스에서 CDC를 사용 설정합니다.
```
EXEC msdb.dbo.gcloudsql_cdc_enable_db 'DATABASE_NAME'
```
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를 사용 설정하려는 각 테이블에 대해 이 명령어를 실행해야 합니다.
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. Google Cloud 콘솔에서 Cloud SQL 인스턴스 페이지로 이동합니다.
  
  Cloud SQL 인스턴스로 이동
2. 로그인을 만듭니다.
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';
```

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

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

소스에 변경사항을 적용할 보관 기간을 설정합니다.
```
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 클라이언트를 사용하여 데이터베이스에 연결합니다.

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

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)
  );

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

CREATE PROCEDURE [dbo].[DatastreamLogTruncationSafeguard] @transaction_logs_retention_time INT
AS
BEGIN
  -- Start a new transaction
  BEGIN TRANSACTION;
  INSERT INTO dbo.gcp_datastream_truncation_safeguard (char_column) VALUES ('a')

DECLARE @formatted_time VARCHAR(5)
SET @formatted_time = CONVERT(VARCHAR(5), DATEADD(MINUTE, @transaction_logs_retention_time, 0), 108);
  -- Wait for X minutes before ending the transaction
  WAITFOR DELAY @formatted_time;
  -- Commit the transaction
  COMMIT TRANSACTION;
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')

    -- Add 3 schedules to the job to run again after specified time.
    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 to run an active transaction for x minutes.';

EXEC msdb.dbo.sp_add_jobstep @job_name = @job_name,
@step_name = N'Execute_DatastreamLogTruncationSafeguard',
@subsystem = N'TSQL',
@command = @command_str;

  -- Add a schedule that runs the stored procedure every given minutes starting now.
  DECLARE @schedule_name_1 VARCHAR(MAX);
  SET @schedule_name_1 = CONCAT(@database_name, '_', 'DatastreamEveryGivenMinutesFromNow')

  DECLARE @start_time_1 time;
  SET @start_time_1 = DATEADD(SECOND, 1, GETDATE());
  DECLARE @formatted_start_time_1 INT;
  SET @formatted_start_time_1 = CONVERT(INT, REPLACE(CONVERT(VARCHAR(8), @start_time_1, 114), ':' ,''));

  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 = @transaction_logs_retention_time,
  @active_start_time = @formatted_start_time_1;

  EXEC msdb.dbo.sp_attach_schedule
  @job_name = @job_name,
  @schedule_name = @schedule_name_1 ;

  -- Add a schedule that runs the stored procedure after every given minutes starting after some delay.
  DECLARE @schedule_name_2 VARCHAR(MAX);
  Set @schedule_name_2 = CONCAT(@database_name, '_', 'DatastreamEveryGivenMinutesAfterDelay');

  DECLARE @start_time_2 time;
  SET @start_time_2 = DATEADD(MINUTE, @transaction_logs_retention_time / 2, GETDATE());

  DECLARE @formatted_start_time_2 INT;
  SET @formatted_start_time_2 = CONVERT(INT, REPLACE(CONVERT(VARCHAR(8), @start_time_2, 114), ':' ,''));

  EXEC msdb.dbo.sp_add_schedule
  @schedule_name = @schedule_name_2,
  @freq_type = 4,  -- daily start
  @freq_subday_type = 4,  -- every x minutes daily
  @freq_interval = 1,
  @freq_subday_interval = @transaction_logs_retention_time,
  @active_start_time = @formatted_start_time_2;

  EXEC msdb.dbo.sp_attach_schedule
  @job_name = @job_name,
  @schedule_name = @schedule_name_2 ;

  -- 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 = @formatted_start_time_1;

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