Please read the below table to find out the diffrence between mirroring and log shipping.
| Database Mirroring | Log-shipping |
| Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance. Database mirroring can operate synchronously or asynchronously. | 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 secondary’s for each primary database. |
| 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. | Log shipping is always asynchrony. Log shipping totally depends on the log backup and restore schedule |
| Database mirroring supports only one mirror for each principal database. That means DB mirroring is at database level | Log-shipping can work on database and server level. You can configure multiple databases in logshipping |
| Data Transfer: Individual T-Log records are transferred using TCP endpoints Transactional Consistency: Only committed transactions are transferred Server Limitation: Can be applied to only one mirror server Failover: Automatic Failover Duration: Failover is fast, sometimes < 3 seconds but not more than 10 seconds Role Change: Role change is fully automatic Client Re-direction: Fully automatic as it uses .NET 2.0/.Net 3.0 |
With Log Shipping:
Data Transfer: T-Logs are backed up and transferred to secondary server Transactional Consistency: All committed and un-committed are transferred Server Limitation: Can be applied to multiple stand-by servers Failover: Manual Failover Duration: Can take more than 30 mins Role Change: Role change is manual Client Re-direction: Manual changes required |
| Support only full recovery model | Supports full and bulk-logged recovery model |
| Mirror database is always in recovery mode. To read it you have use database snapshot. | You can use the stand-by option to read the database on standby server |
| Auto Page Recovery introduced with SQL SERVER 2008 so it will recover the damaged pages. | Not supported |
I was just seeing at this table and was intrigued by the possibility of configuring multiple databases in logshipping. I have to configure logshipping for several databases in the same instance, is there a way to do this with a single configuration? or do I have to configure logshipping for each database? As far as I know the latter is the way to do it but I’ll be happy to be wrong.
Yes. You can do it. Check online docs.
very good article it’s really helpfull
Thanks Siva!!! OM NAMAY SHIVAY 🙂
Pingback: Database Mirroring and Log Shipping vs Oracle Dataguard | Integrated Approach - Oracle
Can i take tail log backup if database datafile is currupted?
what is the current stauts of the database?
How an automatic failover will be implemented on master slave replication. My master slave replication is working properly but i did not getting information about how an application knows about failed master in case of primary master fails. and also tell me that how an slave server become master automatically.
I am using mysql database server.