Not setting NOT FOR REPLICATION on subscriber foreign keys.

Question

Not setting NOT FOR REPLICATION on subscriber foreign keys.

Tim Januario-145496

Ten Centuries

Points: 1391
More actions
June 24, 2010 at 1:38 pm

#223188

So it seems that the default behavior for transaction replication is to mark several things as NOT FOR REPLICATION. This includes foreign key constraints. However, by doing this, the foreign key is marked as is_not_trusted in sys.foreign_keys.
From my understanding, the query optimizer uses foreign keys during generation of query plans which isn't done when the foreign key isn't trusted (assumedly because referential integrity cannot be guaranteed).
I'm trying to get the subscription to not mark the foreign keys as NOT FOR REPLICATION by modifying the schema options that are set during sp_addarticle to not include 0x20000, which does not seem to work. And my search on the internet is not coming up with any solutions, possibly because a search for NOT NOT FOR REPLICATION (or any other negation of the word NOT) doesn't really add anything to the search at all or because nobody out there doing replication thinks this is a good idea.
I'm looking for some input on the subject. Does anybody have either a qualified view point that says that this is a bad idea or an idea of what I may be doing incorrectly? Just in case somebody is interested, the schema option that I am passing in for tables is 0x0000004448DC7FDF which results in the following (un)bitmask used during sp_addarticle:
0x0000000000000001
0x0000000000000002
0x0000000000000004
0x0000000000000008
0x0000000000000010
0x0000000000000040
0x0000000000000080
0x0000000000000100
0x0000000000000200
0x0000000000000400
0x0000000000000800
0x0000000000001000
0x0000000000002000
0x0000000000004000
0x0000000000040000
0x0000000000080000
0x0000000000100000
0x0000000000400000
0x0000000000800000
0x0000000008000000
0x0000000040000000
Any help would be greatly appreciated
Tim Januario

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

henners72 SSC Eights! Points: 934 More actions · Answer 1

Hi I havn't messed around with transactional replication on 08 and was unaware that FK constraints were used by query optimiser, I will look into that. But the easiest way to solve your problem is to drop all the constraints and recreate them, this should not have any affect on the replication, because it is transactional and unidirectional. Just make sure you stop the replication agent prior to the update.

Tim Januario-145496 Ten Centuries Points: 1391 More actions · Answer 2

Hello Henners,

I was also unaware until recently, and am not sure how much it is used by the optimizer (the only thing I can think of that this would allow would be to determine if a relationship is one to one that a seek could stop after a single record is found). I just found it to be an iteresting fact. It also seems that changing the options on replication doesn't work as advertised because regardless of what I do, I can't turn on the subscriber without the foreign keys getting set to NOT FOR REPLICATION. This is regardless of how replication is set up.

Thanks for your reply. I am thinking that recreating the foreign keys is my only option. It just seems that this should be unnecessary since the foreign keys are valid until replication is activated and then they are all marked as not trusted.

Does anybody have any idea of how how optimization is performed on foreign keys? It seems to me that maybe it doesn't really matter enough to really care about it. If simply replicating a database caused a significant performance hit, that would seem to make replication useless.

Tim Januario

noeld SSC Guru Points: 96590 More actions · Answer 3

here is one example of how the Query optimizer can use FK metadata.

Now on to your non-trusted issue.

The reason replication marks them as non-trusted is because you could potentially use multiple subscription streams which do not guarantee order.

Secondly FK per se is not that big a deal for a subscriber that is only going to read, as long as you have indexes covering your joins you should be fine and that is not the "end-of-the-world" 🙂

* Noel

Mike Arney SSChasing Mays Points: 617 More actions · Answer 4

If you specify @sync_type='none' in sp_addsubscription, it seems SQL Server does not call sp_MSrepl_setNFR and mess up your foreign keys. This comes up in a different context (replicating to Sybase) in this forum post: http://social.msdn.microsoft.com/Forums/en-IE/sqlreplication/thread/e1743eed-2a5a-450c-843b-75c420909ae5.