Post reply

The limits of SQL 2008 Replication

  • March 12, 2015 at 10:13 am

    #315648

    I have a server with 500+ databases, each of which with a few hundred tables.

    Would it be OK if I set up replication for each of these 500+ databases but only to about 100 tables each? The end goal for this is to have a DR location which can also be used to offload reporting. If not OK, then how bad would it actually be? Also, are there any other options I could implement that would give me a DR location that is <1min old?

    The total size of these databases is around 600 GB, and all are pretty much the same size.

    The server (2-node sql 2008 cluster) handles about 800 batches/second during peaks, which is usual US business hours and is rarely above 20% CPU.

    I hope this gives you enough information, if not I will happily provide anything else you may need.

    Thank you!

  • March 12, 2015 at 10:53 am

    #1783649

    For DR and Reporting purposes you old use replication but I don't think it's the best solution. You would have a lot of publishers to deal and a lot of subscriptions to monitor. I think you would be better off handling this using an SSIS package. It would be easier to log, troubleshoot and performance test using SSIS.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • March 12, 2015 at 11:01 am

    #1783655

    Thanks for the suggestions on SSIS. Since I have only done very basic SSIS stuff, could please elaborate a bit more or maybe suggest a few links? Does SSIS have some built-in replication-alike features and tools?

    Thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply