2

I would like to add non-clustered indexes to my Publication for Transactional Replication. I am aware that replication works by reading the transaction log for the published database and sends the updates to the subscribers. My question is: does transaction log include updates to indexes and therefore would this double the updates sent to the subscriber? Will it send updates to subscriber when I run Index maintenance ie: Rebuilding/Reorganizing indexes?

If so, then perhaps creating non-clustered indexes as part of post snapshot script application is a better way. If not, then I prefer to keep it as part of Publication.

  • Ugh, just as I wrote the question I realized I could test this. I published the indexes in a test environment, reorganized the index while watching synchronization status. No transactions were sent to subscribers. Question answered! – Dina Sep 21 '17 at 20:09
  • 1
    Feel free to post your 'comment' as an answer and then accept it - Can I answer my own question? – Scott Hodgin Sep 21 '17 at 20:11
  • I am hoping someone will have a more technical explanation of how replication & transaction log interaction works in general - as that may be much more beneficial. I am still researching and will do this myself as I dig up the answers (unless other deadlines get in the way...sigh). – Dina Sep 21 '17 at 20:14
1

@Dina I would still prefer that you write an answer. This was too long to put in comments.

From my experience, I agree with your statement no transaction were sent to subscribers but log reader still have to read every entry in the transaction log. Does not matter if that log entry is being replicated or not. If you are rebuilding large indexes with high number of maxdop option your replication latency can increase very quickly. I use to pause in between index rebuilds for the logreader to catch up so I do not exceed latency SLA.

Below comments are copy pasted from 2 articles I mentioned, which are relevant to your question.

Ref:

Frequently Asked Questions for Replication Administrators

Performance Tuning SQL Server Transactional Replication: A Checklist by Kendra Little

How do I rebuild indexes or tables in replicated databases?

There are a variety of mechanisms for rebuilding indexes. They can all be used with no special considerations for replication, with the following exception: primary keys are required on tables in transactional publications, so you cannot drop and recreate primary keys on these tables.

How do I add or change indexes on publication and subscription databases?

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.

Are You replicating non-clustered indexes blindly?

It’s very easy to set up transactional replication and send all the indexes over to the subscriber: you just set “Copy nonclustered indexes” in the articles property to “true”. But you’re only required to replicate the Primary Key and unique indexes. There’s two big problems with replicating all the nonclustered indexes:

  • It can make re-initialization slower. By default the subscriber will have objects created, bulk load the articles, create “extra” nonclustered indexes, then “catch up” on any changes that came in after the snapshot was pushed. You definitely want to make sure that all “extra” nonclustered indexes are disabled or don’t exist while that bulk load is happening. But if a lot may have changed since the snapshot ran, you may not want the indexes to be created until the very end, anyway. Handling the nonclustered index creation outside of replication gives you that flexibility.
  • It’s very rare for the exact same queries to run on the publisher and subscriber. You usually want nonclustered indexes that are specific to the workload on the subscriber, anyway.

Identify the “extra” nonclustered indexes specific to the queries that run on the subscriber. Script them out, check them into your source control, and have a process to deploy them whenever replication needs to be re-initialized.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.