SQL Server Transaction Logs record the entire information about the transactions and data modifications made by the transactions in the server. In order to create backup or ensure the availability of transaction logs at the instance of any disaster, SQL offers two processes- SQL Log Shipping and SQL Log Mirroring.
In this section, we will describe both these processes and the parameters in which they differ from each other.
Overview Of Log Shipping & Log Mirroring
SQL Server Log Shipping is the process of automatically sending transaction log backups from a primary database belonging to a primary server instance to a secondary database present on secondary server instance. There can be more than one secondary databases present on separate secondary server instances.Monitor server, the third server instance is responsible for recording the status and history of the entire backup and restore operations. This server alerts the user in case the operations fail to occur as they are scheduled.
SQL Server Log Mirroring is the process of creating and maintaining the required number of copies of the server database. The main purpose of mirroring is to ensure that the database is continuously available. In addition, it aims to avoid the downtime that may arise due to data corruption or due to compromise with the network availability. Mirroring ensures that at least one feasible database copy will always be available at the time of system upgrades.
How Does Log Shipping & Log Mirroring Work?
Log Shipping
There are three main operations, which form the Log Shipping process:
- Take the backup of the transaction log at primary server instance.
- Copying the log file at secondary server instance.
- Restoration of copied log files on secondary server instance.
In this process, the log file database can be shipped to multiple server instances. The copying and restoration of the secondary server instances are need to be done separately for each instance. In the log shipping configuration, primary database may sometime fail in automatically loading the log to the secondary database. In such cases, the secondary database can be manually brought online.
Log Mirroring
Database mirroring can either run in Synchronous or Asynchronous operation.The synchronous operation is characterized by the transactions commitment without waiting for the mirror server for writing the log to the disk. This independency increases the performance.
In the synchronous mode, the transactions are committed on both the servers, but the transaction latency is increased.The operating modes of mirroring are:
1. High-Safety Mode:This mode supports synchronization operation. When a session is started, the mirror database of the mirror server is synchronized with the primary database very quickly.As soon as the synchronization is done, a transaction gets committed on both the servers at the cost of increased transaction latency.
2. High-Performance Mode:In this mode, the principal server sends a log to the mirror server and without waiting for any acknowledgment from the mirror server, sends confirmation to the client.Although, this mode ensured reduced transaction latency, but at the cost of some data loss.
Benefits of SQL Server Transaction Log Shipping & Mirroring
Log Shipping
- Works as a database recovery solution for a primary database and multiple secondary databases on different server instances.
- Gives the provision to give read-only access to the secondary databases.
- The delay provided between the primary server backup and the process of restoring the same by the secondary server instance, is user-defined.
Log Mirroring
- When a disaster occurs, the standby copy of the database is brought online by the automatic failover without any data loss.
- Complete redundancy of data is provided by log mirroring, depending upon the mode of operation.
- During up-gradation, the availability of the production database is improved
Conclusion
Both Log Mirroring and Log Shipping are efficient processes to ensure data security and availability in the scenario of data accidents. Therefore, the choice between both of them can be made on the basis of the type of service your organization needs.
DB Cloud TECH 
Good article!
LikeLike