Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

When using MS SQL mirroring, changed rows on the principal are pushed to the mirror.

What I imagine/guess is that the data itself is pushed over, but not indexes. (I imagine this because I think of indexes as a secondary entity that exists to improve performance, not the data per se.)

And so for example after reorganizing or rebuilding the indexes on the principal, I imagine this would not be pushed to the mirror. Good news is, it doesn't cause a surge in mirror traffic. Bad news is, the indexes must also be reorgged or rebuilt on the mirror (if desired).

Is this correct -- are indexes part of what is pushed to the mirror, or not?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

EVERYTHING is mirrored. The mirror is a physical copy of the principal. All operations are reflected on the mirror: a reorganize will reorganize the mirror identically, a rebuild will result in a rebuild on the mirror, a rollback will rollback on mirror.

share|improve this answer
    
Being nit´picky to the OP. he is not pushing rows that change, put EXTENTS that change - database file blocks. Those are synchronized. –  TomTom May 8 '11 at 18:36

Just to complement the Remus excellent answer, the way mirroring works is by copying the transaction log records from the principal and replaying on the mirror. So anything that is logged into the transaction log on the principal is replayed on the mirror.

share|improve this answer
    
My (maybe naive) understanding of transaction log files is that the transactions are operations like INSERT, UPDATE, DELETE. I think of mirroring as a kind of "realtime streaming backup and recovery". So that's why I guessed that INSERT/UPDATE/DELETE trx would be mirrored, but not indexes -- because indexes are secondary, derivative structures (deleting an index loses no data). The index change wouldn't be transferred, instead the index on the mirror would be updated by the mirror as a result of the trx being pushed over. But I guess I'm wrong? –  Greg Hendershott May 10 '11 at 2:39

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.