Reinitializing Subscription with Existing FKs
in [Replication]
|
Prev: Error Executing Replication Job
Next: Data sinchro
From: mEmENT0m0RI on 12 Oct 2009 17:06 Working on a scale-out architecture for our main db. There are some tables that have basically static data that should be the same in all the databases. I'm looking for a solution where I can have those tables stored in a look-up database and then use transactional replication to propagate the changes into each database. One problem that I envision with that approach is the fact that I'm not going to be able to re-initialize subscriptions for the tables that already have FK defined against in the destination databases. @pre_creation_cmd parameter of the sp_addarticle proc only gives me choices of either delete, drop or truncate the destination table; what I really looking for, would be an update for all columns based on the existing PKs and delete/insert the rest. Is there a solution for that? Would merge replication behave any differently? Thanks, Igor *** Sent via Developersdex http://www.developersdex.com ***
From: Med Bouchenafa on 13 Oct 2009 01:42 You still have the option to keep the data contained in those tables by configuring @pre_creation_cmd = 'none' On the other side, if your FK were created with the "NOT FOR REPLICATION" option, the replication process does not check them and ignore them Med Bouchenafa "mEmENT0m0RI" <nospam(a)devdex.com> wrote in message news:#Y7ii$3SKHA.352(a)TK2MSFTNGP02.phx.gbl... > Working on a scale-out architecture for our main db. There are some > tables that have basically static data that should be the same in all > the databases. > I'm looking for a solution where I can have those tables stored in a > look-up database and then use transactional replication to propagate the > changes into each database. > One problem that I envision with that approach is the fact that I'm not > going to be able to re-initialize subscriptions for the tables that > already have FK defined against in the destination databases. > @pre_creation_cmd parameter of the sp_addarticle proc only gives me > choices of either delete, drop or truncate the destination table; what I > really looking for, would be an update for all columns based on the > existing PKs and delete/insert the rest. > Is there a solution for that? Would merge replication behave any > differently? > > Thanks, > Igor > > > > > > *** Sent via Developersdex http://www.developersdex.com ***
From: mEmENT0m0RI on 13 Oct 2009 12:16 I don't believe @pre_creation_cmd = 'none' would work in my case because I still want to make sure that the tables are in sync if I ever need to reinitialize the subscription. "NOT FOR REPLICATION" setting is a bit confusing to me as it relates to a FK. The articles I'm trying to replicate would be the ones that have PKs related to FKs in other tables that are not included in the replication. Would it still be able to not enforce that constraint if, let's say, a delete operation against a related PK table has been initiated by the snapshot agent? Thanks! *** Sent via Developersdex http://www.developersdex.com ***
From: mEmENT0m0RI on 13 Oct 2009 12:30 I don't believe @pre_creation_cmd = 'none' would work in my case because I still want to make sure that the tables are in sync if I ever need to reinitialize the subscription. "NOT FOR REPLICATION" setting is a bit confusing to me as it relates to a FK. The articles I'm trying to replicate would be the ones that have PKs related to FKs in other tables that are not included in the replication. Would it still be able to not enforce that constraint if, let's say, a delete operation against a related PK table has been initiated by the snapshot agent? Thanks! *** Sent via Developersdex http://www.developersdex.com ***
From: Med Bouchenafa on 14 Oct 2009 01:58
@pre_creation_cmd = 'none' works only during the snapshot The snapshot process does not delete existing data in this table and therefore no FK are violated The merge process will keep working the usual way. Regarding FK, I would then suggest excluding them from the publication Med Bouchenafa "mEmENT0m0RI" <nospam(a)devdex.com> wrote in message news:u6dbPCCTKHA.1232(a)TK2MSFTNGP05.phx.gbl... > I don't believe @pre_creation_cmd = 'none' would work in my case because > I still want to make sure that the tables are in sync if I ever need to > reinitialize the subscription. > > "NOT FOR REPLICATION" setting is a bit confusing to me as it relates to > a FK. The articles I'm trying to replicate would be the ones that have > PKs related to FKs in other tables that are not included in the > replication. Would it still be able to not enforce that constraint if, > let's say, a delete operation against a related PK table has been > initiated by the snapshot agent? > > Thanks! > > *** Sent via Developersdex http://www.developersdex.com *** |