locked
SQL Server 2008 Replication with Large Sets of Data RRS feed

  • Question

  • Hello,

    I have a few questions regarding SQL replication and how it handles large sets of data.   We are exploring different options to offload reporting from the OLTP system.  The database in question is almost 1TB in size.  The tables that are used for reporting have millions and millions of records.  Some have billions.  We currently have a mirror setup and do a snapshot and then report off the snapshot.  The business now requires almost "real-time" data for their reports instead of hourly snapshots.  It's a daunting process for the server to keep generating a snapshot and the application has many errors from it because the server is constantly generating the snapshot with the latest data.  Long story short the requirements have changed so we are looking into transactional replication for our reporting solution.

    We'd like to keep the mirror in place and add in another server(s) for reporting.  We are thinking two more SQL servers.  One subscriber and one distributor.  The existing servers would be the principal and mirror.   From what I've been reading it's best to have the distributor database on a dedicated server.  

    1. What would be the space requirements for the distribution database?  
    2. Are there any potential issues with replicating such large sets of data?

    Thank you for your time.

    Wednesday, June 19, 2013 5:52 PM

Answers

  • 1) space requirements are unpredictable. It all depends on the type and volume of data you are moving. I would say several hundred gig should be sufficient, but that could be way high or low.

    2) I would initialize from a backup. As you are mirroring you may run into problems when enabling the database for replication.  I've seen cases where a database being mirrored locks up when you try to add replication. If your data is updated as part of a transaction it will be replicated as a transaction and this can be slower than singleton updates.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    • Marked as answer by Steve Moran Wednesday, June 19, 2013 7:11 PM
    Wednesday, June 19, 2013 6:21 PM
    Answerer

All replies

  • 1) space requirements are unpredictable. It all depends on the type and volume of data you are moving. I would say several hundred gig should be sufficient, but that could be way high or low.

    2) I would initialize from a backup. As you are mirroring you may run into problems when enabling the database for replication.  I've seen cases where a database being mirrored locks up when you try to add replication. If your data is updated as part of a transaction it will be replicated as a transaction and this can be slower than singleton updates.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    • Marked as answer by Steve Moran Wednesday, June 19, 2013 7:11 PM
    Wednesday, June 19, 2013 6:21 PM
    Answerer
  • Thank you for the reply.  I think a good rule of thumb would make the distributor as large as the publisher.  Good information to know about the mirror locking up.  
    Wednesday, June 19, 2013 7:11 PM