0

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

Jimbo
  • 309
  • 1
  • 5
  • 15
  • 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 04:55
  • What is the value for sysarticles.schema_option as compared with the breakdown of the bitmask in https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addarticle-transact-sql?view=sql-server-ver15 (look for @schema_option in there)? – Ben Thul Feb 12 '20 at 05:00
  • @BenThul - I have updated my question with that info. I hope I understood your question correctly :) – Jimbo Feb 12 '20 at 09:09

0 Answers0