万紫千红总是春 – 适用于Babelfish为目标的SQL Server数据迁移方法

1.    前言

数据库是通用应用系统的核心模块之一，它负责基础和应用数据的存储、处理、扭转、分析和调用，并为应用的正常运行提供基本支撑。随着云环境的成熟，很多企业的业务都在向云端迁移。云数据库有即开即用、稳定可靠、便捷管理、弹性伸缩等优势。基于这些优势，越来越多的企业也将数据库迁移上云以满足业务发展的需求。

Babelfish for Aurora PostgreSQL (以下简称Babelfish) 是Amazon Aurora PostgreSQL为客户提供的一个服务选项，该服务是一个全托管、高可用、兼容 PostgreSQL 和 ACID 的关系数据库引擎，运行在 Amazon 云上。Babelfish通过支持Microsoft SQL Server 的数据类型、语法和函数来支持 T-SQL 和 SQL Server 行为，可以在不切换数据库驱动程序和少量的代码更改的情况下，快速从商业的SQL Server 切换到云上的Amazon Aurora PostgreSQL，从而可实现更快、风险更低且更具成本效益的迁移。关于Babelfish的更多服务信息和技术内容，请查看服务说明以及官方技术文档。

在实际的SQL Server迁移到Babelfish项目中，一般都会经历以下几个阶段：迁移的技术评估、POC验证测试和正式迁移。下图显示了在迁移的各个阶段中的主要流程、技术步骤和输出内容。

从Babelfish 迁移项目流程中可以总结出想要成功完成迁移的两个关键要素：一是不兼容SQL语句的改写，二是SQL Server 数据的迁移。关于不兼容语句的改写，请参考之前的博客文章：使用Babelfish 加速迁移 SQL Server的代码转换实践。本文则聚焦在SQL Server 数据的迁移上，重点介绍有哪些适用于 Babelfish为目标的SQL Server数据迁移方法及在不同场景和需求下该如何选择合适的方法。

2.    SQL Server数据迁移方法简述

我们知道，云迁移里面最重要的是数据迁移，因为数据价值最高，且不能丢失，数据库中的数据可以说是整个企业的最关键的资产。另外，数据迁移的难度最大，既要保证数据的完整性，又要保证数据的安全性以及基于数据的应用的业务连续性。

数据迁移过程应遵循以下几个基本原则：

• 数据迁移的过程应该适应业务停机窗口，即使用何种方法去迁移数据必须考虑业务停机时间，并从数据量的大小来规划全量迁移和增量数据同步
• 迁移完成后，数据应该是完整的，可校验的，即迁移后目标库与源库的数据应该要保持一致
• 数据迁移过程中如果出现问题，应该是可以回滚的

由于Babelfish是运行在PostgreSQL之上的扩展，数据实际存放在PostgreSQL数据库的表中，显而易见它不支持常规的SQL Server 数据库备份和恢复方式来迁移，数据迁移是以表和其他对象的级别做迁移。有多种方法可以将数据从现有SQL Server迁移到 Amazon Aurora 数据库群集，具体取决于迁移时间、数据规模、成本大小等考虑。一般而言，适用于Babelfish为目标的SQL Server数据迁移方法通常有以下几种

2.1 原生 SQL Server数据迁移

您可以使用原生的SQL Server工具如SQL Server Management Studio (SSMS)、bcp 实用工具以及SQL Server Integration Services (SSIS) 来迁移SQL Server的数据到Babelfish中。另外，一些常用的数据库管理工具，例如DBeaver、DbVisualizer和Navicat for SQL Server等也都带有数据导出导入功能。从具体实现流程来看，这些工具的迁移方法实际上分为两类：

