Difference between Database Mirroring and Replication

Key Difference: Database mirroring and database replication are two high data availability techniques for database servers. In replication, data and database objects are copied and distributed from one database to another. It reduces the load from the original database server, and all the servers on which the database was copied are as active as the master server. On the other hand, database mirroring creates copies of a database in two different server instances (principal and mirror). These mirror copies work as stand by copies and are not always active like in the case of data replication.

Database replication refers to the group of techniques in which data is copied and these copies are distributed from one database to another. To maintain the consistency between data and database objects, databases are also synchronized. The source database server can be referred to as the master and the servers in which the data is replicated can be known as slaves. This helps in decreasing the load on master server as more than one server can be used by the clients.

The behavior of master and slave servers is generally the same. Any change in the data is also reflected in the slave servers. This technique can be used to copy the data in more than one database. Partial replication is implemented for only a subset of tables or columns of rows, and therefore it must not essentially replicate the whole database.

Database mirroring refers to the techniques by which redundant copies of a database are created and maintained. The mirrored copy is consistently synchronized with the principal database. This technique helps in ensuring uninterrupted data availability and also cuts the downtime which can otherwise occur due to data corruption or loss. During the process of up-gradation, database mirroring provides at least one viable copy to be accessed.

Microsoft SQL server uses this technique by maintaining two copies (principal and mirror)of a single database on different computers known as server instances. These mirror copies act as the standby copies of the database. Database mirroring is limited to only two servers.

Replication and mirroring techniques are also used in combinations to achieve higher availability of databases. The key difference between the replication and mirroring is that unlike mirroring, there are no standby servers in replication, as all the servers act as active servers. Mirroring is preferred when one wants to lessen the down time and wants to have a cost effective solution in context to shared storage, switches, etc. On the other hand, replication is generally preferred in a scenario where distributed processing needs to be supported by sharing the workload of an application.

Comparison between Database Mirroring and Replication:

 

Database Mirroring

     Replication

Definition

Database mirroring creates copies of a database in two different server instances. These copies work as stand by copies and are not always active like in the case of data replication.

 

In replication, data and database objects are copied and distributed from one database to another. It reduces the load from the original database server, and all the servers on which the database was copied are as active as the master server.

Advantages

Database mirroring helps to reduce planned and unplanned downtime as follows:

 

  • Automatic or manual failover can both be worked out for mirrored databases.
  • It keeps the mirrored database properly synchronized with the original database
  • It improves data availability and scalability
  • It provides fail safe backup
  • It provides load spreading
  • It provides option during a disconnected operation

Disadvantages (SQL Server 2008)

  • High safety without automatic failover setting may be network overhead.
  • A third server is required for "automatic failover"
  • Other items need to be handled outside of mirroring such as logins, SQL Agent jobs, etc..
  • Requires additional storage for mirrored copy
  • If Snapshots are used for read only, the snapshot is only as current as when the snapshot was created.
  • Reconfiguration may require manual intervention.
  • Need to rely on distributor to push changes.
  • Other items need to be handled outside of Replication such as logins, SQL Agent jobs, etc...
  • It is more complicated to setup and maintain then the other options.
  • SQL Server will need to be fully licensed for the secondary server.

Image Courtesy: mysqlperformanceblog.com, technet.microsoft.com

Most Searched in Food and Drink Most Searched in Games and Recreation
Most Searched in Education and References Most Searched in Entertainment and Music
Queer vs Transgender
Internship vs Apprenticeship
Flammable vs Inflammable
Movie vs Cinema

Add new comment

Plain text