HostWeb Forums » Microsoft Databases » microsoft.public.sqlserver.replication » Complex Condition Based Transactional Replication
Topic: Complex Condition Based Transactional Replication
I need to implement a transactional replication on SQL Server 2005, which is
condition based and i would like to know the best approach for the same. I
have provided below the approach i am going to take, you suggestion are
welcome.
Table "AccountTransactons" has column AccountID & TransactionID
Table "AccountTransactonsHistory" has column AccountID and StatusID.
When the column "StatusId" in table "AccountTransactonsHistory" is updated
as "3" for a AccountID, all the rows in "AccountTransactons" which has the
equivalent AccountID should be replicated to (update or insert) another
database.
So this is what i am planning to do. The column "StatusId" gets updated via
a procedure "ups_UpdateStatusID". This procedure has 2 parameters
'@AccountID' & '@StatusID'. I am planning to replicate the execution of
procedure "ups_UpdateStatusID" .
The code in the procedure will be different at publisher and subscriber. In
the Subscriber, the procedure will select the table "AccountTransactons" in
publishing database where the column AccountID equivalent to the parameter
'@AccountID' of procedure "ups_UpdateStatusID" and insert or update in the
subscriber.
Please let me know if you can think of a better approach. Please let me know
asap as i need to close the design by end of this week.
Why i would use replication here?
a) The process should be asynchronous and should be online (least latancy).
b) i am a dba.
Appreciate the help and for sparing the time,
Raj
Replies below ↓
Replies
Re: Complex Condition Based Transactional Replication
This is one way of doing it, but it is not scaleable. The network hop that
would be necessary for this dml can be expensive and if the link goes down
or the publisher is busy your distribution agent can hang.
I would try to create an indexed view on the publisher of what you want the
row to look like in your subscription datbase. Then replicate this indexed
view as a table to your subscriber.
"Raj" <Raj@discussions.microsoft.com> wrote in message
news:2DCDFFA0-9BA5-4F2C-A242-49BD3044900B@microsoft.com...
>I need to implement a transactional replication on SQL Server 2005, which
>is
> condition based and i would like to know the best approach for the same.
> I
> have provided below the approach i am going to take, you suggestion are
> welcome.
>
> Table "AccountTransactons" has column AccountID & TransactionID
> Table "AccountTransactonsHistory" has column AccountID and StatusID.
>
> When the column "StatusId" in table "AccountTransactonsHistory" is updated
> as "3" for a AccountID, all the rows in "AccountTransactons" which has
> the
> equivalent AccountID should be replicated to (update or insert) another
> database.
>
> So this is what i am planning to do. The column "StatusId" gets updated
> via
> a procedure "ups_UpdateStatusID". This procedure has 2 parameters
> '@AccountID' & '@StatusID'. I am planning to replicate the execution of
> procedure "ups_UpdateStatusID" .
>
> The code in the procedure will be different at publisher and subscriber.
> In
> the Subscriber, the procedure will select the table "AccountTransactons"
> in
> publishing database where the column AccountID equivalent to the parameter
> '@AccountID' of procedure "ups_UpdateStatusID" and insert or update in
> the
> subscriber.
>
> Please let me know if you can think of a better approach. Please let me
> know
> asap as i need to close the design by end of this week.
>
> Why i would use replication here?
> a) The process should be asynchronous and should be online (least
> latancy).
> b) i am a dba.
>
> Appreciate the help and for sparing the time,
> Raj
>