0

In SQL server Database mirroring, I need to bring down the Mirror server for maintenance for 2 hours. However If I have a transaction log backup on the Primary database running every 15 mins, Will database mirroring still work, when the mirror server comes back up? Or will the LSN be out of sync due transaction being truncated after log backup?

Assuming I didn't pause the mirroring

waazi
  • 25
  • 1
  • 6

1 Answers1

0

However If I have a transaction log backup on the Primary database running every 15 mins, Will database mirroring still work, when the mirror server comes back up? Or will the LSN be out of sync due transaction being truncated after log backup?

Transaction log backup on principal server would NOT affect database mirroring in any way. As a fact it is recommended to take frequent transaction log backup for database which has participated in mirroring.

Dont confuse it with log shipping which involves taking transaction log backup and moving it to secondary server. In mirroring there is NO transaction log backup but Movement of Log information from principal to mirror. This would not be affected in any way by transaction log backup.

LSN comes into picture when you are taking transaction log backup, which you do in log shipping. In mirroring we just have transfer of log information

Shanky
  • 607
  • 4
  • 23
  • Thanks, Here is where i am still confused. My understanding is when a transaction log back up is taken, it is truncated to free up the space. Now in my scenario, there will be multiple transaction log taken on primary database before the mirror server is up. – waazi Feb 12 '15 at 15:04
  • Thanks, Here is where i am still confused. My understanding is when a transaction log back up is taken, it is truncated to free up the space. Now in my scenario, there will be multiple transaction log taken on primary database when the mirror server is down. so you are saying, those transaction log be still available(or not be in transaction log backup during mirror server is down) and not be truncated till it is mirrored? If so, does the transaction log backup size be ZERO during the time the mirror server is down? – waazi Feb 12 '15 at 15:13
  • Transaction Log backup JUST truncates the logs making them reusable it wont free space. Space is freed by shrinking using dbcc shrinkfile. There is no issue if you take multiple log backups it wont affect mirroring. Before going in maintenance mode mke sure you pause mirroring please read affects of pausing https://msdn.microsoft.com/en-us/library/ms190664.aspx – Shanky Feb 12 '15 at 15:15
  • sorry when i meant free up the space, i meant truncate to free up the inactive log space. – waazi Feb 12 '15 at 15:26
  • See commit on mirror is different its actual commit of transaction and transaction log backup is making that information permanent in backup file and making that space reusable for other transaction. – Shanky Feb 12 '15 at 15:35
  • Thanks, could you please elaborate or share a LINK on, commit on mirror vs actual commit on transaction and affect of transaction log back on each – waazi Feb 12 '15 at 21:15
  • Please read https://msdn.microsoft.com/en-us/library/dd207006.aspx and read table 9 in https://technet.microsoft.com/en-us/library/cc917680.aspx – Shanky Feb 13 '15 at 07:04