Index statements on Transactional Replication

Question

I read that in Transaction replication, create/alter/drop index statements are not replicated. Is it true?

Answer 1

Since SQL Server 2005, replicating DDL statements became the default in transactional replication. You can, however, turn it off in the options for the publication if you really want to, but most don't.
That will though, only replicate indexes that are there to support constraints like primary key constraints or unique constraints.

Generally, indexes are separate objects and are not replicated.

That might seem odd at first, but it's often desirable. It is quite common to want different indexing at the source and at the subscriber. This is particularly the case where we are replicating to a location that we're then using as a source for analytics. We often want very different indexes for the analytic queries than we want on the source system.

There's also the practical issue that indexes might have key or included columns that are not being replicated. (Keep in mind that you can control which columns are replicated).

Answer 2

Hi，Chaitanya Kiran

SQL Server Replication does support schema changes, but not all of them.

You can refer to this article.

Please ensure you read the whole article, to be fully aware of what can be replicated, and under what circumstances.

Explicitly adding, dropping, or altering indexes is not supported. Indexes created implicitly for constraints (such as a primary key constraint) are supported.

Also attention to

Index changes are not propagated to Subscribers: if you drop a column at the Publisher and a dependent index is dropped, the index drop is not replicated. You should drop the index at the Subscriber before dropping the column at the Publisher, so that the column drop succeeds when it is replicated from the Publisher to the Subscriber. If synchronization fails because of an index at the Subscriber, manually drop the index and then rerun the Merge Agent.

Best regards

Mikey Qiao