SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Not setting NOT FOR REPLICATION on subscriber foreign keys.


Not setting NOT FOR REPLICATION on subscriber foreign keys.

Author
Message
Tim Januario-145496
Tim Januario-145496
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 185
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
henners72
henners72
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 313
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
Tim Januario-145496
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 185
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
noeld
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10136 Visits: 2048
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" Smile


* Noel
Mike Arney
Mike Arney
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 109
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search