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.
If you wish to compare data and not schema, please refer to our post Data comparison with PartitionDB.
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:
|TargetDatabaseName||Name of target database||Yes|
|SourceDatabaseName||Name of database to compare to||Yes|
1 – Full comparison
2 – Compare differences between objects
3 – Find missing or additional objects
|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|