Review: xSQL Software's xSQL Object 2, xSQL Data Compare
by Stephen Wynkoop
Overview
If you've used SQL Server for any length of time, if you've built an application and then deployed it, or even just updated it, you've seen how much of a mess it can be to catch all of the little changes you invariably make along the way. From changing data types to updating schemas, indexes and the other many attributes of a SQL Server-based application or tool, there are a lot of things to keep track of.
Typically this can be controlled manually, perhaps, with logs of changes that have been made. Of course, if you're the only person working, you already have a leg-up on the process, but still, missing a single updated item can cause real issues. The real challenge I've personally run into is the case where you have updated the application, tested the systems in the test environment, have everything working as it seems it should. You deploy the application to production, things seem fine.
A little time passes.
You find out later - perhaps weeks or even longer - that a small change you made in a report or a view that supported your monthly reporting was missed. Now retrofitting means that your application doesn't "know" about the change either (otherwise it would have complained during testing). You're faced with an in-line update, probably with less testing than it really should have, and probably in a more haphazard way than you had wanted.
This is where xSQL's products come in. They address the need to compare and contrast all updates and changes and make sure you have the update scripts you need to synchronize the structures. The products are split between schema and object synchronization and the actual raw data comparison and synchronization. They are available as a bundle, providing both functions for your environment.
Installation/Activation
Installation was very simple, just running the installation routine with no real options or issues to consider during that process. The tools reside on your workstation and do not require any server-side components or scripts. Interestingly, you don't even need to provide the software key on the download. With xSQL's licensing (see "Conclusion") you don't need to enter this information as you start up your new software.
Usage
This is easily one of the most simple to use, clear, well-designed interfaces for comparing database schemas and information that I've used, period. Selecting your databases, selecting the comparison options, running the comparison... it all basically feels like it "should." Here's a look at an example comparison:
.png)
Interested in what's causing the flag on the issue? You can open the object that's highlighted and it'll show you what's different (left, right side) and what's equal. It'll also show you the difference below, in the schema script. As you move around the object tree above, the script follows, showing the differences and similarities. On the right, you have the script links to generate a script that will synchronize the database schemas in either direction.
There's a concept of "Left" and "Right" - and you can apply and synchronize changes in either direction. There are clear indicators about the status of each element and you can pick and choose which elements you are interested in and which to ignore. For example, you may want to ignore certain components in the comparison, perhaps because you know you'll have to rebuild full-text catalogs and indexes. Or, perhaps you are interested in just examining particular index structures you've updated. It's just a matter of selecting the objects to include in the comparison options dialog.
I really liked the fail-back protections built into the system. Transactions are placed around data modification steps, data is moved to temporary tables, then taken "live" instead of just hoping that things will convert correctly. Error messages in the case of issues in the process are provided. You will know that it was successful and that precautions were taken not only on the one data element you were updating, but also in getting your system completely updated - related indexes, options, constraints, etc. - that are sometimes left to chance in manual updates.
My one usage suggestion would be to add a title bar to the displays below - the comparisons in script - that shows the database in the respective panes. It's not a big thing because it is indicated just below the script panes, but I found myself double-checking to make sure I knew which was "left" and which was "right" in the comparison.
Each step along the way you can get really meaningful information about the differences. In the script panes, "make left as right" and the opposite show the scripts that will be created to synchronize. The ever-present "Synchronization Script" window let's you click and generate a full script, with fail-back and full transaction protection for the update to the schema. The comparison shows the items, and offers drill-down into the keys, indexes, constraints and triggers that were also considered in the comparison.
In short, the information you need is there and is sometimes even presented a couple of different ways so you have what you need.
Conclusion
It's frankly hard to write reviews that are for products that just flat out work right. You tend to keep looking for issues, ideas, things to change. I really liked the xSQL comparison and synchronization tools. They are solid; it seems like whenever I was looking to do the "next step," there it was. I didn't have to go hunting for what needed to be done. The comparisons are fast, the interface makes sense. Then, there's the licensing.
I have to say, the licensing is really nice. They have a two-week trial during which you can use the product fully on your system. Test it out, use it, see how it works - pretty standard trial stuff. But after that time period, their licensing actually is quite different from many. If you'll be using the tools against SQL Server Express, you get to continue using them, free of charge. Their product for use against Express is free forever. Even if you're using it against a SQL Server database with a small footprint, the product is free. Here's an outline of their license:
"- up to 25 tables
- up to 40 views
- up to 40 stored procedures
- up to 40 functions
Of course they do have licensed versions beyond these and those will depend on the number of users, upgrades and such.
Ratings
Here are the ratings and brief commentary for the product.
Overall Rating: 
(5 stars)
I think this is a seriously helpful product that you should strongly consider.
Installation (5 stars)
Usage (5 stars)
Good help file, even better prompting as you go along, helping to understand the options and what you can do with the product.
Support n/a
We did not have the need to engage the support team for the product.
Real-World Usefulness (5 stars)
Using SQL Server for any length of time? You'll quickly come to appreciate this product as you migrate between servers, upgrade existing systems, document things that have changed.
On the web: http://www.xsqlsoftware.com
Trial available? Yes (free version also available, see above)
About ratings:
5-outstanding, solid coverage of that product area
4-solid coverage of that aspect of the product
3-stars passing support, but nothing special
2-stars minimal functionality or support of that aspect of the product
1-star complete failure of that aspect of the product
Please note: While some products, services or other items reviewed on SSWUG may be those of sponsors, this in no-way impacts our rating or review process.
If any of the above 4 limits is exceeded then you will not be able to use the lite edition to compare and synchronize the given database.
Note: The above limitations do not apply to SQL Server Express edition. As of June 18, 2007 xSQL Software's comparison and synchronization tools are FREE with no restrictions for SQL Server 2005 Express edition. "
