将SQL Server作为源端
1. 适用场景
本文适用于使用百度智能云数据传输服务DTS(以下简称DTS),支持自建SQL Server数据库为源端的数据迁移任务。
2. 迁移前置条件
2.1 数据库账号权限
数据库 | 结构迁移和全量迁移 | 增量迁移 |
---|---|---|
自建SQL Server | 至少db_datareader权限 | 至少db_owner权限 |
2.2 针对增量数据迁移任务的前置条件
- 数据库恢复模式设置为Full。
- 增量迁移任务使用变更数据捕获(Change Data Capture,以下简称CDC)功能, 不同版本对CDC功能存在限制:
版本 | Enterprise | Standard |
---|---|---|
12 | 支持 | |
14 | 支持 | |
16 | 支持 | 支持 |
17 | 支持 | 支持 |
19 | 支持 | 支持 |
3. 将SQL Server数据库作为源的限制
3.1 针对结构迁移任务的限制
- SQL Server→SQL Server的同构数据库迁移支持表、视图、自定义数据类型、函数、存储过程和触发器的结构迁移。
- 异构数据库间仅支持库表结构的迁移。
3.2 针对全量迁移任务的限制
- 如果源库中待迁移的表没有主键或唯一约束,且所有字段没有唯一性,可能会导致目标数据库中出现重复数据。
- 数据类型MONEY和SMALLMONEY仅支持小数点后两位。
3.3 针对增量数据迁移任务的限制
- 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仅支持小数点后两位。
4. 使用SQL Server数据库作为源端
使用SQL Server数据库作为源端,在任务创建、任务配置、前置检查、任务启动、任务暂停、任务终止的操作流程请参考典型实践文档。 在任务配置参数和对象映射部分与其他数据源有些许不同。
4.1 任务配置参数
如上图所示。目前DTS的源端支持公网自建SQL Server实例,SQL Server源端配置参数说明如下:
- 接入类型:支持公网/BCC/BBC/DCC自建SQL Server实例。
- 数据类型:固定选择SQL Server。
- IP/端口:自建SQL Server数据库的访问IP与服务端口。
- 数据库:自建SQL Server数据库中待迁移的数据库名。
- 账号:自建SQL Server的数据库账号。
- 密码:该数据库账号对应的密码。
4.2 对象映射(异构迁移)
SQL Server为三级schema,当需要向两级schema的目的端进行数据迁移时, DTS提供了两种库表名映射方式供用户选择。如下图所示,选择好的迁移对象会出现在右边的已选择对象列表中。DTS支持上下游库表名映射、列过滤黑白名单等功能。可以点击【编辑】,对每一个迁移对象配置映射和过滤规则。目标数据库对象可以映射为源库中database和schema:
- 数据库对象映射为源库中的Database: 忽略SQL Server中的schema, SQL Server中不同schema下的表均映射到mysql指定库中,目标端库名默认使用源端的库名。
- 数据库对象映射为源库中的Schema: 忽略SQL Server中的库名,SQL Server中不同schema映射为mysql中不同库,目标库名默认使用源端schema名。
完成对象映射配置后,点击【保存并预检查】,启动任务的前置检查。
5. 在SQL Server数据库中使用CDC
使用CDC功能之前需要启动数据库代理服务。如果您使用的是RDS for SQL Server,可能存在启动失败,解决方案:
- 配置账号,请参考https://docs.microsoft.com/zh-cn/sql/ssms/agent/configure-a-user-to-create-and-manage-sql-server-agent-jobs?redirectedfrom=MSDN&view=sql-server-ver15
- 如果 SSMS 中"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,或者其他存在的路径。
- 修改本地服务”SQL Server代理“的属性,选择登录身份为"本地系统账号"。
- 然后启动本地服务、SSMS 中启动SQL Server代理。
5.1 启动数据库级别CDC功能
-- 启动数据库级别CDC命令
EXEC sys.sp_cdc_enable_db;
-- 检查是否启动成功
SELECT name,is_cdc_enabled FROM sys.databases WHERE name = 'DB_name'
如果是您使用的是RDS,可能会报错:
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'
5.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功能会提示启动两个作业:捕获作业和清除作业。
5.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';
5.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';
6. 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。