I need some help in designing the physical architecture of my database
system for replication. Here is my scenario
I have 1 server each in North America, Europe and Asia. They will be
connected 99 % of the time. All the data needs to be replicated between these
servers. There is very less data which will create contain conflicts while
replicating.
What type of replication should i be having ?
Should i go with the local distributors ?
If there is a conflict and i have local distributors then how can that be
resolved between 3 subscribers ? If possible please provide examples
The above is one scenario
Now each of these servers will be connected by local servers running MSDE.
They do not have a guaranteed connection. They need to upload data and also
recieve fresh updates from these regional servers. There can be a conflict
between the data of 2 local servers which will need to be resolved before
uploading to the regional server. There will be specific conflict policies
defined.
How do i achieve the above ?
I would appreciate if someone can help me in this with examples. I have not
implemented replication till date.
Thanks in advance
Afaq
Afaq,
for this type of scenario, merge replication functions best. There would be
no need to republish and have ultiple distributors - just designate the
'main' server as the publisher (and distributor) and the others as
subscribers. From your description, conflicts can arise as the data is not
partitioned - merge replication will allow you to have column-level or
row-level conflict detection. By default, a change on the publisher will
'win' against a change on the subscribers, and if 2 subscribers conflict,
the first one to synchronize 'wins'. There are other algorithms that can be
used, and you can create your own conflict resolution routine if necessary.
For more info, have a look in BOL for "conflict resolution, overview".
HTH,
Paul Ibison
|||Paul,
Thanks for the reply. Another question i had was regarding the PK for the
tables. I wanted to have identities as the PK. After reading the posts i
realised that identities are not so easy. If i have GUID's then my indexing
performance goes down which is a issue for me.
Can someone suggest me how best i can use identities with merge replication.
I cn use the Not for replciation clause but for that i will have to specify a
range of identity values for a table. That is also acceptable. But how do i
manage when i reach the end of the range? Do i have to manually increase the
range? how to handle this when i have 500 msde's working together. how to
achieve the range on each of these servers.
Any help would be appreciated
Afaq
"Paul Ibison" wrote:
> Afaq,
> for this type of scenario, merge replication functions best. There would be
> no need to republish and have ultiple distributors - just designate the
> 'main' server as the publisher (and distributor) and the others as
> subscribers. From your description, conflicts can arise as the data is not
> partitioned - merge replication will allow you to have column-level or
> row-level conflict detection. By default, a change on the publisher will
> 'win' against a change on the subscribers, and if 2 subscribers conflict,
> the first one to synchronize 'wins'. There are other algorithms that can be
> used, and you can create your own conflict resolution routine if necessary.
> For more info, have a look in BOL for "conflict resolution, overview".
> HTH,
> Paul Ibison
>
>
|||Afaq,
500 MSDE's is a lot
As far as I can see, you have 3 choices:
(a) you can partition the PK values. In this way, each subscriber has an
identity column combined with another column as a PK. The identity column
functions as a normal one - no partitioning - and the other column
identifies the subscriber. In this way there will be no conflicts and no
need for ranges.
(b) you can use automatic range management. An integer column goes to
+2billion (ish), so you should be able to assign large ranges which will
never need to be updated. If you use small ranges, when they are full up the
check constraint will error and new records can't be entered until the
subscriber synchronizes and you want to avoid this possibility.
(c) you can manually assign the ranges. For this you'd need to set up each
subscriber separately and this might turn out to be too much work...
HTH,
Paul Ibison
|||I guess the first option is doable.
Now there is another issue
All my servers are publishers and subscribers. So please consider the
following example
Table A is an article in a publication on Server X. Server Y is a subscriber
to this pubication. Server Y also can be a publisher for Table A and Server X
can be a subscriber for the same. WHen i have a subscription on table A from
server Y i cant have a publication for the same table from Server Y.
How do i achieve the same? I need Table A to be a part of different
publications from different servers as they are in different time zones and
they will publish the data on different times. And we want each of the
servers to publish the data for the same table at different times.
I hope the issue is properly conveyed. Forgive me for asking this question
as i am a newbie in replication.
Thanks
Afaq
"Paul Ibison" wrote:
> Afaq,
> 500 MSDE's is a lot
> As far as I can see, you have 3 choices:
> (a) you can partition the PK values. In this way, each subscriber has an
> identity column combined with another column as a PK. The identity column
> functions as a normal one - no partitioning - and the other column
> identifies the subscriber. In this way there will be no conflicts and no
> need for ranges.
> (b) you can use automatic range management. An integer column goes to
> +2billion (ish), so you should be able to assign large ranges which will
> never need to be updated. If you use small ranges, when they are full up the
> check constraint will error and new records can't be entered until the
> subscriber synchronizes and you want to avoid this possibility.
> (c) you can manually assign the ranges. For this you'd need to set up each
> subscriber separately and this might turn out to be too much work...
> HTH,
> Paul Ibison
>
>
No comments:
Post a Comment