SQLServerCentral Article

Review of Data Compare v.151

,

Back in June I took at look at SQL

Compare by Red-Gate Software. SQL Compare is a good product, reasonably

priced. Red-Gate offers two other 'similar' products, Data Compare and Com

Compare - of the two Data Compare seemed to be of more immediate interest to our

readers, so I decided to review that one.

I reviewed a full copy of SQL

Data Compare v1.51. To sum it up quickly, this product lets you compare the

data in two tables and synchronize the differences. Think replication, but

without the complexities or all the features. 

When would you use this? I can see it being pretty handy for our developers.

They often keep a copy of a production database on a development server, but of

course as time goes by it gets further and further out of date. If they want a

refresh, they usually DTS the data over - which means unless you want to spend a

lot of time on the package, the easiest way is to truncate your table, then just

bring over ALL the records rather than only the changed ones. As you'll see

shortly, Data Compare offers an alternative that is well within the skill level

of even a junior developer and offers a practical alternative to DTS.

If you've used SQL Compare, the interface is similar. Here I've registered

two databases, Northwind and a test database called Na2thwnd.

To do a very simple demo, I created a copy of the categories table in

Northwind and added one test record to it. I duplicated the structure in

Na2thwnd. This lead to the first interesting discovery - you can only compare

tables if they have a primarykey or a unique index - makes sense! I added the

primary key to both tables. The next step is to select the tables you want to

compare. You have the option to exclude columns at this point, you can see both

dialogs here:

I ran the compare and received this as a result:

A good sign, this is showing that there no identical records and one record

that exists in one table but not the other. Double click to get some details:

I've selected the missing records tab. It is correctly showing the one test

record I entered in Northwind. I'm going to click on the blue SQL tab next to

see what the application is going to generate. Rather than a screen shot, I've

copied the code out (which is what you do to run it anyway) to make sure you

could see everything.

-- Run this on EG.na2thwnd to synchronize the contents of the [dbo].[CategoriesCopy] table

-- Please make sure you BACK UP THE DATABASE before running this script

BEGIN TRANSACTION

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[prcTmp_SDC_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[prcTmp_SDC_Insert]

GO

CREATE PROCEDURE prcTmp_SDC_Insert(@P1 AS int, @P2 AS nvarchar (15), @P3 AS ntext, @P4 AS image, @P5 AS uniqueidentifier, @P6 AS varchar (20)) AS

INSERT INTO [dbo].[CategoriesCopy] ([CategoryID], [CategoryName], [Description], [Picture], [rowguid], [TestCol]) VALUES (@P1, @P2, @P3, @P4, @P5, @P6)

GO

SET IDENTITY_INSERT [dbo].[CategoriesCopy] ON

EXEC prcTmp_SDC_Insert 1, 'Test', NULL, NULL, NULL, NULL

SET IDENTITY_INSERT [dbo].[CategoriesCopy] OFF

DROP PROCEDURE prcTmp_SDC_Insert

COMMIT

Not bad. It's building a stored procedure to do the inserts. Although in this

case it's only one record, it would certainly help performance if you were

moving hundreds or thousands of records. It's not going to be as fast as BCP,

but it's a lot easier to set up! A key point is that you don't/can't synchronize

from within the application, you have to copy/paste the SQL into Query Analyzer

to run.

It worked pretty well on a simple missing record, so I added the record to

Na2thwnd and ran again, it correctly identified that the tables were in sync.

For a final test, I modified the record in Na2thwnd and ran a final comparison,

which resulted in the following display:

In this case I think I'll synchronize Northwind to have the same data as

Na2thwind (the one in red), so I'll click on the red SQL tab to see what it

generated:

-- Run this on EG.Northwind to synchronize the contents of the [dbo].[CategoriesCopy] table

-- Please make sure you BACK UP THE DATABASE before running this script

BEGIN TRANSACTION

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[prcTmp_SDC_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[prcTmp_SDC_Update]

GO

CREATE PROCEDURE prcTmp_SDC_Update(@P1 AS int, @P2 AS nvarchar (15), @P3 AS ntext, @P4 AS image, @P5 AS uniqueidentifier, @P6 AS varchar (20)) AS

UPDATE [dbo].[CategoriesCopy] SET [CategoryName]=@P2, [Description]=@P3, [Picture]=@P4, [rowguid]=@P5, [TestCol]=@P6 WHERE [CategoryID]=@P1

GO

SET IDENTITY_INSERT [dbo].[CategoriesCopy] ON

EXEC prcTmp_SDC_Update 1, 'Test2', NULL, NULL, NULL, NULL

SET IDENTITY_INSERT [dbo].[CategoriesCopy] OFF

DROP PROCEDURE prcTmp_SDC_Update

COMMIT

Again you can see it's building and using a stored procedure to do the

update, so it should scale pretty well if you have a lot of rows to sync.

Data Compare will work across servers (via a linked server) and will even

compare the common columns from tables that have different structures (perfect

for the developer refresh scenario I suggested earlier). It doesn't work on

text, ntext, image, timestamp, or rowguid columns. It also doesn't address

referential integrity. The help file has a short to the point FAQ that covers

these points clearly. Of the two, I'd consider the inability to handle text the

greater liability. For now, if you have the requirement to synchronize one of

the data types not supported, you're back to DTS or BCP.

So what do I think of the product? In my testing it seemed solid and usable.

I can definitely see where it would be nice to have in the toolbox. It's

strength is hides complexity from the user - think about the SQL you'd need to

write to figure out which rows need to be added, then going back to figure out

which rows have been modified, then writing the code to do the inserts and

updates. It's doable, but it's busy work when you can find a tool that will do

it better AND faster. I do wish that you could apply the changes from within the

app (which means there is no way that I can see to create a 'fake' replication

job that I could run at night) and that it could at text columns - maybe if

there is enough demand we'll see it in a future revision. At time of this

review, prices range from $95 for a single license, $450 for a site license and

$190 a bundle (SQL Compare, Data Compare, and COM Compare) and $850 for a site

bundle license.

Red-Gate has a trial version available on their site good for 14 days.

Download it and try it out!

Note: Red-Gate recently recently a minor version upgrade. Based on

information I received, Version 1.60 supports scripting of multiple tables, will

enforce referential integrity, and allows you to save workspaces.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating