Comparing against a SQL Script is an advanced feature in DBChangeManager which is hard to find in other SQL Server comparison tools in the market. This is very important when you have to compare databases where you do not have direct access to the database server. You can ask any person at the site to send the database script and compare it locally and send the synchronization script. In case you need to compare only a few objects, you can get the script for those objects and compare them.
The two other comparison types are SQL Server full backups and SQL Server snapshots which are not in this tool. Specially, if you had the option of comparing backup databases it would be very helpful.
Let us see the available options for you when performing a comparison. The following is the list of available comparison options.
Ignore Case, Ignore white spaces, ignore collations are the most important options. For example, if you are using different collations there is a chance that you will get alert for comparison for almost all varchar, nvarchar and char columns. If that is the case it will be extremely difficult to distinguish other changes. To avoid this, you can enable the ignore collations settings.
There are few settings which are missing in this list and developers would love to have:
|
Option |
Importantance |
|
Ignore SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements |
Ignore these statements when comparing views and stored procedures etc. |
|
Ignore replication triggers |
Triggers are created for tables when merge replication is configured and option will ignore when comparing them. |
|
Ignore Fill Factor |
Fill factor is set when an index is created. |
|
Ignore Statistics |
|
|
Trigger order |
You can define your triggers to be executed in different orders. |
Filtering Comparison
Most of the time you don’t need to compare all objects. If you have a filter you can narrow down your comparison and hence improve the speed of the comparison.
With DBChangeManager, you have two types of filtering. One type is object type filter.
Above is the list of objects that can be compared using DBChangeManager. You cannot find any objects in the SQL Server which are not in the above list. If you want to compare only tables and views, you can simply select the Tables and Views option from the above list and perform a compare.
Another option for filtering is by using criteria. For example, if you want to compare a table which starts with tbl prefix, this is the option that you should use.
A slight issue with above option is that you can only have an AND condition not an OR condition.
Usually your source and target databases will not have same schema. So there is a requirement to map these, otherwise you will end up with an unwanted list of objects after the comparison.
From the following screen, you can map the source and target schemas.
By default, schemas with same name are mapped together.
After all configuration and setting up it is time to compare two databases. After clicking the Compare button real fun starts. The following is the comparison result screen.
If you click the changed object, you are able to see the code for both source and target databases. When columns are either missing or newly added, they are indicated by arrows and when a column is modified the differences are highlighted with cyan background so that user can identify the modification easily. You have the luxury of filtering by object type, object name and type of change.