Database Mirroring Vs Log-Shipping
Communication mode
Database mirroring, principal and mirror server communicate through TCP\IP ports connection and write data change directly to remote transaction log file. Database mirroring process architecture is more robust and efficient than log shipping.
Log shipping always propagate changes from primary database asynchronously by backing up transaction log file, copying it across to target server through share directory, and restoring it at the target or secondary server. All of the processes are scheduled at predefined interval by SQL Jobs.
Database State
In database mirroring, Secondary database will be in restoring mode.
In Log-Shipping, Secondary database can be used as read-only mode.
Failover Process
DB mirroring can be automatic or manual failover process.
Log-shipping is manual failover process.
Recovery Model
DB mirroring supports full recovery model only.
Log shipping supports both bulk logged recovery model and full recovery model.
Operations
In database mirroring can handle alter database operations such as adding files, file groups etc.
Log shipping cannot handle database file operations, such as adding files etc.
Replica\Secondary Servers
Database mirroring will not have multiple database destinations for mirroring the principal database. It will have one mirror database synchronizes with principal database.
Log shipping will have multiple secondary databases for the synchronization.
Communication mode
Database mirroring, principal and mirror server communicate through TCP\IP ports connection and write data change directly to remote transaction log file. Database mirroring process architecture is more robust and efficient than log shipping.
Log shipping always propagate changes from primary database asynchronously by backing up transaction log file, copying it across to target server through share directory, and restoring it at the target or secondary server. All of the processes are scheduled at predefined interval by SQL Jobs.
Database State
In database mirroring, Secondary database will be in restoring mode.
In Log-Shipping, Secondary database can be used as read-only mode.
Failover Process
DB mirroring can be automatic or manual failover process.
Log-shipping is manual failover process.
Recovery Model
DB mirroring supports full recovery model only.
Log shipping supports both bulk logged recovery model and full recovery model.
Operations
In database mirroring can handle alter database operations such as adding files, file groups etc.
Log shipping cannot handle database file operations, such as adding files etc.
Replica\Secondary Servers
Database mirroring will not have multiple database destinations for mirroring the principal database. It will have one mirror database synchronizes with principal database.
Log shipping will have multiple secondary databases for the synchronization.
Thanks for sharing this Informative content. Well explained. Got to learn new things from your Blog on. SQL server dba Online Training
ReplyDelete