locked
which to prefer SSRS or Database mirroring RRS feed

  • Question

  • Hi,

    We have two different databases on a single SQL server. Whatever changes happen on the Db1 should be synched to Db2.

    What is the best approach to have these both databases synchronized? Is it transactional replication or database mirroring.

    What would you suggest?

    Thanks

     


    BRP
    Tuesday, August 23, 2011 2:53 PM

Answers

  • Use transactional replication.

    • Marked as answer by PB29129 Tuesday, August 23, 2011 3:17 PM
    Tuesday, August 23, 2011 3:06 PM

All replies

  • Use transactional replication.

    • Marked as answer by PB29129 Tuesday, August 23, 2011 3:17 PM
    Tuesday, August 23, 2011 3:06 PM
  • Hi PB29129,

    We have two different databases on a single SQL server. Whatever changes happen on the Db1 should be synched to Db2.

    What is the best approach to have these both databases synchronized? Is it transactional replication or database mirroring.

    What would you suggest?

    Depends on why you want to do this.

    If you want a copy of the data for some purposes (reporting?), I'd
    suggest replication.
    If you want a copy of the data so that you can continue your
    operations if a hard drive fails, use mirroring. (And consider moving
    the copy to a different server, so that you are also protected from
    other failures).


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Tuesday, August 23, 2011 10:33 PM
  • Best way to achive syncronization is mirroring, this is just one to one. You could achive one to many via Log Shipping, which takes more time to keep the db syncronized

    and also has a weak bound with log application, which requires exclusive access and interrupts all the business db operations during aligment. With Mirroring the db will

    instead be available constantly, provided you create a snapshot on it. Mirroring also is syncronous in applying changes, so unless you go for a High Performance mode

    (available only in ENT Ed) each commit must be done on both side to take place, which slows operations a bit, expecially if the network between the sites is not performant.

    I definetly will not go for replication if the aim is to have a completely equal copy, as replication is more powerfull (gives you a better level of customization and granularity

    of  intervention) but it is more complex and has generally more impact on the systems and their maintenance. Mirroring, Log Shipping and Replication are great solutions

    and work well; best solution for you is yet based on your needs. If you can specify exaclty what you intend to do with your db copy then we can give you more suitable

    solutions.

    Regards,

              Marco

     

    • Proposed as answer by Marco Carozzi Wednesday, September 7, 2011 6:47 PM
    Sunday, August 28, 2011 8:50 AM