RI, Replication Conflicts and architecture

Hi there,
We're looking at setting up a database infrastructure to handle the
following.
2 Physical sites on an 85% uptime VPN Link
There are multiple (10+) IT systems for various businesses on both sides of
this link.
We need to store 1 shared client table and then each systemwill store
auxillary client data and other tables with RI to the this client table.
We have large amounts of data in this client table with about 15% bad data
(Dups etc)
A hot topic of debate at current is:
If I have DB X (on Side A of the link) that deletes a client record after
deleting all RI, what happens to the client record in DB Y (on Side B of the
link) that still has RI. How should we architect for this - Merge Repl?
Transactional? Web Services? could these sorts of conflicts be handled via a
custom resolver? This is complexed by the fact that we may need to merge the
RI from 2 dups and delete one of them. This needs to be replicated out to
the other side of the link until all (10+) databases are accurate. As near
to real time as possible is required.
Any help much appreciated
For transactional replication:
If you have a parent table with a child table and do cascading
deletes/updates, and do not maintain the DRI on the Subscriber or you do
maintain it, but uncheck the enforce relationship for replication option on
the Subscriber, and both tables are published DRI will be maintained on the
subscriber automatically.
So if you delete a parent record, the corresponding child records will be
deleted on the Publisher. The delete on the parent will be replicated to the
subscriber and the delete of the child records will also be replicated to
the Subscriber as part of the same transaction.
For merge replication:
You need to set the cascading updates and deletes option on the Publisher
and deselect the enforce relationship for replication. The problem here is
that the deletes on the parent table might arrive on the subscriber before
or after the delete on the child. If these deletes or updates happen
between a batch boundary you may run into problems. Microsoft's solution to
this problem is to increate the UploadGenerationsPerBatch and
DownloadGenerationsPerBatch to something large like 1000, to ensure that the
deletes or updates which are part of the same transaction occur in the same
batch.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Murray Foxcroft" <murray(UseADot)foxcroft@.ast.co.za> wrote in message
news:ejvjTnJdEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> We're looking at setting up a database infrastructure to handle the
> following.
> 2 Physical sites on an 85% uptime VPN Link
> There are multiple (10+) IT systems for various businesses on both sides
of
> this link.
> We need to store 1 shared client table and then each systemwill store
> auxillary client data and other tables with RI to the this client table.
> We have large amounts of data in this client table with about 15% bad data
> (Dups etc)
> A hot topic of debate at current is:
> If I have DB X (on Side A of the link) that deletes a client record after
> deleting all RI, what happens to the client record in DB Y (on Side B of
the
> link) that still has RI. How should we architect for this - Merge Repl?
> Transactional? Web Services? could these sorts of conflicts be handled via
a
> custom resolver? This is complexed by the fact that we may need to merge
the
> RI from 2 dups and delete one of them. This needs to be replicated out to
> the other side of the link until all (10+) databases are accurate. As near
> to real time as possible is required.
> Any help much appreciated
>
>
>
|||Hi Hilary, thanks for the reply - our complexity comes in when we need to
de-dup on the databases involved in the replication.
So: We find a duplicate client record - now we need to move all the DRI
through all systems to one of the clients THEN delete the other once all DRI
is removed.
How and which replication would you use for this?
Thanks in advance
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:esY7v9JdEHA.3476@.tk2msftngp13.phx.gbl...
> For transactional replication:
> If you have a parent table with a child table and do cascading
> deletes/updates, and do not maintain the DRI on the Subscriber or you do
> maintain it, but uncheck the enforce relationship for replication option
> on
> the Subscriber, and both tables are published DRI will be maintained on
> the
> subscriber automatically.
> So if you delete a parent record, the corresponding child records will be
> deleted on the Publisher. The delete on the parent will be replicated to
> the
> subscriber and the delete of the child records will also be replicated to
> the Subscriber as part of the same transaction.
> For merge replication:
> You need to set the cascading updates and deletes option on the Publisher
> and deselect the enforce relationship for replication. The problem here is
> that the deletes on the parent table might arrive on the subscriber before
> or after the delete on the child. If these deletes or updates happen
> between a batch boundary you may run into problems. Microsoft's solution
> to
> this problem is to increate the UploadGenerationsPerBatch and
> DownloadGenerationsPerBatch to something large like 1000, to ensure that
> the
> deletes or updates which are part of the same transaction occur in the
> same
> batch.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Murray Foxcroft" <murray(UseADot)foxcroft@.ast.co.za> wrote in message
> news:ejvjTnJdEHA.2520@.TK2MSFTNGP12.phx.gbl...
> of
> the
> a
> the
>
sql