迁移指南:DB2 到 SQL 数据库Migration guide: DB2 to SQL Database

适用于: Azure SQL 数据库

本指南介绍如何使用适用于 DB2 的 SQL Server 迁移助手将 DB2 数据库迁移到 Azure SQL 数据库。This guide teaches you to migrate your DB2 databases to Azure SQL Database using SQL Server Migration Assistant for DB2.

有关其他方案,请参阅数据库迁移指南For other scenarios, see the Database Migration Guide.


要将 DB2 数据库迁移到 SQL 数据库,需要:To migrate your DB2 database to SQL Database, you need:


满足先决条件后,就可以发现环境的拓扑并评估迁移的可行性。After you have met the prerequisites, you are ready to discover the topology of your environment and assess the feasibility of your migration.

评估和转换Assess and convert

使用 SQL Server 迁移助手 (SSMA) 创建评估。Create an assessment using SQL Server Migration Assistant (SSMA).

要创建评估,请执行以下步骤:To create an assessment, follow these steps:

  1. 打开 DB2 SQL Server 迁移助手 (SSMA)。Open SQL Server Migration Assistant (SSMA) for DB2.

  2. 选择“文件”,然后选择“新建项目”。Select File and then choose New Project.

  3. 提供项目名称和项目的保存位置,然后从下拉列表中选择Azure SQL 数据库作为迁移目标。Provide a project name, a location to save your project, and then select Azure SQL Database as the migration target from the drop-down. 选择“确定”。Select OK.


  4. 为“连接到 DB2”对话框上的 DB2 连接详细信息输入值。Enter in values for the DB2 connection details on the Connect to DB2 dialog box.

    “连接到 DB2 ”实例

  5. 右键单击要迁移的 DB2 架构,然后选择“创建报表”。Right-click the DB2 schema you want to migrate, and then choose Create report. 这将生成一个 HTML 报表。This will generate an HTML report. 或者,可以在选择架构后,从导航栏中选择“创建报表”。Alternatively, you can choose Create report from the navigation bar after selecting the schema.


  6. 查看 HTML 报表,了解转换统计信息以及任何错误或警告。Review the HTML report to understand conversion statistics and any errors or warnings. 另外,还可以在 Excel 中打开报表以获取 DB2 对象的清单,以及执行架构转换所需的工作量。You can also open the report in Excel to get an inventory of DB2 objects and the effort required to perform schema conversions. 报表的默认位置在 SSMAProjects 内的报表文件夹中。The default location for the report is in the report folder within SSMAProjects.

    例如:drive:\<username>\Documents\SSMAProjects\MyDB2Migration\report\report_<date>For example: drive:\<username>\Documents\SSMAProjects\MyDB2Migration\report\report_<date>.


验证数据类型Validate data types

验证默认的数据类型映射,并根据需要对其进行更改(如有必要)。Validate the default data type mappings and change them based on requirements if necessary. 为此,请执行下列步骤:To do so, follow these steps:

  1. 在菜单中,选择“工具”。Select Tools from the menu.

  2. 选择“项目设置”。Select Project Settings.

  3. 选择“类型映射”选项卡。Select the Type mappings tab.


  4. 可以通过在“DB2 元数据资源管理器”中选择表来更改每个表的类型映射。You can change the type mapping for each table by selecting the table in the DB2 Metadata explorer.

架构转换Schema conversion

要转换架构,请执行以下步骤:To convert the schema, follow these steps:

  1. (可选)向语句中添加动态或临时查询。(Optional) Add dynamic or ad-hoc queries to statements. 右键单击节点,然后选择“添加语句”。Right-click the node, and then choose Add statements.

  2. 选择“连接到 Azure SQL 数据库”。Select Connect to Azure SQL Database.

    1. 输入连接详细信息以在 Azure SQL 数据库中连接数据库。Enter connection details to connect your database in Azure SQL Database.
    2. 从下拉菜单选择目标 SQL 数据库。Choose your target SQL Database from the drop-down.
    3. 选择“连接”。Select Connect.

    填写详细信息以连接到 Azure 中的逻辑服务器

  3. 右键单击架构,然后选择“转换架构”。Right-click the schema and then choose Convert Schema. 或者,可以在选择架构后,从顶部导航栏中选择“转换架构”。Alternatively, you can choose Convert Schema from the top navigation bar after selecting your schema.


  4. 转换完成后,比较并查看架构的结构,以识别潜在问题并根据建议解决这些问题。After the conversion completes, compare and review the structure of the schema to identify potential problems and address them based on the recommendations.


  5. 在本地保存该项目以进行脱机架构修正练习。Save the project locally for an offline schema remediation exercise. 在“文件”菜单中选择“保存项目”。Select Save Project from the File menu.


完成对数据库的评估并解决任何分歧后,下一步就是执行迁移过程。After you have completed assessing your databases and addressing any discrepancies, the next step is to execute the migration process.

要发布架构并迁移数据,请执行以下步骤:To publish your schema and migrate your data, follow these steps:

  1. 发布架构:右键单击“Azure SQL 数据库元数据资源管理器”中的“数据库”节点中的数据库,然后选择“与数据库同步” 。Publish the schema: Right-click the database from the Databases node in the Azure SQL Database Metadata Explorer and choose Synchronize with Database.


  2. 迁移数据:右键单击“DB2 元数据资源管理器”中的架构,然后选择“迁移数据”。Migrate the data: Right-click the schema from the DB2 Metadata Explorer and choose Migrate Data.


  3. 提供 DB2 和 Azure SQL 数据库的连接详细信息。Provide connection details for both the DB2 and Azure SQL Database.

  4. 查看“数据迁移报表”。View the Data Migration report.


  5. 使用 SQL Server Management Studio 连接到 Azure SQL 数据库,并通过查看数据和架构来验证迁移。Connect to your Azure SQL Database by using SQL Server Management Studio and validate the migration by reviewing the data and schema.

    在 SSMS 中比较架构


