Mirroring versus Replication

Both replication and mirroring are methods of transferring data from a primary database to a secondary database. However, they both serve different purposes – and are used in completely different ways.

Scenario 1 – A Reporting Application (in conjunction with your primary application)

Say you have a second application (in addition to your primary app) – and all that app does is ‘reporting’. Create reports, print reports, create pdfs etc. Is there  any point bogging down your primary application with traffic from this reporting app? No – there isn’t.  All you need is a separate copy of the database for reporting purposes.

This is what replication was invented to solve.

Scenario 2 – Disaster Recovery (in case of failure of the Primary database)

You would want to have a ‘safe’ copy of the primary database (most likely offsite) in the event of disaster on the primary database (btw – ‘flooding’ is the most common destroyer of databases).

This is what mirroring is used for.

When creating a ‘mirror’, you want to be able to a) Capture all schema changes in the primary as they occur and b) Not let ANYONE write data to the secondary database (so that it is GUARANTEED to be safe when the primary fails). This is best accomplished by placing the secondary database in ‘offline’ mode.

That summarizes the key differences between Mirroring and Replicating

Types of Replication

There is more than one way to do replication. The three methods are listed below:

· Transactional – Writes transactions to your secondary database as they occur on your Primary with a slight delay.

· Snapshot – Takes dumps at scheduled times of the changes recorded in your logs since the last synch and inserts them at a scheduled time.

· Merge – will merge content between databases so you can update data in both databases. If we have App 1 talking to DB 1 and App 2 talking to DB 2, at the end of the day we want to merge the transactions that occurred during the day to make sure both databases have the full lists of transactions.

Summary

Both Replication and Mirroring provide powerful ways to synchronize two (or more) databases. While one is used to solve performance problems (Replication), the other is used to guarantee disaster recovery (Mirroring).