Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Not setting NOT FOR REPLICATION on subscriber foreign keys. Expand / Collapse
Author
Message
Posted Thursday, June 24, 2010 1:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 2:16 PM
Points: 150, Visits: 148
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
Post #942671
Posted Monday, July 5, 2010 5:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 5:06 PM
Points: 42, Visits: 272
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.
Post #947672
Posted Tuesday, July 6, 2010 5:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 2:16 PM
Points: 150, Visits: 148
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
Post #947827
Posted Tuesday, July 6, 2010 2:16 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028
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
Post #948177
Posted Monday, March 11, 2013 2:39 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 15, 2014 6:04 AM
Points: 91, Visits: 108
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.
Post #1429479
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse