0

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

  • My VM SQL Server edition: SQL Server 2012 Standard edition
  • My localhost edition : SQL Server 2019 Developer edition

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?

  • Update

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

Mark
  • 2,041
  • 2
  • 18
  • 35
  • 1
    you're trying to create a NC index on a temp table. temp tables do not get replicated. – Mitch Wheat May 18 '21 at 02:15
  • hi @MitchWheat sorry, its just the answer that I copied. its not my table. my table is a normal table, not a temptable. so do you mean that "that" answer does not applied into my situation? because my table is not a temp table? – Mark May 18 '21 at 02:57

1 Answers1

1

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!

Benny
  • 21
  • 1