Overlapping or nonoverlapping publication? Which type applies to my design?

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,
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
|||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
"Polaris431" <polaris431@.gmail.com> wrote in message
news:1172415914.436083.96840@.a75g2000cwd.googlegro ups.com...
> Hi Hilary,
> You wrote:
> 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:
>
>