CODEWITHSUNDEEP
sql-serverreplicationtransactional-replication
Mark
Mark

Reputation: 2063

SQL Server transactional replication cannot copy non-clustered index

As mentioned in the title, I have been trying to include the non-clustered index on my transactional replication.

When I created the publication, I have checked the option for "copy nonclustered index" in the [Set properties for all table articles]

But after I finished creating the publication, when I check the publication properties again, the option for "copy nonclustered index" is changed to the default of false. No matter how many times I tried to change it, it won't change.

My next try was to delete my VM and re-create the VM from the scratch. but it still won't allow me to copy the nonclustered index.

So then, I was thinking that maybe it was a bug with the SQL Server version that I'm using in my VM. So I tried to copy the whole database into my localhost, and the problem still occurs

Eventually I found someone said that

With the following code: the stored procedure in the snapshot will failed to be applied:

CREATE NONCLUSTERED INDEX [IX_TestTable] ON [dbo].#TestTable

But, changing the syntax slightly causes the stored procedure to create without issue:

ALTER TABLE dbo.#TestTable ADD CONSTRAINT IX_TestTable UNIQUE NONCLUSTERED ( ID )

But I can't afford to change my index / recreate my index in my production environment. Is there any solution for this?

what I'm trying to replicate is my whole database. including all of my tables and store procedures. so my tables are not temp table

Upvotes: 0

Views: 1358

Answers (1)

Benny
Benny

Reputation: 31

I'm stuck in the same situation SQL-Server standard 2017. I can see that the publication created idx-files already contain the information to create indices, but the subscriber ignores them. As far as I can see this problem is old as hell - fun fact if you use "Snapshot publication" instead of "Transactional publication" the setting "Copy non Clustered indexes = true" works. But for me I need the near time syncronity, so the snapshots don't work for me. So back to your problem: Currently (yes right now as I'm writing) I'll try to transfer the indices manually:

  • SourceDB->Tasks -> Generate Scripts -> Select only the Tables -> Next -> click Advanced
  • set everything to false except the Script indices
  • Save the script
  • Open the script and remove all "Create table"-entries -> bam you have your index-creation script.
  • Within your already created publication properties there is an entry "Snapshot". There you can put this script as to be run "after applying the snapshot, execute script"

Ohhh - right now - it creates the indices on its own. I've not installed my Post-Run-SQL-script, but the indices were created. I only can assume, that if on first hand the "Copy non Clustered indexes = true" was not set, it could not be set afterwards. After serveral mistakes I dropped the target database, the subscriber and the publication. I recreated the database. Created the Transactional publication (made sure the "Copy non Clustered indexes = true" was set while creating it), created the subscriber and waited. And what should I say, they are there :-) But yes if I open the publication again, the "true" is gone... so at least here is a bug I think!

Upvotes: 1

Related Questions