Database Structure Synchronization using Navicat 15

Mar 11, 2020 by Robert Gravelle

Perform an Internet search for “database synchronization” and you’re likely to receive a lot of information on synchronizing database data. Meanwhile, instructions on synchronizing database schema structure is less prevalent. There is an inherent risk of destroying existing data that comes with altering the database structure. For that reason, you have to be extra careful when doing so.

Navicat can be a tremendous ally in synchronizing database structures. In today’s blog, we’ll learn how to use Navicat Premium 15‘s Structure Synchronization wizard to update one database’s schema structure to match that of another.

The Structure Synchronization Wizard can be launched from the Tools menu. You’ll find the Data transfer… and Data Synchronization… commands there as well:

Navicat introduced a new mechanism for structure synchronization back in version 12. It provides an easier and more intuitive way to visually compare and identify the differences between two databases. And it shows side-by-side Data Definition Language (DDL) comparison that makes it easy to locate all the object differences. You can then choose and reorder your synchronization scripts to update the destination database.

It should be noted that Navicat Data Modeler 3.0 also supports Structure Synchronization. It helps you to discover and capture changes made in the model and then apply them to a targeted schema.

Altering the structure of a database that already contains data is fraught with danger. Therefore, you should always backup your data before attempting to synchronization database structures. This can be easily accomplished using Navicat’s Backup utility. You’ll find it on the main button bar:

You can backup many types of database entities, including tables, views, functions/stored procedures, and events:

The wizard guides you through the each step of the synchronization process via several screens as follows:

Setting the Source and Destination Databases

The first screen sets the connection and database details where the target database structure will be compared to that of the source:

Structure Comparison

The Structure Comparison screen is where you can compare and identify the differences between two databases. You can group items by Operation or Object Type:

Deploying the Script

The third and final screen shows the generated deployment script:

To run the script, click the Execute button at the bottom of the screen. Results will be displayed on the Message Log tab of the same screen:

You can save the current Synchronization profile for later use or load an existing profile at any time.

There is also a Back button, should you wish to recompare database structures.

In today’s blog, we learned about the inherent risk of destroying existing data that comes with altering the database structure and how to minimize it using Navicat Premium 15‘s Structure Synchronization wizard to update one database’s schema structure to match that of another.

Interested in finding out more about Navicat Premium 15? You can try it for 14 days completely free of charge for evaluation purposes!