Home Forums SQL Server 2008 SQL Server 2008 - General What, if any, is the best type of replication for implementing on hundreds of databases on one server? RE: What, if any, is the best type of replication for implementing on hundreds of databases on one server?

  • We've tried mirroring many DB on the same instance and we started having problem at around 50 DBs, that might have changed on SQL 2012 though but mirroring is not designed for that kind of things.

    Log shipping is the only real solution (beside geo-cluster + SRDF SAN).

    The initial load is pretty easy to compute, just check the size of all your full backups, copy of few of them to your DR site and find out an AVG copy speed (in your case you might want to get another network card dedicated to copying files to the DR site).

    For log shipping to work all your DB need to be in full recovery, if that's not already the case you should switch all the DBs to full recovery and see how much log backup you get per hour.

    The limiting factor is the network speed, doing log backup is only an issue on heavily used system.

    Handling the Copy/Restore/Cleanup is just an industrialized version of any log shipping scripts, powershell would probably be the quickest way to get you started. Log shipping being so old there are probably scripts floating around resilient enough to resist anything you can throw at it.

    Depending on your RTO and RPO, and the size of some DBs you can do a mix of log shipping and regular backup/restore (Full & Diff can also be mixed).

    In the end it all depend on how much data can be lost. If "none" is the answer, check the price of a geocluster + admins with enough experience + 2 san box linked with SRDF + san admins, show it to the service owner and ask the question again.