|
|
You're in the
sql server replication
group:Overlapping or nonoverlapping publication? Which type applies to my design?
sql server replication:
example the subscribers will be unable to update this column to have another value. Your second question means that if you add a row which has a salesmanID value of 5 and your filter for that subscription is 4, this row will remain in the subscriber and never move to the publisher. The way to look at partitions is that it is a block of related data. So its the salesman row, his clients, and all of its orders and all related data. Should a salesman take on new clients, the partition will have the new clients and their orders added to it. Should the salesman change territories with another salesman there would be a partition realignment which would include all the new clients, their orders, etc. By default if you were to update the client salespersonID on the client table, only the client row would move to the subscriber. With join filters all the clients data will also move as the update statement on the parent table will also affect all child rows. With overlapping partitions data can be owned by more than two salesmen, with non-overlapping it would be only one. From what you describe you should use non-overlapping partitions. You can have one partition for every salesman on the publisher, and if you are not using hierarchies (or doing republishing) you will only have one partition on the subscriber. HTH -- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Polaris431" <polaris431@gmail.com> wrote in message news:1172401242.554319.221230@a75g2000cwd.googlegroups.com... > I'm going through the documentation for SQL Server 2005 replication > and am trying to understand what type of publication I should be > using. Here is how replication should be working in my system: > > I have a database on a server that will be accessible over the > Internet. This database can be used by multiple companies. Tables are > designed so that a company ID is used to identify records that belong > to a specific company. Every record in every table can be filtered out > to belong to a unique company. That means that no two companies ever > share the same data. Each company can have multiple mobile users using > PDAs to communicate with the server. Each user receives data only from > their company. Some of the data is created on the server and > replicated to the PDA. Some data is also created on the PDA and > replicated to the server. Under no condition is data that is created > on the server ever edited on a PDA. Data created on a PDA is always > inserted into the table and once inserted it can never be modified. > Mobile users cannot modify or delete data. When one mobile user > replicates their data with the server, the newly inserted records that > were created on their PDA are also replicated to all other PDAs within > the same company but this data can never be modified by any mobile > user. In essence, the entire database on the server is replicated to > all of the PDA users but the application on the PDA will only allow > inserts into certain tables - no modifications or deletions. All > mobile users should always get newly inserted data made by other > mobile users within the same company. > > I'm having a hard time trying to figure out what kind of publication > is required. In a section of the documentation on replication under > the title "Considerations for Nonoverlapping Partitions", it is > written: > > * The Subscriber cannot update columns referenced in a parameterized > filter. > *If an insert at a Subscriber does not belong to the partition, it is > not deleted. However, it will not be replicated to other Subscribers. > > I'm not sure what these two statements mean. The first statement > mentions "cannot update columns". No columns will be updated, but > inserts will be done. > > In the second statement, it refers to an insert "not belonging to the > partition". If all PDA users are subscribing to the same publication, > won't they all get a copy of the inserted records that are made on all > other PDAs? > > If you have a nonoverlapping partion, which is what I assume is what I > should be using, how could any insert possibly belong to some other > partition? Isn't there just one partition? > > What kind of publication should I be using: overlapping or > nonoverlapping? > > Thanks for your help > Polaris >
I'm going through the documentation for SQL Server 2005 replication
and am trying to understand what type of publication I should be using. Here is how replication should be working in my system: I have a database on a server that will be accessible over the Internet. This database can be used by multiple companies. Tables are designed so that a company ID is used to identify records that belong to a specific company. Every record in every table can be filtered out to belong to a unique company. That means that no two companies ever share the same data. Each company can have multiple mobile users using PDAs to communicate with the server. Each user receives data only from their company. Some of the data is created on the server and replicated to the PDA. Some data is also created on the PDA and replicated to the server. Under no condition is data that is created on the server ever edited on a PDA. Data created on a PDA is always inserted into the table and once inserted it can never be modified. Mobile users cannot modify or delete data. When one mobile user replicates their data with the server, the newly inserted records that were created on their PDA are also replicated to all other PDAs within the same company but this data can never be modified by any mobile user. In essence, the entire database on the server is replicated to all of the PDA users but the application on the PDA will only allow inserts into certain tables - no modifications or deletions. All mobile users should always get newly inserted data made by other mobile users within the same company. I'm having a hard time trying to figure out what kind of publication is required. In a section of the documentation on replication under the title "Considerations for Nonoverlapping Partitions", it is written: * The Subscriber cannot update columns referenced in a parameterized filter. *If an insert at a Subscriber does not belong to the partition, it is not deleted. However, it will not be replicated to other Subscribers. I'm not sure what these two statements mean. The first statement mentions "cannot update columns". No columns will be updated, but inserts will be done. In the second statement, it refers to an insert "not belonging to the partition". If all PDA users are subscribing to the same publication, won't they all get a copy of the inserted records that are made on all other PDAs? If you have a nonoverlapping partion, which is what I assume is what I should be using, how could any insert possibly belong to some other partition? Isn't there just one partition? What kind of publication should I be using: overlapping or nonoverlapping? Thanks for your help Polaris
Hi Hilary,
[quoted text, click to view] You wrote: > With overlapping partitions data can be owned by more than two salesmen, > with non-overlapping it would be only one. > From what you describe you should use non-overlapping partitions. So if you recommend non-overlapping, then are you saying that each PDA client within a single company would be its own "owner"? Yet I want it such that all the data on ALL PDAs is identical after replication. Each user on each PDA can insert data but not modify or delete existing data. Will non-overlapping satisfy this requirement? Thanks Johann [quoted text, click to view] On Feb 25, 3:10 pm, "Hilary Cotter" <hilary.cot...@gmail.com> wrote: > Basically it means if you are filtering on the salespersonID column for > example the subscribers will be unable to update this column to have another > value. Your second question means that if you add a row which has a > salesmanID value of 5 and your filter for that subscription is 4, this row > will remain in the subscriber and never move to the publisher. > > The way to look at partitions is that it is a block of related data. So its > the salesman row, his clients, and all of its orders and all related data. > Should a salesman take on new clients, the partition will have the new > clients and their orders added to it. Should the salesman change territories > with another salesman there would be a partition realignment which would > include all the new clients, their orders, etc. > > By default if you were to update the client salespersonID on the client > table, only the client row would move to the subscriber. With join filters > all the clients data will also move as the update statement on the parent > table will also affect all child rows. > > With overlapping partitions data can be owned by more than two salesmen, > with non-overlapping it would be only one. > > From what you describe you should use non-overlapping partitions. You can > have one partition for every salesman on the publisher, and if you are not > using hierarchies (or doing republishing) you will only have one partition > on the subscriber. > > HTH > > -- > Hilary Cotter
Perhaps I am missing something here, but I don't think you need to do any
filtering at all, or at least not join filtering which is where partitions come in. -- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Polaris431" <polaris431@gmail.com> wrote in message news:1172415914.436083.96840@a75g2000cwd.googlegroups.com... > Hi Hilary, > > You wrote: >> With overlapping partitions data can be owned by more than two salesmen, >> with non-overlapping it would be only one. >> From what you describe you should use non-overlapping partitions. > > So if you recommend non-overlapping, then are you saying that each PDA > client within a single company would be its own "owner"? Yet I want > it such that all the data on ALL PDAs is identical after replication. > Each user on each PDA can insert data but not modify or delete > existing data. Will non-overlapping satisfy this requirement? > > Thanks > Johann > > > > On Feb 25, 3:10 pm, "Hilary Cotter" <hilary.cot...@gmail.com> wrote: >> Basically it means if you are filtering on the salespersonID column for >> example the subscribers will be unable to update this column to have >> another >> value. Your second question means that if you add a row which has a >> salesmanID value of 5 and your filter for that subscription is 4, this >> row >> will remain in the subscriber and never move to the publisher. >> >> The way to look at partitions is that it is a block of related data. So >> its >> the salesman row, his clients, and all of its orders and all related >> data. >> Should a salesman take on new clients, the partition will have the new >> clients and their orders added to it. Should the salesman change >> territories >> with another salesman there would be a partition realignment which would >> include all the new clients, their orders, etc. >> >> By default if you were to update the client salespersonID on the client >> table, only the client row would move to the subscriber. With join >> filters >> all the clients data will also move as the update statement on the parent >> table will also affect all child rows. >> >> With overlapping partitions data can be owned by more than two salesmen, >> with non-overlapping it would be only one. >> >> From what you describe you should use non-overlapping partitions. You can >> have one partition for every salesman on the publisher, and if you are >> not >> using hierarchies (or doing republishing) you will only have one >> partition >> on the subscriber. >> >> HTH >> >> -- >> Hilary Cotter > > >
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 |