Edit

Share via

Migration guide: IBM Db2 to SQL Server on Azure VM

  • Article

Applies to: SQL Server on Azure VM

This guide teaches you to migrate your user databases from IBM Db2 to SQL Server on Azure VM, by using the SQL Server Migration Assistant for Db2.

For other migration guides, see Azure Database Migration Guides.

Prerequisites

To migrate your Db2 database to SQL Server, you need:

Pre-migration

After you have met the prerequisites, you're ready to discover the topology of your environment and assess the feasibility of your migration.

Assess

Use SSMA for DB2 to review database objects and data, and assess databases for migration.

To create an assessment, follow these steps:

  1. Open SSMA for Db2.

  2. Select File > New Project.

  3. Provide a project name and a location to save your project. Then select a SQL Server migration target from the drop-down list, and select OK.

    Screenshot that shows project details to specify.

  4. On Connect to Db2, enter values for the Db2 connection details.

    Screenshot that shows options to connect to your Db2 instance.

  5. Right-click the Db2 schema you want to migrate, and then choose Create report. This will generate an HTML report. Alternatively, you can choose Create report from the navigation bar after selecting the schema.

    Screenshot that shows how to create a report.

  6. Review the HTML report to understand conversion statistics and any errors or warnings. You can also open the report in Excel to get an inventory of Db2 objects and the effort required to perform schema conversions. The default location for the report is in the report folder within SSMAProjects.

    For example: drive:\<username>\Documents\SSMAProjects\MyDb2Migration\report\report_<date>.

    Screenshot of the report that you review to identify any errors or warnings.

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.

    Screenshot that shows selecting the schema and type mapping.

  4. You can change the type mapping for each table by selecting the table in the Db2 Metadata Explorer.

Convert schema

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. Select Connect to SQL Server.

    1. Enter connection details to connect to your instance of SQL Server on your Azure VM.
    2. Choose to connect to an existing database on the target server, or provide a new name to create a new database on the target server.
    3. Provide authentication details.
    4. Select Connect.

    Screenshot that shows the details needed to connect to your SQL Server on Azure VM.

  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.

    Screenshot that shows selecting the schema and converting it.

  4. After the conversion finishes, compare and review the structure of the schema to identify potential problems. Address the problems based on the recommendations.

    Screenshot that shows comparing and reviewing the structure of the schema to identify potential problems.

  5. In the Output pane, select Review results. In the Error list pane, review errors.

  6. Save the project locally for an offline schema remediation exercise. From the File menu, select Save Project. This gives you an opportunity to evaluate the source and target schemas offline, and perform remediation before you can publish the schema to SQL Server on Azure VM.

Migrate

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. Publish the schema. In SQL Server Metadata Explorer, from the Databases node, right-click the database. Then select Synchronize with Database.

    Screenshot that shows the option to synchronize with database.

  2. Migrate the data. Right-click the database or object you want to migrate in Db2 Metadata Explorer, and choose Migrate data. Alternatively, you can select Migrate Data from the navigation bar. To migrate data for an entire database, select the check box next to the database name. To migrate data from individual tables, expand the database, expand Tables, and then select the check box next to the table. To omit data from individual tables, clear the check box.

    Screenshot that shows selecting the schema and choosing to migrate data.

  3. Provide connection details for both the Db2 and SQL Server instances.

  4. After migration finishes, view the Data Migration Report:

    Screenshot that shows where to review the data migration report.

  5. Connect to your instance of SQL Server on Azure VM by using SQL Server Management Studio. Validate the migration by reviewing the data and schema.

Screenshot that shows comparing the schema in SQL Server Management Studio.

Post-migration

After the migration is complete, 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

Testing consists of the following activities:

  1. 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 the 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 tests against the source and the target, and then analyze and compare the results.

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 data assets discovery and assessment package After running the SQL script on a database, you can export the results to a file on the file system. 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 inventory scripts and artifacts 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.
IBM Db2 to SQL Server - Database Compare utility The Database Compare utility is a Windows console application that you can use to verify that the data is identical both on source and target platforms. You can use the tool to efficiently compare data down to the row or column level in all or selected tables, rows, and columns.

The Data SQL Engineering team developed these resources. This team's core charter is to unblock and accelerate complex modernization for data platform migration projects to Microsoft's Azure data platform.

Next steps

After migration, review the Post-migration validation and optimization guide.

For Microsoft and third-party services and tools that are available to assist you with various database and data migration scenarios, see Data migration services and tools.

For video content, see Overview of the migration journey.