Compare taking a lot longer on SQL 2008R2 then 2005

hbucknerhbuckner Posts: 67 Bronze 3
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

Comments

  • James BJames B Posts: 1,124 Silver 4
    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?
    Systems Software Engineer

    Redgate Software

  • hbucknerhbuckner Posts: 67 Bronze 3
    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 BJames B Posts: 1,124 Silver 4
    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?
    Systems Software Engineer

    Redgate Software

  • hbucknerhbuckner Posts: 67 Bronze 3
    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 BJames B Posts: 1,124 Silver 4
    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.
    Systems Software Engineer

    Redgate Software

  • hbucknerhbuckner Posts: 67 Bronze 3
    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 BJames B Posts: 1,124 Silver 4
    I'll mail you an FTP link shortly thanks.
    Systems Software Engineer

    Redgate Software

  • James BJames B Posts: 1,124 Silver 4
    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.
    Systems Software Engineer

    Redgate Software

Sign In or Register to comment.