AG Rebuild Index

Question

In Availability Groups, If I run rebuild index job on primary replica, does SQL Server rebuild index on secondary replica also?

Answer 1

Yes. More precisely, the log records from the rebuild on the primary are applied on the secondary as well. Thus, sorting etc needed to do the rebuild on the primary does not occur on the secondary.

Answer 2

Thanks for your question. 

Since the primary replica in an Always On Availability Group is always the writeable replica, the ALTER INDEX command used to rebuild indexes should be executed on the primary replica. 

The secondary replicas are read-only replicas. Therefore, you need to rebuild the index on the primary replica and then synchronize the changes with the secondary replicas. 

Please have a look at below useful article about “Recommendations for Index Maintenance with AlwaysOn Availability Groups”

https://techcommunity.microsoft.com/t5/sql-server-support-blog/recommendations-for-index-maintenance-with-alwayson-availability/ba-p/318518

Hope this helps!

Answer 3

Hi @Chaitanya Kiran ,

In light of the question you asked yesterday, let me explain Alwayson.

AlwaysOn has to accomplish three things.

1. Log the data changes that occur on the primary replica.
2. Transfer these records to each secondary replica.
3. Complete the data changes on the secondary replicas as well.

For a database configured as AlwaysOn primary replica, SQL Server creates a worker thread called Log Scanner for it. This thread is responsible for reading log records from the log buffer or log file, packing them into log blocks, and sending them to each secondary replica. Thanks to its uninterrupted work, data changes on the primary replica can be continuously propagated to the secondary replica.

On the secondary replica, there are also two threads that perform the corresponding data update actions, Harden and Redo. The Harden thread writes the log blocks sent by the primary replica Log Scanner to the log file on the secondary replica's disk (a process known as "Hardening"). The redo thread, on the other hand, reads the log blocks from the disk and translates the log records into data modification operations on the database of the secondary copy.

AlwaysOn uses this mechanism to keep the replicas in sync with each other.

So the answer is yes.

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