Difference Between Log Shipping and Database Mirroring


Log Shipping:-

  • It automatically sends transaction log backups from one database On another server.

 

  • T-Logs are backed up and transferred to secondary server.

 

  • It can be configured as one to many

 

  • requires a manual failover

 

  • You can use a secondary database for reporting purposes when the secondary database restore in STANDBY mode.

 

  •  Supports both Bulk Logged Recovery Model and Full Recovery Model.

 

  • The restore can be completed using either the NORECOVERY or STANDBY option.

 

  • No limit. Can log ship to many server

 

  • There will be data transfer latency. >1min.

 

  • Both committed and uncommitted transactions are transferred to the secondary database.

 

  • It provides a warm standby solution that has multiple copies of a database and require a manual failover.

DATABASE MIRRORING
 
  • Two copies of a single database on different server instances of SQL Server Database Engine.
  • Individual T-Log records are transferred using TCP endpoints.
  •  One principal server to one mirror server.
  • Automatic or manual
  • In a continous revoery state: Mirrored DB can only be accessed using snapshot DB.
  •  Supports only Full Recovery model.
  • The restore can be completed using with NORECOVERY.
  • Generally good to have 10 DB’s for one server.
  • There will not be data transfer latency.
  • Only committed transactions are transferred to the mirror database.
  • When a database session is synchronized, database provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).

Leave a Reply

Your email address will not be published. Required fields are marked *