2

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?

Greg Hendershott
  • 816
  • 1
  • 8
  • 12

2 Answers2

2

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.

Remus Rusanu
  • 8,283
  • 1
  • 21
  • 23
  • 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 08 '11 at 18:36
1

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.

Sankar Reddy
  • 1,374
  • 8
  • 8
  • 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 02:39