Database Schema Compare Tool

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

Introduction

Customers migrating and modernizing to Azure SQL (Azure SQL DB / Azure SQL MI / Azure SQL VM) can benefit from multiple salient functionalities as well as fundamental capabilities like security, scalability, that comes with a fully managed database offerings in Azure. It supports different deployment choices, service levels, language SDKs, data types, and more. Azure SQL also provides high reliability, disaster recovery, performance, and availability, as well as advanced features such as intelligent query processing, automatic performance tuning, serverless compute, and copilot for query generation.

A key step in migrating the data tier to Azure SQL is to validate the consistency and accuracy of the schema / metadata between the source database and target Azure SQL databases. This can be a difficult and prone to error task if done manually, especially when working with large and complex databases. To make this process easier and faster, you can utilize this tool that can perform a thorough metadata comparison between the source and target database on SQL. The tool can verify the names of tables, column names, ordering of columns, data types of columns, view names, function names, indexes, constraints, triggers, and other objects, as well as their properties and definitions. The tool generates a detailed report that shows the differences and similarities between the two databases.

This technical blog will explain the features and functionalities of Db2 and Azure SQL database Schema comparison Tool that can help you compare schema from Db2 to Azure SQL with ease.

The Database Schema Compare tool currently supports Db2 z/OS and Db2 LUW as source databases and SQL as target database.

Microsoft SQL Server Migration Assistant (SSMA) for Db2 is a tool to automate migration from IBM Db2 database to SQL Server, Azure SQL Database and Azure SQL Database Managed Instance. By migrating from Db2 to Azure SQL, customers can increase their agility and innovation, and improve their data security and compliance with ease.

For data comparison, Database Compare Utility helps to easily compare data Db2 and Azure SQL.  Database Compare Utility supports Oracle, and other source databases as well. Now with addition of this Database Schema Compare Tool overall migration from Db2 to Azure SQL will further ease the migration.

Comparing Schema between Db2 and Azure SQL database

How does the Schema comparison tool work?

Below steps provides details about important part of the schema comparison process followed in the Tool.

1a. Connect to Source Db2 : Schema comparison tool connects to source Db2 database. It fetches all the meta data for the given schema.

The tool supports Db2 z/OS and Db2 LUW currently.

1b. Connect to Target Sql : Tool in parallel also connects to SQL database (SQL DB / SQL MI / SQL VM to fetch all meta data for the given schema.

2a and 2b. Store Object Metadata details: Metadata information across 25+ parameters is collected  which includes (not complete list) Table names, Column names, Column order, Column data type, Column default value, View names, Function names, Stores procedure names, Indexes, Index order, Index Type, Primary Key / Foreign key constraints etc.

3a and 3b. Write meta data to Azure SQL : Meta data collected from Db2 and SQL Catalogue tables is stored in staging table in target Azure SQL.

4. Schema comparison Process : Db2 and SQL Schema differences identification process runs on the top of the meta data stored in Azure SQL.

5. Write differences to Azure SQL: This process writes details about mismatches in Db2 and SQL Schema into Azure SQL differences table. Example: Table Table1 present in Db2 and not in SQL, Column Order for Col1 is different in Db2 and SQL etc.

6. Power BI or Excel connection to Azure SQL: Power BI connects to Azure SQL in Direct Query mode to access differences data.

7. Schema comparison report for Db2 and Azure SQL: Power BI Tool is used to create a report on the schema differences between Db2 and Azure SQL. Alternatively, Excel can be used to view the report.

Db2 and Azure SQL Sample Schema differences report

Below is a sample differences report generated to compare schema SCHEMACOMPARE01 present on Db2 LUW and Azure SQL Database. Numbers marked in red circle are used for annotation and are explained below:

1. Schema Name SCHEMACOMPARE01 for which meta data differences is shown.

2. The total number of differences found between source and target schema.

3. X – axis represents Object categories for which differences are found.

4. Y – axis represents Total number of differences for a given Object category.

5. It shows four differences that were found for Column Order Id in which Order of Column in Db2 and Azure SQL was different.

Tool also has the capability to drill down and show exact difference between source and target object as shown below where column names PRSTAFF and PRSTDATE present in PROJ Table on Db2 but not in target Azure SQL Database.

Db2 and Azure SQL example valid differences which should be ignored.

The following table shows some examples of how Db2 and Azure SQL have some inherent differences which are expected and hence accepted by the Tool when it compares them. This list is not exhaustive and only illustrates some differences.

How to get access to this tool?

You can request setting up this Tool for your use case by emailing at alias: datasqlninja@microsoft.com

Summary

This tool is beneficial to customers to perform automatic schema comparison between Db2 and Azure SQL and ease the migration from Db2 to Azure SQL. Using this tool before testing / migrating / cut-over can help you to minimize any issues related to schema differences while performing data migration or application execution.

We will incrementally update the supported databases based on customer feedback.

Feedback and suggestions

If you have feedback or suggestions for improving this data migration asset, please send an email to Databases SQL Engineering Team.