Should we create an index maintenance such as Rebuild / Reorganize in the MIRRORING Server ?

Question

Should we create an index maintenance such as Rebuild / Reorganize in the MIRRORING Server ?

WhiteLotus

SSChampion

Points: 12122
More actions
October 6, 2014 at 5:18 pm

#299859

Should we create an index maintenance such as Rebuild / Reorganize in the MIRRORING Server ?
I already have the index maintenance in PRINCIPAL server .
Please kindly advise .
Many Thanks !!
Cheers

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

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

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

The current secondary does not need a maintenance plan - it is not updateable and attempting to do index maintenance would require updates to be done.

The results of any index maintenance on the primary are sent to the secondary anyway so there is no need to worry about it.

However, when the roles of the primary and secondary database change, you may need to change the maintenance done on the what was the secondary (and is now the primary).

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Agree with HappyCat. I'd set up the same jobs on both servers, but disable the jobs on the secondary. If you failover, you can enable them.

WhiteLotus SSChampion Points: 12122 More actions · Answer 3

Steve Jones - SSC Editor (10/6/2014)
Agree with HappyCat. I'd set up the same jobs on both servers, but disable the jobs on the secondary. If you failover, you can enable them.

thanks a lot for both of u !!

your suggestion is very helpful

happycat59 One Orange Chip Points: 29389 More actions · Answer 4

Steve Jones - SSC Editor (10/6/2014)
Agree with HappyCat. I'd set up the same jobs on both servers, but disable the jobs on the secondary. If you failover, you can enable them.

Rather than just leaving the job disabled, I would include a step that checks whether the database is the current primary and conditionally do the index maintenance. Not only do you end up with mirroring failing over automatically, your index maintenance is also automatic. Otherwise you end up with yet another thing to remember to do.

Gazareth One Orange Chip Points: 27737 More actions · Answer 5

happycat59 (10/30/2014)
Steve Jones - SSC Editor (10/6/2014)
Agree with HappyCat. I'd set up the same jobs on both servers, but disable the jobs on the secondary. If you failover, you can enable them.
Rather than just leaving the job disabled, I would include a step that checks whether the database is the current primary and conditionally do the index maintenance. Not only do you end up with mirroring failing over automatically, your index maintenance is also automatic. Otherwise you end up with yet another thing to remember to do.

+1