成功完成迁移阶段后,需要执行一系列的迁移后任务,确保一切尽可能顺利高效地运行。After you have successfully completed the Migration stage, you need to go through a series of post-migration tasks to ensure that everything is functioning as smoothly and efficiently as possible.

修正应用程序Remediate applications

将数据迁移到目标环境后,以前使用源的所有应用程序都需要开始使用目标。After the data is migrated to the target environment, all the applications that formerly consumed the source need to start consuming the target. 在某些情况下,实现这一点需要对应用程序进行更改。Accomplishing this will in some cases require changes to the applications.

执行测试Perform tests

数据库迁移的测试方法包括以下活动:The test approach for database migration consists of the following activities:

  1. 开发验证测试:要测试数据库迁移,需要使用 SQL 查询。Develop validation tests: To test database migration, you need to use SQL queries. 必须创建针对源数据库和目标数据库运行的验证查询。You must create the validation queries to run against both the source and the target databases. 验证查询应涵盖已定义的范围。Your validation queries should cover the scope you have defined.

  2. 设置测试环境:测试环境应包含源数据库和目标数据库的副本。Set up test environment: The test environment should contain a copy of the source database and the target database. 请确保隔离测试环境。Be sure to isolate the test environment.

  3. 运行验证测试:针对源和目标运行验证测试,然后分析结果。Run validation tests: Run the validation tests against the source and the target, and then analyze the results.

  4. 运行性能测试:针对源和目标运行性能测试,然后分析和比较结果。Run performance tests: Run performance test against the source and the target, and then analyze and compare the results.


    为帮助开发和运行迁移后验证测试,请考虑使用合作伙伴 QuerySurge 提供的数据质量解决方案。For assistance developing and running post-migration validation tests, consider the Data Quality Solution available from the partner QuerySurge.

利用高级功能Leverage advanced features

请确保利用 Azure SQL 数据库提供的基于云的高级功能,例如内置高可用性威胁检测以及监视和优化工作负荷Be sure to take advantage of the advanced cloud-based features offered by SQL Database, such as built-in high availability, threat detection, and monitoring and tuning your workload.

只有将数据库兼容性级别更新到最新的兼容性级别 (150) 后,某些 SQL Server 功能才可用。Some SQL Server features are only available once the database compatibility level is changed to the latest compatibility level (150).

迁移资产Migration assets

如需更多帮助,请参阅以下资源,这些资源是为支持实际迁移项目而开发的:For additional assistance, see the following resources, which were developed in support of a real-world migration project engagement:

资产Asset 说明Description
数据工作负荷评估模型和工具Data workload assessment model and tool 此工具为给定的工作负荷提供了建议的“最佳匹配”目标平台、云就绪和应用程序/数据库修正级别。This tool provides suggested "best fit" target platforms, cloud readiness, and application/database remediation level for a given workload. 它提供简单的一键式计算和报表生成功能,通过提供统一的自动化目标平台决策过程,帮助加速大规模评估。It offers simple, one-click calculation and report generation that helps to accelerate large estate assessments by providing and automated and uniform target platform decision process.
DB2 zOS 数据资产发现和评估包DB2 zOS data assets discovery and assessment package 在数据库上运行 SQL 脚本后,可以将结果导出到文件系统上的文件。After running the SQL script on a database, you can export the results to a file on the file system. 支持多种文件格式(包括 *.csv),方便你在外部工具(如电子表格)中捕获结果。Several file formats are supported, including *.csv, so that you can capture the results in external tools such as spreadsheets. 如果你想要与未安装工作台的团队轻松共享结果,此方法会很有用。This method can be useful if you want to easily share results with teams that do not have the workbench installed.
IBM DB2 LUW 清单脚本和项目IBM DB2 LUW inventory scripts and artifacts 此资产包含一个 SQL 查询,该查询可访问 IBM DB2 LUW 版本 11.1 系统表,并按架构和对象类型提供对象计数、每个架构中“原始数据”的粗略估计、每个架构中表的大小,以及 CSV 格式的存储结果。This asset includes a SQL query that hits IBM DB2 LUW version 11.1 system tables and provides a count of objects by schema and object type, a rough estimate of 'Raw Data' in each schema, and the sizing of tables in each schema, with results stored in a CSV format.
Azure 上的 DB2 LUW 纯扩展 - 设置指南DB2 LUW pure scale on Azure - setup guide 本指南用作 DB2 实现计划的起点。This guide serves as a starting point for a DB2 implementation plan. 尽管业务要求有所不同,但均适用相同的基本模式。While business requirements will differ, the same basic pattern applies. 此体系结构模式还可用于 Azure 上的 OLAP 应用程序。This architectural pattern may also be used for OLAP applications on Azure.

这些资源是作为 Data SQL Ninja 计划的一部分开发的,该计划由 Azure 数据组工程团队提供赞助。These resources were developed as part of the Data SQL Ninja Program, which is sponsored by the Azure Data Group engineering team. Data SQL Ninja 计划的核心宗旨是解锁和加速复杂的现代化进程,并争取数据平台向 Microsoft Azure 数据平台迁移的机会。The core charter of the Data SQL Ninja program is to unblock and accelerate complex modernization and compete data platform migration opportunities to Microsoft's Azure Data platform. 如果你认为贵组织有意参与 Data SQL Ninja 计划,请联系帐户团队并让他们提交提名。If you think your organization would be interested in participating in the Data SQL Ninja program, please contact your account team and ask them to submit a nomination.

后续步骤Next steps