if index creation happens on primary Server, how this change updates on SEcondary servers i.e having eith of HA/DR Clusters, Mirroing, Always On etc

Question

Hi Guys,

I wanted to know what happens if index is created on Large size table in VHDB, how will this index creation reflects on secondary server?

lets say there is any of HA/DR solutions like Clusters, Mirroring, always on, replication.

Thanks in Advance
sql365

Answer 1

Hi @sql365 ,

Always On group

You can add indexes online with SQL Server Enterprise Edition in SQL AG as shoebshabibi-3140 mentioned. Index created on AG primary replica will be updated on secondary replica automatically.

Replication

Indexes can be added at the Publisher or Subscribers with no special considerations for replication (be aware that indexes can affect performance). CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.

Refer to MS document.

Mirroring

EVERYTHING is mirrored include index. Index created in principal server will be mirrored to mirror server.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Provided it's enterprise edition, Indexes will be created online automatically on AG primary and secondary as well.