Search This Blog

March 19, 2013

SQL Server : Database Schema Comparison Tool for Different Environments

My colleague asked me: how do you validate the discrepancy of database Schemas in different environments (DEV, TEST, STAGE, and PROD) in SQL Server?

The reason why she asked me is that from our previous experience of pushing the code from DEV/TEST to STAGE, the features in our Silverlight Application will not work well.

Why are the features broken often?

·        The code is based on the Entity Framework. If table structure changes, after pushing code to STAGE, but forget to update the table structure (add/delete columns), the features will not work well.

·        Silverlight Application is using ArcGIS Rest Services. If the changes are in GIS level, but not applied in STAGE, the feature will not work well.

·         DB change will cause the missing primary keys in some tables often.

I talked to her: If you have Visual Studio Ultimate installed, it is easy to compare the discrepancy in different environments such as missing columns in some tables, missing Stored Procedures and Views.

It is easy for her to see the discrepancy of database Schemas in different environments. I don’t have to bother her so much in the future.

No comments: