maintenance of mirror database?

Question

maintenance of mirror database?

bodhilove

SSCarpal Tunnel

Points: 4035
More actions
October 1, 2008 at 11:27 pm

#194368

Hi Folks,
What maintenance should be done on a mirror database on a regular basis.. which would mean failover. For example.. is database defragmentation needed? Also is it ok to do trans log backups on a principal database? (for some reason mirroring requires database to be in full recovery)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Scott Ohar Hall of Fame Points: 3607 More actions · Answer 1

bodhilove (10/1/2008)
Hi Folks,
What maintenance should be done on a mirror database on a regular basis.. which would mean failover. For example.. is database defragmentation needed? Also is it ok to do trans log backups on a principal database? (for some reason mirroring requires database to be in full recovery)

Here is part of my Daily process, I use asynchronous due to the mirror is to DR site.

Nightly Backups - of the Principal

Monthly I do a Fail Over which is switching the mirror to synchronous.

If you need to do any type of shrinking see this link.

http://support.microsoft.com/kb/937531

Pam Brisjar SSChampion Points: 12094 More actions · Answer 2

The information in the t-log is what is used to keep the mirror up to date. Yes, you need to run t-log backups on the Principal.

The only "maintenance" you need to do on the mirror is making sure the server is running fine and that you have adequate resources. The mirror is non-alterable by anything other than the mirroring process itself.

bodhilove SSCarpal Tunnel Points: 4035 More actions · Answer 3

thanks Guys,

doesn't the principal use the trans log to send data to the mirror. If that is the case if I do a t-log backup and truncate the log I would think mirroring would be effected. Could I have some thoughts on that?

Pam Brisjar SSChampion Points: 12094 More actions · Answer 4

Well, for one thing, you should never do anything to the t-log other than to back it up.

SQL Server is smart enough to keep track of what transactions have been sent to the mirror and which haven't. The only ones that will be cleared from the t-log with a backup operation are the ones that have been sent.

I strongly suggest you read through all the BOL documentation on mirroring. This is all covered there.

bodhilove SSCarpal Tunnel Points: 4035 More actions · Answer 5

thanks Pam... I will read up on that - I really appreciate your help!