使用DTS实现自建SQL Server到百度智能云RDS for SQL Server的数据迁移
所有文档

          数据传输服务 DTS

          使用DTS实现自建SQL Server到百度智能云RDS for SQL Server的数据迁移

          1. 使用场景

          本文适用于使用百度智能云数据传输服务DTS,将自建SQL Server或百度智能云RDS for SQL Server数据库实例的数据迁移到自建SQL Server或百度智能云RDS for SQL Server中。

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

          2.1 针对结构迁移任务的限制

          • SQL Server→SQL Server的同构数据库迁移支持表、视图、自定义数据类型、函数、存储过程和触发器的结构迁移。

          2.2 针对全量迁移任务的限制

          • 如果源库中待迁移的表没有主键或唯一约束,且所有字段没有唯一性,可能会导致目标数据库中出现重复数据。
          • 数据类型MONEY和SMALLMONEY仅支持小数点后两位

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

          • SQL Server增量迁移依赖SQL Server CDC功能,需要先启动CDC功能,详见官网:About Change Data Capture (SQL Server)

            • 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。
          • 暂不支持无主键表增量迁移的DELETE操作。
          • 数据类型MONEY和SMALLMONEY仅支持小数点后两位

          2.4 数据库账号权限

          注:如使用拥有sysadmin服务器角色的账号,则不受下面权限限制,如sa账号。

          2.4.1 SQL Server作为源端

          数据库 结构迁移和全量迁移 增量迁移
          自建SQL Server 至少db_datareader权限 至少db_owner权限

          2.4.2 SQL Server作为目标端

          数据库 结构迁移、全量迁移和增量迁移
          百度智能云RDS for SQL Server 至少db_owner(读写)权限

          3. 准备工作

          3.1 数据源准备

          本文以公网自建数据库到百度智能云RDS上云迁移为例,迁移对象是mssql_test.dbo.table1/mssql_test.dbo.table2/mssql_test.dbo.table3,迁移类型是结构/全量/增量,数据源准备:

          • 自建SQL Server 2012实例
          • 百度智能云RDS for SQL Server 2012实例

          对于源端和目标端实例的其他要求详见文档:将SQL Server作为源端将SQL Server作为目标端

          3.2 源端为公网自建SQL Server的准备工作

          3.2.1 数据库迁移账号授权

          1. 在SSMS左侧"对象资源管理器"中,列表中打开"安全性"中的"登录名",右击对待迁移账号,点击「属性」,如"dts_online",如图:

          image.png

          1. 弹出登录属性框,在左侧选择"用户映射",点击数据库查看账号对数据库拥有的权限,可以勾选添加账号对数据库的权限,如读写权限"db_owner",设置之后点击「确定」。

          image.png

          3.2.2 源端增量迁移开启CDC准备工作

          注:如果不选择增量迁移,可忽略3.2.2节准备工作

          1. 启动数据库级别CDC功能

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

          如果您遇到如下报错:

          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'

          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,并忽略实际的设置。

          如果您遇到如下报错:

          消息 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功能会提示启动两个作业:捕获作业和清除作业。

          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. 修改捕获作业读取事务日志的周期(可选)

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

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

          3.3 数据目标端为百度智能云RDS for SQL Server的准备工作

          3.3.1 数据库迁移账号授权

          1. 进入待迁移目标端实例的详情页后,点击「账号管理」,点击「创建账号」。创建对应数据库具有"读写"权限的账号,例如创建账号"dts_online",并且对目标数据库"mssql_test"授权读写权限(db_owner),点击「确定」。如果已经存在迁移账号则忽略该步骤

          image.png

          image.png

          1. 如果已经存在迁移账号,在账号列表中点击「修改权限」,对迁移的目标数据库添加读写权限,点击「确定」。

          image.png

          image.png

          3.3.2 开通百度智能云云数据库公网访问

          1. 登录百度智能云访问控制台,点击实例名称,进入待迁移目标端实例的详情页;

          image.png

          1. 进入实例详情页后,在公网访问选项点击「开通」,弹出公网开通的确认框后点击「确定」。

          image.png

          image.png

          1. 开通成功后,可以在详情页中看到公网访问状态变成"已开通"。点击「获取IP」可以查看外网IPV4,后续可以使用该地址配置DTS任务。

          image.png

          image.png

          3.3.3 创建百度智能云云数据库待迁移数据库

          1. 进入待迁移目标端实例的详情页后,点击「数据库管理」,点击「创建数据库」。

          image.png

          1. 例如上云迁移至目标库"mssql_test",则需要手动创建对应的数据库名,即需要在百度智能云RDS中已存在数据库。点击「确定」。

          image.png

          4. 创建百度智能云DTS迁移任务

          4.1 源库及目标库配置

          1. 登录百度智能云访问控制台,创建数据迁移实例,源端和目标端选择"自建数据存储",点击「下一步」后支付购买;

          image.png

          1. 点击「配置任务」按钮,开始对实例进行迁移任务的配置;

          image.png

          1. 根据需要填写任务名称;在源端连接设置中,接入类型选择「公网」,数据类型选择「SQl Server」,然后填入源端自建SQL Server实例的连接信息。在目标端连接设置中,接入类型选择「公网」,数据类型选择「SQl Server」,然后填入目标端百度智能云RDS for SQL Server实例的连接信息。填写连接信息后点击「测试连通性」,可在右上角弹出连通成功信息。

          image.png

          1. 点击「授权白名单进入下一步」按钮继续配置迁移类型及库表。

          4.2 迁移类型及库表

          1. 根据迁移需求,在迁移类型中勾选:结构、全量、增量。注意:增量迁移需要先对库表启动CDC功能,详见本文3.2.2小节。
          2. 在源库对象中选中需要迁移的对象,右边可以查看已选择对象。如果需要更改迁移对象在目标库中的名称,可以点击对象右侧的编辑按钮进行重命名。点击「保存并预检查」开始预检查

          image.png

          4.3 任务校验及启动

          1. 稍等片刻,页面显示任务校验成功。

          image.png

          1. 点击「立即开启任务」按钮即可启动任务。
          上一篇
          使用DTS实现Oracle到MySQL数据迁移
          下一篇
          API参考