Compare taking a lot longer on SQL 2008R2 then 2005

hbuckner · May 18, 2011 8:25AM

We are in the process of moving our databases from SQL 2005 to SQL 2008R2.

One of our nightly job uses the data compare to compare the database to the night before backup to write changes to another database.

In the GUI, to compare the SQL 2005 to the backup file it takes 5min. When we moved the databases to SQL 2008R2 it is now taking 20 min.

Has anyone there seen this hugh difference in compare time. Any suggestions on how to reduce the time?

I have seen the same results in Data Compare 7 and 9

Thanks

James B · May 18, 2011 9:08AM

Thanks for your post.

I'm not immediately aware of anything that would cause this kind of difference.

Is everything else identical? So for instance:

Is the 2008 server on the same network as the 2005 one?
Are you running Data Compare on one of those servers, or a separate PC?
Is the database the same (i.e. same number of records + objects)
Are you using identical settings within SQL Data Compare for each server?
Is the 2008 server hosting more databases and thus under more load than the 2005 one?

hbuckner · May 18, 2011 10:07AM

Is everything else identical? So for instance:

Is the 2008 server on the same network as the 2005 one?

Yes

Are you running Data Compare on one of those servers, or a separate PC?

We run from another dedicated server

Is the database the same (i.e. same number of records + objects)

Yes

Are you using identical settings within SQL Data Compare for each server?
As far as I know. We use a Data Compare Project. I just changed the connections from SQL 2005 to SQL 2008

Is the 2008 server hosting more databases and thus under more load than the 2005 one?

No, Actully less.

James B · May 18, 2011 10:51AM

Thanks for the reply.

Can you just confirm if it's the comparison stage that's slow? Or the Synchronization? Or both?

Have you also tried the command-line version of Data Compare to see if this exhibits the same behaviour?

hbuckner · May 18, 2011 11:10AM

It seems it is just comparison stage that's slow.

We have used the Command - line version with Data Compare 7 and it is the same. We have not done it with Data Campare 9 yet but we will be trying it soon.

James B · May 18, 2011 11:12AM

OK. I'm not really sure what would account for the differences then. Is it possible to obtain a backup of the 2005 and the 2008 DBs? I can test them here and see if I observe the same discrepancy. If I don't, then it will be something specific to your environment causing it.

hbuckner · May 18, 2011 3:26PM

It should be fine. Each database zipped is 350 MB I have both DB's and the Datacompare project file that we use. You can create the backup to compare to correct? I compare the DB to the backup.

Do you have a way to get the file to you?

James B · May 18, 2011 3:28PM

I'll mail you an FTP link shortly thanks.

James B · May 20, 2011 4:11AM

Just to update this thread in case other users are noticing the same thing...

This problem only seems to occur when comparing to a backup taken from a 2008 (R2) database. Comparing a 2008 R2 DB to a 2005 backup is fine, so it would seem to be something in the 2008 Backup format that is perhaps causing it.

This will need to be investigated however, but hopefully a fix can be put in place in the future.

Compare taking a lot longer on SQL 2008R2 then 2005

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk