Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Monday, 18 July 2016

Difference between Database Mirroring and Log Shipping in SQL Server

Difference between Database Mirroring and Log Shipping in SQL Server:

Database mirroring is a functionality in the SQL Server engine that will read from the
transaction log and then copies those 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 mirror 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. It also can provide you with a more flexible solution to
use a reporting server instance with older data.

In my experience, database mirroring is preferable to log shipping in most cases, although log
shipping does have the following advantages:

1. Provides backup files as part of the process
2. Multiple secondaries are supported
3. Possible to introduce a fixed delay when applying logs to allow the secondary to be used for
recovering from user error or data corruption





Sno
Item
Database Mirroring
Log Shipping



1



Jobs


Jobs are not involved. Manual Copy/Restore method copy data principal to Mirror supported. Attach/detach method not supported

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).
2
Server Limitation
Only one mirror Server Supported
Can be applied to multiple standby server
3
Recovery model
Full Recovery Model only
Full & Bulk Recovery Model
4
Read from Destination
Mirror DB always recovering state To Read it we need to use Database Snapshot
We can use stand-by option to read db on stand by server
5
DB support
We can support more than one db between principal and Mirror. It works database level
We can support multiple db between instances and it works both server and db level
6
Role Change
Fully automatic
Manual
7
Auto page Recovery
Auto page Recovery supported from SQL 2008 onwards. It will recover damage pages
Auto Recovery Page not supported
8
Failover
It s automatic in high safety with automatic failover only
Manual changes required
9
Failover Duration
Failover is Fast, sometimes <3seconds but not more than 10 seconds
Can make more than 30 mints
10
Data Transfer
Individual T-Log Records are Transferred using TCP endpoints
T-Logs are Backedup and Transferred to Secondary server.