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).