Adding Foreign Keys & Indexes to Tables in Replicated Database

Question

Hi,

Running SQL Server 2008 R2 in a merge replication topology, publisher and distributor are on same machine.

I have been tasked with alleviating some performance issues in a production database. Some (but not all) of the target tables are included in replication. I would like to add foreign keys and indexes (apparently this was never pursued previously due to the potential of causing issues with the merge replication process). However I think this can be achieved with a clearer understanding of how FKs relate to the merge replication process, hence my post here.

I know I can set article properties to disable copying foreign key constraints, as well as non-clustered indexes but I need definitive answers to a few questions:

1. If I set the 'copy foreign key constraints' and 'copy non-clustered indexes' properties on the relevant merge articles to False, will this cause our snapshot to be invalidated, or cause our subscriptions to require re-initialization? I have examined SQL BOL but the descriptions on that page are not the same as the options on the screen so I want to be clear on this point.
2. After changing these properties on the relevant merge articles would best practice be to generate a new snapshot?
3. Would changing the relevant merge article properties cause the same behaviour on a subsequent sync to an existing subcription (FKs and NCIs on Publisher not copied to subscriber) as they would on a new subscription (FKs and NCIs on Publisher not copied to subscriber) after generating a new snapshot?
4. If I create a foreign key on a column in a replicated table that references a column in a non-replicated table (but use the NOT FOR REPLICATION keyword when creating the FK) will we have issues, or will the NOT FOR REPLICATION ensure we don't?
5. Some of the merge articles use join filters (to ensure that only detail records related to an 'eligible' master record are replicated). Will having FKs on the master table which relate to the child table, or vice-versa, cause issues?
6. Do I need to be concerned with merge article processing order? And if I am required to change this, do I need to worry about snapshot invalidation or subscription re-initialization?
7. Will adding foreign keys and indexes on the publication database cause an adverse impact on the performance of the merge replication process?

Apologies in advance for the litany of questions but as this is a production system I want to ensure I'm clear on the behavior as well as the issues I may encounter after making these changes (better to measure ten times and cut once as a wise man once said).

Thanks!

Answer 1

Hi @VDT-7677，

For the issue is complex and will affect your production system, I'd like to suggest you may open a ticket to Microsoft support and the engineers will help to resolve the issue professionally.

Services Hub (microsoft.com)

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.