INFO Requested Distributed DB Design

I am in a Process of designing database deployment layout for a system.
There are 4 major subsystem in the application each uses its own database.
Database :
1) Primary DB
2) Processing A DB
3) Reporting DB
4) Processing B DB
The Purpose of Primary is to hold all the Input data in unmodified state.
Processing A DB is a Exact copy of Primary DB.
In Processing A DB the the computation & Validation happens .The Results (
More than one Table )of Processing A is updated in Primary DB.
This Updates needs to be transfered to Reporting DB , Processing B DB.
In Processing B DB some status column is updated and this Updates needs
to be propogated back to the Primary
I am Looking at transactional Replication as a Option for this . Let me
know your views on this
You are trying to do work flow. Replication is not an easy fit with this.
Basically with a replication solution you need a server which will act as
the clearing house figuring out what goes where. With sql2000 you do not
have this - with SQL 2005 you sort of do with the peer to peer replication
model with transactional replication.
It seems that your data flow is this
Primary -> Processing A -> Reporting
-> Processing B -> Primary
Primary <- (with filtering) Processing A
By what criteria does data flow from Processing A back to Primary and how
are you going to prevent data from Processing B flowing back to Processing
A?
If you can filter this data, you could use transactional replication from
one node to another. If you can't I don't think transactional replication
will be a good fit.
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
"Raaj" <Raaj@.discussions.microsoft.com> wrote in message
news:DF063537-EE93-4742-AFBB-8BBAF557CEE4@.microsoft.com...
> I am in a Process of designing database deployment layout for a system.
> There are 4 major subsystem in the application each uses its own
database.
> Database :
> 1) Primary DB
> 2) Processing A DB
> 3) Reporting DB
> 4) Processing B DB
>
> The Purpose of Primary is to hold all the Input data in unmodified
state.
> Processing A DB is a Exact copy of Primary DB.
> In Processing A DB the the computation & Validation happens .The Results (
> More than one Table )of Processing A is updated in Primary DB.
> This Updates needs to be transfered to Reporting DB , Processing B DB.
> In Processing B DB some status column is updated and this Updates needs
> to be propogated back to the Primary
> I am Looking at transactional Replication as a Option for this . Let me
> know your views on this
>
|||Thanks for your response.
Well I have a Status Column on the Primary field based on which i Push the
(Set of tables) data to the Processing A DB .
Process A DB does some computation and Updates Data & the staus field on th
Tables .
My Question is whats the Best way of Pushing this data back(from Processing
A) to the Primary server .
There are around 30 table which gets updated .
I am currently looking at DTS/ Transactional Replication to move the
tables back to the Primay DB. I feel there would be Cyclic Updates ?
any advice on this
Best Reagrds
"Hilary Cotter" wrote:

