Replication Vs Mirroring

  • Whats the difference between Replication & Database Mirroring?

    When to use which?

    Thanks,

    Usman

  • transactional replication is near real time data being sent to your subscriber. your subscriber is accessible during this time.

    mirroring is near real time data however, the mirrored server isn't accessible. it is there when the primary fails, then the mirror will kick in.

    see BOL for more info. always see BOL for more info.

    -----------------------------
    www.cbtr.net
    .: SQL Backup Admin Tool[/url] :.

  • With the use of a Witness server Mirroring can offer automated failover (it's pretty quick too).

    SQL Server native client and .Net 2.0 onwards provide a 'Failover Partner' parameter to support mirroring and seamless failover.

    It offers data resilience where Clusters can not and is considerably cheaper than mirroring a SAN.

    Replication will give you a copy of a database on a different server. In the event of a problem with the primary you must manually intervene to instruct users / applications to talk to the copy.

    So the moral is: -

    If you want another online copy of a DB to run reports and queries against without affecting performance of the Primary Server use replication.

    If you want High Availability go for Mirroring.

  • Some good points have been mentioned, but I think one is missing:

    When using database mirroring with transaction safety set to FULL, the mirror database will always be 'up to speed' with the principal database. With replication, and with transaction safety set to OFF in mirroring, you can't be certain that the replicated/mirrored database is up to date with the main/principal database.

    This supports mark.summer's statement; use mirroring if your goal is high availability. There is no point (usually) in having a replicated copy of a database for failover purposes, if you can't be sure that it's up to speed.

    See this page for information on mirroring.

    Replication on the other hand is good for load balancing. If your for example have some webservers that needs massive amounts of reads, you can distribute your database to 1,2,3 or even more servers, so the webservers can distribute the load amongst the replicated databases.

    A lot more could be written about the differences between the two techniques - a thing I'm afraid I don't have time for. If you have any questions about something specific though, I'll be happy to answer.

  • What about changes on the database structure scheme?

    I know for sure that with Replication, the publication must be changed every time there is a database structure change.

    Does the same apply to Mirroring?

    Thanks!

    Ab

  • aocon (12/16/2010)


    What about changes on the database structure scheme?

    I know for sure that with Replication, the publication must be changed every time there is a database structure change.

    Does the same apply to Mirroring?

    Thanks!

    Ab

    Hi Ab

    If you mean DDL changes (creating tables, dropping tables etc etc) the answer is no - the same does not apply to mirroring.

    DDL changes are applied automatically on the mirrored database, so there is no need to recreate/change the mirroring 🙂

    Best regards

    Lars Søe Mikkelsen

  • Thank you Lars, that is what I was looking for!

    Regards,

    Ab

  • Thanks for the wonderful information. I wanted to check the gurus with a request for advice. We are a medium shop processing and handling HR data. We use a BPM (Business Process Management) tool for designing and implementing workflows. For daily transactions and reporting we use the same database and we are in the process of setting up a separate reporting server since we anticipate our reports to grow by leaps and bounds and to many more users. The production is in SQL2005-std edition and for reporting we were able to get SQL2008-R2. We need near-time data for reporting. So I tried setting up mirroring but I cannot make use of the 'asynchrounous' mode because of the production sql server edition. Hence I've set up T-replication which seems to work fine (ofcourse a pilot with few articles..FYI- the DB is about 30 GB)... what are the main things that I need to watch out for if I proceed with this approach.

    I'd really appreciate if any of the Gurus can advise me on this approach.

  • in addition to above ....

    Mirror - High Availability for ENTIRE database,global, automatic failover both on database side and application side is possible.

    Replication - For selected articles only, granular,not for fail-safe but for distributed usages and load balancing.

  • For your reporting, replicated database, you might want to create another database on the subscriber server and keep all report definitions, sp's, views, stored procs in this database, referencing the subscriber. This way users are working out of the "work" db and your definition of your subscription db will remain static, otherwise you will end up with a subscription db with the subscription objects intermingled with the user and reporting objects.....

  • FYI - I dont think its been previously mentioned, a mirrored database can be queried by creating a database snapshot.

Viewing 11 posts - 1 through 10 (of 10 total)

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