Difference Between Mirroring and Replication

The key difference between mirroring and replication is that mirroring occurs on the database while replication occurs on the data and database objects. Another important difference between mirroring and replication is that mirroring does not support distributed environment but, replication supports distributed database environment.

Mirroring and replication are two techniques in DBMS that improves data availability and reliability. Mirroring involves redundant copies of a database while replication involves duplication of data and database objects such as tables views etc.

CONTENTS

1. Overview and Key Difference
2. What is Mirroring
3. What is Replication
4. Side by Side Comparison – Mirroring vs Replication in Tabular Form
5. Summary

What is Mirroring?

Database mirroring involves duplicating a database stored in a machine or a server to another server. The original database is the principal database. The copied database is the mirror database. The system copies all changes made to principal’s content to the mirror. In other words, the principal server automatically shifts the transaction log updates on to the mirror server database. If a failure occurs, the system can restore the data by copying from one database to another. Therefore, if a failure occurs, the mirror database starts performing similarly to the principal database.

Figure 01: DBMS

Furthermore, database mirroring is expensive and the frequent updates can increase latency and slow down the performance. Usually, server fails can cause data loss but data mirroring is a better solution to overcome this issue.

What is Replication?

Data replication is frequently copying data and data objects from a database to another database. Usually, the server that provides the data for replication to other servers is the publisher. The server that receives replicated data from the publisher is the subscriber.

There are three types of database replications. They are the snapshot, merging and transactional replication. Firstly, in snapshot replication, data on a server copies to another server’s database or another database in the same server. Secondly, in merging replication, data from multiple databases combine into a single database. Thirdly, in transactional replication, initially, users receive full copies of the data and then receive constant updates as the data changes.

Overall, Database replication provides a distributed database environment that helps the users to access data relevant to their task. One common database that provides database mirroring and replication is MSSQL Server.

What is the Difference Between Mirroring and Replication?

Mirroring is the process of creating and maintaining redundant copies of a database. On the other hand, Replication is the process of continuously copying data changes from one database to another database. Mirroring is performed on the database while replication is performed on both data and database objects.

The mirrored database is on another machine. On the contrary, the replication data and data objects are located in another database. With concern to supporting the distributed database, mirroring does not support distributed environment. However, replication supports distributed database environment. Overall, mirroring is considered expensive in comparison to replication, which is less expensive.

Summary – Mirroring vs Replication

Mirroring and replication are two techniques that help to improve data availability and reliability in DBMS. The difference between mirroring and replication is that mirroring occurs on the database while replication occurs on data and database objects.

Reference:

1.PrasadBadana. Database Mirroring Lesson 13.5, PrasadBadana, 19 Sept. 2012. Available here
2.“What Is Database Replication? – Definition from WhatIs.com.” SearchSQLServer. Available here
3.“What Is Database Mirroring? – Definition from WhatIs.com.” WhatIs.com. Available here
4.SQL Server DBA Tutorial 99-What Is Replication, Types of Replication and When to Use Each Type, TechBrothersIT, 1 Jan. 2015. Available here

Image Courtesy:

1.’Database Management’ by Nick Youngson (CC BY-SA 3.0) via Alpha Stock Images

ncG1vNJzZmivp6x7pbXFn5yrnZ6YsqOx07CcnqZemLyue8OinZ%2Bdopq7pLGMm5ytr5Wau265yKupqKqZo7Rurc2dZKudoKG2pK3ToqanZw%3D%3D