I have an instance of SQL Server 2014 running replication. I have selected the option to copy nonclustered indexes on all tables:

copy nonclustered indexes setting

When this was first enabled, all existing nonclustered indexes were replicated, but since then no new nonclustered indexes have been replicated.

Have I missed a config somewhere? Or is it necessary to manually replicate new nonclustered indexes each time they are created?

The replicate_ddl option (for publishing schema changes) is already enabled on the publication subscription: (Publication Properties -> Subscription Options)

enter image description here

The schema_option value on all tables is 0x00000000080350DF which breaks down to (if I am not mistaken):

enter image description here

Which indicates that the option to Generate corresponding nonclustered indexes (0x40) is enabled

  • 1
    It's been a while since I've worked with replication, but i think there's an option to have it replicate ddl changes. Is that turned on? – Ben Thul Feb 11 '20 at 13:53
  • @BenThul - thanks for the idea, it seemed like it was going to be a winner... unfortunately replicate_ddl option is already turned on :( I have updated my question with that info – Jimbo Feb 12 '20 at 4:55
  • What is the value for sysarticles.schema_option as compared with the breakdown of the bitmask in docs.microsoft.com/en-us/sql/relational-databases/… (look for @schema_option in there)? – Ben Thul Feb 12 '20 at 5:00
  • @BenThul - I have updated my question with that info. I hope I understood your question correctly :) – Jimbo Feb 12 '20 at 9:09

Your Answer

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

Browse other questions tagged or ask your own question.