> You are trying to do work flow. Replication is not an easy fit with this.
> Basically with a replication solution you need a server which will act as
> the clearing house figuring out what goes where. With sql2000 you do not
> have this - with SQL 2005 you sort of do with the peer to peer replication
> model with transactional replication.
> It seems that your data flow is this
> Primary -> Processing A -> Reporting
> -> Processing B -> Primary
> Primary <- (with filtering) Processing A
> By what criteria does data flow from Processing A back to Primary and how
> are you going to prevent data from Processing B flowing back to Processing
> A?
> If you can filter this data, you could use transactional replication from
> one node to another. If you can't I don't think transactional replication
> will be a good fit.
>
> --
> 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
> "Raaj" <Raaj@.discussions.microsoft.com> wrote in message
> news:DF063537-EE93-4742-AFBB-8BBAF557CEE4@.microsoft.com...
> database.
> state.
>
>
|||I think you want to replicate these tables back, but ensure that the
replication stored procedures have logic built in them to only execute is
the status column is an update.
For instance going from primary to processing a the status column might have
a value of 0, when processed on A it is updated to 1.
In your update replication procs have a wrapper which looks like this going
back to Primary.
if StatusColumn=1 then
do work
else
return 0
In your update replication procs have a wrapper which looks like this going
to PRocessing A.
if StatusColumn=0 then
do work
else
return 0
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
"Raaj" <Raaj@.discussions.microsoft.com> wrote in message
news:2242A1DF-2047-4C2B-A8F6-A63835767DF5@.microsoft.com...
> Thanks for your response.
> Well I have a Status Column on the Primary field based on which i Push the
> (Set of tables) data to the Processing A DB .
> Process A DB does some computation and Updates Data & the staus field on
th
> Tables .
> My Question is whats the Best way of Pushing this data back(from
Processing[vbcol=seagreen]
> A) to the Primary server .
> There are around 30 table which gets updated .
> I am currently looking at DTS/ Transactional Replication to move the
> tables back to the Primay DB. I feel there would be Cyclic Updates ?
> any advice on this
> Best Reagrds
>
> "Hilary Cotter" wrote:
this.[vbcol=seagreen]
as[vbcol=seagreen]
replication[vbcol=seagreen]
how[vbcol=seagreen]
Processing[vbcol=seagreen]
from[vbcol=seagreen]
replication[vbcol=seagreen]
system.[vbcol=seagreen]
Results ([vbcol=seagreen]
DB.[vbcol=seagreen]
needs[vbcol=seagreen]
me[vbcol=seagreen]
|||Thanks Hilary
Well I have not yet decided on the mechanism to updating the data back to
primary
Server .
I am considering to create a Publication on the Processing A server and
let Primary server subscribe this datas ( seems to me a cyclic and bit over
head) .
Note : there will be more that 30 table updates which needs to be send
back-forth
do you have any suggesstion
"Hilary Cotter" wrote:

> I think you want to replicate these tables back, but ensure that the
> replication stored procedures have logic built in them to only execute is
> the status column is an update.
> For instance going from primary to processing a the status column might have
> a value of 0, when processed on A it is updated to 1.
> In your update replication procs have a wrapper which looks like this going
> back to Primary.
> if StatusColumn=1 then
> do work
> else
> return 0
> In your update replication procs have a wrapper which looks like this going
> to PRocessing A.
> if StatusColumn=0 then
> do work
> else
> return 0
> --
> 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
> "Raaj" <Raaj@.discussions.microsoft.com> wrote in message
> news:2242A1DF-2047-4C2B-A8F6-A63835767DF5@.microsoft.com...
> th
> Processing
> this.
> as
> replication
> how
> Processing
> from
> replication
> system.
> Results (
> DB.
> needs
> me
>
>
|||it will be cyclic unless you can figure out some criteria to use as a basis
for not applying the transaction. Use this criteria as a basis as to whether
the proc applies the data on the subscriber/publisher or not.
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
"Raaj" <Raaj@.discussions.microsoft.com> wrote in message
news:127378BB-BBAB-4F77-9A10-817170920414@.microsoft.com...
> Thanks Hilary
> Well I have not yet decided on the mechanism to updating the data back to
> primary
> Server .
> I am considering to create a Publication on the Processing A server and
> let Primary server subscribe this datas ( seems to me a cyclic and bit
over[vbcol=seagreen]
> head) .
> Note : there will be more that 30 table updates which needs to be send
> back-forth
> do you have any suggesstion
>
> "Hilary Cotter" wrote:
is[vbcol=seagreen]
have[vbcol=seagreen]
going[vbcol=seagreen]
going[vbcol=seagreen]
the[vbcol=seagreen]
on[vbcol=seagreen]
?[vbcol=seagreen]
act[vbcol=seagreen]
not[vbcol=seagreen]
and[vbcol=seagreen]
unmodified[vbcol=seagreen]
B[vbcol=seagreen]
Updates[vbcol=seagreen]
Let[vbcol=seagreen]
|||Thanks for the info .
Can you please suggest me a mechanism for updatating the data back to the
primary server from the Processing A DB .
I am planning to use replication from Processing A back to the primary
server ... any suggestion here
Raj
"Hilary Cotter" wrote:

> it will be cyclic unless you can figure out some criteria to use as a basis
> for not applying the transaction. Use this criteria as a basis as to whether
> the proc applies the data on the subscriber/publisher or not.
> --
> 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
> "Raaj" <Raaj@.discussions.microsoft.com> wrote in message
> news:127378BB-BBAB-4F77-9A10-817170920414@.microsoft.com...
> over
> is
> have
> going
> going
> the
> on
> ?
> act
> not
> and
> unmodified
> B
> Updates
> Let
>
>
|||replication with custom stored procedures.
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
"Raaj" <Raaj@.discussions.microsoft.com> wrote in message
news:501BF2AB-FF83-4EE4-9927-F80606684F85@.microsoft.com...[vbcol=seagreen]
> Thanks for the info .
> Can you please suggest me a mechanism for updatating the data back to the
> primary server from the Processing A DB .
> I am planning to use replication from Processing A back to the primary
> server ... any suggestion here
> Raj
> "Hilary Cotter" wrote:
basis[vbcol=seagreen]
whether[vbcol=seagreen]
back to[vbcol=seagreen]
and[vbcol=seagreen]
bit[vbcol=seagreen]
execute[vbcol=seagreen]
might[vbcol=seagreen]
this[vbcol=seagreen]
this[vbcol=seagreen]
Push[vbcol=seagreen]
field[vbcol=seagreen]
the[vbcol=seagreen]
Updates[vbcol=seagreen]
with[vbcol=seagreen]
will[vbcol=seagreen]
you do[vbcol=seagreen]
Primary[vbcol=seagreen]
Primary[vbcol=seagreen]
replication[vbcol=seagreen]
for a[vbcol=seagreen]
own[vbcol=seagreen]
..The[vbcol=seagreen]
DB.[vbcol=seagreen]
Processing[vbcol=seagreen]
this .[vbcol=seagreen]