Comparing live with a backup

Hah! You thought I'd disappeared!

You will remember that I was having problems with the alpha version (whatever happened to the Beta?) trying to compare a backup with a live database. It was a simple database with just two million rows of nicely indexed data. It didn't go well.

I re-ran the test on the released version, and on another machine. It finished it this time. The initial tab, showing what tables to match, took over an hour on a dual core Conroe with 1 Gig core (I didn't hang about with a stopwatch) and I had to leave the actual Data Compare process to run overnight. In the morning, there is was, finished.

Whilst running the test, I hit a bizarre problem where it kept timimg out on trying to connect to my test SQL Server 2000 server, only on one workstation and only on the left-hand pane. It worked happily on the right-hand pane.

Comments

  • Morning,

    Well, I'm amazed it took that long, I have to say... how big is the backup file you're using, and is it SQL Backup or native? Are you accessing it over the network, or is it on the local disk?

    Did you manage to find out whether you're using a non-clustered index or a clustered one as the comparison key on the particularly large table? As I said before, this can have a huge effect on performance.

    As for the timeouts... I'm confused. Especially since it was only on one side, despite the fact they're identical controls!

    Thanks,
    Robert
    Robert Chipperfield
    Red Gate
  • The primary key is non-clustered. The clustered index is being used elsewhere. I've just tried it on the database server to eliminate the possibility of network problems. We're now three and a half hours into the compare and it is on 5%

    The SQB file is about 800 megs- not even a gig!

    The timeout problem baffled me too. I had to repeat it several times just to convince myself it was happening. It could, I suppose, be a genuine timeout first time around (Why? I don't know, because SSMS was happily connected at the time) and the control became deranged and wasn't able to reset itself properly. I can't replicate the problem.
  • If you get a moment (and you can stand it), could you just have a quick look and see what the CPU usage is doing during the compare (the main "body" of the compare, not the registering databases) bit? In other words, are we maxing out the CPU, or is it I/O bandiwidth somewhere that's the bottleneck?

    Thanks,
    Rob
    Robert Chipperfield
    Red Gate
  • Robert,
    I've re-run it locally on the server to eliminate the network from the equation. I've set all the tables with primary keys. The first part wasn't CPU or Resource-bound. The actual compare is taking 100% CPU- but it is a v slow CPU. Resources are not particularly hit. Unfortunately it is still taking several hours. My guess is that it is a problem with a very large 'inversion' table, so next step is to delete it and try again.
  • Still running! It is now seventy-three percent done, nine hours later. Putting in the clustered primary key doesn't seem to have speeded it up. On the bright side, it is not eating resources. CPU varies betwwen 15 and 50%
  • Morning,

    If it's not chewing up CPU any more, then I guess it must be I/O bound somewhere - I think you said you're now running everything off the local disk, so it's not a slow network link. That said, I've seen some disks perform quite badly when seeking around - in particular one of the NAS boxes we have.

    Rob
    Robert Chipperfield
    Red Gate
  • We've been doing some work on this recently, and the performance has been significantly improved.

    Andrew kindly provided us with a copy of his database, and on our internal builds, that which used to take several hours now takes under ten minutes for the whole database - about 45 millions rows of it!

    These improvements will be included in SQL Data Compare 6.1, which should be released in Q1 2008.
    Robert Chipperfield
    Red Gate
  • I'm confident that the problem has been fixed. I'm sorry to have been such a nag but I was really looking forward to being able to read data rapidly from encryped SQBs. I suspect we've now got the only guaranteed tamper-proof read-only data medium in the industry. 6.1 will be great.
  • Hi Andrew,

    Thanks for the encouraging comment :-). And don't worry about the nagging - it's really useful to know what's causing people pain, so we can then do something about it!

    Cheers,
    Rob
    Robert Chipperfield
    Red Gate
  • Hi,

    Just a quick update - Data Compare 6.1 is now released, so you should be able to compare in a matter of minutes now :-)

    Robert
    Robert Chipperfield
    Red Gate
Sign In or Register to comment.