A software package a day. Day 6: RedGate SQL Compare

If your team needs to deal with SQL Data (and I’m guessing you do) then you eventually will need to manage the database over time. For us, that means SQL Server and for the most part the tools provided get the job done. There are a couple of holes in the tools that are filled with third party tools and the biggest one is the ability to compare schema’s and data between instances of a database.

When making changes to a database in your development environment, you will typically create TSQL scripts to make the changes you need. When you roll those changes out to your staging or testing environment, how can you be sure that *every* detail was captured properly? Having been the victim of missing change scripts too many times, I find RedGate’s SQL Compare to be a “must have” for this process. Simply point it at the development environment and the target environment that is supposed to be now matching and it will report any differences between the two. Even better, it will create a change script for the missing changes (although I prefer to go back and ferret out the original missing change script where possible because this will *not* detect any data updates, just schema changes).

Many avoidable bugs come from schema maintenance issues, and with a tool like this you won’t have to suffer at least one class of deployment bug anymore. Of course you could also write a unit test against every table and execute those in your test or stage environment, but I find most programmers don’t write unit tests against the database access code itself.

There is a companion product, SQL Data Compare which can be used in a similar way, except in terms of the data contained within the schemas. On of my favorite uses of this is to create the scripts that will populate or update a database’s lookup tables which are not user defined. In one of our systems we have dozens of tables that are “system” controlled, and it is important that we deploy the changes to our system controlled tables to all of our live systems without disturbing the user data. With this tool we just compare the new base data and deploy the changes, making it another task we don’t have to create a custom solution for… avoiding all the testing and support that would go with it.

RedGate has many more tools available, but the two mentioned here by far and away are the ones that get the most use in the systems I’m involved in.