• 通过数据文件迁移：数据库管理工具导出源库表数据为可执行的SQL脚本、常见的格式文件或dump数据文件，再导入目标库中。如SSMS导出的含数据的DDL脚本、平面文件或Excel格式文件，以及其他数据库管理工具导出的各种格式文件，而bcp实用工具则是导出dump数据文件。可执行SQL脚本的通用性最好，适合各种客户端和管理工具直接运行并导入数据，但由于文件中包含 “insert into …” 等SQL 语句，最终文件大小一般会是表数据原始大小的3-6倍。而各种格式文件则一般仅适用于同一导出工具的导入。
• 通过网络传输迁移：数据库管理工具通过TDS协议连接源库和目标库，将数据从源库读取至内存缓冲区或临时文件并写入到目标库中来完成数据迁移。如SSIS数据流任务和SSMS连接源库和目标库的数据导出、导入功能。这种方法对网络的带宽和稳定性要求较高，适合于源库和目标库部署在同一区域，相隔较近的场景。

总结而言，使用原生SQL Server迁移方法配置简单，兼容性好，需要考虑的就是工具的安装、数据中间文件的存放和传输，以及网络间的传输效率等。

2.2 专业的数据迁移服务

随着企业上云需求的日趋增长，越来越多的云服务厂家也相应地提供了专业的数据传输/迁移服务来满足客户数据库迁移上云的需求。此类数据迁移服务通常提供基于Web的控制台来操作，无需安装任何驱动程序或应用程序，其主要特点在于简单易用、支持数据库切换的最短停机时间、支持广泛使用的数据库、支持异构数据库迁移以及支持持续复制等。

AWS Database Migration Service (AWS DMS) 可帮助您快速并安全地将数据库迁移至 AWS。源数据库在迁移过程中可继续正常运行，从而最大程度地减少依赖该数据库的应用程序的停机时间。AWS DMS 可以在广泛使用的开源或商业数据库之间迁移您的数据。

AWS DMS 在基于 Amazon VPC 服务的Amazon EC2 实例上创建复制实例并使用此复制实例来执行数据库迁移。AWS DMS 使用复制实例连接到您的源数据存储、读取源数据并格式化数据以供目标数据存储使用。复制实例还将数据加载到目标数据存储中。这种处理大部分发生在内存中。但是，大型事务可能需要在磁盘上进行一些缓冲。缓存的事务和日志文件也被写入磁盘。

到目前为止，AWS DMS是唯一支持 Babelfish 为数据迁移目标库的数据迁移服务，您只需通过控制台上几个简易的配置步骤就能完成数据迁移任务设置，由此大大降低了数据库整体迁移的复杂性并提高了迁移效率。

注意：支持Babelfish的AWS DMS复制实例版本要求是 3.4.7 或以上，Amazon Aurora PostgreSQL的版本则要求13.6或更高版本。更多AWS DMS详细配置说明请参考使用手册。

2.3 ETL 工具数据迁移

ETL(Extract-Transform-Load) 是用来描述将数据从来源端经过抽取(extract)、转换(transform)、加载(load)至目的端的过程。ETL过程本质上是数据流动的过程，从不同的数据源流向不同的目标数据，由此可见，在省略数据转换步骤的情况下，ETL工具同样可以完成数据迁移的任务。

ETL工具的典型代表有商业的 Informatica PowerCenter、IBM Datastage、Oracle Data Integrator(ODI) 以及开源的 Kettle、Apache Camel 和 Knime等。特别是一些开源ETL工具，许多是使用Java编写，兼容性高、数据抽取高效稳定并且非常易于上手。

Babelfish的兼容性就在于您可以使用相同的客户端工具，使用和连接SQL Server一样的数据库驱动去连接Babelfish来做同样的数据库查询和操作，例如使用相同的odbc驱动或jdbc驱动来连接Babelfish。如果您在日常工作中经常使用这些ETL工具，非常熟悉相关任务配置，那么使用ETL工具来迁移SQL Server数据未尝不是一个简单、快速的选择。

3.    数据迁移实践指引

在介绍了常见的SQL Server数据迁移方法后，接下来我们演示这些数据迁移方法的具体操作过程，并评估这些方法的性能和适用性。对于SQL Server数据迁移的目标，是要通过TDS协议连接到Babelfish的1433端口，目前为止只支持全量数据加载，而增量数据同步，则需要在 PostgreSQL一侧去配置。我们的操作演示有以下几点需要注意：

• 本文所演示和讨论的数据迁移内容，都是指SQL Server全量的数据迁移，限于篇幅原因，在此只列出一些主要的关键配置步骤
• 本文演示的数据迁移过程，SQL Server源库、Babelfish目标库以及安装迁移工具的EC2实例都是部署在Amazon云上同一VPC中
• 本文演示的案例都是在SQL Server源库中使用一张名为 “employee” 的表来做数据迁移，employee表的总行数为1千万，占用存储空间约为564 Mb 大小。下图为 employee表的样例数据和大小统计：

3.1 使用SSMS导出数据脚本迁移数据

SQL Server Management Studio (SSMS) 是一种集成环境，用于管理从 SQL Server 到 Azure SQL 数据库的任何 SQL 基础结构。SSMS 提供用于配置、监视和管理 SQL Server 和数据库实例的工具。您可以在官方链接中下载单独的安装程序来安装。SSMS生成的数据脚本是包含插入数据内容的可执行SQL语句。以下为使用SSMS导出数据脚本并迁移数据至Babelfish的主要操作步骤

1) 使用SSMS登陆到源库后，选择要迁移的表所在的数据库，并点击右键，在弹出的菜单中依次选择“任务”和“生成脚本”

2) 在选择对象页面中，首先点击“选择具体的数据库对象”，再选择需要生成脚本的具体表名

3) 在设置脚本编写选项页面中选择“另存为脚本文件”，输入脚本文件路径和名称，并点击“高级”菜单按钮

4) 在高级脚本编写选项页面中找到“要编写的脚本的数据的类型”选项，并选择“架构和数据”，此选项生成的脚本会包含建表语句和插入数据语句

5) 完成上述设置后导出数据脚本，脚本导出约耗时约2分钟，生成的Unicode格式的数据脚本大小为6GB，无法使用像SSMS这样的图形化管理工具来加载文件并在目标库上执行导入，您可以使用SQLCMD工具在命令行上执行以下导入操作：

C:\> sqlcmd -S "服务连接端点" -U "用户" -P "密码" -d "数据库" -i "脚本"

6) 数据脚本中包含所有数据的insert语句，执行过程是单条SQL语句的串行处理，导入速度非常慢，1千万数据耗时约5个小时才能完成导入

3.2 使用 BCP工具迁移数据

bcp 实用工具 (bcp.exe) 是一个使用大容量复制程序 (Bulk copy program) API 的命令行工具。bcp 实用工具可将 SQL Server 表中的数据导出到数据文件，以供其他程序使用。此实用工具还可将其他程序（通常为另一数据库管理系统 (DBMS)）中的数据导入 SQL Server 表。数据首先从源程序导出到数据文件，然后再通过单独的操作将数据文件中的数据复制到 SQL Server中。bcp程序包含在 sqlcmd 实用工具中，可在官网链接中下载并单独安装。Babelfish支持使用 bcp实用程序进行数据导出和导入，但当前版本不支持某些 bcp 选项（-b、-C、-E、-G、-h、-K、-k、-q、-R、-T、-V）。以下为使用BCP迁移数据的主要操作步骤

1) 使用BCP工具连接源库并导出表数据，生成的bcp文件大小和表大小基本相同

C:\> bcp 表名 OUT 文件名 -c -t"," -r"\n" -S "服务连接端点" -U "用户" -P "密码" -d "数据库"

2) 使用BCP工具连接目标库并导入表数据，由于Babelfish不支持 -b 批量写入的选项，bcp导入数据到Babelfish时是一次将所有数据发送到Babelfish目标库中的内存区域，为避免内存不足，导入时需要根据目标库内存大小来设置一次导入的数据量大小

C:\> bcp表名 IN文件名 -c -t"," -r"\n" -F 首行 -L 末行 -S "服务连接端点" -U "用户" -P "密码" -d "数据库"

3) bcp测试场景是使用r6g.12xlarge的实例，实例配置为384Gb内存，一次导入数据量为500万，耗时4分钟不到，分两次操作完成1000万数据导入，总耗时约8分钟

3.3 使用SSMS导出功能迁移数据

