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

sql365 21 Reputation points
2021-12-12T12:30:00.123+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,309 questions
{count} votes

Accepted answer
  1. CathyJi-MSFT 21,116 Reputation points Microsoft Vendor
    2021-12-13T01:33:34.227+00:00

    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".

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Shoeb Shabibi 0 Reputation points
    2021-12-12T12:54:12.653+00:00

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

    0 comments No comments