Comparing live with a backup
AndrewRMClarke
Posts: 73 Bronze 2
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.
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
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
Red Gate
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.
Thanks,
Rob
Red Gate
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.
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
Red Gate
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.
Red Gate
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
Red Gate
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
Red Gate