Note: This post was requested by the company that makes SQL Examiner Suite in exchange for consideration. This has not affected my review and the company was not allowed to change my wording in any way. After reviewing this product, I have begun using it in production. That alone should tell you what I think of it. I just want to be very clear that I was given this product to try in exchange for this review.
Every good developer looks to automate as many processes as possible. If you can automate a task, it is one less task you need to do. An added benefit of automation is that typically it results in predictable results. So, when Intelligent Database Solutions came to me and asked me to review their SQL Examiner Suite, I was excited about the possibilities. Here was a tool that is designed to automate one of my messier tasks: database synchronization. Keeping my local database, development database, and staging database in sync with the production database while doing development can be a tricky business. Could this be the tool I was looking for?
Let me set the stage by explaining my environment a bit. I work for a small company whose entire product line is web-based. Each of our developers is also expected to be a database developer as well. Each developer has a local copy of our production database (the schema – the data has been changed for security reasons). Then, we have a shared development database and a shared staging database. These two databases support our development and staging environments respectively. When a developer wants to make a change to the database, they first change their local database and do testing. If things work out, they move their code and their SQL change scripts to the development servers. This process gets repeated again to move to staging, and finally again when we move to production. Simple, right?
Not so much. As developers, we rarely work on just one issue at a time. Usually, at any given time, we have three or more projects, bug fixes, and/or minor tweaks that we are working on. In code, this can easily be separated using version control, but with databases, it isn’t quite so clean cut. Sure, you could have your change scripts in source control but that gets really messy when you involve data as well. That’s why typically our databases are a jumbled mess of changes. Want to push just one bug fix up to the development environment? No problem, you just need to keep track of which database changes were associated with that fix. This problem happens again when you go to push changes to staging. Which database changes get pushed with the code? Do you even know what changes were made to the database? This is the minor chaos that I wanted the SQL Examiner Suite to resolve.
The SQL Examiner Suite (which I’ll refer to as SES) is actually two distinct applications: SQL Examiner (SE) and SQL Data Examiner (SDE). In short, SE’s job is to synchronize the schema of two databases and the SDE’s job is to synchronize the data of two databases. When you run either tool, you will be asked to identify which database you want to use as the model (the source) and which database you wanted to synchronize to become like the model (the target). The system will then run a comparison on the two databases and tell you what the differences are. You can then manually synchronize the two databases, you can schedule the synchronization, or you can script out the synchronization and run it on your own. Even better, you get the option of selecting only certain items to synchronize.
Remember how I said moving database changes to development could be tricky when you had multiple changes that didn’t all need to be pushed? Not anymore. Now I just run SE and it tells me everything that is different. I then pick and choose which changes need to be pushed by just checking the boxes next to the changed items names. Then I synchronize the databases. Done. No guesswork, no forgetting a critical script, and no mistakes.
Even better, if I make a mistake in my local database, I just reverse the process. I synchronize back the versions from development of the objects that I messed up in my local copy. Within seconds, my local copy is back up and running.
The SDE works in nearly the same manner for me. I have the SDE watch certain tables (my tables with static data like city names, state names, or other list-type data that rarely changes). If a change has been made, I can synchronize it up the chain.
There is a lot of positives to say about the SES. Here is just a brief list of some of the benefits that I found most useful:
- It is easy to use.
- It generates the SQL script for any change it is going to do, so I can store that or even run it manually.
- It generates and uses good SQL.
- Synchronization can be scheduled.
- The tool defaults to backing up your database before making any changes.
There isn’t a ton of bad things to say about the SES, and there definitely isn’t any show-stoppers, but there are a few things that I wish were different. They include:
- The two tools are almost identical but they are separate. I wish they were combined into one tool that could synchronize both schemas and data.
- While the tools are easy to use, initially they can be a bit daunting because they don’t have an opening wizard or other device to orient you to how the tool works.
- The documentation for these tools is sorely lacking.
- Synchronization goes one way. If you want to go the other direction, you need to create a new comparison going the opposite direction.
- There isn’t a quick sync option that would allow you to sync one object right away.
The SQL Examiner Suite is a good tool struggling for greatness. It has already become a tool that I use on a daily basis to get my job done more efficiently. I look forward to the improvements to the tool that will make it simply irresistible. In the meantime, I would encourage you to check it out for yourself. You can get a free trial as http://www.sqlaccessories.com/sql-examiner-suite/.