변경 내용 추적 구성 및 관리
이 항목에서는 변경 내용 추적을 설정, 해제 및 관리하는 방법에 대해 설명합니다. 또한 보안을 구성하는 방법과 변경 내용 추적을 사용할 때 저장소 및 성능에 미치는 영향을 확인하는 방법에 대해서도 설명합니다.
데이터베이스에 변경 내용 추적 설정
변경 내용 추적을 사용하기 전에 데이터베이스 수준에서 변경 내용 추적을 설정해야 합니다. 다음 예에서는 ALTER DATABASE를 사용하여 변경 내용 추적을 설정하는 방법을 보여 줍니다.
ALTER DATABASE AdventureWorks2008R2
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
데이터베이스 속성(변경 내용 추적 페이지) 대화 상자를 사용하여 SQL Server Management Studio에 변경 내용을 설정할 수도 있습니다.
변경 내용 추적을 설정할 때 CHANGE_RETENTION 및 AUTO_CLEANUP 옵션을 지정할 수 있으며, 변경 내용 추적을 설정한 후 언제든지 이 값을 변경할 수 있습니다.
변경 내용 보존 기간 값은 변경 내용 추적 정보가 유지되는 기간을 지정합니다. 이 기간보다 오래된 변경 내용 추적 정보는 정기적으로 제거됩니다. 이 값을 설정할 때 응용 프로그램이 이 데이터베이스에 있는 테이블을 동기화하는 빈도를 고려해야 합니다. 지정한 보존 기간은 최대 동기화 간격 이상이어야 합니다. 응용 프로그램이 좀 더 긴 간격으로 변경 내용을 가져오면 일부 변경 내용 정보가 제거되지 않았을 수도 있으므로 반환되는 결과가 정확하지 않을 수 있습니다. 잘못된 결과가 생성되는 것을 방지하기 위해 응용 프로그램에서는 CHANGE_TRACKING_MIN_VALID_VERSION 시스템 함수를 사용하여 동기화 간의 간격이 너무 긴지 여부를 확인할 수 있습니다.
AUTO_CLEANUP 옵션을 사용하여 오래된 변경 내용 추적 정보를 제거하는 정리 태스크를 설정하거나 해제할 수 있습니다. 이 설정은 응용 프로그램이 동기화되지 않는 일시적인 문제가 발생하여 이 문제가 해결될 때까지 보존 기간보다 오래된 변경 내용 추적 정보를 제거하는 프로세스를 일시 중지해야 하는 경우에 유용합니다.
변경 내용 추적을 사용하는 데이터베이스의 경우 다음 사항에 주의하십시오.
변경 내용 추적을 사용하려면 데이터베이스 호환성 수준이 90 이상으로 설정되어야 합니다. 데이터베이스 호환성 수준이 90 미만인 경우에도 변경 내용 추적을 구성할 수 있지만 변경 내용 추적 정보를 얻기 위해 사용되는 CHANGETABLE 함수에서 오류를 반환합니다.
스냅숏 격리를 사용하는 것은 모든 변경 내용 추적 정보가 일관되도록 보장하는 가장 쉬운 방법입니다. 이러한 이유로 데이터베이스에 대해 스냅숏 격리를 ON으로 설정하는 것이 가장 좋습니다. 자세한 내용은 변경 내용 추적 사용을 참조하십시오.
테이블에 변경 내용 추적 설정
추적하려는 테이블마다 변경 내용 추적을 설정해야 합니다. 변경 내용 추적이 설정되면 DML 작업에 의해 영향을 받는 테이블의 모든 행에 대해 변경 내용 추적 정보가 유지 관리됩니다.
다음 예에서는 ALTER TABLE을 사용하여 테이블에 변경 내용 추적을 설정하는 방법을 보여 줍니다.
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
테이블 속성(변경 내용 추적 페이지) 대화 상자를 사용하여 SQL Server Management Studio에서 테이블에 대해 변경 내용 추적을 설정할 수도 있습니다.
TRACK_COLUMNS_UPDATED 옵션이 ON으로 설정되면 SQL Server 데이터베이스 엔진에서는 내부 변경 내용 추적 테이블에 업데이트된 열에 대한 추가 정보를 저장합니다. 열 추적을 사용하면 응용 프로그램이 업데이트된 열만 동기화하도록 설정할 수 있습니다. 이로 인해 효율성과 성능이 향상될 수 있습니다. 그러나 열 추적 정보 유지 관리로 인해 저장소 오버헤드가 추가되기 때문에 이 옵션은 기본적으로 OFF로 설정됩니다.
변경 내용 추적 해제
우선 변경 내용 추적이 설정된 모든 테이블에 대해 변경 내용 추적을 해제해야 해당 데이터베이스에 대한 변경 내용 추적을 OFF로 설정할 수 있습니다. 데이터베이스에서 변경 내용 추적이 설정된 테이블을 확인하려면 sys.change_tracking_tables 카탈로그 뷰를 사용합니다.
다음 예에서는 ALTER TABLE을 사용하여 테이블에 대해 변경 내용 추적을 해제하는 방법을 보여 줍니다.
ALTER TABLE Person.Person
DISABLE CHANGE_TRACKING;
데이터베이스의 테이블에 변경 내용 추적이 설정되어 있지 않으면 해당 데이터베이스에 대해 변경 내용 추적을 해제할 수 있습니다. 다음 예에서는 ALTER DATABASE를 사용하여 데이터베이스에 대한 변경 내용 추적을 해제하는 방법을 보여 줍니다.
ALTER DATABASE AdventureWorks2008R2
SET CHANGE_TRACKING = OFF;
변경 내용 추적 관리
다음 섹션에서는 변경 내용 추적 관리와 관련된 카탈로그 뷰, 사용 권한 및 설정에 대해 설명합니다.
카탈로그 뷰
다음 카탈로그 뷰를 사용하여 변경 내용 추적이 설정된 테이블 및 데이터베이스를 확인할 수 있습니다.
sys.internal_tables 카탈로그 뷰는 사용자 테이블에 대한 변경 내용 추적을 설정할 때 만든 내부 테이블도 나열합니다.
보안
변경 내용 추적 함수를 사용하여 변경 내용 추적 정보에 액세스하려면 보안 주체에 다음 사용 권한이 있어야 합니다.
변경 내용 추적이 설정된 쿼리하는 테이블에 있는 한 개 이상의 기본 키 열에 대한 SELECT 권한
변경 내용을 가져오는 테이블에 대한 VIEW CHANGE TRACKING 권한. VIEW CHANGE TRACKING 권한이 필요한 이유는 다음과 같습니다.
변경 내용 추적 레코드에는 삭제된 행, 특히 삭제된 행의 기본 키 값에 대한 정보가 포함됩니다. 일부 중요한 데이터를 삭제한 후에 보안 주체에게 변경 내용 추적이 설정된 테이블에 대한 SELECT 권한이 부여되었을 수 있습니다. 이러한 경우 해당 보안 주체가 변경 내용 추적을 사용하여 삭제된 정보에 액세스하지 못하도록 해야 합니다.
변경 내용 추적 정보는 업데이트 작업으로 변경된 열에 대한 정보를 저장할 수 있습니다. 보안 주체는 중요한 정보가 포함된 열에 대한 권한이 거부될 수 있습니다. 변경 내용 추적 정보를 사용할 수 있으므로 보안 주체는 열 값이 업데이트되었는지를 확인할 수 있지만 해당 열의 값은 확인할 수 없습니다.
변경 내용 추적 오버헤드 이해
테이블에 변경 내용 추적이 설정되면 일부 관리 작업에 영향을 줄 수 있습니다. 다음 표에서는 작업 및 고려해야 하는 영향에 대해 설명합니다.
연산 |
변경 내용 추적이 설정된 경우 |
|---|---|
DROP TABLE |
삭제된 테이블에 대한 모든 변경 내용 추적 정보가 제거됩니다. |
ALTER TABLE DROP CONSTRAINT |
PRIMARY KEY 제약 조건을 삭제하려는 시도가 실패합니다. 변경 내용 추적을 해제해야 PRIMARY KEY 제약 조건을 삭제할 수 있습니다. |
ALTER TABLE DROP COLUMN |
삭제된 열이 기본 키의 일부일 경우 변경 내용 추적과 관계없이 해당 열을 삭제할 수 없습니다. 삭제된 열이 기본 키의 일부가 아닐 경우 해당 열을 삭제할 수 있습니다. 그러나 이 데이터를 동기화하는 응용 프로그램에 미치는 영향에 대해 먼저 이해해야 합니다. 테이블에 열 변경 내용 추적이 설정되어 있을 경우 삭제된 열이 여전히 변경 내용 추적 정보의 일부로 반환될 수도 있습니다. 삭제된 열은 응용 프로그램에서 처리해야 합니다. |
ALTER TABLE ADD COLUMN |
변경 내용 추적이 설정된 테이블에 새 열이 추가될 경우 이 열 추가 작업은 추적되지 않습니다. 새 열에 대해 수행된 업데이트 및 변경 내용만 추적됩니다. |
ALTER TABLE ALTER COLUMN |
기본 키가 아닌 열의 데이터 형식 변경 내용은 추적되지 않습니다. |
ALTER TABLE SWITCH |
테이블 중 하나 또는 둘 모두에 변경 내용 추적이 설정된 경우 파티션 전환에 실패합니다. |
DROP INDEX 또는 ALTER INDEX DISABLE |
기본 키를 강제 적용하는 인덱스는 삭제 또는 해제할 수 없습니다. |
TRUNCATE TABLE |
테이블 잘라내기는 변경 내용 추적이 설정된 테이블에서 수행할 수 있습니다. 그러나 이 작업으로 삭제된 행은 추적되지 않으며 유효한 최소 버전이 업데이트됩니다. 응용 프로그램이 버전을 검사할 때 해당 버전이 너무 오래되어 다시 초기화해야 한다고 나타납니다. 이는 해당 테이블의 변경 내용 추적이 해제된 다음 다시 설정되는 것과 동일합니다. |
변경 내용 추적을 사용하더라도 변경 내용 추적 정보가 작업의 일부로 저장되므로 DML 작업에는 오버헤드가 추가되지 않습니다.
DML에 미치는 영향
변경 내용 추적은 DML 작업에 대한 성능 오버헤드를 최소화하도록 최적화되었습니다. 테이블에 변경 내용 추적을 사용하는 것과 연관된 증분 성능 오버헤드는 테이블에 대해 인덱스가 생성되어 유지 관리가 필요해질 때 발생하는 오버헤드와 비슷합니다.
DML 작업으로 변경된 각 행의 경우 내부 변경 내용 추적 테이블에 한 개의 행이 추가됩니다. DML 작업과 관련한 이런 영향은 다음과 같은 다양한 요소에 따라 달라집니다.
기본 키 열의 수
사용자 테이블 행에서 변경되는 데이터의 양
트랜잭션에서 수행되는 작업의 수
스냅숏 격리를 사용하는 경우에도 변경 내용 추적의 설정 여부와 관계없이 모든 DML 작업에 대한 성능에 영향을 미칩니다.
저장소에 미치는 영향
변경 내용 추적 데이터는 다음과 같은 유형의 내부 테이블에 저장됩니다.
내부 변경 테이블
변경 내용 추적을 설정한 각 사용자 테이블에 대해 하나의 내부 변경 테이블이 있습니다.
내부 트랜잭션 테이블
데이터베이스에 대해 하나의 내부 트랜잭션 테이블이 있습니다.
이러한 내부 테이블은 다음과 같은 방법으로 저장소 요구 사항에 영향을 미칩니다.
사용자 테이블에 있는 각 행에 대한 개별 변경 내용의 경우 내부 변경 내용 테이블에 한 행이 추가됩니다. 이 행에는 작은 고정 오버헤드와 기본 키 열의 크기와 같은 가변 오버헤드가 있습니다. 이 행은 응용 프로그램이 설정한 선택적 컨텍스트 정보를 포함할 수 있습니다. 그리고 열 추적이 설정된 경우 변경된 각 열은 추적 테이블에 4바이트가 필요합니다.
커밋된 각 트랜잭션에 대해 하나의 행이 내부 트랜잭션 테이블에 추가됩니다.
다른 내부 테이블을 사용할 때와 같이 sp_spaceused 저장 프로시저를 사용하여 변경 내용 추적 테이블에 사용되는 공간을 결정할 수 있습니다. 내부 테이블의 이름은 다음 예와 같이 sys.internal_tables 카탈로그 뷰를 사용하여 가져올 수 있습니다.
sp_spaceused 'sys.change_tracking_309576141';
sp_spaceused 'sys.syscommittab';