SQL Server Performance

Need SQL Server Solution....please advise

Discussion in 'SQL Server 2005 General DBA Questions' started by sql_jr, May 5, 2010.

  1. sql_jr New Member

    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.

    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.

    Finally, is there any column limitation on Merge replication? Many thanks!

    Any way, appreciate your help
  2. satya Moderator

    Why not go for Database Mirroring in this case?
  3. sql_jr New Member

    thanks, Satya! The reason why not to use DB Mirroring, is that the "secondary" needs to have updates and inserts made to them. Even with a snapshot off the mirror, is READ-ONLY.

    I think we will try to set up MERGE replication where they are both publishers and subscribers. Thoughts on that?
  4. rohit2900 Member

    Peer-to-Peer Transactional Replication can also be a good choice.
  5. ejs New Member

    Hi, I have only just seen this on Saturday 22May2010. It is an interesting but not an unusual problem.
    You don't say how often the external feeds arrive, eg 30mins, hourly, 2hourly, 4 hour etc not that it particularly matters with a solution I have in mind.
    One solution: Have you considered using one or more Queues, one per feed? Let us assume you could have feeds arriving together or overlapping in time.
    Usage: Import each of your feeds into a its own Queue then process each queue asynchronously to merge new data into your main database, doing so in a manner that always gives the primary focus/priority to your live users? In this manner you won't need any second database complicating what is a fairly simple thing to deal with.
  6. satya Moderator

    Softfan, your reference is entirely different to the original question.
    If you need to promote your tool then please get in touch with webmaster of this site for a review to post in articles section.

Share This Page