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
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.
|
No comments:
Post a Comment