MSSQL의 CDC를 위한 SQL Server Agent 설정 및 특정 테이블 CDC 설정

Change Data Capture (CDC)는 테이블 데이터의 변경 사항을 캡처하고 기록하는 SQL Server의 기능입니다. 이 기능은 이력 변경 추적, 데이터 웨어하우싱 등의 데이터 관련 작업에 매우 유용할 수 있습니다. 특정 테이블에 CDC를 활성화하기 전에 SQL Server Agent를 실행하고 데이터베이스 수준에서 CDC를 활성화해야 합니다.

1. SQL Server Agent 시작:

CDC를 활성화하기 전에 SQL Server Agent가 실행 중인지 확인하세요. SQL Server Agent는 데이터 변경을 캡처할 작업을 실행하는 데 책임이 있습니다.

SQL Server Management Studio (SSMS)를 엽니다.
객체 탐색기에서 SQL Server 인스턴스에 연결합니다.
서버 노드를 확장하고 "SQL Server Agent" 노드를 클릭합니다.
Agent가 시작되지 않았다면 마우스 오른쪽 버튼을 클릭하고 "시작"을 선택합니다.

추가 인증 관련 문제가 발생한다면 하기 설정을 수정하세요

서버 속성 > 보안 > SQL Server 및 Windows 인증모드(S)

2. 데이터베이스에서 CDC 활성화:

SSMS를 사용하여 데이터베이스에 연결합니다.
다음 T-SQL 명령을 실행할 수 있습니다:

EXEC sp_changedbowner [계정명];

create database [데이터베이스명];

USE [데이터베이스명];

EXEC sys.sp_cdc_enable_db;

3. 특정 테이블에서 CDC 활성화:

데이터베이스 수준에서 CDC를 활성화한 후 특정 테이블에 대해 활성화할 수 있습니다.

객체 탐색기에서 CDC를 활성화하려는 데이터베이스를 확장합니다.
테이블을 마우스 오른쪽 버튼으로 클릭한 다음 “Change Data Capture 활성화”를 선택합니다.
또는 다음 T-SQL을 사용할 수 있습니다:

CREATE TABLE '테이블명' (
    id INT PRIMARY KEY,
    name NVARCHAR(255),
    modified_date DATETIME DEFAULT GETDATE()
);


EXEC sys.sp_cdc_enable_table
@source_schema = '스키마명',
@source_name = '테이블명',
@role_name = NULL;

4. CDC 활성화 확인

데이터베이스와 테이블 각각 CDC 설정되었는지 확인합니다.

SELECT * FROM sys.server_principals WHERE name = '계정명';
SELECT * FROM sys.database_principals ;
SELECT [name], is_cdc_enabled FROM sys.databases WHERE [name] = '데이터베이스명';
SELECT [name], is_tracked_by_cdc FROM sys.tables WHERE [name] = '테이블명';

5. 모니터링:

CDC를 활성화한 후 변경 테이블을 사용하여 변경 사항을 모니터링할 수 있습니다. 이러한 테이블은 특정 테이블에 CDC가 활성화되면 SQL Server에 의해 자동으로 생성됩니다. 일반적으로 "cdc.[스키마_테이블]_CT"로 이름이 지어집니다.

INSERT INTO '테이블명' (id, name)
VALUES (1, 'Test Name');

UPDATE '테이블명'
SET name = 'Test Name2'
WHERE ID = 1;


DELETE FROM '테이블명' WHERE id = 1;

변경 테이블을 쿼리하려면:

sql
SELECT * FROM cdc.[스키마명_테이블명_CT]

cdc.[스키마명_테이블명_CT] 테이블 내에 아래 이미지와 같이 CDC 로그가 적재되는 걸 확인 할 수 있다.

6. 정리 및 관리:

시간이 지남에 따라 변경 테이블이 커질 수 있습니다. SQL Server Agent는 변경 테이블에서 오래된 레코드를 정리하는 작업을 관리합니다. 기본적으로 데이터는 3일 동안 보관되지만 필요에 따라 조정할 수 있습니다.