SSMS中的导入导出功能实质上是调用了SQL Server 导入导出向导程序(DTSWizard.exe)，它是一种将数据从源复制到目标的简单方法。另外，您也可以将导入导出任务保存为SSIS的包来做流式调用。对于本文中导入导出功能使用的SQL Server数据库连接方法，简要介绍如下：

• SQL Server Native Client

也称为 SNAC 或 SQLNCLI，是一个单一的动态链接库 (DLL)，其中包含用于 Windows 的 SQL OLE DB 提供程序和 SQL ODBC 驱动程序。 它包含对使用本机代码 API（ODBC、OLE DB 和 ADO）连接到 SQL Server 的应用程序的运行时支持。

• .NET Framework Data Provider

.NET Framework 数据提供程序用于连接到数据库、执行命令和检索结果。这些结果将被直接处理，放置在 DataSet 中以便根据需要向用户公开、与多个源中的数据组合，或在层之间进行远程处理。.NET Framework 数据提供程序是轻量程序，可以在数据源与代码之间创建一个精简层，并在不弱化功能的情况下提高性能。

下面是使用SSMS中的导出来迁移数据的主要操作步骤

1) 使用SSMS登陆到源库，选择要迁移的表所在的数据库，并点击右键，在弹出的菜单中依次选择“任务”和“导出数据”

2) 设置SQL Server数据源，请选择“SQL Server Native Client 11.0”，并输入服务器地址、用户及连接的数据库信息

3) 设置Babelfish目标库，请选择“.NET Framework Data Provider for SQL Server”， 并在 ConnectionString 属性栏中输入以下连接信息

Server=Babelfish端点信息;Database=数据库名;User Id=用户名;Password=密码;

4) 选择通过指定表复制或是使用查询来复制，本文使用指定表的方式

5) 在列出的源库中的所有表中，选择要复制的表“employee”

6) 检查迁移数据类型映射情况，对于提示的信息“字符串数据在迁移时使用Unicode 字符串”，可忽略

7) 导出任务设置完成后选择立即执行，完成1千万的数据迁移耗时约1小时50分

3.4 使用AWS Database Migration Service迁移数据

使用AWS DMS迁移数据至Babelfish目标时，选择的复制实例版本和目标库Aurora PostgreSQL版本必须符合上文所述要求。需要注意的是：一些较小的复制实例类足够用于测试服务或小型迁移。如果迁移涉及大量表，或者如果您打算运行多个并发复制任务，则考虑使用较大的复制实例。以下是AWS DMS迁移数据的主要操作步骤

1) 设置SQL Server源库的终端节点属性

2) 设置Babelfish目标库的终端节点属性，注意目标引擎选择为Babelfish

3) 两边终端节点设置完成后检查状态并测试是否能正常访问

4) 设置AWS DMS迁移任务，注意在将Babelfish作为目标数据库时，现阶段还存在以下限制：

• 仅支持全量数据加载，不支持以 Babelfish 为目标的变更记录复制(CDC)
• 目标表准备模式仅支持”不执行任何操作”，即目标表中存在数据不会被更改，只会插入不一样的数据
• 不支持完整LOB(Large Objects)模式

5) 在设置迁移任务时，为追踪任务执行流程和状态，建议启用CloudWatch日志记录相关信息，方便诊断和调试

6) 添加新的选择规则，即哪些表需要复制，可以使用通配符%，在此输入“employee”表名，并设置为“包含”此表的操作

7) 迁移任务创建成功后，手动启动任务

8) 数据迁移任务执行17分钟后完成，1千万的数据迁移成功

3.5 使用ETL工具迁移数据

上文介绍了许多ETL工具都能执行数据迁移的任务，本次演示我们选用一款开源的ETL工具KNIME 分析平台（KNIME Analytics Platform）来演示SQL Server数据迁移方法。 KNIME是用于创建数据科学应用程序和服务的开源软件，借助KNIME，您可以使用直观的拖放式图形界面创建可视化工作流程，而无需编码。

