Continuous Deployments for SQL Server – Part 3

In part 2, I covered how to compare the schemas of two databases, in part 3 I’ll to cover how to compare the data held within these two databases, for that I am going to use SQL Data Compare.

Again we will use the same two databases, imagine your comparing UAT to Production and wish to compare the data within these two databases:-

(If the links are too small click on the image and then when the new window appears click on Original size at 1896 × 316)

The above image shows us that the t_depot database table has 4 rows which are in the Stock_DB_Deisgn table which aren’t within the STOCK_DB_DESIGN_ORIGINAL database. Again same as with SQL Compare, if we now click on Synchronization wizard we will see the following screen:-

And again as with SQL Compare, we get 2 options:-

• Create a deployment script – use this option if you want to script the changes and review before running in the changes.
• Synchronize Using SQL Data Compare – use this option if you want the tool to make the changes for you, you also get the option after its ran to automatically compare the 2 database data after the changes, to verify.

And that is it, easy, simple and straightforward, no manual steps involving creating scripts, no real chance for it to go wrong, plus once again you can source control the script if you really wanted to or share it with colleagues.

In part 4 I’ll cover how to deploy changes to your database whether its schema changes, data changes or both.