This seems really odd to me, as I've used SQL Server's replication feature for a while now and have never seen this occur before.
I'm using One-Directional Transactional Replication and I have ServerA – the Publisher, and ServerB the Subscriber setup. I'm only replicating about 20 tables or so total.
In ServerA, Table1
has a couple of nonclustered indexes on it (including the primary key itself), and a separate clustered index. When the snapshot is generated to create Table1
on ServerB, all of it's indexes (even the clustered index) are created as nonclustered on the subscriber database in ServerB.
I even went so far as to take the following steps:
- Removed
Table1
from the Publication's Articles - Dropped
Table1
from the subscriber database - Re-initialized and generated a new snapshot
- Then re-added
Table1
with the Article Properties set to false for "Copy nonclustered indexes" - Then re-initialized and generated a new snapshot again
And it still recreated Table1
on the subscriber database with it's clustered index as a nonclustered index. It did not copy any of its actual nonclustered indexes except the primary key (which again is defined as a nonclustered index in the publisher's database).
Here's some screenshots to show it (sorry for all the obfuscation):
Table1 in Publisher vs Subscriber Database:
Publisher Table1's sys.Indexes:
[
Subscriber Table1's sys.Indexes:
Publication's Article Properties for Table1:
What the heck is going on?
Best Answer
maybe it's a bug in the SSMS; try to scritp the @schema_option and verity the proper value based on the list here:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addarticle-transact-sql?view=sql-server-ver15