Comparing live with a backup

Compares and synchronizes SQL database content.

Comparing live with a backup

Postby AndrewRMClarke » Thu Oct 11, 2007 11:38 am

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.
AndrewRMClarke
 
Posts: 51
Joined: Wed Aug 16, 2006 3:17 pm
Location: Cavendish

Postby Robert » Thu Oct 11, 2007 12:10 pm

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
Robert
 
Posts: 412
Joined: Mon Oct 30, 2006 11:15 am
Location: Cambridge, UK

Postby AndrewRMClarke » Fri Oct 12, 2007 6:50 pm

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.
AndrewRMClarke
 
Posts: 51
Joined: Wed Aug 16, 2006 3:17 pm
Location: Cavendish

Postby Robert » Fri Oct 12, 2007 9:52 pm

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
 
Posts: 412
Joined: Mon Oct 30, 2006 11:15 am
Location: Cambridge, UK

Postby AndrewRMClarke » Tue Oct 16, 2007 10:56 am

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.
AndrewRMClarke
 
Posts: 51
Joined: Wed Aug 16, 2006 3:17 pm
Location: Cavendish

Postby AndrewRMClarke » Tue Oct 16, 2007 6:18 pm

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%
AndrewRMClarke
 
Posts: 51
Joined: Wed Aug 16, 2006 3:17 pm
Location: Cavendish

Postby Robert » Wed Oct 17, 2007 11:44 am

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
Robert
 
Posts: 412
Joined: Mon Oct 30, 2006 11:15 am
Location: Cambridge, UK

Postby Robert » Mon Dec 17, 2007 3:22 pm

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
Robert
 
Posts: 412
Joined: Mon Oct 30, 2006 11:15 am
Location: Cambridge, UK

my evil database

Postby AndrewRMClarke » Wed Jan 02, 2008 11:47 pm

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.
AndrewRMClarke
 
Posts: 51
Joined: Wed Aug 16, 2006 3:17 pm
Location: Cavendish

Postby Robert » Thu Jan 03, 2008 11:26 am

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
Robert
 
Posts: 412
Joined: Mon Oct 30, 2006 11:15 am
Location: Cambridge, UK

Postby Robert » Thu Jan 24, 2008 12:25 pm

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
Robert
 
Posts: 412
Joined: Mon Oct 30, 2006 11:15 am
Location: Cambridge, UK


Return to SQL Data Compare Previous Versions

Who is online

Users browsing this forum: No registered users and 1 guest