在KNIME Analytics Platform中，每一个任务由节点表示。每个节点都带有输入和输出端口、以及状态。输入是节点要处理的数据，输出是处理后的数据结果集。每个节点都有特定的设置，您可以在配置对话框中进行调整。每个节点都有四种状态，未配置的、已配置的、已执行的、错误的。节点可以执行各种任务，包括读/写文件，转换数据，训练模型，创建可视化等等。下图所示即为本次演示的数据迁移的任务，其中包含数据库连接、数据库表选择、数据库读和写的节点，依序构建成一个数据从SQL Server复制到Babelfish的工作流程。

以下为使用KNIME迁移SQL Server数据到Babelfish的主要操作步骤

1) 配置SQL Server源库的连接，注意选择的驱动为官网下载并导入到KNIME的jdbc for SQL Server驱动

2) 配置Babelfish目标库连接，使用相同的jdbc for SQL Server驱动

3) 配置数据库读取节点，可以将数据放置在内存中，也可以将数据写到本地磁盘，实测配置成写入本地时耗时约30秒

4) 配置数据库写入节点，注意写入时设置按批次写入能极大提高性能，在此设置为每批次1万条数据

5) 任务配置完成后，在流程中最后的数据库写入节点上点击执行，整个任务的各节点依次启动，顺序执行，执行成功的节点状态显示为绿色

6) 数据迁移完成后，在节点监控器中查看写入任务执行时间约为17分钟

4.    总结

通过上面的案例演示，我们为您展示了多种适用于Babelfish为目标的SQL Server数据迁移方法。那么，如何选择合适的迁移方法，来满足数据迁移上云的需求呢？接下来我们会从以下几个方面做具体分析和建议：

• 性能

数据迁移中性能是最重要的考量因素，特别是在有大量数据需要迁移的情况下，它直接影响迁移进度和迁移流程。同时，性能表现也和迁移工具通过网络访问云厂商服务的速度、是否产生中间数据文件、目标数据库实例配置等因素相关联。

从实际测试迁移一张大表的数据到Babelfish的结果中我们可以看到：数据导出的时间每个工具差别不大，数据导入部份，得益于使用大容量复制程序 (Bulk copy program) ，bcp工具的导入性能最好，而数据脚本的导入性能最差，相差接近40倍。要注意的是：当前版本的Babelfish (2.1.0)是首次支持bcp操作，基于上文提到的原因，使用bcp 导入数据到Babelfish目标时，需要考虑实例的内存大小来设置bcp导入参数。随着Babelfish的持续更新和迭代，相信能做到完全的兼容，进一步提升导入性能。

• 功能

数据迁移中对功能的需求主要有是否支持全量加载和增量数据同步、是否支持按指定行数或条件查询迁移数据、是否支持迁移的定时任务或任务中断恢复等。

上述的几种数据迁移方法，大部份都支持按指定行数或条件查询迁移数据。做为专业的数据迁移服务，AWS DMS功能相比其他迁移方法会支持的更多，比如任务中断恢复、任务跟踪日志、数据的映射转换等，而数据脚本迁移的方法功能最单一。

• 成本

选择不同的数据迁移方法会产生不同的成本，这主要涉及到迁移工具本身是否收费、工具部署方式产生的费用、生成数据中间文件需要存放以及网络流量等产生的费用。

对于成本的考量，还要将迁移数据大小，迁移时间进度、迁移的优先级等各种因素放在一起来仔细衡量。

• 简易性

不同的数据迁移方法，在实际使用中会涉及到迁移工具的部署方式、学习曲线、产品支持力度等问题。简单易用，稳定可靠的迁移工具能提高操作人员工作效率，加快迁移速度。

虽然我们介绍的几种数据迁移方法都非常简单易用，但AWS DMS完美地诠释了云时代的工作方式：所有的组件都部署在云端，服务即开即用，通过Web直接操作，鼠标简单地几步点击就能完成一个迁移任务的设置。

最后，根据以上几个迁移工具选择的重要维度和实测结果，我们总结了以上适用于Babelfish为目标的SQL Server数据迁移方法的比较供您参考。需要注意的是：数据大小，软硬件环境，数据结构等因素都会对迁移时长产生影响。

本篇作者