As the days seem to get shorter, leveraging the skills and time of others seems to be the only option to keep from drowning from too many things on our to-do lists. While we all enjoy building tools, taking your own time to build utilities doesn’t always make the most business sense. The challenge is finding the really good tools that actually save you time!
Recently I had the chance to try out the utility bundle SQL Tools from Red Gate Software. SQL Tools consists of SQL Compare, SQL Data Compare and DTS Compare.
SQL Compare allows you to compare the schema of two databases. SQL Data Compare allows you to compare the data that is contained within the structure of the tables that SQL Compare made sure was the same. DTS Compare helps you make sure that your DTS packages and jobs are the same between two servers. First of all, let me be up front in the fact that I did not need to try out the DTS compare at this time. Most of my review will focus on the SQL Compare tool and the SQL Data Compare tools. If you are a heavy user of DTS, I can’t imagine it not being worth your time to download their evaluation and kick the tires on your own.
This isn’t the first time I took a quick peek at Red Gates tools, only this time I had a little more time (translated – it was required for me to meet my project deadline that I got help from someone else’s tools!) The first time I looked at the toolset it was a quick look and my conclusion was that next time it was useful I should just purchase the SQL Compare tool. It really did a good job of helping me synchronize my development database with a pre-production copy to make sure that my deployment to production was smoother. That time I had looked at the data compare but the code wizard in me said “How hard could it be to write something like that myself…”
As my most recent deadline loomed near I had the chance to once again try out the tools – Who says that the 3rd time is the charm. I started with a quick run though of the SQL Compare tool. This tool quickly goes through all the tables in your database – highlights the ones that are different, missing or added to your target database. Then with a simple click of the button you can generate a script to update the target database to match your source database.
The attention to detail is great – they allow you to switch between views of differences, additions and deletions, and by a simple double click it will highlight for you the changes that were made between the two databases.
Yes we have the issue tracker and the list of changes that were made – but it never fails that as you head towards production and its 3am and you must be live in 2 hours – it’s simply not smart to delay your implementation because you didn’t know what table / database object changes you had made during your development cycle. SQL Compare provides deployment insurance to tell you what is different and make fixing it a couple clicks away with your mouse.
Interestingly enough, SQL Compare was not the star of the show. The application we were deploying was heavily data table driven and we had to manage the code / lookup tables as they migrated from our development database to the production environment. Having one person change the source tables was not an option if we wanted to meet our deadline. As we entered the final week of the preparation for deployment we were doing daily migrations to our pre-production environment and SQL Data Compare of our application lookup tables was part of the daily ritual.
SQL Data Compare allows you to simply select the tables you wanted to compare and then perform the compare. Each table is then detailed out with row counts for how many rows are the same, different and missing. For each type of change you can view the detail of what is causing the discrepancy between the two databases. With a simple check of the box and a hit of the synchronization button the utility will generate a insert/update, delete script that can optionally be applied to the target database with a click of the mouse.
I honestly did not appreciate this tool the first time I evaluated it compared to my recent experience– in simple terms what took 4 hours with the tool would have taken over 20 hours without the tool. I will spare you the finance lesson on cost analysis for a consulting company. With the cost of the tools starting at $195 it’s not hard for even the non financial type readers to figure out that purchasing this is a no brainer! Those saved hours were spent making sure the application was ready for production and not wasted on trying to figure out what data was changed. There is nothing more frustrating in a data driven application than researching a problem to realize that it was caused by a problem in the data lookup table not being updated.
SQL Tools is an important part of any serious software shops tool chest. If you use more than a single environment (e.g. Development, QA, and Production) in your applications you owe it to yourself to purchase deployment insurance and get a copy of SQL Tools.
Unlike some tools; SQL Tools provide the return on investment that makes them an easy choice as you spend your tool budget for the year. If your manager is not a fan of spending money on tools, sit them down with a calculator and a few rough numbers and you will surely win over your manager in no time.
Red-Gate Software
http://www.red-gate.com/
