I can't get a proper Data Compare

omrsafetyo · May 7, 2010 5:54PM

I am using the latest version of SQL Data Compare - 8.1.0.4 on a trial basis and I am evaluating this and two other products prior to making a purchasing decision on the SQL Comparison Bundle.

My intent is to use Data Compare to keep non-production databases in sync with production databases. However, during my evaluation, I am running into numerous errors and have not been able to successfully synchronize a database yet (or even generate a script).

I am running SQL Data Compare from a Win2k8 64 bit server with 32GB of memory. I have (at this point after reviewing the forums) set up the RGTEMP system variable and set it to a directory on the E: drive which has plenty of free space. Running a sync direct from one database to another (located on different database servers), I seem to always receive a "System.OutOfMemoryException" during the "Comparing Databases" step. This occurs in random places during the compare - and I am running against a very small database for my testing.

I have also tried running a sync against a SQL Server Native compressed backup (.bak) file without any luck. Another product that I am evaluating does not have support for compressed bak files - which would be a deciding factor in my purchasing decision, except that I can't get a sync to work properly at all with SQL Data Compare. The error I receive in this is "Object reference not set to an instance of an object". This seems to fail in a more consistent place - here is the schema for the table:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE &#91;dbo&#93;.&#91;mytable&#93;&#40;
	&#91;col1&#93; &#91;char&#93;&#40;8&#41; NULL,
	&#91;col2&#93; &#91;char&#93;&#40;6&#41; NULL,
	&#91;col3&#93; &#91;char&#93;&#40;5&#41; NULL
&#41; ON &#91;PRIMARY&#93;

GO

SET ANSI_PADDING OFF
GO

This table has no records in it.
SQL Compare runs through the backup vs. database comparison just fine and finds no differences on this table, same goes for DB vs. DB compare with SQL Compare.

Are there any log files generated that might point me in the right direction?
Any ideas??

peter.peart · May 10, 2010 3:30PM

Hi there,

I have tried this comparing a RG Backup file against a slightly different table, and a native compressed backup file too and both work.

My table is slightly different in that I had to create an ID column and assign it a primay key so I could compare against the backup file, as comparison against a backup requires that a primary key be set on the DB as you cannot assign a custom comparison key when comparing against backups:

/****** Object:  Table &#91;dbo&#93;.&#91;mytable&#93;    Script Date: 05/10/2010 20:24:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE &#91;dbo&#93;.&#91;mytable&#93;&#40;
	&#91;col1&#93; &#91;char&#93;&#40;8&#41; NULL,
	&#91;col2&#93; &#91;char&#93;&#40;6&#41; NULL,
	&#91;col3&#93; &#91;char&#93;&#40;5&#41; NULL,
	&#91;col4&#93; &#91;int&#93; IDENTITY&#40;1,1&#41; NOT NULL,
 CONSTRAINT &#91;PK_Col4&#93; PRIMARY KEY NONCLUSTERED 
&#40;
	&#91;col4&#93; ASC
&#41;WITH &#40;PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON&#41; ON &#91;PRIMARY&#93;
&#41; ON &#91;PRIMARY&#93;

GO

SET ANSI_PADDING OFF
GO

Just out of curiosity, are you receiving this error when you are using an existing project, when you set up the data sources from scratch or both?

Many thanks!

Pete

omrsafetyo · May 10, 2010 5:07PM

Hi Pete,

Thanks for the reply. As stated, I am using SQL Server native compressed backups, not RGs proprietary backup.

I have only generated my data sources from scratch, creating a new project each time I have attempted. I have not ever had a successful synchronization, so I have not had a project worth saving. If I can get the software figured out, I will likely always run it from the command line; the syntax of which I just finally found after extensive unsuccessful testing through the GUI. It sounds like you are telling me that the object that is not instantiated is the comparison key - basically, I am getting to a table in the comparson that doesn't have a comparison key, so it is failing. Is there a way to ignore this error and continue, e.g. to perform a full table scan instead of comparing on keys, or ignore the table all together?

It sounds like my database structure ( I do not engineer this database and have no control over adding key constraints) is unsupported. Does this sound accurate?

The direct DB to DB fails at a random place each time - is it possible this is due to table locks being placed by the table being in use? What is the timeout value?

I was able to get a good comparison (DB to DB) just now using the compare checksum option. Can I make this default, or specify it from the command line? I notice this option is not available from backups.

omrsafetyo · May 10, 2010 6:02PM

Additional info - if I look at the comparison keys on the tables before running the scan, this table is mapped on the one index on the table.

David A · May 11, 2010 6:32PM

I am also on an evaluation copy (just changed jobs, this is the 4th company I'm introducing to Red-Gate

).

My laptop is a 32 bit XP, the Source SQL Server is a 32 bit SQL 2005, the Destination is a 64 bit SQL 2008. The tool runs on my laptop.

SQL Data Compare errors out every time with "FieldMapping not valid for MatchingMappings." (Note the spacing.) There is no indication of which table is causing the issue. [This should be considered a design flaw; the generic message gives no assistance for potential work-arounds.] I have gone as far as selecting only one table that has a primary key, same failure. I've tried this in several databases.

It also appears the SQL Packager has issues. The Destination db's were created using SQL Packager, but the SQL Server's canned "Disk usage by Table" shows different record counts. (Hence the reason I attempted to use Data Compare.)

I saw the cumulative patch, but installing it had no effect.

Bottom line, this is making the tools look bad while I'm trying to convince the company to purchase them. With 350+ db's to migrate to a new hosting facility a thousand miles away, I would like the tools to perform in the typical stellar Red-Gate fashion. All help is appreciated.

David A · May 12, 2010 9:56AM

I read posts in older versions where creating a new project solves the problem. A preliminary test proves this to be true, though I've only done limited testing (only a few tables, all with primary keys). If you don't hear more details from me later, then that is the fix.

peter.peart · May 13, 2010 2:52PM

Hi David - I am glad that the re-creation of the project is working for you.

Hi Omrsafetyo - SQL Data Compare shoudn't bomb out if the table doesn't have a comparison key; it should just miss that object out from the comparison.

If you want to ignore the table in question, you can just uncheck it from the list of tables to be compared under the tables and views tab. This should also show you where comparison keys have been set. Unfortunately, the tool needs a comparison key to be set on something to enable it to perform a compare. With regards to locks on the DB, this shouldn't matter at all really. On the checksum front, this can be run from the command line providing the option is specified. That is:

/options:ucc

A full list of command line switches can be generated if you run the following:

sqldatacompare /? /v /html &gt;"%FilePath%.htm"

When you say very small database, what sort of size are we talking about? Also, is there any possibility of getting either (ideally) backups of these DB's or copies of the schema that we can populate with test data?

Many thanks!

Pete

I can't get a proper Data Compare

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk