Merge Replication with Limiting Data in Subscriber

I am using merge replication with row filtering to limit replication of a
table for the past 10 days of data. The problem is that I want the
subscribers to automatically remove the data that is older than 10 days
after the replication is done (assume that the table has a date field).
Question 1:
Using row filters if I make a change on the subscribers (in the event that
it is disconnected for more that 10 days), will the data that is outside of
the 10 days be send back to the main server (the subscriber is updated even
when disconnected).
Question 2:
How can I get the subscribers to delete the data outside of the 10 days ONLY
after replication is completed successfully.
Thanks
1) The filter is evaluated using todays day. So if your filter looks like
this where DateEntered >=Getdate()-10 and your subscriber is offline for 13
days, the filter will evaluate use todays date, and not the date the data
was entered in the subscriber.
2) AFAIK You can't use merge replication for this. I'd consider
bi-directional transactional replication as you can use custom stored
procedures to build this functionality.
"Darion Mapp" <godd@.caribsurf.com> wrote in message
news:#wwkoN1EEHA.2212@.TK2MSFTNGP09.phx.gbl...
> I am using merge replication with row filtering to limit replication of a
> table for the past 10 days of data. The problem is that I want the
> subscribers to automatically remove the data that is older than 10 days
> after the replication is done (assume that the table has a date field).
> Question 1:
> Using row filters if I make a change on the subscribers (in the event that
> it is disconnected for more that 10 days), will the data that is outside
of
> the 10 days be send back to the main server (the subscriber is updated
even
> when disconnected).
> Question 2:
> How can I get the subscribers to delete the data outside of the 10 days
ONLY
> after replication is completed successfully.
> Thanks
>
|||Darion,
perhaps you could cheat a little here. If you update the old rows on the
subscriber, then synchronize, replication (the merge agent) should remove
them for you. So, you could do an update to the same value on another
column -
update table set col1=col1 where datediff(dd,dateentered, getdate()) > 10.
This won't effectively change the publisher and will be replicated back to
the subscriber as a delete because it doesn't satisfy the filter clause
(view).
Regards,
Paul Ibison
|||Let me fully explain. The table under question is appended to daily. each
row that is added is date stamped. the subscriber is on a laptop. so when
the laptop is off the network there are rows added to its copy of the table
and i want to know if the laptop is reconnected to the network about 15 days
after will all 15 days of data get back to the main server or not? given
that the replication of the table is setup with a row filter that limits its
replication to 10 days worth of data from the main server to the laptop.
"Darion Mapp" <godd@.caribsurf.com> wrote in message
news:%23wwkoN1EEHA.2212@.TK2MSFTNGP09.phx.gbl...
> I am using merge replication with row filtering to limit replication of a
> table for the past 10 days of data. The problem is that I want the
> subscribers to automatically remove the data that is older than 10 days
> after the replication is done (assume that the table has a date field).
> Question 1:
> Using row filters if I make a change on the subscribers (in the event that
> it is disconnected for more that 10 days), will the data that is outside
of
> the 10 days be send back to the main server (the subscriber is updated
even
> when disconnected).
> Question 2:
> How can I get the subscribers to delete the data outside of the 10 days
ONLY
> after replication is completed successfully.
> Thanks
>
|||Darion,
the 15 days of data will go to the publisher. The first 5 days of data
(oldest 5 days) will be replicated back as deletes to the subscriber but
will remain on the publisher. Is that the behaviour you want?
Regards,
Paul Ibison
|||That is EXACTLY what i wanted to know.
Thanks Paul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:etGkHP2EEHA.2460@.TK2MSFTNGP10.phx.gbl...
> Darion,
> the 15 days of data will go to the publisher. The first 5 days of data
> (oldest 5 days) will be replicated back as deletes to the subscriber but
> will remain on the publisher. Is that the behaviour you want?
> Regards,
> Paul Ibison
>