Jared Poché, MCSE, MCDBA
SQL Server Sr. Support Escalation Engineer
Product Support Services (PSS) – Charlotte, NC
— updated 2/24/2019
One of the advantages of replication is that subscribing servers can be used for reporting thereby offloading RO query activity from the Publisher. Recently I worked on a performance case on such a replication subscriber, which would have been prevented with a simple change to the article properties for this publication. We discovered the nonclustered indexes were missing from the subscriber.
Indexes are key to the performance of SQL Server, but only clustered key indexes are replicated by default. Unique constraints are replicated by default, so their indexes will also be created on subscribers, but nonclustered indexes are not replicated by default. While setting up transaction replication on a database, we can change the default settings for articles on the Articles window to include nonclustered indexes.
Once the necessary tables and other articles have been selected, choose Article Properties and Set Properties of All Table Articles.
This page shows us a number of settings for indexes, constraints, and so on. Find the Copy Nonclustered Indexes setting, set this option to true, and click OK. This will cause all nonclustered indexes on the publisher to be included in the snapshot for delivery on the Subscribers.
Changing the article options will effectively create indexes on all subscribers with only a few clicks. Also, if a new snapshot is taken for the publication and applied to the subscribers, any indexes created directly on those subscribers will be removed. If all indexes are not needed, alternatively, you can create selective indexes directly on a subscriber. However, it reduces administrative overhead to change the option directly in the replication settings for the article.
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. Add only the non-clustered indexes supporting the business needs of the Subscriber. If all indexes are needed, perhaps solution based on AlwaysOn Availability Groups is a better fit.