In the field of databases in computer science, a transaction log (also transaction journal, database log, binary log or audit trail) is a history of actions executed by a database management system used to guarantee ACID properties over crashes or hardware failures. Physically, a log is a file listing changes to the database, stored in a stable storage format.

If, after a start, the database is found in an inconsistent state or not been shut down properly, the database management system reviews the database logs for uncommitted transactions and rolls back the changes made by these transactions. Additionally, all transactions that are already committed but whose changes were not yet materialized in the database are re-applied. Both are done to ensure atomicity and durability of transactions.

This term is not to be confused with other, human-readable logs that a database management system usually provides.

In database management systems, a journal is the record of data altered by a given process.

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

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.