November 16, 2010 at 6:50 am
Hi,
If i have a job that rebuild all the indexes, and it was in mirroring session.
Before doing the rebuild if "suspend" the mirroring, how the changes will be applied to mirror db, why we need to "SUSPEND" AND "RESUME"
thanks,
🙂
November 16, 2010 at 8:59 am
Why do you think you need to suspend mirroring before you rebuild indexes?
The Redneck DBA
November 16, 2010 at 10:25 am
no housekeeping is required on a mirrored database. Maintain the primary.
---------------------------------------------------------------------
November 16, 2010 at 12:30 pm
Just keep in mind thing like index rebuilds on large tables can cause a large amount of data to change, which in turn causes a large volume of data to be transferred across your network. On a decent LAN this may or may not be an issue, but keep it in mind if you are mirroring across a WAN or a slow network, or have a mirror server that is significantly slower than your principal...especially if you are mirroring in synchronous mode.
The Redneck DBA
November 16, 2010 at 4:31 pm
If your mirroing is setup in asynchronous mode, there is no reason to pause mirroring and resume it later.
However, if you are running in synchronous mode - you can pause mirroring (suspend) during the index rebuild operations and resume after it is completed. When you do this, mirroring will act as if it is running asynchronously until it has caught up.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 16, 2010 at 4:40 pm
Hmmm...did I misunderstand the question?
Be aware if you suspend the mirroring session, you will not be able to truncate the log of the primary .
---------------------------------------------------------------------
November 16, 2010 at 4:57 pm
SQL* (11/16/2010)
Hi,If i have a job that rebuild all the indexes, and it was in mirroring session.
Before doing the rebuild if "suspend" the mirroring, how the changes will be applied to mirror db, why we need to "SUSPEND" AND "RESUME"
thanks,
The changes will occur after you resume the mirror. The log will start to get replayed.
The approach you are attempting is actually a good idea. Just make sure your disk that holds the trans log is sized properly to handling it.
Why it is good is for performance. An index rebuild on a paused mirror will be faster than on an active mirror. And the replay of the log has less overhead.
November 16, 2010 at 11:10 pm
thanks geoff a,
now i have a clear picture,
🙂
November 17, 2010 at 6:16 am
Jeffrey Williams-493691 (11/16/2010)
If your mirroing is setup in asynchronous mode, there is no reason to pause mirroring and resume it later.However, if you are running in synchronous mode - you can pause mirroring (suspend) during the index rebuild operations and resume after it is completed. When you do this, mirroring will act as if it is running asynchronously until it has caught up.
But if you are keeping this mirror for disaster recovery I wouldn't recommend pausing while in synchrnous mode...every second it is left paused is a second of data you could lose if your primary explodes.
The Redneck DBA
November 17, 2010 at 6:18 am
Geoff A (11/16/2010)
Why it is good is for performance. An index rebuild on a paused mirror will be faster than on an active mirror. And the replay of the log has less overhead.
Interesting...I've not heard that before. Just curious...do you have any documentation on that?
The Redneck DBA
November 17, 2010 at 6:28 am
No Jason. I do not have an docs, but i have a lot of hands on experience.
pausing the mirror and resuming for a index rebuild was the way I solved a problem with performance on several mirrors at my last contract....
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply