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.
