4

I create transactional replication ,but forget to include non clustered indexes and now my subscriber have only clustered indexes (primary key) and I want to transfer all nonclustered indexes to subscriber.Based on this answer http://www.sqlservercentral.com/Forums/FindPost1325516.aspx is good idea to script all non clustered indexes on publisher and then execute it on subscriber or is there another way which is better ?

1

This can be done.

But as far as i know, it will break the replication.You'll have to enable the option and re-initialize the subscriber with a new snapshot. Also, if you have non-clustered indexes with INCLUDED, you'll have to modify the definitions (.IDX files) manually to add INCLUDED to them.

You can enable the option where you can replicate the non clustered indexes as described here.

As mentioned:

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.

  • @KASQLDBA your answer is having improper info But as far as i know, it will break the replication. Creating nonclustered indexes on subscriber wont break replication. Have you tried it ? A pub & subscriber can have different indexes (not recommended) and you can have a scripted version of custom indexes that you can apply post snapshot. – Kin Shah Jun 21 '16 at 15:23

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.