Steve Fenton

SQL Server replication and non-clustered indexes

If you want non-clustered indexes to be copied to your replication slaves (you probably do) you simply adjust the setting that you’ll find here…

SQL -> Replication -> Local Publications -> Publication Name -> Properties -> Articles -> Article Properties -> Copy nonclustered indexes

When you set this to “True”, the indexes will be copied.

Watch out for the following surprise that SQL Server has in store though!

If you select “Set Properties of All Table Articles” when opening up the properties for an article:

Article Properties

You may see that the “Copy nonclustered indexes” property is shown as “False”, like this:

Properties of All Tables

But if you check each table laboriously individually, you’ll find that they are actually all “True”…

Highlighted Table Properties

So before you go all snapshotting, double-check whether your setting may already be true.

Written by Steve Fenton on