Have you ever given a task to do validate two databases on different servers or on the same server?
Usually this will be done in post migration to make sure the existing production database is migrated with no errors. Also to verify the replicated tables and data
Here I’m not going to talk about migration any way but we are talking about how to validate two databases. Immediately the question that should rise in our mind is
What we need to validate or compare in a database with another database?
In order to validate and synchronize one database to another database, you will need a legacy database in one server which acts as primary and the one which you want to synchronize with legacy I will refer it as secondary on different or on the same server.
In order to do this task, we need to do two different comparisons as below
Schema compare is nothing but finding out for any structural changes or we can also called it as difference in definitions (DDL) of Objects in a database like Schema, tables, Views, stored procedures, users and roles.
Data compare is validating each and every row in each table in a database to another database.
This task is inserting new rows into tables on the secondary database those rows exist in primary and do not exist on secondary.
This task can be done in multiple methods; I prefer the best and easiest way to do it is using tools like
- Red Gate Tools
- Visual Studio 2010
I will explain briefly how to use these tools below;
1. Red Gate Tools -Comparing database Schema and data
Red Gate has many tools that makes DBA’s job more easy and easy but we use only two tools out of them in order to complete our task.
- SQL Compare 9.0
- SQL Data Compare 9.0
The really good thing about these Red Gate tools is you can not only compare the databases but also the backup to back up file directly and also snapshot to snapshot comparison is also available.
You can get the trial version of these tools from here
a) SQL Compare 9.0 – Schema Compare Tool
Always do `the schema compare first before doing the data compare because this compares the objects and tells you if any objects are missing.
Step 1: Establish connection to your legacy and target server, use windows authentication or SQL server authentication depends on how you login to the servers. As shown below
Step 2: Red Gate also provides the choice of customizing comparison with the options tab as you see in the above figure. Once you click on the compare now then it compares the two databases objects and displays the results as shown in below screenshot.
Step 3: When you click on ok on the above screen, you will see a screen like below with differences in objects and also the objects with no differences.
From the above screen you see the objects that have difference in their definition and also the identical objects. If you get the connections to the databases were wrong then click on edit project as highlighted in the above screenshot.
In order make these changes effect on the target server click on synchronization wizard that applies all the changes to the target server from legacy server database
Click on specific object to see the code or definition change in both databases and also it is highlighted.
Step 4: Once synchronization is complete you can click on refresh to re-compare and see the changes are applied or not.
b) SQL Data Compare 9
You can do the data compare in a similar way as you did the schema compare.
Step 1: Open SQL Data Compare and make connection to your legacy and target.
Step 2: Select specific databases to synchronize data in it and then click on compare now. You will see the below screen showing the differences in the rows like differences in both , rows exits only on target and rows exits only in legacy, if already synchronized it displays all rows are Identical.
Step 3: In order to synchronize these rows click on synchronization wizard to apply changes from legacy to target then it generates a script to run on the target. You have two choices to apply these changes by running that script manually or you select the tool to do it.
Step 4: Click finish and refresh to re-compare and verify all rows are identical in each table in a database compared to legacy.
2) Visual Studio 2010 – Comparing database Schema and data
Similar to Red Gate you can accomplish this task even using Visual Studio 2010 but Red Gate provides additional options like comparing backup to back up, script folder to script folder.
If you want to just synchronize databases either of them can do it. Now let’s see how we do this task in Visual studio 2010.
Step 1: Open new project and select Schema compare or data compare in the drop down menu under data tab as shown in the fig below
Step 2: When you select the Schema compare or data compare, you will see the below screen
Step 3: select the source database which is your legacy server and database and target would be your secondary server where changes needs to be made.
Step 4: Check twice to make sure you have connected to the right databases otherwise it applies changes to your legacy server which it shouldn’t be.
Then finally click next and follow the instructions and click finish to see if there are any differences in data in each table in a database compared to your legacy database.
Thanks for reading this post. Please leave your comments.