Sql-server – SQL Server 2012 Transaction Log needed to create database mirror

mirroringsql serversql-server-2012transaction-log

There's a somewhat lengthy discussion of Transaction Logs in Microsoft's instructions for setting up a database mirror. We've never bothered with Transaction Logs in the past; do we really need to now?

Isn't the Transaction Log effectively an incremental backup that follows a full backup?

Unless the database drive fails while building the mirror, won't the mirror get from the primary all of the events that occurred while the backup file was being copied and restored to the mirror server?

It seems to me that the full backup alone might be slower, but it would be simpler.

Additional information

When building a mirror, Microsoft's documentation instructs us to restore the last full backup (plus the last differential, if there is one) and all subsequent Transaction Logs.

Unless one puts the database in single-user mode while building the mirror, there will be at least a small activity gap between the last data restored and the time the mirror goes live.

Will the mirror fill in that gap (as it does when it temporarily goes off-line)?

There's a caveat in the instructions if the database path is different on the mirror server: we cannot "add" TLs. The activity gap could be quite big if one had to skip several TLs.

That's what I meant above: wouldn't it be simpler to ignore the Transaction Logs when building the mirror, and just let the mirror fill in the gap?

Best Answer

The short answer to your first question is yes, your database must be in full recovery mode in order to use the database mirroring feature:

The database must use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, bulk operations are always fully logged for a mirrored database. For information about recovery models, see Recovery Models (SQL Server).

Prerequisites, Restrictions, and Recommendations for Database Mirroring

In a database mirroring scenario, every update to a database, the principal database, is immediately reproduced in a separate, full copy of the database, the mirror database. The principal server instance sends each log record immediately to the mirror server instance which applies the incoming log records to the mirror database, continually rolling it forward. For more information, see Database Mirroring.

The Transaction Log (SQL Server)

To answer your second (and third) question - that is an over simplification but you could view it like that. I would highly recommend reading about the Transaction Log in more detail using the link I have provided above. The Transaction Log is used for much more than just synchronising two databases - it is also used to implement point-in-time recovery. Try to understand how the transaction log behaves under each of the different recovery modes - this will improve your understanding of why full logging is required in order to implement high-availability on SQL Server.

The transaction log supports the following operations:

  • Individual transaction recovery.
  • Recovery of all incomplete transactions when SQL Server is started.
  • Rolling a restored database, file, filegroup, or page forward to the point of failure.
  • Supporting transactional replication.
  • Supporting high availability and disaster recovery solutions: Always On availability groups, database mirroring, and log shipping.

EDIT

Based on the additional information that you added, the answer is no. You cannot ignore the transaction log when setting up database mirroring - the mirror database must be an exact copy of the source before you can start. Once it has been established, then yes it will fill in the gaps - but that only works after you have begun the mirror. I normally enable mirrors during non-business hours, do a full backup and take the database offline while I copy the backup to the mirror server.

Related Question