How to compare database differences?

Working with databases is often a tedious task. When deploying a new database, or deploy changes within the database to production, you want to be sure that the schema is right. Not only the changes to the schema are important, but also the contents of views and stored procedures need to be compared.

However, it is hard to compare databases and schemas on the same server, or between servers. Additionally, it is a time consuming and often recurring task.

Therefore we added the 'Compare database' functionality to the SQL DataTool. We made it as easy as possible to use, and saves time. This article covers how to compare databases using the SQL DataTool.

Why should you compare databases?

When you are working in software development, this is not a question that you will have to ask. There are several reasons why you want to compare databases.

The first reason is when you are releasing a new version of the companies application. This means when you are deploying the new version to production. Or when your application is an on premise application, when you are installing a new application or the new version of your application at a client. You want to make sure that the database does not contain any errors or misses columns. You also want to make sure that all columns have the right column type and length. This all is necessary to run your application flawless.

Another reason why you might want to compare your databases, is when you are migrating your application or database to new servers. One of the most overlooked problems is differences in the databases, or an incomplete migration of all the scripts that are available in the databases. In addition, you also should want to have access to the database for all users that should have access. Therefore, you should compare the users that have access to the database too.

A third scenario is when your application runs on several database servers, or is mirrored to guarantee uptime and availability. The databases that the application can connect to, should be exactly the same. This prevents errors happening in your application and also prevents loss of data.

How to compare databases using SQL DataTool?

So now you know why you need to compare databases, let's see how this can be done using the SQL DataTool. First, we select the option 'Compare databases' in the SQL DataTool menu. The screen show a few options now.

As a default, the connected database is selected as 'Source database' per default. No second database, or target database, is selected. The other options that you can choose are now to select a different database ('Other database') as the source database, and/or select a different database ('Other database') as 'Target database'.

If we only select one database (only 'Source database'), we will get the details of that database when we click on the button 'Compare now'. This way, we can review all details of that specific database. Since no differences can be identified, all the details of that database will remain the default color.

When we actually want to compare two databases, we have to select a second database under 'Target database'. When we now click on 'Compare now', the analysis will run on the comparison of the two selected databases.

SQL DataTool does not only compares the source database with the target database, but also the other way around. This way, it is possible to review missing objects in the source database, as well as unnecessary objects in the target database.

What does the comparison result show?

The result list will contain all the details of the comparison. Besides the differences between the databases, the similarities are listed too. You can filter in different ways in the details. First of all, you can select whether you only want to view the differences. By checking the checkbox 'Show differences only', only the differences between the databases will be shown.

SQL DataTool uses different colors to indicate the differences between databases. Green indicate that there is no difference between the databases. This not only mean that the object itself does not differ, but also the underlying contents (e.g. in case of a table, it also shows that the columns are similar).

An orange object indicates that the object itself does exist and is similar between the compared databases, but the underlying contents differ. When the schema is orange, it means that one or more of the objects within that schema is missing or different (e.g. one or more tables are missing). When a table is orange, it means that the columns within that table is missing or do not have similar data types or other details. When a view or procedure is orange, the contents or script of the view differs between the databases.

A red object means that the object itself is missing in the other database. A missing object will also result in an orange parent object. This means for example that a missing column will result in an orange table.

Next, we can also filter on the object type that you are interested in. The following selections are possible:

• All: All objects are displayed within the details. The details of all differences are visible;
• Tables: Only tables are displayed in the details. The details now indicate when a table is missing;
• Tables - Columns: All tables and columns are displayed in the details. The details now not only indicate whether a table is missing, but also whether a column is missing or when the columns in the databases differ in data type, primary key or whether the column can contain NULL;
• Views: Only views are displayed in the details. Differences between the databases in views indicate whether a view is missing or when the contents of the views differ;
• Procedures: Only procedures are displayed in the details. Differences between the procedures in the databases indicate whether a procedure is missing or whether the script of the procedures differ;
• Users: Only the users of the databases are displayed.

Finishing the database comparison

When you have compared the databases and solved the differences between the databases, you can run the comparison again. When you have made the right changes, no unexpected differences should come out of the comparison.

SQL DataTool has saved you a lot of manual scrolling and reviewing, and you can now confidently move your application to production and let your client working with your application.

You can try or purchase SQL DataTool by clicking on the button below.