SQL SERVER – How to Detect Schema Change Across Two Servers

If you are database developer or database administrator, you will absolutely feel the story. You can download the software discussed in this blog post to play it along.

Story of My Early Career

A very interesting scenario, I have encountered in my earlier career. In earlier days, we used to develop everything on my local box. Our application and database both stayed in the same box. I was allowed to code only on the module or section which was assigned to me. I can’t go out and code or do anything else besides what I was assigned. When we code this modular way, it was easy to deploy the code upon completion of the project. This all worked fine till I went for vacation.

Once I went on vacation and the development of my project was done by my friend and colleague Romesh. Upon returning, Romesh wanted to hand over the entire project to me and he wanted to continue on his own project. The problems started here as his project and my project were totally different. Our database has been the same, but the table and stored procedure which we worked were mostly very different. In this situation the best practice for me was to take the schema changes he has done on the machine to my machine and continue the project. I had to be very careful as I should not take any other schema from his current project. If I have a part of his project, when I deploy my project that will go live and it should not happen.

The Epic Dilemma

Here was the problem – we were sitting together and discussing how to synchronize the schema from his machine to my machine selectively. I did not want the schema of the project he was earlier working on as that will further complicate my deployment, but I do want all the object which belonged to my projects. How do we achieve this complicated goal of selective schema difference detection and synchronization?

Selective Schema Compare Detection and Synchronization

After some research online, we landed on Embarcadero’s DB Change Manager. We downloaded the trial version and tried to do a selective schema comparison. It is pretty easy to use tool which walks us through the wizard and instantly we were able to detect schema change between my database server and Romesh’s database server. We used a couple of filters to indicate which are my tables and which are his tables and quickly able to only see the selective changes.

Once we detected the changes between schema, we quickly generated the script to deploy on my database server with the help of Manual Synchronization features. I copied the script and I was instantly able to move on with the my project. I really feel that sometime tools like Embarcadero’s DB Change Manager makes quite a positive impact on efficiency of work life.

Here is the quick video which demonstrates how quickly one can use Embarcadero’s DB Change Manager compares schema.

http://www.youtube.com/watch?v=1d_dpSAkUfU

Let me know what you think of this video. You can download Embarcadero’s DB Change Manager.

Reference: Pinal Dave (https://blog.sqlauthority.com)