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.
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:
Table1from the Publication's Articles
Table1from the subscriber database
- Re-initialized and generated a new snapshot
- Then re-added
Table1with 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?