Index rebuild job on primary replica

Chaitanya Kiran 676 Reputation points
2023-03-05T13:20:58.7533333+00:00

I have a rebuild index job on primary replica. When this job runs on primary replica, it rebuilds indexes on availability group databases. So, my question is, will the indexes on secondary replica AG databases also get rebuilt?

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,596 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 100.8K Reputation points MVP
    2023-03-05T13:33:26.44+00:00
    0 comments No comments

  2. Seeya Xi-MSFT 16,436 Reputation points
    2023-03-06T02:07:46.1066667+00:00

    Hi @Chaitanya Kiran,

    The behavior of index rebuild job on secondary replicas of an Availability Group (AG) depends on the configuration of the AG and the type of index rebuild operation you are performing.

    If the AG is configured with synchronous-commit mode and the index rebuild job is performed on the primary replica, then the indexes on the secondary replicas will also get rebuilt in real-time as the changes are applied to the primary replica. This is because in synchronous-commit mode, all transactions must be committed on the primary and secondary replicas before they can be considered committed.

    However, if the AG is configured with asynchronous-commit mode, then the indexes on the secondary replicas may not get rebuilt in real-time. This is because asynchronous-commit mode allows the secondary replicas to lag behind the primary replica in terms of transaction commits. In this case, the indexes on the secondary replicas will eventually get rebuilt, but it may take some time for the changes to propagate to the secondary replicas.

    Also noticed that you mentioned the same question before. So I'll add another answer as well.

    Best regards,

    Seeya


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

    0 comments No comments