Compare schemas with Schema Comparison

Introduction

Schema comparison plays an important role in databases development and management. But comparison has its own rocks, each of them requires a separate article to discuss. However, we would like to focus not on the variety of difficulties, but how to make schema comparison smooth and ensure the best comparison results.

What you'll need

This article describes the basics of qualitative schema comparison and examines the functionality of Schema Comparison, a professional tool that automates MySQL schema comparison and synchronization. It is integrated into dbForge Studio for MySQL or provided as part of dbForge Fusion for MySQL. Click on the names of these products to download the evaluation versions. To find what else you can do using dbForge Studio for MySQL, go here.

Steps

Convenient Setting Up of Comparison Parameters

Each schema comparison begins with setting parameters: finding required source and target schemas, selecting connections, databases, etc. Schema Comparison wizard replaces manual setting of parameters and significantly save your time. You can quickly select comparison settings to compare either two schemas or a schema and a project.

A project is a set of SQL files, united into one logical structure, which are used to create a new schema or to add new objects to the existing schemas. (You can read about working with projects in the coming article "Database Project Benefits of dbForge Studio for MySQL".)

The wizard is visually divided into left part (it's for source schema setting) and right part (it's for target schema settings). This design will help you to choose schemas and never mix their settings.

To compare two schemas, specify the following:

• Schema type - (Select the Schema radio buttons on the left and right parts of the wizard.)
• Connection - (Open the drop-down lists of the Connection fields and select the required connections. Also you can edit them or create new ones pressing the corresponding buttons on the wizard.)
• Database - (The Database fields provide the lists of databases related to the selected schemas.)

You can use the quickest way to compare two schemas: select two schemas in Database Explorer (a convenient tool for enhanced work with databases), pressing the Ctrl key, and choose the New Schema Comparison option from the right-click menu. Schema Comparison wizard opens with all schema comparison parameters already selected.

To compare a schema and a project:

Check that the project, which should participate in comparison, is open at the moment. Set the schema parameters as was previously described either in the left or right part of the wizard, then on the opposite part select the Project radio button and choose the existing project from the drop-down list.

When all parameters are specified, you can change your mind and swap the source and target schemas pressing the Swapping source and schema comparison button in the middle of the wizard. Click OK to close the wizard window and start the comparison.

Schema comparison is asynchronous. Usually, comparing large amount of data can take some time, so to do any other tasks, you should wait till the process is finished. Asynchronous synchronization does not prevent you from doing other tasks. If any errors occur during comparison, error notifications are shown. You can either stop or retry comparison, or ignore the errors. When the comparison is finished, you can see the results in the SchemaComparison window. It automatically opens after comparison is finished and provides essential functionality for easy management of schema differences.

You can additionally save your time while setting up schema comparison next time. Save the opened SchemaComparison document as a file. It will contain source and target schema settings, skipped objects, and filtering. You will enjoy using this option, because it helps to escape some monotonous work.

Easy Handling of Schema Differences

Nowadays, you can find a lot of new tools for schema comparison and stick to any of them. They all provide good comparison, but sometimes you fail to quickly analyze presented schema differences. Understanding and analysis of schema differences are even more important than a quick preparation to comparison and the process itself. The clearer schema differences are presented, the better and correspondingly quicker you understand what schema updates to apply. It's obvious, the quality of schema comparison and synchronization greatly depends on mere understanding of schema differences.

Getting schema differences and understanding them

The SchemaComparison window is specially designed to ease processing of schema differences. When the comparison is finished, this window automatically opens to show the comparison results. Schema differences are neatly presented both in the grid, which is a good way for perception, and in SQL editors under the grid, which in detail show SQL differences of the compared schema objects. As well as in Schema Comparison wizard, the source schema name and connection are displayed on the left part of the window and the target schema name and connection are on the right.

The grid shows the comparison results in the following way:

