Most of us maintain a few databases that share the same metadata; An example can be a database for production, and a database for QA. Or, a SaaS company that manages similar databases for all its customers.
However, changes are inevitable. With time our databases evolve and change, and maintanance may be complex. Once we change a database, the change needs to be applied across all the databases that use the same schema.
PartitionDB gives you a simple and quick way to compare and match your databases’ schemas; A comparison function that is using one of three modes:

  • Full compare – this mode lists all the differences between the two databases, including differences in objects and missing objects

  • Comparison of differences between objects – this mode lists the differences between the database’s objects. It does not list missing or additional objects.

  • Comparison of objects in the databases – this mode shows the missing or additional objects between the two databases that are examined. The comparison ignores differences in the objects themselves.

Each of the modes that you choose to compare your data with, will give you the script to match your databases based on your comparison results.

We will now go through examples to explain each of the modes (In order to run the examples below you need to download PdbLogic and restore it to your database).

If you wish to compare data and not schema, please refer to our post Data comparison with PartitionDB.

Full comparison

The script below creates two databases: TestCompareDB1 and TestCompareDB2. It creates a table and a procedure on TestCompareDB1. On TestCompareDB2 it creates a table only, which is missing one column comparing to TestCompareDB1.

Then it runs the PartitionDB compare function, passing 1 to CompareMode parameter.

Running this script will output the following:

The comparison results lists all objects there are compared in a table with the following columns:

  • SchemaName – Name of the schema
  • ObjectName –  Name of the object
  • CompareObject – Type of object that is compared, such as table, procedure etc.
  • CompareAction – In what sense the object is different, is it changed, missing, etc.
  • SyncCommand – The suggested code to fully match the two databases in regards to this object.

In our example, comparing TestCompareDB2 to TestCompareDB1 shows that table1 is different between the two databases, and that the procedure is missing from TestCompareDB2. Running the code in SyncCommand will match TestCompareDB2 to TestCompareDB1. Let’s try to run it:

We can see that running the recommended script will fully match the two databases:

Comparison of differences between objects

You may want to be more cautious, and match only difference in objects (and not remove or add objects). In this case set CompareMode parameter to 2. Here is an example:

TestCompareDB1 has a table, testTable1 while TestCompareDB2 has a table testTable1 that is missing one column relatively to the equivalent table at TestCompareDB1, and a procedure, testProc1.
The comparison will show us the difference in tables only:

In order to match the two databases, such that the existing objects will be the same, it is required to add the missing column “id” to testTable1 on TestCompareDB2. Let’s run again the suggested script for matching:

alter table [dbo].[testTable1] add [id] [int];

The matching script has no reference to the procedure testProc1, since in this mode only changes are taken into account.

Comparison of objects in the databases

Compare mode 3 takes care of the missing objects and ignoring the differences that mode 2 is taking care of.

The following script creates again the two databases TestCompareDB1 and TestCompareDB2. It creates a table and a procedure on TestCompareDB1, and a table on TestCompareDB2 that is a bit different than the table on TestCompareDB1:

Running this script results in the following:

TestCompareDB2 is missing a procedure relatively to TestCompareDB1. Therefore, the suggested match adds the procedure to the database. It ignores the difference in the tables, since in this mode changes are not taken into account.

Running this code will match to two databases in the sense of objects:

create proc testProc2 @id int as select * from testTable1

Keeping your databases matching becomes a pretty simple task with PartitionDB. Take advantage of it!

Stay tuned to our next post – compare and match data.

Below is the list of parameters to pass to PdbcompareDatabase:

Parameter Description Mandatory
TargetDatabaseName Name of target database Yes
SourceDatabaseName Name of database to compare to  Yes
CompareMode Comparison mode:
1 – Full comparison
2 – Compare differences between objects
3 – Find missing or additional objects
No
TargetLinkedServerName Name of server on which the table resides No
SourceLinkedServerName Name of server on which the table to compare to resides at No
IncludeObjects  Which object types to include in the comparison No
ExcludeObjects  Which object types to exclude in the comparison No

To use compare and match and other PartitionDB utilities, download our PdbLogic and attach to your database.
To learn more about PartitionDB capabilities check out our blog.