Pause Mirroring to Speed up Large Data Update

Database mirroring transports the changes in the production database to a mirror database, either synchronously or asynchronously. Asynchronous database mirroring is supported only by SQL Server Enterprise edition. During large data update, the mirror commit overhead of a Synchronous mirroring session becomes significant. In order to speed up your data update process, you can change the operating mode to Asynchronous, if your sql server is Enterprise edition. But if it's Standard edition, you can Pause the mirror session. During bottlenecks, pausing can be useful to improve performance on the principal server. Below is a demonstration.

1. Pause the mirroring session.

 2. In “Database Mirroring Monitor”, also shows the mirroring session as Suspended.

3. Run some loading on the exposed principal database, to fill up the Unsent Log Queue:
SET NOCOUNT ON;
WHILE 1 = 1
BEGIN
        INSERT Table_1 VALUES (99, 'Test')
    DELETE Table_1 WHERE pk = 99
END

4. In this example, I accumulate 14Gb of unsent transaction log.

5. Now “Resume” the mirroring session.

6. As there’s quite a lot of unsent log in the queue, it takes time to synchronize.

7. Even the synchronous (high safety mode) mirroring session is synchronizing, you can still commit any new transactions WITHOUT any delay!

You should resume a paused session as soon as possible, because as long as a database mirroring session remains paused, the transaction log can NOT be truncated.