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

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