Database Reference
In-Depth Information
4. By design of the database mirroring, the mirror database will be in a restoring state,
which prevents the log shipping backup jobs from backing up the transaction log on
the mirror database. This is to ensure that the mirroring database does not interfere
with log shipping backup patterns and when an automatic failover occurs the log
shipping can continue.
5. The interoperability between log shipping and database mirroring is supported and
provides an additional standby to the existing data platform.
6. The database mirroring session can run in any operating mode, synchronous
(transaction safety set to
FULL
) or asynchronous (transaction safety set to
OFF
).
7. Restore the backup of the Principal/Primary database with
NORECOVERY
onto the
mirror server instance, then configure and set up the database mirroring.
8. Set up the log shipping configuration on the principal database as the
primary database.
9. Create a file share on the entire server instance, which is part of the database
mirroring partner in addition to the log shipping secondary server.
10. Manually perform a database mirroring failover from the principal to the
mirror instance.
11. Now, set up the log shipping configuration on the new principal (previously mirror) as
the primary database by using the same database backup that was performed earlier.
12. Perform a database mirroring failback from the existing principal database to the
original principal server instance.
13. Configure the log shipping monitor server to monitor the log shipping pair.
This completes the steps to manage the log shipping in a database mirroring environment,
which runs in high-safety mode with automatic failover mode. This setup will enable extra
standby for an existing high-availability environment.
How it works...
The log shipping process is dependent on SQL Server Agent service. The primary server
instance executes the backup job to back up the transaction log on a primary database, and
then sends the same file to a backup folder-share, which is created as part of the prerequisite
steps. The principal database in a mirroring session can also act as the primary database in a
log shipping configuration or vice-versa.
Internally, for the log shipping process to prevent the spurious alerts, whenever a backup
job is executed on the mirror/primary database, the backup job logs a message to the
log_
shipping_monitor_history_detail
table in the
msdb
system database, which enables
the agent job to return a status of success.
Search WWH ::
Custom Search