Server replication and server mirroring are two prevalent methods for creating backups of databases. They are both effective in their ways but also have some key differences. This article offers an in-depth look at the two methods, their differences, and when you might want to use each one
What is SQL Server Replication?
SQL server replication is a technology that facilitates the copying and transferring data and database objects from one database to another before synchronizing between the two databases to maintain integrity and consistency. In simple terms, it is the process of reproducing database data at the selected targets.
SQL server replication is continuous or scheduled, depending on the business needs. It is a process you can use to copy data from a source database to multiple destination databases simultaneously, making it ideal for large-scale data distribution.
There are multiple types of replication:
-
Transactional replication
In this case, any changes made at the publisher level are sent to the subscriber in real time. The application of the changes follows the same order and is made under the exact transactional boundaries associated with the publisher.
-
Merge replication
Merge replication allows for data changes at both publisher and subscriber levels. Triggers are used to track the changes. When connected to a network, the subscriber will synchronize with the publisher allowing for the exchange of any rows that have changed since they were last synchronized.
-
Snapshot replication
Here, data is sent from publisher to subscriber at specific intervals defined by the administrator. The server replication software takes a snapshot of the data and applies it to the subscriber. Any changes made to the source database during the interval will not be reflected on the destination database until the subsequent replication.
-
Bidirectional replication
You can make data changes at the publisher and subscriber databases in this configuration. Each server publishes data and subscribes to a specific publication with the same data from a different server.
SQL Server Mirroring
SQL Server mirroring is a high availability and disaster recovery technique involving two separate SQL server scenarios on different or the same machines. One scenario acts as the principal, the primary instance, while the other is the mirror. Some forms of server mirroring may involve a third SQL server instance known as the witness.
Depending on your needs, you can choose from three different modes of server mirroring:
-
High safety mode
This mode allows for the writing of data on the principal and the mirror database synchronously. The database application can resume activity after committing to both the principal and mirror databases.
You can choose from two options if the principal moves out of the equation. The first is to do nothing, wait until the principal is available again, and resume the mirroring process from where it previously stopped. The second is to force the SQL server instance on the mirror database, thus transforming the mirror database into the principal.
-
High safety with automatic failover mode
This mode needs three servers. Like the previous, writing and committing data on the principal and the mirror servers must happen synchronously. The application only continues running after committing to all databases.
It may be slower since you must commit transactions on both databases. If the principal database fails, you can only wait for the automatic failover process to complete, with the mirrored database becoming the principal.
-
High-performance mode
It involves the writing and committing data on the principal server before sending and committing it to the mirror server. In this case, you cannot add a server witness or use the automatic failover feature.
If the principal server fails, you can choose to do nothing and wait for it to resume function. You can also force the SQL server instance on the mirror database by using it as the principal database.
However, this increases the risk of data loss due to the asynchronous communication between the different databases. Another option is to use manual updates to reduce data loss.
In summary, you can use various techniques to manage data changes in a SQL server database. The most appropriate one to use depends on the specific needs of your application and environment.