| Groups | Blog | Home |
sql server replication : More on merge replication an FK constraints
For foreign keys can you not set them all to "Not for replication", combine
CUSTOMERS, INVOICES and INVOICES_DET in one publication? Jim. [quoted text, click to view] "Mario Splivalo" wrote:
> So, let me relate to my earlier post in this group about merge replication > and foreign keys. > > As I've mentioned, I have one publication that pushes 'customers' table to > the subscribers. > > I have another publication that pushes 'invoices' and 'invoices_det' tables > to the subscribers. In table invoices there is cust_id field wich is foreign > key to the customers table primary key 'cust_id'. I have created all > publications like so that first table is DROPed and then recreated. I have > 'checked' options for creating primary keys and referential integrity. > > FIrst I push CUSTOMERS publication to the subsrciber, it's ok. customer > table is created on the subscriber, primary key is set, everything is ok. > > Then I push INVOICES publication to the subscriber. That's not ok. I don't > have foreign key to customers table in the table invoices. > > Enterprise manage wizzard suggests that I include customers table in > INVOICES publication to avoid things like mentioned above. But, I have > problem with that approach. That would mean that I'd have only as many > publications as I have subscribers (one subscriber - one publication). That > means that I'd have to have, for instance, table 'customers' published dozen > times, if I have dozen subscribers. > > How do I solve this one? > > As far as I see it, i could: > > 1) drop the foreign key constraints > - I'd hate to do this! > > 2) set up publication snapshot so it 'delete the data on the subscriber': > This seems ok, but! If I, for any reason, need to break replication, > when setting it back agan, I'll still have data on the subscriber. I then > push CUSTOMERS publication, the snapshot tries to DELETE data from > 'customers' table, but, since it's FKeyd to 'invoices' table, it will bring > up an error. > > 3) set up publication snapshot so it 'leaves the existing table unchanged'. > This is the best bet I could have, as far as I see. I only need to > make sure that the database on the remote is 'fresh and clear'. Wich means, > do the script to do the DDL on the subscriber (with all the constraints!), > and then push the snapshot in 'right' order (meaning, first the customers, > then the invoices, in the above example). > > Any help here would be much appreciated. > > Mike > > -- > "I can do it quick. I can do it cheap. I can do it well. Pick any two." > > Mario Splivalo > msplival@jagor.srce.hr
So, let me relate to my earlier post in this group about merge replication
and foreign keys. As I've mentioned, I have one publication that pushes 'customers' table to the subscribers. I have another publication that pushes 'invoices' and 'invoices_det' tables to the subscribers. In table invoices there is cust_id field wich is foreign key to the customers table primary key 'cust_id'. I have created all publications like so that first table is DROPed and then recreated. I have 'checked' options for creating primary keys and referential integrity. FIrst I push CUSTOMERS publication to the subsrciber, it's ok. customer table is created on the subscriber, primary key is set, everything is ok. Then I push INVOICES publication to the subscriber. That's not ok. I don't have foreign key to customers table in the table invoices. Enterprise manage wizzard suggests that I include customers table in INVOICES publication to avoid things like mentioned above. But, I have problem with that approach. That would mean that I'd have only as many publications as I have subscribers (one subscriber - one publication). That means that I'd have to have, for instance, table 'customers' published dozen times, if I have dozen subscribers. How do I solve this one? As far as I see it, i could: 1) drop the foreign key constraints - I'd hate to do this! 2) set up publication snapshot so it 'delete the data on the subscriber': This seems ok, but! If I, for any reason, need to break replication, when setting it back agan, I'll still have data on the subscriber. I then push CUSTOMERS publication, the snapshot tries to DELETE data from 'customers' table, but, since it's FKeyd to 'invoices' table, it will bring up an error. 3) set up publication snapshot so it 'leaves the existing table unchanged'. This is the best bet I could have, as far as I see. I only need to make sure that the database on the remote is 'fresh and clear'. Wich means, do the script to do the DDL on the subscriber (with all the constraints!), and then push the snapshot in 'right' order (meaning, first the customers, then the invoices, in the above example). Any help here would be much appreciated. Mike -- "I can do it quick. I can do it cheap. I can do it well. Pick any two." Mario Splivalo
If you have a set of tables that rely on each other using foreign keys and
they are likely to be updated together then they should all be in the same publication. As I understand it, you have them separated and are getting foreign key errors because items are not present when you need them. You may have very good reasons for splitting them that I am not aware of but I see no other solution to your problem. Jim. [quoted text, click to view] "Mario Splivalo" wrote:
> On 2004-12-06, Jim Breffni <JimBreffni@discussions.microsoft.com> wrote: > > For foreign keys can you not set them all to "Not for replication", combine > > CUSTOMERS, INVOICES and INVOICES_DET in one publication? > > I use 'NOT FOR REPLICATION' for foreign keys. > > Combining all those into one publication now seems like a good thing. But, I > have dozen of publication of wich 8 are non-filtered publications. Customers > is an example of such publication. I have 30 stores around the country, and > I need to be able for all the stores to enter new customers, and that new > customer should be visible in all locations. Invoices, for instance, is > exmaple of filtered publication (i have 4 like those), only the cetnral > database (the publisher) needs to see all the invoices (for the reporting > purposes, and financial stuff). Other subscribers don't see other invoices. > Same goes for inventory lists, some other financial stuff I don't know > english names, and so on. > > So, your approach leaves me with ONE publication for each subscriber! I > don't like that approach, it just creates mess. I'd like if someone could > tell me now 'stop, you're wrong here'. > > My database well normalized (is that the term when you reference data trough > foreign keys?), for instance, in invoices I have payement methods (cash, > credit card,...) wich is FK to the table wich holds payement methods. That > table is updated ONLY at the publisher. Another example is stocks. Each > invoice holds stock_id, wich is foreign key to table l_stocks. Then users. > Then tax rates. Then a lot of other things. All those tables are updated at > the publisher only, so I created snapshot replication to push that data to > subscribers. You suggest I put all that tables into merge publication, just > because they're referenced by FK from tables invoices and such? What is > wrong with my understanding of replication if I belive that is not the way? > > Mike > > -- > "I can do it quick. I can do it cheap. I can do it well. Pick any two." > > Mario Splivalo > msplival@jagor.srce.hr
[quoted text, click to view]
On 2004-12-06, Jim Breffni <JimBreffni@discussions.microsoft.com> wrote: > For foreign keys can you not set them all to "Not for replication", combine > CUSTOMERS, INVOICES and INVOICES_DET in one publication? I use 'NOT FOR REPLICATION' for foreign keys. Combining all those into one publication now seems like a good thing. But, I have dozen of publication of wich 8 are non-filtered publications. Customers is an example of such publication. I have 30 stores around the country, and I need to be able for all the stores to enter new customers, and that new customer should be visible in all locations. Invoices, for instance, is exmaple of filtered publication (i have 4 like those), only the cetnral database (the publisher) needs to see all the invoices (for the reporting purposes, and financial stuff). Other subscribers don't see other invoices. Same goes for inventory lists, some other financial stuff I don't know english names, and so on. So, your approach leaves me with ONE publication for each subscriber! I don't like that approach, it just creates mess. I'd like if someone could tell me now 'stop, you're wrong here'. My database well normalized (is that the term when you reference data trough foreign keys?), for instance, in invoices I have payement methods (cash, credit card,...) wich is FK to the table wich holds payement methods. That table is updated ONLY at the publisher. Another example is stocks. Each invoice holds stock_id, wich is foreign key to table l_stocks. Then users. Then tax rates. Then a lot of other things. All those tables are updated at the publisher only, so I created snapshot replication to push that data to subscribers. You suggest I put all that tables into merge publication, just because they're referenced by FK from tables invoices and such? What is wrong with my understanding of replication if I belive that is not the way? Mike -- "I can do it quick. I can do it cheap. I can do it well. Pick any two." Mario Splivalo
Yes, what you are doing is logical ok - I just don't know of a way to prevent
FK errors when you split things like that. For me, even if the table rarely changes, I include everything in a publication. The only price I pay for that is a larger initial snapshot.
[quoted text, click to view]
On 2004-12-07, Jim Breffni <JimBreffni@discussions.microsoft.com> wrote: > If you have a set of tables that rely on each other using foreign keys and > they are likely to be updated together then they should all be in the same > publication. Yes. That goes for master/detail tables. But, what if I have a table with, let's say, tax rates, and in invocie table I have reference via foreign key to the tax rates table? I'm publishing tax rates table (along with others) via snapshot publication. So, why would I include those in merge publication? And tables in merge publication still reference those fields in tax rates table. So, when deploying initial snapshot the invoices table WON'T have FK constraint to tax rates table. How do I deal with that? [quoted text, click to view] > > As I understand it, you have them separated and are getting foreign key > errors because items are not present when you need them. Initial snapshot won't create foreign key constraints for tables that are not in that publication. That is my problem. [quoted text, click to view] > You may have very good reasons for splitting them that I am not aware of but > I see no other solution to your problem. Do you have any reference on the web where I could read more about this? I see no point in puting all the database tables in one publication just because almost all tables are FKey with each other. Or, to put it more simple, how do I establish snapshot and merge publication so that tables in merge publication have foreign keys to tables in snapshot publication? Mike -- "I can do it quick. I can do it cheap. I can do it well. Pick any two." Mario Splivalo
[quoted text, click to view]
On 2004-12-07, Jim Breffni <JimBreffni@discussions.microsoft.com> wrote: > Yes, what you are doing is logical ok - I just don't know of a way to prevent > FK errors when you split things like that. > > For me, even if the table rarely changes, I include everything in a > publication. The only price I pay for that is a larger initial snapshot. > The benefit I get is simplicity and easier administration. So, you'd generaly be ok with this: put rarely changed tables as articles into many publications, although you could have only one snapshot publication for those? Mike -- "I can do it quick. I can do it cheap. I can do it well. Pick any two." Mario Splivalo
I try to keep it one publication, one snapshot. I then have many subscribers
- it is very easy to administer. [quoted text, click to view] "Mario Splivalo" wrote:
> On 2004-12-07, Jim Breffni <JimBreffni@discussions.microsoft.com> wrote: > > Yes, what you are doing is logical ok - I just don't know of a way to prevent > > FK errors when you split things like that. > > > > For me, even if the table rarely changes, I include everything in a > > publication. The only price I pay for that is a larger initial snapshot. > > The benefit I get is simplicity and easier administration. > > So, you'd generaly be ok with this: put rarely changed tables as articles > into many publications, although you could have only one snapshot > publication for those? > > Mike > -- > "I can do it quick. I can do it cheap. I can do it well. Pick any two." > > Mario Splivalo > msplival@jagor.srce.hr
Mario,
I'm looking for the same answer as you, I have basically the same problem with inicial snapshot with transactional replication, as you, I have also Unchecked the enforce for replication in the relationship's dialog for the foreign keys. If I have some answers or more info I will post it here... Adrian Parra. [quoted text, click to view] "Mario Splivalo" wrote:
> On 2004-12-06, Jim Breffni <JimBreffni@discussions.microsoft.com> wrote: > > For foreign keys can you not set them all to "Not for replication", combine > > CUSTOMERS, INVOICES and INVOICES_DET in one publication? > > I use 'NOT FOR REPLICATION' for foreign keys. > > Combining all those into one publication now seems like a good thing. But, I > have dozen of publication of wich 8 are non-filtered publications. Customers > is an example of such publication. I have 30 stores around the country, and > I need to be able for all the stores to enter new customers, and that new > customer should be visible in all locations. Invoices, for instance, is > exmaple of filtered publication (i have 4 like those), only the cetnral > database (the publisher) needs to see all the invoices (for the reporting > purposes, and financial stuff). Other subscribers don't see other invoices. > Same goes for inventory lists, some other financial stuff I don't know > english names, and so on. > > So, your approach leaves me with ONE publication for each subscriber! I > don't like that approach, it just creates mess. I'd like if someone could > tell me now 'stop, you're wrong here'. > > My database well normalized (is that the term when you reference data trough > foreign keys?), for instance, in invoices I have payement methods (cash, > credit card,...) wich is FK to the table wich holds payement methods. That > table is updated ONLY at the publisher. Another example is stocks. Each > invoice holds stock_id, wich is foreign key to table l_stocks. Then users. > Then tax rates. Then a lot of other things. All those tables are updated at > the publisher only, so I created snapshot replication to push that data to > subscribers. You suggest I put all that tables into merge publication, just > because they're referenced by FK from tables invoices and such? What is > wrong with my understanding of replication if I belive that is not the way? > > Mike > > -- > "I can do it quick. I can do it cheap. I can do it well. Pick any two." > > Mario Splivalo > msplival@jagor.srce.hr
[quoted text, click to view]
On 2004-12-08, Jim Breffni <JimBreffni@discussions.microsoft.com> wrote: > I try to keep it one publication, one snapshot. I then have many subscribers > - it is very easy to administer. > > Thnx. I'll see with what i'll come up at the end, and post here... Mike -- "I can do it quick. I can do it cheap. I can do it well. Pick any two." Mario Splivalo
Thats the same thing I was thinking about, I'm using ApexScript because it
says it will script the dababase and data and elaborate the correct order to maintain referential integrity. What you say about re-inicialization is true. Thanks. [quoted text, click to view] "Mario Splivalo" wrote:
> > Thnx! So far the best thing I came up with is to have complete database in > SQL script file. Apply that file on each subscriber, then, when creating > publication, for article snapshot options tell 'em to 'do nothing at the > subscriber, leave tables as they were'. There is a bit of trouble when > needing to reinitialize replication, because you'd have to connect to > subscriber, truncate/delte/whatever the data there, and then push the > snapshot. > > The 'NOT FOR REPLICATION' clause when defining relations (foreign keys) is > mandatory, because, merge agent can first insert data into detail table, and > then into master table. Since you're keeping referential integrity at both > publisher and subscriber, replication agent merely copies that data. > > I'll post more data here too, as soon as I get them,. > > Mike > > > -- > "I can do it quick. I can do it cheap. I can do it well. Pick any two." > > Mario Splivalo > msplival@jagor.srce.hr
[quoted text, click to view]
On 2004-12-08, Adrian Parra <> wrote: > Mario, > I'm looking for the same answer as you, I have basically the same problem > with inicial snapshot with transactional replication, as you, I have also > Unchecked the enforce for replication in the relationship's dialog for the > foreign keys. > > If I have some answers or more info I will post it here... Thnx! So far the best thing I came up with is to have complete database in SQL script file. Apply that file on each subscriber, then, when creating publication, for article snapshot options tell 'em to 'do nothing at the subscriber, leave tables as they were'. There is a bit of trouble when needing to reinitialize replication, because you'd have to connect to subscriber, truncate/delte/whatever the data there, and then push the snapshot. The 'NOT FOR REPLICATION' clause when defining relations (foreign keys) is mandatory, because, merge agent can first insert data into detail table, and then into master table. Since you're keeping referential integrity at both publisher and subscriber, replication agent merely copies that data. I'll post more data here too, as soon as I get them,. Mike -- "I can do it quick. I can do it cheap. I can do it well. Pick any two." Mario Splivalo
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 |