• Type – the type of schema objects (tables, views, procedures, functions, triggers, and events).
• State – the type of difference. Objects can have the following states:
• Different - for objects with the same names, but different DDL.
• Equal - for identical objects.
• Only in Source - for objects existing only in the source schema.
• Only in Target - for objects existing only in the target schema.
• Inconsistent - for objects that are different, but a target object can not be created or updated due to incompatibility between the source and target MySQL servers. For example, if the source table contains a BIT column and the target MySQL server does not support BIT columns.
• Unknown - for objects which metadata cannot be retrieved.
• Source object name
• Operation – the variant of the action you can apply to the current schema object.
• Target object name

By default, schema objects are grouped by type, so if you intend to update only tables, for example, you will quickly get a fuller appreciation of differences in tables. For better view, you can group objects either by type or operation. Also you can filter the list of objects by state, it's convenient when you need to see, for example, only Different objects or ones which exist only in the source schema.

Analyzing SQL differences of the compared schema objects

You often have to analyze SQL differences of the compared schema objects and quickly update them. Any text comparer, which can be used through the command line, will solve this task, but Schema Comparison expands your choice. In the top menu Tools->Options->Schema Comparer->General you can select one of the following text comparers:

• Internal - If this option is selected, under the grid in the SchemaComparison window, two SQL editors will show DDL of the selected source and target schema objects. It is a convenient option, as you don't lose your time and are not distracted. You can see the object differences and SQL differences of the selected objects at once.
• Araxis Merge - Before selecting this option, check that Araxis Merge is installed on your PC.
• Custom - You can set and use any other text comparer, which can be used through the command line.

You will intuitively find and analyze differences in DDL, as they are marked with three colors:

• light green is for lines existing in the source schema but missing in the target one
• light red - lines existing in the target schema but missing in the source one
• light blue - lines different in the source and target schemas

Corresponding blocks of text, but with any differences, are connected with lines. If required, you will copy text from the SQL editors and then edit or save it in a new SQL document.

Easy-to-understand Schemas Synchronization

After a good overview of schema differences, the next important element of successful schema comparison is to accurately select what objects should be created, updated, dropped, or excluded from synchronization. This step causes many errors, so you should understand what to do.

The SchemaComparison window simplifies this task. You can see the possible operations in the Operations column. These operations are automatically selected based on the object status. If it's necessary, you can change them, selecting the required ones from the drop-down list.

The following operations are available:

• Skip - to exclude the object from synchronization. It can be applied to all objects.
• Update - for objects with Different status.
• Drop - for Only in Source and Only in Target objects.
• Create - for Only in Target objects.

When all update operations are selected, you are ready to synchronize the schemas. Note, you can not roll back synchronization, so, to be on the safe side, backup target schema before synchronization. Click the Applying updates button on the toolbar to synchronize the schemas. As well as comparison, schema synchronization is asynchronous, i.e., the one, which does not prevent you from doing other tasks.

While synchronizing a project with a schema, the synchronization is performed like this:

• If Drop operation is selected for the object, the SQL file with the corresponding object is deleted from the project.
• If Create operation is selected, the SQL file with the corresponding object is added to the project.
• If Update operation is selected, DDL of the selected object is updated in the the SQL file(s).

The updates can be exported either to a file or to the Editor window. You will appreciate this option, because it gives you many benefits. For example, if synchronization can't be done due to the unavailability of a server where the target schema is located, you can apply the updates from the file later. In case, any errors occur during synchronization, you can open the synchronization script to find the errors and, after the errors are corrected, apply the updates. No confusion or extra efforts in the future. You can review the updates at any moment and have clear understanding of your work.

Summary

While comparing schemas, you can use various approaches and multiple tools. But whatever tools you've chosen, pay attention to the aforementioned stepping stones to have accurate comparison. Setting up of comparison parameters should be easy and quick. You must understand the presented schema differences at first glance and, moreover, have no confusion while analyzing how to update schema objects. The Schema Comparison tool meets these requirements and, as a result, ensures the qualitative comparison as well as satisfaction that you have successfully done your tasks.

• This page was last modified 12:23, 12 October 2008.