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. It 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.In case of a failover, we have to manually bring one of the secondaries online and this may increase the data recovery time.
Database mirroring is preferable to log shipping in most cases, although log shipping does have the following advantages:
- it provides backup files as part of the process
- multiple secondaries are supported
- it is possible to introduce a fixed delay when applying logs to allow the secondary to be used for recovering from user error.
- it work in low bandwidth
- the secondary database can be in Stand-By mode, which can be used for reporting purposes
More information about both technologies is available in SQL Server Books Online in the topics "Understanding Log Shipping" and "Overview of Database Mirroring".
