Replication of non-clustered index from Publisher to Subscriber

Question

Hi,

We have replication set-up on sql server 2014. By default non-clustered indexes are not replicated. We are thinking to enable the same from the publisher properties.

Is it safe to perform this action keeping database is online ?

Answer 1

Hi @Srikanth Dusa ,

Yes, just set “Copy no clustered indexes” in the articles property to “true”, this will cause all no clustered indexes on the publisher to be included in the snapshot for delivery on the Subscribers. But additional subscriber non-clustered indexes don’t come without a cost. Like all SQL Server indexes, there is additional overhead for INSERT, UPDATE, DELETE, additional locks\blocking, and index maintenance to consider. If all indexes are not needed, alternatively, you can create selective indexes directly on a subscriber. It reduces administrative overhead to change the option directly in the replication settings for the article.

---

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

Which database is your concern? Publisher or subscriber?

I can't see that there would be any issue on the publisher. But depending on table sizes, workloads, there can certainly be an effect on the subscriber. Even more so if you have Standard Edition and indexes are created without the ONLINE option.

You may want to do this during a maintenance window and not in the middle of the day.

By the way, I guess the reason that NC indexes are not replicated is that often you want a different index structure on the subscriber.