Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have to do some maintenance on a secondary node of a mirror. Since this is the result of the site becoming slow while the mirror re-established after a brief network outage I want to be safe and disable the mirror, then re-enable it after I'm done poking at the offending server.

The plan I have right now is to:

  1. Disable the mirror
  2. Do my potentially dangerous troubleshooting
  3. Restore all missed transaction logs
  4. Re-establish the mirror

Is this the safest way to go about breaking/re-establishing a mirror? Are there any gotcha's I should look out for?

share|improve this question

1 Answer 1

up vote 10 down vote accepted

It sounds as if you simply want to pause and resume your mirror. Have you seen this link yet? http://msdn.microsoft.com/en-us/library/ms190664.aspx

The gotcha with this would be the length of time you expect to have the mirror paused. If you take too long then the principal server could experience a large amount of transaction log growth. if you do not expect to be paused for a long period of time, then you should be fine.

If you feel that you must break the mirror and resume later, then you should do the following:

Break the mirror using this against the principal: ALTER DATABASE SET PARTNER OFF

If you are concerned that your maintenance will take a long time, then you should perform regular transaction log backups, so as to make certain the log does not grow out of control and fill up your disks. however, the more trx log backups you have, the more you need to recover to the mirror later.

After you have restored the trx logs, you should be able to get mirroring running again.

Another option would be to reinitialize everything, but I don't know how big of a database this is, it may be too much for you to restore, so trx logs may be the better option.

This link explains it best: http://msdn.microsoft.com/en-us/library/ms189112.aspx

And this link covers more topics: http://msdn.microsoft.com/en-us/library/ms175059.aspx

HTH

share|improve this answer
    
Thanks, the server would be down for an unknown amount of time, and has relatively limited space - say an hour's worth before it becomes an issue. Which is why I think shutting down the mirror is the better option –  Zypher Mar 21 '11 at 3:22
    
i updated my answer and included some additional links –  SQLRockstar Mar 21 '11 at 4:09
    
yep, we already have a job backing up transaction logs on a regular schedule so that makes that part easy –  Zypher Mar 21 '11 at 13:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.