数据传输服务DTS

    将 SQL Server 数据库作为 DTS 源

    1. 适用场景

    注意:目前仅支持 SQL Server 增量数据迁移

    本文适用于使用百度智能云数据传输服务DTS(以下简称 DTS),支持自建 SQL Server 数据库为源端的增量数据迁移任务。

    2. 迁移前置条件

    • 数据库恢复模式设置为 Full
    • 增量迁移任务使用变更数据捕获(Change Data Capture,以下简称 CDC)功能, 不同版本对 CDC 功能存在限制
    版本 Enterprise Standard
    12 支持
    14 支持
    16 支持 支持
    17 支持 支持
    19 支持 支持

    3. 将 SQL Server 数据库作为源的限制

    3.1 针对增量数据迁移任务的限制

    • SQL Server 启动 CDC 功能需要SQL Server代理服务的支持
    • CDC要求采用独占方式使用 cdc 架构和 cdc 用户,如果某数据库中当前存在名为 cdc 的架构或数据库用户,那么在删除或重命名此架构或用户之前,不能对此数据库启用变更数据捕获
    • 对启动了CDC的源表要执行DDL操作,只能由角色sysadmin、 database role db_owner成员或 database role db_ddladmin成员操作
    • 启动数据库CDC需要账号必须是sysadmin角色的成员,启动表CDC需要账号必须是 sysadmin或 db_owner角色的成员
    • 不支持列集的增量更改
    • 不支持计算列的增量更改
    • 不支持数据类型 sql_variant、cursor、table

    4. 在 SQL Server 数据库中使用 CDC

    使用 CDC 功能之前需要启动数据库代理服务。如果您使用的是 RDS for SQL Server,可能存在启动失败,解决方案:

    sp_configure 'show advanced options', 1;
    reconfigure;
    sp_configure 'Agent XPs', 1;
    reconfigure;
    • 修改代理属性中的错误日志为:C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT,或者其他存在的路径
      MacHi 2020-04-29 10-06-03.png
    • 修改本地服务”SQL Server代理“的属性,选择登录身份为"本地系统账号"
      MacHi 2020-04-29 10-07-27.png
    • 然后启动本地服务、SSMS 中启动SQL Server代理

    4.1 启动数据库级别 CDC 功能

    -- 启动数据库级别 CDC 命令
    EXEC sys.sp_cdc_enable_db;
    -- 检查是否启动成功
    SELECT name,is_cdc_enabled FROM sys.databases WHERE name = 'DB_name'

    如果是您使用的是 RDS for SQL Server,可能会报错:

    Could not update the metadata that indicates database DB_name is enabled for Change Data Capture. The failure occurred when executing the command 'SetCDCTracked(Value = 1)'. The error returned was 15404: 'Could not obtain information about Windows NT group/user 'RDS-WIN-TEST\Administrator', error code 0x534.'. Use the action and error to determine the cause of the failure and resubmit the request.

    解决方法,执行以下命令然后再次执行启动数据库级别 CDC 命令即可:

    ALTER AUTHORIZATION ON DATABASE::[DB_name] TO [sa]
    或者
    EXEC sp_changedbowner 'sa'

    4.2 启动每个需要增量迁移表的表级别 CDC 功能

    -- 启动表级别 CDC 命令
    EXEC sys.sp_cdc_enable_table
    @source_schema= N'schema_name',-- 源表所属的架构的名称,无默认值,且不能为 NULL
    @source_name = N'table_name',-- 源表的名称,无默认值,且不能为 NULL
    @role_name = NULL,-- 建议设置为NULL,用于访问更改数据的数据库角色的名称。必须指定。如果显式设置为 NULL,则没有控制角色用于限制对更改数据的访问。可以为现有的固定服务器角色或数据库角色,如果指定的角色不存在则会自动创建该名称的数据库角色
    @capture_instance = DEFAULT,-- 用于命名特定于实例的变更数据捕获对象的捕获实例的名称。且不能为 NULL,源表最多可以有两个捕获实例。
    @supports_net_changes = 0,-- 是否为此捕获实例启用用于查询净更改的支持,默认值为1, 如果supports_net_changes设置为1,则必须指定index_name ,否则源表必须具有定义的主键
    @index_name = NULL,-- 唯一标识源表中的行的唯一索引的名称,可以为 NULL。未使用则CDC将使用主键,如果表也没有主键,则将忽略后来添加的主键
    @captured_column_list = NULL,-- 标识要包含在变更表中的源表列,NULL则所有列都将包括在变更表中,以逗号分隔的列名称列表,可以选择将列表中的单个列名称放在双引号 ("") 或方括号 ([]) 中,不能包含以下保留列名: __ $ start_lsn、 __ $ end_lsn、 __ $ seqval、 __ $ operation和 __ $ update_mask。
    @filegroup_name = DEFAULT, -- 要用于为捕获实例创建的变更表的文件组,为 NULL则使用默认文件组,建议为变更数据捕获的变更表创建一个单独的文件组。
    @allow_partition_switch = 1 -- 是否可以对启用了变更数据捕获的表执行 ALTER TABLE 的 SWITCH PARTITION 命令,默认值为1,对于非分区表,此开关设置始终为 1,并忽略实际的设置。

    如果您使用的是 RDS for SQL Server,可能会报错:

    消息 22832,级别 16,状态 1,过程 sp_cdc_enable_table_internal,第 623 行
    无法更新元数据来指示已对表 [aqadmin].[AQ_TEST_1] 启用了变更数据捕获。执行命令 '[sys].[sp_cdc_add_job] @job_type = N'capture'' 时失败。返回的错误为 22836: '无法更新数据库 aq11 的元数据来指示已添加某变更数据捕获作业。执行命令 'sp_add_jobstep_internal' 时失败。返回的错误为 14234: '指定的 @server无效(有效值由 sp_helpserver 返回)'。请使用此操作和错误来确定失败的原因并重新提交请求。'。请使用此操作和错误来确定失败的原因并重新提交请求。

    SQL Server 安装后修改了主机名,导致两个语句结果的"servname"不一致 解决方法,执行以下命令然后再次执行启动表级别 CDC 命令即可:

    IF serverproperty('servername')<>@@servername
      BEGIN
      DECLARE  @server SYSNAME  
      SET   @server=@@servername      
      EXEC  sp_dropserver @server=@server    
      SET   @server=cast(serverproperty('servername') AS SYSNAME)   
      EXEC  sp_addserver @server=@server,@local='LOCAL'  
      PRINT 'ok!'
      END 
    ELSE  
      PRINT 'undo!

    第一次启动表 CDC 功能会提示启动两个作业:捕获作业和清除作业。

    4.3 修改清除作业执行周期(可选)

    默认4320(分钟)后清除捕获的增量数据,您可以修改增量数据的保留时间,如下命令将保留时间修改为129600(分钟)。

    EXECUTE sys.sp_cdc_change_job
    @job_type = N'cleanup',
    @retention = 129600;
    
    -- 重新启动
    EXECUTE sys.sp_cdc_start_job
    @job_type = N'cleanup';

    4.4 修改捕获作业读取事务日志的周期(可选)

    默认时间间隔为 5(秒),当间隔时间越长,增量数据记录到变更表的时间就可能越长,因此您可以适当调整周期间隔时间。如下命令将周期间隔时间修改为 1(秒)。

    EXECUTE sys.sp_cdc_change_job
    @job_type = N'capture',
    @pollinginterval = 1;
    
    -- 重新启动
    EXECUTE sys.sp_cdc_start_job
    @job_type = N'capture';

    5. SQL Server 的源数据类型

    将 SQL Server 作为 DTS 源的数据迁移支持大多数 SQL Server 数据类型。下表列出了使用 DTS 时支持的 SQL Server 源数据类型以及来自 DTS 数据类型的默认映射。

    SQL Server 数据类型 DTS 数据类型
    BIGINT DTS_TYPE_INT8
    INT DTS_TYPE_INT4
    SMALLINT DTS_TYPE_INT2
    TINYINT DTS_TYPE_INT2
    BIT DTS_TYPE_INT1
    DECIMAL (p,s) DTS_TYPE_NUMBER
    NUMERIC (p,s) DTS_TYPE_NUMBER
    MONEY DTS_TYPE_NUMBER
    SMALLMONEY DTS_TYPE_NUMBER
    REAL DTS_TYPE_FLOAT
    FLOAT(N) DTS_TYPE_DOUBLE
    DATETIME DTS_TYPE_DATETIME
    DATETIME2 DTS_TYPE_DATETIME
    SMALLDATETIME DTS_TYPE_DATETIME
    DATE DTS_TYPE_DATE
    TIME DTS_TYPE_TIME
    DATETIMEOFFSET DTS_TYPE_STRING
    CHAR(N) DTS_TYPE_STRING
    VARCHAR(N) DTS_TYPE_STRING
    VARCHAR(max) DTS_TYPE_STRING
    TEXT DTS_TYPE_STRING
    NCHAR(N) DTS_TYPE_STRING
    NVARCHAR(N) DTS_TYPE_STRING
    NVARCHAR(max) DTS_TYPE_STRING
    NTEXT DTS_TYPE_STRING
    BINARY(N) DTS_TYPE_BYTES
    IMAGE DTS_TYPE_BYTES
    VARBINARY(N) DTS_TYPE_BYTES
    VARBINARY(max) DTS_TYPE_BYTES
    TIMESTAMP DTS_TYPE_BYTES
    UNIQUEIDENTIFIER DTS_TYPE_STRING
    HIERARCHYID DTS_TYPE_STRING
    XML DTS_TYPE_STRING
    GEOMETRY DTS_TYPE_STRING
    GEOGRAPHY DTS_TYPE_STRING
    ROWVERSION DTS_TYPE_BYTES

    不支持包含以下数据类型的字段的表:

    • CURSOR
    • SQL_VARIANT
    • TABLE
    上一篇
    将PostgreSQL 数据库作为DTS源
    下一篇
    将 Oracle数据库作为DTS源