sql2008::CDC(Change Data Capture)

SQL SERVER 2008 이후

MSDN을 발췌한 내역입니다.

http://msdn.microsoft.com/ko-kr/library/cc645937.aspx

 

[기본 개요]

 

- 변경 데이터 캡처는 SQL Server 테이블에 적용되는 삽입, 업데이트 및 삭제 작업을 기록합니다

-  SQL Server Enterprise, Developer 및 Evaluation Edition에서만 사용할 수 있습니다.

-  이 기술의 대상이 될 수 있는 좋은 데이터 소비자 예로 ETL(추출, 변환 및 로드) 응용 프로그램을 들 수 있습니다. ETL 응용 프로그램은 증분식으로 SQL Server 원본 테이블의 변경 데이터를 데이터 웨어하우스 또는 데이터 마트로 로드합니다. 데이터 웨어하우스 내에서 원본 테이블의 표현은 해당 원본 테이블의 변경 내용을 반영해야 하지만 원본 복제본을 새로 고치는 종단 간 기술은 적합하지 않습니다. 대신 소비자가 다른 종류의 데이터 대상 표현에 적용할 수 있도록 구조화된 안정적인 변경 데이터 스트림이 필요합니다. SQL Server 변경 데이터 캡처가 이 기술을 제공합니다.

 

1. 기본 흐름

 

변경 데이터 원본은 SQL Server 트랜잭션 로그입니다.

 

추적된 원본 테이블에 삽입, 업데이트 및 삭제가 적용되면 이러한 변경을 설명하는 항목이 로그에 추가됩니다.

 

로그는 변경 데이터 캡처 프로세스에 대한 입력으로 사용됩니다. 이 프로세스는 로그를 읽고 변경에 대한 정보를 추적된 테이블의 관련 변경 테이블에 추가합니다.

 

지정된 범위에서 변경 테이블에 나타나는 변경을 열거하여 해당 정보를 필터링된 결과 집합의 형태로 반환하는 함수가 제공됩니다.

 

 

 

 

 

-  sys.sp_cdc_enable_db 사용하여 데이터베이스에 변경 데이터를 캡처 사용하도록 명시적 설정

- sys.sp_cdc_enable_table 사용하여 원본 테이블을 추적된 테이블로 식별할 수 있도록 명시적 설정

- 캡처 인스턴스는 변경테입르과 두개의 쿼리 함수로 구성

- cdc 스키마 , cdc 사용자, SQL Agent 작업 2개 (cdc.database.capture, cdc.database.cleanup) , 메타데이터 테이블, 변경 테이블 생성

 

- 원본 테이블에 적용되는 각 삽입 또는 삭제 작업은 변경 테이블 내에 단일 행으로 나타납니다.

- 삽입 작업의 결과로 생성되는 행의 데이터 열에는 삽입 이후의 열 값이 포함되며 삭제 작업의 결과로 생성되는 행의 데이터 열에는 삭제 이전의 열 값이 포함됩니다. 업데이트 작업의 경우 하나의 행 항목에서 업데이트 이전의 열 값을 식별하고 다른 행 항목에서 업데이트 이후의 열 값을 식별해야 합니다.

- 열 __$operation은 변경 내용과 관련된 1 = 삭제, 2 = 삽입, 3 = 업데이트(이전 이미지) 및 4 = 업데이트(이후 이미지) 작업을 기록합니다.

 

2. 데이터 베이스에 대한 변경 데이터 캡처 유효성 간격

 

-  캡처 인스턴스에 변경 데이터를 사용할 수 있는 시간입니다.

유효성 간격은 데이터베이스 테이블에 대한 첫 번째 캡처 인스턴스가 만들어질 때 시작되어 현재 시간까지 지속됩니다.

- 캡처 프로세스에서 관련 로그 항목을 처리할 때까지 변경 데이터를 사용할 수 없다는 점을 알아두는 것이 중요합니다.  (대기시간이 존재함)

 

3. 캡처 인스턴스에 대한 변경 데이터 뱁처 유효성 간격

 

- 캡처 인스턴스의 유효성 간격은 캡처 프로세스에서 캡처 인스턴스를 인식하여 해당 변경 테이블에 대한 관련 변경 내용을 기록하기 시작할 때 시작합니다. 따라서 여러 캡처 인스턴스가 서로 다른 시간에 만들어지는 경우 각 인스턴스는 처음에 서로 다른 하위 끝점을 포함하게 됩니다

- 시간이 지나도 새 캡처 인스턴스가 만들어지지 않으면 모든 개별 인스턴스에 대한 유효성 간격이 데이터베이스 유효성 간격과 일치하게 됩니다.

- sys.fn_cdc_get_min_lsn : 캡처 인스턴스의 대한 현재 최소 LSN 값을 검색

- sys.fn_cdc_get_max_lsn : 최대 LSN 값을 검색하는데 사용

- 변경 데이터를 쿼리할 때 지정 LSN 범위가 이러한 두 LSN 값 내에 포함되지 않은 경우 변경 데이터 캡처 쿼리 함수가 실패합니다.

 

4. 원본 테이블에 대한 변경 내용 처리

 

-  원본테이블의 열이 데이터 형식이 변경되면 변경테이블로 전파되어 데이터 손실이 생성되지 않는다.

- 원본 테이블열이 삭제되면, 후속 변경 항목의 열에 대해서는 NULL값이 제공된다.

- 단일 원본 테이블에 동시에 연결할 수 있는 최대 캡처 인스턴스 수는 두 개입니다.

- 기본적으로 3일 분량의 데이터가 보존 됩니다.

 

5. 데이터베이스 미러링

- 변경 데이터 갭처가 설정된 데이터베이스에 미러링 걸 수 있습니다.

 *  agent가 실행중인지 확인합니다.

 * 보안 주체가 미러로 장애 조치를 수행하면 미러에서 갭처 작업 및 정리작업을 만듭니다.

 

6. 트랜잭션 복제

 - 변경 데이터 겹처 및 트랜잭션 복제는 동일한 데이터베이스에 함께 존재할 수 있습니다.

- sp_replcmds 가 배포 데이터베이스의 테이블과 캡처에 의한 변경 테이블에 모두 값을 채웁니다. 복제가 해지되면 로그 판독기 에이전트가 제거되고 캡처 작업이 다시 만들어 집니다.

 

7. 변경 데이터 캡처가 설정된 데이터베이스 복원 또는 연결

 - 데이터베이스가 동일한 서버에 동일한 데이터베이스 이름으로 복원되는 경우 변경 데이터 캡처는 설정된 상태를 유지합니다.
 - 데이터베이스가 다른 서버로 복원되는 경우에는 기본적으로 변경 데이터 캡처가 해제되고 관련된 모든 메타데이터가 삭제됩니다. 변경 데이터 캡처를 유지하려면 데이터베이스를 복원할 때 KEEP_CDC 옵션을 사용합니다.
- 데이터베이스가 분리된 후 다시 동일한 서버 또는 다른 서버에 연결될 경우 변경 데이터 캡처는 설정된 상태를 유지합니다.

- 데이터베이스가 KEEP_CDC 옵션을 사용하여 Enterprise 이외의 다른 버전에 연결 또는 복원된 경우 변경 데이터 캡처에 SQL Server Enterprise가 필요하기 때문에 작업이 차단됩니다. 오류 메시지 932가 표시됩니다.
sys.sp_cdc_disable_db 를 사용하여 제거 할 수 있습니다.