| Groups | Blog | Home |
sql server replication : RI and replication
We have a database where consistency is a must, and we use referential
constraints extensively. We are finding a lot of problems in setting up replication since we are replicating only a few tables, but all of them are related to other tables that are not being replicated. So far we have created procs that will drop fks before applying the initial snaphsot, and that will create them again afterwards, to avoid problems when dropping/deleting the replicated tables. But anytime we fix a problem another one comes up. Is there any document out there that explains the implications of using RI and transactional replication? We have 1 central publisher and several subscribers with updatable subscriptions. Regards, Antonio.
Hi Antonio,
It's my pleasure to further assist you with the replication issue on RI. It is pointed out in another post that we can use NOT FOR REPLICATION option to avoid the RI constraints. I'd appreciate it so much that you concentrate and sublime the issue on a general basis. First of all, on the basis of Replication Architecture, it is a consideration that you apply the RI on the Central Publisher, instead of on the Subscribers, so that the central publisher can control the RI and push the suitable publications to the subscribers. Second, if the RI (such as FKs) on the subscribers prevents the replication, you can just disable them rather than drop and re-create them. I believe it's save your time and easier to be implemented: To disable a foreign key constraint for replication: (1) In your database diagram, right-click the table containing the foreign key, then select Properties from the shortcut menu. (2) Choose the Relationships tab. (3) Select the relationship from the Selected relationship list. (4) Clear the Enable relationship for replication check box. After you add or modify data, you should select this option if you want to ensure the constraint applies to subsequent data modifications. #Note# If you plan to use triggers to implement database operations, you must disable foreign key constraints in order for the trigger to run. Furthermore, it is also recommended that you use the "NOT FOR REPLICATION" option when you CREATE a table or ALTER it later: ----------------------- FOREIGN KEY REFERENCES ref_table [ ( ref_column ) ] NOT FOR REPLICATION ----------------------- The NOT FOR REPLICATION clause means the constraint is enforced on user modifications but not on the replication process. In this way, it prevents the check the Foreign Key constraint from being enforced during the distribution process used by replication. When tables are subscribers to a replication publication, do not update the subscription table directly, instead update the publishing table, and let replication distribute the data back to the subscribing table. (That's why I suggest you applying the RI on the Publisher at the beginning of this message) For more information, you can reference the following articles: http://msdn.microsoft.com/library/default.asp?url=/library/en- us/vdbt7/html/dvtskworkingwithrelationships.asp http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/repldata_6xbi.asp Antonio, does this answer your question? Please apply my suggestions above and let me know if this helps resolve your problem. If there is anything more I can do to assist you, please feel free to post it in the group Best regards, Billy Yao Microsoft Online Support ---------------------------------------------------- Get Secure! - www.microsoft.com/security This posting is provided "as is" with no warranties and confers no rights. Please reply to newsgroups only. Thanks.
Hi Billy,
I've read your post below, but I must admit to still being confused. I am busy designing a replication topology for a client. the db makes extensive use of RI. We are uisng autoincrement IDENTITY keys on mamny tables in the db. The "master" db will be located at the Head Office, abnd it will be replicated to five branches around the country. Not all of the tables will be replicated, and those which are replicated will not be in a single publication. The tables may be divided into classes. Class one tables: Typically look=up tables which define classification, group and sub-group qualifiers for the variuos data. All of these tables are managed at Head Office; subscribers may not make changes to these tables. These tables I plan to snapshot replicate every twetny foru hours, as they change infrequently. Class two tables: Transaction type tables such as orders and order dettails (RI here), invoice and invoice details (RI here), Stock and Stock transactions (RI here). These tables will be modified at Head Office (which is in effect a branch as well) and all of the branches. Changes made at the branches must be replicated to Head Office, but no changes made at Head Office are replicated to the branches. I plan to use a transactional pull subscription for each branch executed on a dedicated distributer located at Head Office. Synchronisation will be around every ten minutes or so. Class three tables: Tables which are changed at all branches (including Head Office) which must be replicated to all other branches. The number of tables is very small, 8 in total, although they are quite heavily used. I plan to use a tranctional pull subscription from each branch, followed by a push subscription from the Head Office to each branch. Synchronisation frequency will be of the order of every ten minutes. Class four tables: Tables that are only used at Head Office and never need to be replicated to the branches. Number ranges: I will be implementing number ranges for the identity columns at the time of creating the publications at Head Office for initial snapshot replication to each of the branches. We plan to use blocks of numbers of 100 000 000 with a threshold of 98%. Based on the above, I understand that there will be no RI problems at the time of replication, unless I am very much mistaken and therefore do not understand why I would need to use NOT FOR REPLICATION at all. Please let me have your comments as a matter of urgency. I ma busy with this right now, and we have a go live date of 3 January. Regards, Morpheus [quoted text, click to view] >-----Original Message-----
>Hi Antonio, > >It's my pleasure to further assist you with the replication issue on RI. > >It is pointed out in another post that we can use NOT FOR REPLICATION option to avoid the >RI constraints. I'd appreciate it so much that you concentrate and sublime the issue on a >general basis. > >First of all, on the basis of Replication Architecture, it is a consideration that you apply the RI >on the Central Publisher, instead of on the Subscribers, so that the central publisher can >control the RI and push the suitable publications to the subscribers. > >Second, if the RI (such as FKs) on the subscribers prevents the replication, you can just >disable them rather than drop and re-create them. I believe it's save your time and easier to >be implemented: > >To disable a foreign key constraint for replication: > >(1) In your database diagram, right-click the table containing the foreign key, then select >Properties from the shortcut menu. > >(2) Choose the Relationships tab. >(3) Select the relationship from the Selected relationship list. >(4) Clear the Enable relationship for replication check box. > >After you add or modify data, you should select this option if you want to ensure the constraint >applies to subsequent data modifications. > >#Note# If you plan to use triggers to implement database operations, you must disable foreign >key constraints in order for the trigger to run. > > >Furthermore, it is also recommended that you use the "NOT FOR REPLICATION" option when >you CREATE a table or ALTER it later: > >----------------------- >FOREIGN KEY >REFERENCES ref_table [ ( ref_column ) ] >NOT FOR REPLICATION >----------------------- > >The NOT FOR REPLICATION clause means the constraint is enforced on user modifications >but not on the replication process. > >In this way, it prevents the check the Foreign Key constraint from being enforced during the >distribution process used by replication. When tables are subscribers to a replication >publication, do not update the subscription table directly, instead update the publishing table, >and let replication distribute the data back to the subscribing table. (That's why I suggest you >applying the RI on the Publisher at the beginning of this message) > > >For more information, you can reference the following articles: > >http://msdn.microsoft.com/library/default.asp? url=/library/en- >us/vdbt7/html/dvtskworkingwithrelationships.asp > >http://msdn.microsoft.com/library/default.asp? url=/library/en-us/replsql/repldata_6xbi.asp > > >Antonio, does this answer your question? Please apply my suggestions above and let me >know if this helps resolve your problem. If there is anything more I can do to assist you, please >feel free to post it in the group > > >Best regards, > >Billy Yao >Microsoft Online Support >---------------------------------------------------- >Get Secure! - www.microsoft.com/security >This posting is provided "as is" with no warranties and confers no rights. >Please reply to newsgroups only. Thanks. > > >.
Hello Norman,
Thank you for your patience and kind understanding! I recommended you contact PSS for direct and graceful assistance as the urgent nature of this issue and the big impact on your Business. So I believe PSS will serve you better on this issue. Additionally, the issue seems somewhat complex and maybe not suitable to discuss in newsgroup. If you'd like newsgroup services, it is also recommended to open a new post so that I can focus on the new thread and it will not affect the original one. However, I'd really like to assist you on this issue and notice that you wanted to know why you needed to use NOT FOR REPLICATION. Have I fully understood you? If there is anything I misunderstood, please feel free to let me know. The NOT FOR REPLICATION clause means the constraint is enforced on user modifications but not on the replication process. What I meant in the previous message is if you apply constraint on the subscription table to avoid the modification, this option needs to be used. Books Online says: =========== NOT FOR REPLICATION Keywords used to prevent the CHECK constraint from being enforced during the distribution process used by replication. When tables are subscribers to a replication publication, do not update the subscription table directly, instead update the publishing table, and let replication distribute the data back to the subscribing table. A CHECK constraint can be defined on the subscription table to prevent users from modifying it. Unless the NOT FOR REPLICATION clause is added, however, the CHECK constraint also prevents the replication process from distributing modifications from the publishing table to the subscribing table. The NOT FOR REPLICATION clause means the constraint is enforced on user modifications but not on the replication process. The NOT FOR REPLICATION CHECK constraint is applied to both the before and after image of an updated record to prevent records from being added to or deleted from the replicated range. All deletes and inserts are checked; if they fall within the replicated range, they are rejected. When this constraint is used with an identity column, SQL Server allows the table not to have its identity column values reseeded when a replication user updates the identity column. ====================== As to the auto increment IDENTITY keys, it's usually recommended to use this option in transactional or merge replication when a published table is partitioned with rows from various sites. However, if you are using transactional replication with the immediate-updating Subscribers option, do not use the IDENTITY NOT FOR REPLICATION design. Instead, create the IDENTITY property at the Publisher only, and have the Subscriber use just the base data type (for example, int). Then, the next identity value is always generated at the Publisher. Norman, does this answer your question? If there is anything more I can do to assist you, please feel free to let me know, and I appreciate a new post next time but cannot guarantee the response time in Newsgroup. Thank you for your understanding! Best regards, Billy Yao Microsoft Online Support ---------------------------------------------------- Get Secure! - www.microsoft.com/security This posting is provided "as is" with no warranties and confers no rights. Please reply to newsgroups only. Thanks.
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |