Comparing Database Schema

In this video, we’ll use DB Change Manager to compare two schemas. Within the Change Manager, we’ll find our data source. We’ll connect to it, expand the node, right-click on the schema comparison job, select Create New, and provide a name for the job. Any notes? If we’d like as a source, we’re going to use the archive. We could use the Live database, but we’re going to use the archive that we created in an earlier video. We’ll select the version. We’ll select version one. At this point, we’re going to look at our target. Let’s select the data source and we’re going to use the same data source as our target.


We’ll go to the refinements tab. Here we’re refined based on the archive that we collected. So here’s our GIM schema. If we look, we can see the exact objects that we included. So here’s our list of tables.


Mapping Data Sources

Look at our mapping. DB Change Manager allows us to map different data sources and different owners. In this case, I could drop down and select a different schema if I like to. Here I’m going to keep it with the same GIM schema. Let’s look at our options.

Synchronize Schema

Here we have our synchronization method. We can synchronize using manual or automatic. We default to manual so you can review the changes. We have our include object options. We can tell the Change Manager how we’d like it to behave depending on the options. Here, if it exists in the source only, we’re going to create it. If it exists in the source and target and doesn’t match, we can do an alter. We can also force an extended alter where we drop the object and recreate it using the DDL script.

If we look down a little further, we can look at our Compare options, allowing us to ignore certain things, understanding that different environments may have different settings.



Scroll to the bottom. We can preserve the target with some identifier options. Lastly, we can extract the DDL if we’d like. Let’s go to our Notifications tab.

Here we can enable an email notifier if we’d like, providing the username, the email address as well as the different notification commands. Here it’s going to run from both the command line and the workbench and send the job outcome on both success and error, as well as attach reports if we’d like to as well.

Compare Schema

We’ll go to history. Let’s go ahead and save this job. And now let’s go ahead and execute. With the job completed, we can now see the results of our Compare job, we can see a Compare index of 96%. We can look at the individual results and look at the Generate Synchronization script as well. So let’s look at the individual results.


Database Objects

We see our overview here of all of our objects on the right, we can filter down and show only the objects we’re interested in. We have zero drops, and zero extended alters incompatible. The matches are 49, which are not of interest to me right now. I’m just looking at the differences. Here we can see we have a create and an alter. If I select the object here, we can see it’s letting us know that a column was found in the target that did not exist in the source. Here’s the script to alter that, we can look at the source DDL as well as the target DDL. You can see that mobile number is there. If we look at our other object found, we can see here an index that was dropped from the live data source that existed in our archive. Here is our script to create that.


Data Synchronization

At this point we can limit the objects that are recreated in our synchronization script or select all. At this point, we can generate the synchronization script, export the sync script, generate a report or view object summaries. Let’s look at the generation of the synchronization script. Here is the script needed to synchronize the live data source back in line with the archive. At this point, we can execute it from the Change Manager, or we can copy it and run it in another execution window.

Topics : Database Change Management,Database Development,

Products : DB Change Manager,

Comparing Schema

DB Change Manager

IDERA DB Change Manager is an essential database change management tool for DBAs and database professionals. It uniquely offers automated compare and synchronization capabilities to simplify change operations. Rapidly identify changes, streamline upgrades to new releases and pinpoint environmental differences resulting from changes at the data, schema and database configuration levels.

Adopting reliable, repeatable, and efficient processes to manage database changes is vital to the success of any software project. DB Change Manager puts the power of change tracking, schema comparison, software-generated synchronization scripts, and flexible reporting into the hands of DBAs and development teams. Powerful change control and automated correction capabilities reduce your exposure to downtime. Learn More →


Contact IDERA: