Difference Between Mirroring and Replication

Mirroring and Replication are the terms somehow related to copying of data in a DBMS. The prior difference between mirroring and replication is that mirroring refers to copy a database to another location whereas replication includes the copy of data and database objects from one database to another database.

Both mirroring and replication are advantageous and increases the availability and performance of the data or database.

Content: Mirroring Vs Replication

1. Comparison Chart
2. Definition
3. Key Differences
4. Conclusion

Comparison Chart

Basis for comparison	Mirroring	Replication
Basic	Creation of a database copy at a different location (machine).	Creation of data and database object to enhance the distribution operations.
Performed on	Database	Only data and database objects
Located on	Different machine	Different database
Cost	Very expensive	Inexpensive
Distributed database	No provision for distributed database support	Supports distributed database

Definition of Mirroring

Mirroring is the process of generating multiple copies of databases and it is also known as shadowing. These database copy usually located on different machine. If any primary server crashes or engaged for maintenance, at that instant, the system can automatically failover to the mirrored database. At any given time, only one copy can be accessed.

The tight coupling between the primary database and mirrored database is established with the help of sending blocks of the transaction log to the mirrored database. In case of any failure, it is also capable of restoring the data by copying it from one database to another. When any failover takes place, the mirror database becomes principal database.

Mirroring incorporate the redoing of the update, insert and delete operation that are committed in a primary database on to mirrored database without any delay. In a fully secure mode, a transaction cannot commit until the log records for the transaction have made it to disk on the mirror. Mirroring doesn’t support distributed database.

Definition of Replication

Replication is the process of creating distributing redundant data and database objects at different databases to enhance the availability of the data. It is capable of rolling up the corporate data from geographically dispersed sites and spreading the data to remote users on a local area networks or internet. It increases the execution of the parallel commands.

In Microsoft SQL server, the publisher is an entity that provides the data for replication to other servers. The subscriber is a server that usually receives the replicated data from a publisher.

There are three types of options available in the Microsoft SQL server for replication operation: Snapshot replication, Transaction replication and Merge replication.

• Snapshot replication copies the data and database objects same as they appear in an instant.
• Transaction replication generates an initial snapshot of the data to the subscriber then its incremental modification is sent to the subscriber as a discrete process. There are two replication processes defined under transactional replication namely log reader agent and distribution agent.The former process log reader agent reads the transactions from the database transaction log, applies an optional filter and stores them in a distribution database, which resembles a queue supporting store and forward mechanism of transactional replication. The latter process distribution agent forwards the changes to each subscriber.
• Merge replication enables each replica to be self-governing whether it is online or offline. Whenever any change is committed on metadata of published object, it is tracked back to the subscriber’s and publisher’s end. It does not replicate the instance of data and database objects.

The data can be replicated by using database triggers with the help of some specific tools. The specialized stored procedure in the database signals the replicating agent to capture and transport the changes. Replication can also be employed with database mirroring, which should have a distributor.

Conclusion

Mirroring and replication are the methods that help in improvising the data availability, reliability, and performance. But, mirroring involves redundant copies of a database whereas replication includes the duplication of data and database objects such as tables, stored procedures, user-defined functions, views, materialized views, etc.