difference between Mirroring and log shipping

Question

What is difference between Mirroring and log shipping?

Kunjahamed P · Answer

Log Shipping:: It automatically sends transaction log backups from one database (Known as the primary database) to a database (Known as the Secondary database) on another server. An optional third server, known as the monitor server, records the history and status of backup and restore operations. The monitor server can raise alerts if these operations fail to occur as scheduled. Data Transfer: T-Logs are backed up and transferred to secondary server Transactional Consistency: All committed and un-committed are transferred Server Limitation: Can be applied to multiple stand-by servers Failover: Manual Failover Duration: Can take more than 30 mins Role Change: Role change is manual Client Re-direction: Manual changes required Mirroring:: Database mirroring is a primarily software solution for increasing database availability. It maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance. Database mirroring can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror. Database mirroring supports only one miror for each principal database. Database mirroring also supports automatic failover if the principal database becomes unavailable. The mirror database is always offline in a recovering state, but you can create snapshots of the mirror database to provide read access for reporting, etc. Log shipping is based on SQL Server Agent jobs that periodically take log backups of the primary database, copy the backup files to one or more secondary server instances, and restore the backups into the secondary database(s). Log shipping supports an unlimited number of secondaries for each primary database. Database mirroring is preferable to log shipping in most cases, although log shipping does have the following advantages: 1. it provides backup files as part of the process 2. multiple secondaries are supported 3. it is possible to introduce a fixed delay when applying logs to allow the secondary to be used for recovering from user error Data Transfer: Individual T-Log records are transferred using TCP endpoints Transactional Consistency: Only committed transactions are transferred Server Limitation: Can be applied to only one mirror server Failover: Automatic Failover Duration: Failover is fast, sometimes Role Change: Role change is fully automatic Client Re-direction: Fully automatic as it uses .NET 2.0

Sanwar Ranwa · Answer

Hi Dipak Please check the following articles https://lalitkale.wordpress.com/2014/09/11/difference-between-log-shipping-and-database-mirroring/ https://www.sqlservercentral.com/blogs/difference-between-log-shipping-and-database-mirroring