Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Review of Data Compare v.151

By Andy Warren,

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.

Total article views: 8750 | Views in the last 30 days: 4
 
Related Articles
FORUM

Compare records between table

Compare records between table

FORUM

Compare records between table

Compare records between table

FORUM

COMPARE QUERY

WOULD LIKE TO COMPARE NEXT RECORD TO PREVIOUS RECORD

FORUM

Compare records in same table

Compare records in same table

ARTICLE

Compare Dates in an iterative manner for a particular set of records

Compare Dates in an iterative manner for a particular set of records in a single query using SQL Se...

Tags
miscellaneous    
replication    
scripts    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones