Introduction
A long
time ago (Oct 2001) I took Data Compare v1.41 from Red Gate for a test drive and
wrote up a review. Recently they've released an upgrade, so I decided to take a
look to see what had changed. For those not familiar with the product, it will
compare two databases (same server or different, SQL 7 and above) and show you
all the instances where the data doesn't match, then generate a script to sync
the two databases. Looking back over my original review, the biggest negative
item was lack of support for sync'ing text columns, something fixed in this
release.
When would you use this product? One situation I mentioned in my
earlier review was that of needing to sync a few tables on the development
server with data from the production server. We rarely want to restore the
entire production db over the development version, but often need the latest
data from a couple tables for testing or debugging. Using this is a nice
alternative to DTS. Another similar scenario is when you need to fix a bad data
change. When I get a call from our data team that they've had something go
wrong, I restore a copy of the database from the previous nights full backup,
then do the compare and undo the changes.
Environment
I tested on a local machine by making a copy of Northwind and making some minor
data changes.
Installation
Absolutely no problems. It uses the Windows Installer format (msi) and installs
quickly. Note that this program does require the .Net framework to be installed
on the machine.
Using The Application
Using this application doesn't
really require any training or help file reading - it's that easy to use. As I
noted above, I restored a backup of Northwind to the same server with a new
name, then changed a column of data elements, one of them being in a text
column. Because pictures are worth more than my 1000 words, here is a quick run
through of what it took to get the two databases back in sync using the product.
Step 1 - Select the databases to compare
Step 2
- Select the tables to compare. Notice that I've clicked the dropdown for the
comparison key. The app will let you use the primary key or any unique index.
Step 3 - Choose the type of compare. For this test, I left all four types
selected.
Step 4 - After clicking compare, you get this summary display. This matched the
changes I had made to NorthwindCopy. I clicked in the row for categories which
yield the detail display that shows there is 1 record different in both tables.
Step 5 - I double clicked the detail row which brought up a display so that I
could see the actual difference. In this case I had added " - and lots of other
stuff" to NorthwindCopy.
Step 6 - Decide which way to sync. For this test, I wanted to get NorthwindCopy
looking like Northwind again.
Step 7 - It generates the change script. It's hard to read here, so I've
provided the entire script just below this image. You'll see it disables all the
foreign key constraints, makes the changes using two update statements, then
reenables the foreign keys. It's all wrapped in a transaction so there is no
danger of your foreign keys somehow remaining disabled if a change failed. I
went back and did a second compare only looking for changed records, the code
generated for that did not change the foreign key constraints, which makes sense
since an update should not break anything (where an out of order insert might).
/*Run this script on (local).NorthwindCopyThis script will make changes to (local).NorthwindCopy
to make it the same as (local).NorthwindNote that this script will carry out all
DELETE commands for all tables first, then all the UPDATES and then all the
INSERTSIt will disable foreign key constraints at the beginning of the script,
and re-enable them at the end*/
SET XACT_ABORT ON
SET ARITHABORT ON
GO
-- Pointer used for text / image updates. This might not be needed, but is
declared here just in case
DECLARE @ptrval binary(16)
BEGIN TRANSACTION
ALTER TABLE [dbo].[CustomerCustomerDemo] NOCHECK CONSTRAINT [FK_CustomerCustomerDemo]
ALTER TABLE [dbo].[CustomerCustomerDemo] NOCHECK CONSTRAINT [FK_CustomerCustomerDemo_Customers]
ALTER TABLE [dbo].[Employees] NOCHECK CONSTRAINT [FK_Employees_Employees]
ALTER TABLE [dbo].[EmployeeTerritories] NOCHECK CONSTRAINT [FK_EmployeeTerritories_Employees]
ALTER TABLE [dbo].[EmployeeTerritories] NOCHECK CONSTRAINT [FK_EmployeeTerritories_Territories]
ALTER TABLE [dbo].[Orders] NOCHECK CONSTRAINT [FK_Orders_Customers]
ALTER TABLE [dbo].[Orders] NOCHECK CONSTRAINT [FK_Orders_Employees]
ALTER TABLE [dbo].[Orders] NOCHECK CONSTRAINT [FK_Orders_Shippers]
ALTER TABLE [dbo].[Products] NOCHECK CONSTRAINT [FK_Products_Categories]
ALTER TABLE [dbo].[Products] NOCHECK CONSTRAINT [FK_Products_Suppliers]
ALTER TABLE [dbo].[Order Details] NOCHECK CONSTRAINT [FK_Order_Details_Orders]
ALTER TABLE [dbo].[Order Details] NOCHECK CONSTRAINT [FK_Order_Details_Products]
ALTER TABLE [dbo].[Territories] NOCHECK CONSTRAINT [FK_Territories_Region]
UPDATE [dbo].[Categories] SET [Description]=N'Soft drinks, coffees, teas, beers,
and ales' WHERE [CategoryID]=1
UPDATE [dbo].[Customers] SET [CompanyName]=N'White Clover Markets' WHERE
[CustomerID]=N'WHITC'
ALTER TABLE [dbo].[CustomerCustomerDemo] CHECK CONSTRAINT [FK_CustomerCustomerDemo]
ALTER TABLE [dbo].[CustomerCustomerDemo] CHECK CONSTRAINT [FK_CustomerCustomerDemo_Customers]
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Employees]
ALTER TABLE [dbo].[EmployeeTerritories] CHECK CONSTRAINT [FK_EmployeeTerritories_Employees]
ALTER TABLE [dbo].[EmployeeTerritories] CHECK CONSTRAINT [FK_EmployeeTerritories_Territories]
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Customers]
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Employees]
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Shippers]
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Categories]
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Suppliers]
ALTER TABLE [dbo].[Order Details] CHECK CONSTRAINT [FK_Order_Details_Orders]
ALTER TABLE [dbo].[Order Details] CHECK CONSTRAINT [FK_Order_Details_Products]
ALTER TABLE [dbo].[Territories] CHECK CONSTRAINT [FK_Territories_Region]
COMMIT TRANSACTION
Step 8 - Pick what you want to do. I opted for lauching query analyzer so I
could view the script, but it is nice to have options.
Technical Support
None needed for testing. The online help gives
instructions for creating a test environment and also has a short list of
frequently asked questions. I haven't needed support on any of their products so
far, but I've seen forum postings from several users who did contact them for
support and were pleased with the results. A good sign! I also see that they are
using the
Installshield Update service, allowing you to easily check for upgrades by
clicking Help, Check for Updates.
Conclusions
Good solid offering.
Ratings
| Ease of Use |
5 |
I don't see how you could make it any easier. |
| Feature Set |
4 |
I'd like to see the option to save a compare session and reapply it
via a job, maybe a command line switch that pointed to a config file.
This would actually run a new compare/sync each time. This would let me
automate the task, essentially creating a lightweight replication tool.
Note that you always write your own using their SDK (separate purchase). |
| Lack of Bugs |
5 |
None noted during testing. |
| Value |
5 |
I consider the pricing fair. I don't see that I could write anything
similar for anywhere near the cost and if you're doing these types of
compares often, the savings in time easily justifies the cost. |
| Technical Support |
0 |
Not evaluated |
| Documentation |
5 |
Help file has a tutorial and FAQ, product is simple enough to use
that extensive help not required. |
| Performance |
0 |
Not evaluated. Ran very quickly in my test, but a very small data
set. Obviously the more data you have to compare and the more changes
there are, the longer it will take. |
| Installation |
5 |
No issues. |
| Learning Curve |
5 |
You're up and running in 5 minutes. |
| Overall |
5 |
I liked the previous version and upgrading to .Net plus fixing the
text column issue just make it better. |
Product Information
http://www.red-gate.com/SQL_Data_Compare.htm
Pricing: $195 single user license, $350 for the bundle that includes SQL Compare
and COM Compare
