Need HELP - SOLUTION ASAP!!

Last Post 06 May 2010 01:25 PM by SQL_Jr. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
SQL_Jr
New Member
New Member

--
06 May 2010 06:07 AM
I'm trying to come up with a solution.

Have a DB around 1TB+, that receives Batch update/loads throughout the day.

This of course slows to a crawl, production, as users using the system need to make changes SUIDs. Of course the db is poorly designed - this primary table has 40 cols, and is the majority of the data size.
 
So, the thought of the client is this: 1) Secondary DB would be used to server the users where they can make their minimal changes SUID. 2) Have the primary db a)receive the updates/loads, b) reindex, c)synch (get the changes from the secondary) 3) Then once a "good" copy of the master is complete, they want to push out (in essence replace) the Primary to the Secondary (ex: backup and restore) What do you think is best performance solution for this? Replication? SSIS?

How would we go about it given the size of the db? Backup/Restore could only be accomplished w/3rd pty compression software.

But, with backup/restore Replication would have to be setup again.

My idea is to use Merge Replication, and just have the updates from the user db to the Primary master db - but how will the changes from the primary db get to the secondary user db? I'm not worried about Merge changes from B to A, but A-B is large data size.

TIA
gunneyk
New Member
New Member

--
06 May 2010 10:52 AM
Merge replication or even the other scenario wont solve the problem. You still eventually have to apply those changes to the primary server and if it can't handle the changes directly it wont be able to handle the changes period. Why is it slowing to a crawl? Is it blocking, excessive CPU ot what? Are you missing a cruicial index to help with the updates etc. My guess is it's a combination of poorly configured or under powered hardware and poor db design. But until you pinpoint the actual bottlenecks you can't propse a solution.
Jason Love
New Member
New Member

--
06 May 2010 11:44 AM
Have you considered table partioning or transactional replication for a report server?
SQL_Jr
New Member
New Member

--
06 May 2010 01:25 PM
Hi.  Thank you all for the replies.  I hope you will follow the thread.
First, the slowing down is due to the updates from the load blocking the user Selects, Upd, Ins, Dels.

Table partitioning has been discussed, and is a longer term solution.  Right now we need to have a short term solution.

How would transactional replication work with such large "batch" updates to the Master, won't it take some time to mass update the secondary server?

Ideas?  Thanks!


Acceptable Use Policy
---