Red Gate forums :: View topic - Comparing live with a backup
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Compare Previous Versions
SQL Data Compare Previous Versions forum

Comparing live with a backup

Search in SQL Data Compare Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
AndrewRMClarke



Joined: 16 Aug 2006
Posts: 59
Location: Cavendish

PostPosted: Thu Oct 11, 2007 11:38 am    Post subject: Comparing live with a backup Reply with quote

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.
Back to top
View user's profile Send private message
Robert



Joined: 30 Oct 2006
Posts: 428
Location: Cambridge, UK

PostPosted: Thu Oct 11, 2007 12:10 pm    Post subject: Reply with quote

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
Developer, Red Gate Software Ltd
Back to top
View user's profile Send private message MSN Messenger
AndrewRMClarke



Joined: 16 Aug 2006
Posts: 59
Location: Cavendish

PostPosted: Fri Oct 12, 2007 6:50 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Robert



Joined: 30 Oct 2006
Posts: 428
Location: Cambridge, UK

PostPosted: Fri Oct 12, 2007 9:52 pm    Post subject: Reply with quote

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
Developer, Red Gate Software Ltd
Back to top
View user's profile Send private message MSN Messenger
AndrewRMClarke



Joined: 16 Aug 2006
Posts: 59
Location: Cavendish

PostPosted: Tue Oct 16, 2007 10:56 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
AndrewRMClarke



Joined: 16 Aug 2006
Posts: 59
Location: Cavendish

PostPosted: Tue Oct 16, 2007 6:18 pm    Post subject: Reply with quote

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%
Back to top
View user's profile Send private message
Robert



Joined: 30 Oct 2006
Posts: 428
Location: Cambridge, UK

PostPosted: Wed Oct 17, 2007 11:44 am    Post subject: Reply with quote

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
Developer, Red Gate Software Ltd
Back to top
View user's profile Send private message MSN Messenger
Robert



Joined: 30 Oct 2006
Posts: 428
Location: Cambridge, UK

PostPosted: Mon Dec 17, 2007 3:22 pm    Post subject: Reply with quote

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
Developer, Red Gate Software Ltd
Back to top
View user's profile Send private message MSN Messenger
AndrewRMClarke



Joined: 16 Aug 2006
Posts: 59
Location: Cavendish

PostPosted: Wed Jan 02, 2008 11:47 pm    Post subject: my evil database Reply with quote

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.
Back to top
View user's profile Send private message
Robert



Joined: 30 Oct 2006
Posts: 428
Location: Cambridge, UK

PostPosted: Thu Jan 03, 2008 11:26 am    Post subject: Reply with quote

Hi Andrew,

Thanks for the encouraging comment Smile. 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
Developer, Red Gate Software Ltd
Back to top
View user's profile Send private message MSN Messenger
Robert



Joined: 30 Oct 2006
Posts: 428
Location: Cambridge, UK

PostPosted: Thu Jan 24, 2008 12:25 pm    Post subject: Reply with quote

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 Smile

Robert
_________________
Robert Chipperfield
Developer, Red Gate Software Ltd
Back to top
View user's profile Send private message MSN Messenger
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group