Adding FK Index to existing table in Merge Replication Topology

Question

I have a table that has grown quite large that we are replicating to about 120 subscribers. A FK on that table does not have an index and when I ran an Execution Plan on a query that was causing issues it had this to say -->

/*
Missing Index Details from CaseNotesTimeoutQuerys.sql - mylocal\sqlexpress.MATRIX (WWCARES\pschaller (54))
The Query Processor estimates that implementing the following index could improve the query cost by 99.5556%.
*/

/*
USE [MATRIX]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[tblCaseNotes] ([PersonID])

GO
*/

I would like to add this but I am afraid it will FORCE a reinitialization. Can anyone verify or validate my concerns? Does it even work that way or would I need to run the script on each subscriber?

Any insight would be appreciated.

Answer 1

Adding an index shouldn't change the table to cause a reinitialise, but I suggest you set up a test version to make sure.

Answer 2

Adding a FK constraint on a field WILL NOT force the reinitialisation of the subscription. This is the complete code that we use when (1) adding a filed to a table and (2) defining this field as a FK:

use myDb

alter table Tbl_myTable
    add 
    id_myOtherTable uniqueIdentifier Null
go
alter table Tbl_myTable
    add 
        constraint Tbl_myTable_myOtherTableP foreign key(id_myOthertable)
        references dbo.tbl_myOtherTable (id_myOtherTable)
go

These instructions (adding a field, adding an FK constraint) are replicated to subscribing databases without reinitialisation. In case you do not have to add the field, you must imperatively check that the constraint will be valid on all databases. If, for a reason or another, subscriber (s) does not respect the constraint set on publisher (p), then the constraint will not be propagated, and subscription will stop. You'll then have to arrange manually data on (s) so that it accepts the constraint propagated from (p)