기계 번역으로 제공되는 번역입니다. 제공된 번역과 원본 영어의 내용이 상충하는 경우에는 영어 버전이 우선합니다.
이 주제에서는 SQL Server 소스에서 CDC 복제를 설정하는 방법을 설명합니다.
온프레미스 또는 Amazon EC2에서 자체 관리형 SQL Server의 데이터 변경 캡처
소스 Microsoft SQL Server 데이터베이스에서 변경 내용을 캡처하려면 데이터베이스가 전체 백업을 수행하도록 구성되어 있어야 합니다. 데이터베이스를 전체 복구 모드 또는 대량 로그 모드로 구성합니다.
자체 관리형 SQL Server 소스의 경우 AWS DMS는 다음을 사용합니다.
- MS-REPLICATION
-
프라이머리 키가 있는 테이블의 변경 사항을 캡처합니다. 소스 SQL Server 인스턴스에서 AWS DMS 엔드포인트 사용자에게 sysadmin 권한을 제공하여 이를 자동으로 구성할 수 있습니다. 또는 이 섹션의 단계에 따라 소스를 준비하고 sysadmin 권한이 없는 사용자를 AWS DMS 엔드포인트에 사용할 수 있습니다.
- MS-CDC
-
프라이머리 키가 없는 테이블의 변경 사항을 캡처합니다. 데이터베이스 수준에서 모든 테이블에 대해 개별적으로 MS-CDC를 활성화합니다.
지속적 복제(CDC)를 위해 SQL Server 데이터베이스를 설정할 때 다음 중 하나를 수행할 수 있습니다.
-
sysadmin 역할을 사용하여 지속적 복제를 설정합니다.
-
sysadmin 역할을 사용하지 않도록 지속적 복제를 설정합니다.
자체 관리형 SQL Server에서 지속적 복제 설정
이 섹션에는 sysadmin 역할을 사용하거나 사용하지 않고 자체 관리형 SQL 서버에서 지속적 복제를 설정하는 방법에 대한 정보가 나와 있습니다.
주제
자체 관리형 SQL Server에서 지속적 복제 설정: sysadmin 역할 사용
SQL Server용 AWS DMS 지속적 복제는 프라이머리 키가 있는 테이블에는 네이티브 SQL Server 복제를 사용하고, 프라이머리 키가 없는 테이블에는 변경 데이터 캡처(CDC)를 사용합니다.
지속적 복제를 설정하기 전에 SQL Server 소스에서 지속적 복제(CDC) 사용을 위한 사전 요구 사항 섹션을 참조하세요.
프라이머리 키가 있는 테이블의 경우, AWS DMS는 일반적으로 소스에 필요한 아티팩트를 구성할 수 있습니다. 하지만 자체 관리형인 SQL Server 소스 인스턴스의 경우, 먼저 SQL Server 배포를 수동으로 구성해야 합니다. 그런 다음 sysadmin 권한이 있는 AWS DMS 소스 사용자는 프라이머리 키가 있는 테이블에 대한 게시를 자동으로 생성할 수 있습니다.
배포가 이미 구성되어 있는지 확인하려면 다음 명령을 실행합니다.
sp_get_distributor
열 배포 결과가 NULL
인 경우 배포가 구성되지 않은 것입니다. 다음 절차에 따라 배포를 설정할 수 있습니다.
배포를 설정하려면
-
SQL Server Management Studio(SSMS) 도구를 사용하여 SQL Server 소스 데이터베이스에 연결합니다.
-
복제 폴더의 컨텍스트 메뉴(마우스 오른쪽 버튼 클릭)를 열고 배포 구성을 선택합니다. 배포 구성 마법사가 나타납니다.
-
마법사에 따라 기본값을 입력하고 배포를 생성합니다.
CDC를 설정하려면
AWS DMS 버전 3.4.7 이상에서는 읽기 전용 복제본을 사용하지 않는 경우, 데이터베이스와 모든 테이블에 MS CDC를 자동으로 설정할 수 있습니다. 이 기능을 사용하려면 SetUpMsCdcForTables
ECA를 true로 설정합니다. ECA에 대한 자세한 내용은 엔드포인트 설정 섹션을 참조하세요.
3.4.7 이전 AWS DMS 버전이나 읽기 전용 복제본을 원본으로 사용하는 경우, 다음 단계를 수행합니다.
프라이머리 키가 없는 테이블의 경우, 데이터베이스에 MS-CDC를 설정합니다. 이렇게 하려면 sysadmin 역할이 할당된 계정을 사용하고 다음 명령을 실행합니다.
use [DBname] EXEC sys.sp_cdc_enable_db
다음으로, 각 소스 테이블마다 MS-CDC를 설정합니다. 고유 키는 있지만 프라이머리 키가 없는 각 테이블마다 다음 쿼리를 실행하여 MS-CDC를 설정합니다.
exec sys.sp_cdc_enable_table @source_schema = N'schema_name', @source_name = N'table_name', @index_name = N'unique_index_name', @role_name = NULL, @supports_net_changes = 1 GO
-
프라이머리 키가 없거나 고유 키가 없는 각 테이블마다 다음 쿼리를 실행하여 MS-CDC를 설정합니다.
exec sys.sp_cdc_enable_table @source_schema = N'schema_name', @source_name = N'table_name', @role_name = NULL GO
특정 테이블에서 MS-CDC를 설정하는 방법에 대한 자세한 내용은 SQL Server 설명서
독립 실행형 SQL Server에서 지속적 복제 설정: sysadmin 역할 없음
이 섹션에서는 사용자 계정에 sysadmin 권한이 필요 없는 SQL Server 데이터베이스 소스에 대해 지속적 복제를 설정하는 방법을 설명합니다.
참고
sysadmin이 아닌 DMS 사용자는 이 섹션의 단계를 실행한 후 다음을 수행할 수 있는 권한을 갖게 됩니다.
온라인 트랜잭션 로그 파일에서 변경 사항 읽기
트랜잭션 로그 백업 파일의 변경 사항을 읽을 수 있는 디스크 액세스
DMS에서 사용하는 게시물 추가 또는 변경
게시물에 문서 추가
SQL Server에서 지속적인 복제를 위한 데이터 변경 사항 캡처에 설명된 대로 복제를 위해 Microsoft SQL Server를 설정합니다.
소스 데이터베이스에서 MS-REPLICATION을 활성화합니다. 이 작업은 수동으로 수행하거나, sysadmin 사용자로 태스크를 한 번 실행하여 수행할 수 있습니다.
아래의 스크립트를 사용하여 소스 데이터베이스에서
awsdms
스키마를 생성합니다.use master go create schema awsdms go -- Create the table valued function [awsdms].[split_partition_list] on the Master database, as follows: USE [master] GO set ansi_nulls on go set quoted_identifier on go if (object_id('[awsdms].[split_partition_list]','TF')) is not null drop function [awsdms].[split_partition_list]; go create function [awsdms].[split_partition_list] ( @plist varchar(8000), —A delimited list of partitions @dlm nvarchar(1) —Delimiting character ) returns @partitionsTable table —Table holding the BIGINT values of the string fragments ( pid bigint primary key ) as begin declare @partition_id bigint; declare @dlm_pos integer; declare @dlm_len integer; set @dlm_len = len(@dlm); while (charindex(@dlm,@plist)>0) begin set @dlm_pos = charindex(@dlm,@plist); set @partition_id = cast( ltrim(rtrim(substring(@plist,1,@dlm_pos-1))) as bigint); insert into @partitionsTable (pid) values (@partition_id) set @plist = substring(@plist,@dlm_pos+@dlm_len,len(@plist)); end set @partition_id = cast (ltrim(rtrim(@plist)) as bigint); insert into @partitionsTable (pid) values ( @partition_id ); return end GO
아래의 스크립트를 사용하여 마스터 데이터베이스에서
[awsdms].[rtm_dump_dblog]
프로시저를 생성합니다.use [MASTER] go if (object_id('[awsdms].[rtm_dump_dblog]','P')) is not null drop procedure [awsdms].[rtm_dump_dblog]; go set ansi_nulls on go set quoted_identifier on GO CREATE procedure [awsdms].[rtm_dump_dblog] ( @start_lsn varchar(32), @seqno integer, @filename varchar(260), @partition_list varchar(8000), — A comma delimited list: P1,P2,... Pn @programmed_filtering integer, @minPartition bigint, @maxPartition bigint ) as begin declare @start_lsn_cmp varchar(32); — Stands against the GT comparator SET NOCOUNT ON — – Disable "rows affected display" set @start_lsn_cmp = @start_lsn; if (@start_lsn_cmp) is null set @start_lsn_cmp = '00000000:00000000:0000'; if (@partition_list is null) begin RAISERROR ('Null partition list waspassed',16,1); return end if (@start_lsn) is not null set @start_lsn = '0x'+@start_lsn; if (@programmed_filtering=0) SELECT [Current LSN], [operation], [Context], [Transaction ID], [Transaction Name], [Begin Time], [End Time], [Flag Bits], [PartitionID], [Page ID], [Slot ID], [RowLog Contents 0], [Log Record], [RowLog Contents 1] FROM fn_dump_dblog ( @start_lsn, NULL, N'DISK', @seqno, @filename, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default) where [Current LSN] collate SQL_Latin1_General_CP1_CI_AS > @start_lsn_cmp collate SQL_Latin1_General_CP1_CI_AS and ( ( [operation] in ('LOP_BEGIN_XACT','LOP_COMMIT_XACT','LOP_ABORT_XACT') ) or ( [operation] in ('LOP_INSERT_ROWS','LOP_DELETE_ROWS','LOP_MODIFY_ROW') and ( ( [context] in ('LCX_HEAP','LCX_CLUSTERED','LCX_MARK_AS_GHOST') ) or ([context] = 'LCX_TEXT_MIX' and (datalength([RowLog Contents 0]) in (0,1)))) and [PartitionID] in ( select * from master.awsdms.split_partition_list (@partition_list,',')) ) or ([operation] = 'LOP_HOBT_DDL') ) else SELECT [Current LSN], [operation], [Context], [Transaction ID], [Transaction Name], [Begin Time], [End Time], [Flag Bits], [PartitionID], [Page ID], [Slot ID], [RowLog Contents 0], [Log Record], [RowLog Contents 1] — After Image FROM fn_dump_dblog ( @start_lsn, NULL, N'DISK', @seqno, @filename, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default) where [Current LSN] collate SQL_Latin1_General_CP1_CI_AS > @start_lsn_cmp collate SQL_Latin1_General_CP1_CI_AS and ( ( [operation] in ('LOP_BEGIN_XACT','LOP_COMMIT_XACT','LOP_ABORT_XACT') ) or ( [operation] in ('LOP_INSERT_ROWS','LOP_DELETE_ROWS','LOP_MODIFY_ROW') and ( ( [context] in ('LCX_HEAP','LCX_CLUSTERED','LCX_MARK_AS_GHOST') ) or ([context] = 'LCX_TEXT_MIX' and (datalength([RowLog Contents 0]) in (0,1)))) and ([PartitionID] is not null) and ([PartitionID] >= @minPartition and [PartitionID]<=@maxPartition) ) or ([operation] = 'LOP_HOBT_DDL') ) SET NOCOUNT OFF — Re-enable "rows affected display" end GO
아래의 스크립트를 사용하여 마스터 데이터베이스에서 인증서를 생성합니다.
Use [master] Go CREATE CERTIFICATE [awsdms_rtm_dump_dblog_cert] ENCRYPTION BY PASSWORD = N'@5trongpassword' WITH SUBJECT = N'Certificate for FN_DUMP_DBLOG Permissions';
아래의 스크립트를 사용하여 인증서에서 로그인을 생성합니다.
Use [master] Go CREATE LOGIN awsdms_rtm_dump_dblog_login FROM CERTIFICATE [awsdms_rtm_dump_dblog_cert];
아래의 스크립트를 사용하여 sysadmin 서버 역할에 로그인을 추가합니다.
ALTER SERVER ROLE [sysadmin] ADD MEMBER [awsdms_rtm_dump_dblog_login];
아래의 스크립트를 사용하여 [master].[awsdms].[rtm_dump_dblog]에 서명을 추가합니다.
Use [master] GO ADD SIGNATURE TO [master].[awsdms].[rtm_dump_dblog] BY CERTIFICATE [awsdms_rtm_dump_dblog_cert] WITH PASSWORD = '@5trongpassword';
참고
저장된 프로시저를 다시 생성할 경우 서명을 다시 추가해야 합니다.
아래의 스크립트를 사용하여 마스터 데이터베이스에 [awsdms].[rtm_position_1st_timestamp]를 생성합니다.
use [master] if object_id('[awsdms].[rtm_position_1st_timestamp]','P') is not null DROP PROCEDURE [awsdms].[rtm_position_1st_timestamp]; go create procedure [awsdms].[rtm_position_1st_timestamp] ( @dbname sysname, -- Database name @seqno integer, -- Backup set sequence/position number within file @filename varchar(260), -- The backup filename @1stTimeStamp varchar(40) -- The timestamp to position by ) as begin SET NOCOUNT ON -- Disable "rows affected display" declare @firstMatching table ( cLsn varchar(32), bTim datetime ) declare @sql nvarchar(4000) declare @nl char(2) declare @tb char(2) declare @fnameVar nvarchar(254) = 'NULL' set @nl = char(10); -- New line set @tb = char(9) -- Tab separator if (@filename is not null) set @fnameVar = ''''+@filename +'''' set @sql='use ['+@dbname+'];'+@nl+ 'select top 1 [Current LSN],[Begin Time]'+@nl+ 'FROM fn_dump_dblog (NULL, NULL, NULL, '+ cast(@seqno as varchar(10))+','+ @fnameVar+','+@nl+ @tb+'default, default, default, default, default, default, default,'+@nl+ @tb+'default, default, default, default, default, default, default,'+@nl+ @tb+'default, default, default, default, default, default, default,'+@nl+ @tb+'default, default, default, default, default, default, default,'+@nl+ @tb+'default, default, default, default, default, default, default,'+@nl+ @tb+'default, default, default, default, default, default, default,'+@nl+ @tb+'default, default, default, default, default, default, default,'+@nl+ @tb+'default, default, default, default, default, default, default,'+@nl+ @tb+'default, default, default, default, default, default, default)'+@nl+ 'where operation=''LOP_BEGIN_XACT''' +@nl+ 'and [Begin Time]>= cast('+''''+@1stTimeStamp+''''+' as datetime)'+@nl --print @sql delete from @firstMatching insert into @firstMatching exec sp_executesql @sql -- Get them all select top 1 cLsn as [matching LSN],convert(varchar,bTim,121) as [matching Timestamp] from @firstMatching; SET NOCOUNT OFF -- Re-enable "rows affected display" end GO
아래의 스크립트를 사용하여 마스터 데이터베이스에서 인증서를 생성합니다.
Use [master] Go CREATE CERTIFICATE [awsdms_rtm_position_1st_timestamp_cert] ENCRYPTION BY PASSWORD = '@5trongpassword' WITH SUBJECT = N'Certificate for FN_POSITION_1st_TIMESTAMP Permissions';
아래의 스크립트를 사용하여 인증서에서 로그인을 생성합니다.
Use [master] Go CREATE LOGIN awsdms_rtm_position_1st_timestamp_login FROM CERTIFICATE [awsdms_rtm_position_1st_timestamp_cert];
아래의 스크립트를 사용하여 sysadmin 역할에 로그인을 추가합니다.
ALTER SERVER ROLE [sysadmin] ADD MEMBER [awsdms_rtm_position_1st_timestamp_login];
아래의 스크립트를 사용하여 인증서를 통해 [master].[awsdms].[rtm_position_1st_timestamp]에 서명을 추가합니다.
Use [master] GO ADD SIGNATURE TO [master].[awsdms].[rtm_position_1st_timestamp] BY CERTIFICATE [awsdms_rtm_position_1st_timestamp_cert] WITH PASSWORD = '@5trongpassword';
아래의 스크립트를 사용하여 DMS 사용자에게 새 저장 프로시저에 대한 실행 액세스 권한을 부여합니다.
use master go GRANT execute on [awsdms].[rtm_position_1st_timestamp] to dms_user;
다음 각 데이터베이스에서 다음과 같은 권한 및 역할을 보유한 사용자를 생성합니다.
참고
각 복제본에서 동일한 SID를 사용하여 dmsnosysadmin 사용자 계정을 생성해야 합니다. 아래의 SQL 쿼리는 각 복제본에서 dmsnosysadmin 계정 SID 값을 확인하는 데 도움이 될 수 있습니다. 사용자 생성에 대한 자세한 내용은 Microsoft SQL 서버 설명서
의 CREATE USER (Transact-SQL) 섹션을 참조하세요. Azure SQL 데이터베이스의 SQL 사용자 계정 생성에 대한 자세한 내용은 Active geo-replication 섹션을 참조하세요. use master go grant select on sys.fn_dblog to [DMS_user] grant view any definition to [DMS_user] grant view server state to [DMS_user]—(should be granted to the login). grant execute on sp_repldone to [DMS_user] grant execute on sp_replincrementlsn to [DMS_user] grant execute on sp_addpublication to [DMS_user] grant execute on sp_addarticle to [DMS_user] grant execute on sp_articlefilter to [DMS_user] grant select on [awsdms].[split_partition_list] to [DMS_user] grant execute on [awsdms].[rtm_dump_dblog] to [DMS_user]
use MSDB go grant select on msdb.dbo.backupset to [DMS_user] grant select on msdb.dbo.backupmediafamily to [DMS_user] grant select on msdb.dbo.backupfile to [DMS_user]
소스 데이터베이스에서 다음과 같은 스크립트를 실행합니다.
EXEC sp_addrolemember N'db_owner', N'DMS_user' use Source_DB go
마지막으로, 소스 SQL Server 엔드포인트에 추가 연결 속성(ECA)을 추가합니다.
enableNonSysadminWrapper=true;
가용성 그룹 환경의 SQL Server에서 지속적 복제 설정: sysadmin 역할 없음
이 섹션에서는 사용자 계정에 sysadmin 권한이 필요 없는 가용성 그룹 환경의 SQL Server 데이터베이스 소스에 대해 지속적 복제를 설정하는 방법을 설명합니다.
참고
sysadmin이 아닌 DMS 사용자는 이 섹션의 단계를 실행한 후 다음을 수행할 수 있는 권한을 갖게 됩니다.
온라인 트랜잭션 로그 파일에서 변경 사항 읽기
트랜잭션 로그 백업 파일의 변경 사항을 읽을 수 있는 디스크 액세스
DMS에서 사용하는 게시물 추가 또는 변경
게시물에 문서 추가
가용성 그룹 환경에서 sysadmin 사용자를 사용하지 않고 지속적 복제를 설정하려면
SQL Server에서 지속적인 복제를 위한 데이터 변경 사항 캡처에 설명된 대로 복제를 위해 Microsoft SQL Server를 설정합니다.
소스 데이터베이스에서 MS-REPLICATION을 활성화합니다. 이 작업은 수동으로 수행하거나, sysadmin 사용자를 통해 태스크를 한 번 실행하여 수행할 수 있습니다.
참고
MS-REPLICATION 배포자를 로컬로 구성하거나, 연결된 서버를 통해 sysadmin 이외의 사용자가 액세스할 수 있는 방식으로 구성해야 합니다.
단일 태스크 내에서 sp_repldone을 단독으로 사용 엔드포인트 옵션이 활성화된 경우, MS-REPLICATION Log Reader 작업을 중지합니다.
각각의 복제에서 다음 단계를 수행합니다.
마스터 데이터베이스에서
[awsdms]
[awsdms] 스키마를 생성합니다.CREATE SCHEMA [awsdms]
마스터 데이터베이스에서
[awsdms].[split_partition_list]
테이블 값 함수를 생성합니다.USE [master] GO SET ansi_nulls on GO SET quoted_identifier on GO IF (object_id('[awsdms].[split_partition_list]','TF')) is not null DROP FUNCTION [awsdms].[split_partition_list]; GO CREATE FUNCTION [awsdms].[split_partition_list] ( @plist varchar(8000), --A delimited list of partitions @dlm nvarchar(1) --Delimiting character ) RETURNS @partitionsTable table --Table holding the BIGINT values of the string fragments ( pid bigint primary key ) AS BEGIN DECLARE @partition_id bigint; DECLARE @dlm_pos integer; DECLARE @dlm_len integer; SET @dlm_len = len(@dlm); WHILE (charindex(@dlm,@plist)>0) BEGIN SET @dlm_pos = charindex(@dlm,@plist); SET @partition_id = cast( ltrim(rtrim(substring(@plist,1,@dlm_pos-1))) as bigint); INSERT into @partitionsTable (pid) values (@partition_id) SET @plist = substring(@plist,@dlm_pos+@dlm_len,len(@plist)); END SET @partition_id = cast (ltrim(rtrim(@plist)) as bigint); INSERT into @partitionsTable (pid) values ( @partition_id ); RETURN END GO
마스터 데이터베이스에서
[awsdms].[rtm_dump_dblog]
프로시저를 생성합니다.USE [MASTER] GO IF (object_id('[awsdms].[rtm_dump_dblog]','P')) is not null DROP PROCEDURE [awsdms].[rtm_dump_dblog]; GO SET ansi_nulls on GO SET quoted_identifier on GO CREATE PROCEDURE [awsdms].[rtm_dump_dblog] ( @start_lsn varchar(32), @seqno integer, @filename varchar(260), @partition_list varchar(8000), -- A comma delimited list: P1,P2,... Pn @programmed_filtering integer, @minPartition bigint, @maxPartition bigint ) AS BEGIN DECLARE @start_lsn_cmp varchar(32); -- Stands against the GT comparator SET NOCOUNT ON -- Disable "rows affected display" SET @start_lsn_cmp = @start_lsn; IF (@start_lsn_cmp) is null SET @start_lsn_cmp = '00000000:00000000:0000'; IF (@partition_list is null) BEGIN RAISERROR ('Null partition list was passed',16,1); return --set @partition_list = '0,'; -- A dummy which is never matched END IF (@start_lsn) is not null SET @start_lsn = '0x'+@start_lsn; IF (@programmed_filtering=0) SELECT [Current LSN], [operation], [Context], [Transaction ID], [Transaction Name], [Begin Time], [End Time], [Flag Bits], [PartitionID], [Page ID], [Slot ID], [RowLog Contents 0], [Log Record], [RowLog Contents 1] -- After Image FROM fn_dump_dblog ( @start_lsn, NULL, N'DISK', @seqno, @filename, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default) WHERE [Current LSN] collate SQL_Latin1_General_CP1_CI_AS > @start_lsn_cmp collate SQL_Latin1_General_CP1_CI_AS -- This aims for implementing FN_DBLOG based on GT comparator. AND ( ( [operation] in ('LOP_BEGIN_XACT','LOP_COMMIT_XACT','LOP_ABORT_XACT') ) OR ( [operation] in ('LOP_INSERT_ROWS','LOP_DELETE_ROWS','LOP_MODIFY_ROW') AND ( ( [context] in ('LCX_HEAP','LCX_CLUSTERED','LCX_MARK_AS_GHOST') ) or ([context] = 'LCX_TEXT_MIX') ) AND [PartitionID] in ( select * from master.awsdms.split_partition_list (@partition_list,',')) ) OR ([operation] = 'LOP_HOBT_DDL') ) ELSE SELECT [Current LSN], [operation], [Context], [Transaction ID], [Transaction Name], [Begin Time], [End Time], [Flag Bits], [PartitionID], [Page ID], [Slot ID], [RowLog Contents 0], [Log Record], [RowLog Contents 1] -- After Image FROM fn_dump_dblog ( @start_lsn, NULL, N'DISK', @seqno, @filename, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default) WHERE [Current LSN] collate SQL_Latin1_General_CP1_CI_AS > @start_lsn_cmp collate SQL_Latin1_General_CP1_CI_AS -- This aims for implementing FN_DBLOG based on GT comparator. AND ( ( [operation] in ('LOP_BEGIN_XACT','LOP_COMMIT_XACT','LOP_ABORT_XACT') ) OR ( [operation] in ('LOP_INSERT_ROWS','LOP_DELETE_ROWS','LOP_MODIFY_ROW') AND ( ( [context] in ('LCX_HEAP','LCX_CLUSTERED','LCX_MARK_AS_GHOST') ) or ([context] = 'LCX_TEXT_MIX') ) AND ([PartitionID] is not null) and ([PartitionID] >= @minPartition and [PartitionID]<=@maxPartition) ) OR ([operation] = 'LOP_HOBT_DDL') ) SET NOCOUNT OFF -- Re-enable "rows affected display" END GO
마스터 데이터베이스에서 인증서를 생성합니다.
USE [master] GO CREATE CERTIFICATE [awsdms_rtm_dump_dblog_cert] ENCRYPTION BY PASSWORD = N'@hardpassword1' WITH SUBJECT = N'Certificate for FN_DUMP_DBLOG Permissions'
인증서에서 로그인을 생성합니다.
USE [master] GO CREATE LOGIN awsdms_rtm_dump_dblog_login FROM CERTIFICATE [awsdms_rtm_dump_dblog_cert];
sysadmin 서버 역할에 로그인을 추가합니다.
ALTER SERVER ROLE [sysadmin] ADD MEMBER [awsdms_rtm_dump_dblog_login];
-
인증서를 사용하여 [master].[awsdms].[rtm_dump_dblog] 프로시저에 서명을 추가합니다.
USE [master] GO ADD SIGNATURE TO [master].[awsdms].[rtm_dump_dblog] BY CERTIFICATE [awsdms_rtm_dump_dblog_cert] WITH PASSWORD = '@hardpassword1';
참고
저장된 프로시저를 다시 생성할 경우 서명을 다시 추가해야 합니다.
마스터 데이터베이스에서
[awsdms].[rtm_position_1st_timestamp]
프로시저를 생성합니다.USE [master] IF object_id('[awsdms].[rtm_position_1st_timestamp]','P') is not null DROP PROCEDURE [awsdms].[rtm_position_1st_timestamp]; GO CREATE PROCEDURE [awsdms].[rtm_position_1st_timestamp] ( @dbname sysname, -- Database name @seqno integer, -- Backup set sequence/position number within file @filename varchar(260), -- The backup filename @1stTimeStamp varchar(40) -- The timestamp to position by ) AS BEGIN SET NOCOUNT ON -- Disable "rows affected display" DECLARE @firstMatching table ( cLsn varchar(32), bTim datetime ) DECLARE @sql nvarchar(4000) DECLARE @nl char(2) DECLARE @tb char(2) DECLARE @fnameVar sysname = 'NULL' SET @nl = char(10); -- New line SET @tb = char(9) -- Tab separator IF (@filename is not null) SET @fnameVar = ''''+@filename +'''' SET @filename = ''''+@filename +'''' SET @sql='use ['+@dbname+'];'+@nl+ 'SELECT TOP 1 [Current LSN],[Begin Time]'+@nl+ 'FROM fn_dump_dblog (NULL, NULL, NULL, '+ cast(@seqno as varchar(10))+','+ @filename +','+@nl+ @tb+'default, default, default, default, default, default, default,'+@nl+ @tb+'default, default, default, default, default, default, default,'+@nl+ @tb+'default, default, default, default, default, default, default,'+@nl+ @tb+'default, default, default, default, default, default, default,'+@nl+ @tb+'default, default, default, default, default, default, default,'+@nl+ @tb+'default, default, default, default, default, default, default,'+@nl+ @tb+'default, default, default, default, default, default, default,'+@nl+ @tb+'default, default, default, default, default, default, default,'+@nl+ @tb+'default, default, default, default, default, default, default)'+@nl+ 'WHERE operation=''LOP_BEGIN_XACT''' +@nl+ 'AND [Begin Time]>= cast('+''''+@1stTimeStamp+''''+' as datetime)'+@nl --print @sql DELETE FROM @firstMatching INSERT INTO @firstMatching exec sp_executesql @sql -- Get them all SELECT TOP 1 cLsn as [matching LSN],convert(varchar,bTim,121) AS[matching Timestamp] FROM @firstMatching; SET NOCOUNT OFF -- Re-enable "rows affected display" END GO
마스터 데이터베이스에서 인증서를 생성합니다.
USE [master] GO CREATE CERTIFICATE [awsdms_rtm_position_1st_timestamp_cert] ENCRYPTION BY PASSWORD = N'@hardpassword1' WITH SUBJECT = N'Certificate for FN_POSITION_1st_TIMESTAMP Permissions';
인증서에서 로그인을 생성합니다.
USE [master] GO CREATE LOGIN awsdms_rtm_position_1st_timestamp_login FROM CERTIFICATE [awsdms_rtm_position_1st_timestamp_cert];
sysadmin 서버 역할에 로그인을 추가합니다.
ALTER SERVER ROLE [sysadmin] ADD MEMBER [awsdms_rtm_position_1st_timestamp_login];
인증서를 사용하여
[master].[awsdms].[rtm_position_1st_timestamp]
프로시저에 서명을 추가합니다.USE [master] GO ADD SIGNATURE TO [master].[awsdms].[rtm_position_1st_timestamp] BY CERTIFICATE [awsdms_rtm_position_1st_timestamp_cert] WITH PASSWORD = '@hardpassword1';
참고
저장된 프로시저를 다시 생성할 경우 서명을 다시 추가해야 합니다.
다음 각 데이터베이스에서 다음과 같은 권한/역할을 보유한 사용자를 생성합니다.
참고
각 복제본에서 동일한 SID를 사용하여 dmsnosysadmin 사용자 계정을 생성해야 합니다. 아래의 SQL 쿼리는 각 복제본에서 dmsnosysadmin 계정 SID 값을 확인하는 데 도움이 될 수 있습니다. 사용자 생성에 대한 자세한 내용은 Microsoft SQL 서버 설명서
의 CREATE USER (Transact-SQL) 섹션을 참조하세요. Azure SQL 데이터베이스의 SQL 사용자 계정 생성에 대한 자세한 내용은 Active geo-replication 섹션을 참조하세요. SELECT @@servername servername, name, sid, create_date, modify_date FROM sys.server_principals WHERE name = 'dmsnosysadmin';
각 복제에서 마스터 데이터베이스에 대한 권한을 부여합니다.
USE master GO GRANT select on sys.fn_dblog to dmsnosysadmin; GRANT view any definition to dmsnosysadmin; GRANT view server state to dmsnosysadmin -- (should be granted to the login). GRANT execute on sp_repldone to dmsnosysadmin; GRANT execute on sp_replincrementlsn to dmsnosysadmin; GRANT execute on sp_addpublication to dmsnosysadmin; GRANT execute on sp_addarticle to dmsnosysadmin; GRANT execute on sp_articlefilter to dmsnosysadmin; GRANT select on [awsdms].[split_partition_list] to dmsnosysadmin; GRANT execute on [awsdms].[rtm_dump_dblog] to dmsnosysadmin; GRANT execute on [awsdms].[rtm_position_1st_timestamp] to dmsnosysadmin;
각 복제에서 msdb 데이터베이스에 대한 권한을 부여합니다.
USE msdb GO GRANT select on msdb.dbo.backupset to dmsnosysadmin GRANT select on msdb.dbo.backupmediafamily to dmsnosysadmin GRANT select on msdb.dbo.backupfile to dmsnosysadmin
소스 데이터베이스에서
db_owner
역할을dmsnosysadmin
에 추가합니다. 데이터베이스가 동기화되었으므로, 기본 복제에만 역할을 추가할 수 있습니다.use <source DB> GO EXEC sp_addrolemember N'db_owner', N'dmsnosysadmin'
클라우드 SQL Server DB 인스턴스에서 지속적 복제 설정
이 섹션에서는 클라우드에 호스팅된 SQL Server 데이터베이스 인스턴스에 CDC를 설정하는 방법을 설명합니다. 클라우드에 호스팅된 SQL 서버 인스턴스는 Amazon RDS for SQL Server, Azure SQL Managed Instance 또는 기타 관리형 클라우드 SQL Server 인스턴스에서 실행되는 인스턴스입니다. 각 데이터베이스 유형에 따른 지속적 복제 제한 사항에 대한 자세한 내용은 AWS DMS용 소스로 SQL Server 사용 시 적용되는 제한 사항 섹션을 참조하세요.
지속적 복제를 설정하기 전에 SQL Server 소스에서 지속적 복제(CDC) 사용을 위한 사전 요구 사항 섹션을 참조하세요.
자체 관리형 Microsoft SQL Server 소스와 달리 Amazon RDS for SQL Server는 MS-Replication을 지원하지 않습니다. 따라서 AWS DMS는 프라이머리 키가 있거나 없는 테이블에 MS-CDC를 사용해야 합니다.
Amazon RDS는 AWS DMS가 소스 SQL Server 인스턴스의 지속적 변경에 사용하는 복제 아티팩트를 설정하기 위해 sysadmin 권한을 부여하지 않습니다. 다음 절차와 같이 Amazon RDS 인스턴스에서 MS-CDC를 켜야 합니다(마스터 사용자 권한 사용).
클라우드 SQL Server DB 인스턴스에서 MS-CDC를 켜려면
-
데이터베이스 수준에서 다음 쿼리 중 하나를 실행합니다.
RDS for SQL Server DB 인스턴스에는 다음 쿼리를 사용하세요.
exec msdb.dbo.rds_cdc_enable_db '
DB_name
'Azure SQL 관리형 DB 인스턴스에는 다음 쿼리를 사용하세요.
USE
DB_name
GO EXEC sys.sp_cdc_enable_db GO -
프라이머리 키가 있는 각 테이블마다 다음 쿼리를 실행하여 MS-CDC를 켭니다.
exec sys.sp_cdc_enable_table @source_schema = N'schema_name', @source_name = N'table_name', @role_name = NULL, @supports_net_changes = 1 GO
고유 키는 있지만 프라이머리 키가 없는 각 테이블마다 다음 쿼리를 실행하여 MS-CDC를 켭니다.
exec sys.sp_cdc_enable_table @source_schema = N'schema_name', @source_name = N'table_name', @index_name = N'unique_index_name', @role_name = NULL, @supports_net_changes = 1 GO
프라이머리 키도 없고 고유 키도 없는 각 테이블마다 다음 쿼리를 실행하여 MS-CDC를 켭니다.
exec sys.sp_cdc_enable_table @source_schema = N'schema_name', @source_name = N'table_name', @role_name = NULL GO
-
보존 기간 설정:
DMS 버전 3.5.3 이상을 사용하여 복제하는 RDS for SQL Server 인스턴스의 경우 보존 기간이 기본값인 5초로 설정되어 있는지 확인합니다. DMS 3.5.2 이하에서 DMS 3.5.3 이상으로 업그레이드하거나 이동하는 경우 새 인스턴스 또는 업그레이드된 인스턴스에서 작업이 실행된 후 폴링 간격 값을 변경합니다. 다음 스크립트는 보존 기간을 5초로 설정합니다.
use dbname EXEC sys.sp_cdc_change_job @job_type = 'capture' ,@pollinginterval = 5 exec sp_cdc_stop_job 'capture' exec sp_cdc_start_job 'capture'
DMS 버전 3.5.2 이하를 사용하여 복제하는 Azure SQL MI 및 RDS for SQL Server 인스턴스의 경우 다음 명령을 사용합니다.
use dbname EXEC sys.sp_cdc_change_job @job_type = 'capture' ,@pollinginterval = 86399 exec sp_cdc_stop_job 'capture' exec sp_cdc_start_job 'capture'
@pollinginterval
파라미터는 초 단위로 측정되며 권장 값은 86,399로 설정되어 있습니다. 즉,@pollinginterval = 86399
일 경우 트랜잭션 로그는 86,399초(하루) 동안 변경 사항을 보존합니다. 프로시저exec sp_cdc_start_job 'capture'
에서 설정을 시작합니다.참고
일부 버전의 SQL Server에서는
pollinginterval
값을 3,599초 이상으로 설정하면 값이 기본값인 5초로 재설정됩니다. 이 경우 AWS DMS가 읽기 전에 T-Log 항목이 삭제됩니다. 이 알려진 문제의 영향을 받는 SQL Server 버전을 확인하려면 이 Microsoft KB 문서를 참조하세요. 다중 AZ에서 Amazon RDS를 사용하는 경우, 장애 조치 시 보조 AZ도 올바른 값을 갖도록 설정해야 합니다.
exec rdsadmin..rds_set_configuration 'cdc_capture_pollinginterval' , <5 or 86399>
AWS DMS 복제 작업이 1시간 이상 중지될 때 보존 기간을 유지하려면
참고
다음 단계는 DMS 3.5.3 이상을 사용하여 RDS for SQL Server 소스를 복제하는 데 필요하지 않습니다.
-
다음 명령을 사용하여 트랜잭션 로그를 잘라내는 작업을 중지합니다.
exec sp_cdc_stop_job 'capture'
-
AWS DMS 콘솔에서 작업을 찾아 작업을 재개합니다.
-
모니터링 탭을 선택하고
CDCLatencySource
지표를 확인합니다. -
CDCLatencySource
지표가 0이고 그대로 유지되면 다음 명령을 사용하여 트랜잭션 로그를 잘라내는 작업을 다시 시작합니다.exec sp_cdc_start_job 'capture'
SQL Server 트랜잭션 로그를 잘라내는 작업을 시작해야 한다는 점을 명심하세요. 그렇지 않으면 SQL Server 인스턴스의 스토리지가 꽉 찰 수 있습니다.
RDS for SQL Server를 AWS DMS 소스로 사용 시 권장되는 설정
AWS DMS 3.5.3 이상
참고
RDS for SQL Server 로그 백업 기능의 초기 릴리스는 DMS 버전 3.5.3 릴리스 후 생성하거나 수정한 엔드포인트에 대해 기본적으로 활성화됩니다. 기존 엔드포인트에 이 기능을 사용하려면 변경하지 않고 엔드포인트를 수정합니다.
AWS DMS 버전 3.5.3에는 로그 백업에서 읽기에 대한 지원이 도입되었습니다. DMS는 주로 활성 트랜잭션 로그의 읽기에 의존하여 이벤트를 복제합니다. DMS가 활성 로그에서 트랜잭션을 읽기 전에 백업되는 경우 작업은 온디맨드 방식으로 RDS 백업에 액세스하고 활성 트랜잭션 로그를 따라잡을 때까지 후속 백업 로그에서 읽습니다. DMS가 로그 백업에 액세스할 수 있도록 하려면 RDS 자동 백업 보존 기간을 최소 1일로 설정합니다. 자동 백업 보존 기간 설정에 대한 자세한 내용은 Amazon RDS 사용 설명서의 백업 보존 기간을 참조하세요.
로그 백업에 액세스하는 DMS 작업은 RDS 인스턴스의 스토리지를 활용합니다. 태스크는 복제에 필요한 로그 백업에만 액세스합니다. Amazon RDS는 다운로드한 백업을 몇 시간 내에 제거합니다. 이 제거는 Amazon S3 또는 Amazon RDS RESTORE DATABASE
기능에 보관된 Amazon RDS 백업에는 영향을 주지 않습니다. DMS를 사용하여 복제하려는 경우 RDS for SQL Server 소스에 추가 스토리지를 할당하는 것이 좋습니다. 필요한 스토리지 양을 추정하는 한 가지 방법은 DMS가 복제를 시작하거나 재개할 백업을 식별하고 RDS tlog backup
메타데이터 함수를 사용하여 모든 후속 백업의 파일 크기를 추가하는 것입니다. tlog backup
함수에 대한 자세한 내용은 Amazon RDS 사용 설명서의 사용 가능한 트랜잭션 로그 백업 나열을 참조하세요.
또는 Amazon RDS 인스턴스의 CloudWatch FreeStorageSpace
지표를 기반으로 스토리지 자동 크기 조정을 활성화하거나 스토리지 크기 조정을 트리거하도록 선택할 수 있습니다.
트랜잭션 로그 백업의 너무 먼 지점에서 시작하거나 재개하지 않는 것이 좋습니다. SQL Server 인스턴스에 스토리지가 채워질 수 있습니다. 이러한 경우 전체 로드를 시작하는 것이 좋습니다. 트랜잭션 로그 백업에서 복제하는 속도가 활성 트랜잭션 로그에서 읽는 것보다 느립니다. 자세한 내용은 RDS for SQL Server에 대한 트랜잭션 로그 백업 처리 단원을 참조하십시오.
로그 백업에 액세스하려면 추가 권한이 필요합니다. 자세한 내용은 클라우드 SQL Server 데이터베이스에서 지속적인 복제를 위한 권한 설정 섹션의 설명을 참조하세요. 태스크 복제를 시작하기 전에 이러한 권한을 부여했는지 확인하세요.
AWS DMS 3.5.2 이하
Amazon RDS for SQL Server를 소스로 사용하는 경우, MS-CDC 캡처 작업은 maxscans
및 maxtrans
파라미터를 사용합니다. 이러한 파라미터는 MS-CDC 캡처가 트랜잭션 로그에서 수행하는 최대 스캔 수와 각 스캔별로 처리되는 트랜잭션 수를 제어합니다.
트랜잭션 수가 maxtrans*maxscans
보다 많은 데이터베이스의 경우, polling_interval
값을 늘리면 활성 트랜잭션 로그 레코드가 누적될 수 있습니다. 결과적으로 이러한 누적으로 인해 트랜잭션 로그 크기가 증가할 수 있습니다.
AWS DMS는 MS-CDC 캡처 작업에 의존하지 않습니다. MS-CDC 캡처 작업은 트랜잭션 로그 항목을 처리된 것으로 표시합니다. 이렇게 하면 트랜잭션 로그 백업 작업이 트랜잭션 로그에서 항목을 제거할 수 있습니다.
트랜잭션 로그의 크기와 MS-CDC 작업의 성공 여부를 모니터링하는 것이 좋습니다. MS-CDC 작업이 실패하면 트랜잭션 로그가 과도하게 증가하여 AWS DMS 복제가 실패할 수 있습니다. 소스 데이터베이스의 sys.dm_cdc_errors
동적 관리 뷰를 사용하여 MS-CDC 캡처 작업 오류를 모니터링할 수 있습니다. DBCC SQLPERF(LOGSPACE)
관리 명령을 사용하여 트랜잭션 로그 크기를 모니터링할 수 있습니다.
MS-CDC로 인한 트랜잭션 로그 증가를 해결하려면
-
AWS DMS가 복제하고 있는 데이터베이스의
Log Space Used %
를 확인하고 데이터베이스가 계속 증가하는지 확인합니다.DBCC SQLPERF(LOGSPACE)
-
트랜잭션 로그 백업 프로세스를 방해하는 요인을 식별합니다.
Select log_reuse_wait, log_reuse_wait_desc, name from sys.databases where name = db_name();
log_reuse_wait_desc
값이REPLICATION
과 같으면 MS-CDC의 지연 시간으로 인해 로그 백업 보존이 발생합니다. -
maxtrans
및maxscans
파라미터 값을 늘려 캡처 작업에서 처리되는 이벤트 수를 늘립니다.EXEC sys.sp_cdc_change_job @job_type = 'capture' ,@maxtrans = 5000, @maxscans = 20 exec sp_cdc_stop_job 'capture' exec sp_cdc_start_job 'capture'
이 문제를 해결하려면 매일 소스 데이터베이스에서 AWS DMS가 복제하는 테이블에 대해 생성되는 평균 이벤트 수와 maxtrans*maxscans
가 같아지도록 maxscans
및 maxtrans
값을 설정합니다.
이러한 파라미터를 권장 값보다 높게 설정하면 캡처 작업은 트랜잭션 로그의 모든 이벤트를 처리합니다. 이러한 파라미터를 권장 값보다 낮게 설정하면 MS-CDC 지연 시간이 늘어나고 트랜잭션 로그가 증가합니다.
워크로드의 변경으로 이벤트 수가 달라지기 때문에 적절한 maxscans
값과 maxtrans
값을 식별하는 것이 어려울 수 있습니다. 이 경우 MS-CDC 지연 시간에 대한 모니터링을 설정하는 것이 좋습니다. 자세한 내용은 SQL Server 설명서에서 Monitor the processmaxtrans
과 maxscans
를 동적으로 구성합니다.
AWS DMS 작업이 작업을 재개하거나 계속하는 데 필요한 로그 시퀀스 번호(LSN)를 찾을 수 없는 경우, 작업이 실패하고 완전히 다시 로드해야 할 수 있습니다.
참고
AWS DMS를 사용하여 RDS for SQL Server 소스에서 데이터를 복제하는 경우, Amazon RDS 인스턴스의 중지-시작 이벤트 후 복제를 재개하려고 하면 오류가 발생할 수 있습니다. 이는 중지-시작 이벤트 후 다시 시작할 때 SQL Server Agent 프로세스가 캡처 작업 프로세스를 다시 시작하기 때문입니다. 이는 MS-CDC 폴링 간격을 우회합니다.
이로 인해 MS-CDC 캡처 작업 처리보다 트랜잭션 볼륨이 적은 데이터베이스에서는 중단된 위치에서 AWS DMS가 다시 시작하기 전에 데이터가 처리되거나 복제 및 백업된 것으로 표시되어 다음 오류가 발생할 수 있습니다.
[SOURCE_CAPTURE ]E: Failed to access LSN '0000dbd9:0006f9ad:0003' in the backup log sets since BACKUP/LOG-s are not available. [1020465] (sqlserver_endpoint_capture.c:764)
이 문제를 완화하려면 앞서 권장한 대로 maxtrans
및 maxscans
값을 설정하세요.