Replication with publication and subscription having different data

Question

Post reply

Replication with publication and subscription having different data

S_Kumar_S

SSCrazy Eights

Points: 9037
More actions
December 23, 2013 at 6:05 am

#280629

Hi
I am replicating a table A from Server A to ServerB with transactional replication.
Published article has around 1 Billion row with one of date column value InsertedOn< Getdate()-30. Basically we keep around 1 month of data in table
and delete the data older than 1 month by running a job every 10 min. This job deleted data in batch of 50000.
The requirement now is to keep only 1 week of data on subscriber. I can't delete the data older than a week on subscription else replication will throw error that data row
was not found. Ignoring the error will be my last resort.
Can someone throw some more ideas on how this can be achieved?
thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andrew G SSChampion Points: 12809 More actions · Answer 1

You can create a static row filter on the published article so that only column > getdate() -7 is published.

http://technet.microsoft.com/en-us/library/a6ebb026-026f-4c39-b6a9-b9998c3babab

The other option is to create an indexed view, basically subset the data on the publisher then replicate.

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 2

for the benefit of those who fall across this thread, here is I solved this:

I created a proc on main server which was schedule and it will delete the records older than 1 month. Then I replicated the execution of this proc to target server. Then I changed the definition of proc on target to delete data older than 1 week.

Goal achieved 🙂

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 3

I've only just seen this post. Your solution is precisely what I was about to suggest. There is no other way to achieve it.

deep_kkumar Hall of Fame Points: 3243 More actions · Answer 4

S_Kumar_S (12/31/2013)
for the benefit of those who fall across this thread, here is I solved this:
I created a proc on main server which was schedule and it will delete the records older than 1 month. Then I replicated the execution of this proc to target server. Then I changed the definition of proc on target to delete data older than 1 week.
Goal achieved 🙂

What if the data on publisher gets updated(30 days retention) and if that row is not present on the subscriber ? Replication will fail as it could not find the data on subscriber( 1 week retention)

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 5

deep_kkumar (12/31/2013)
S_Kumar_S (12/31/2013)
for the benefit of those who fall across this thread, here is I solved this:
I created a proc on main server which was schedule and it will delete the records older than 1 month. Then I replicated the execution of this proc to target server. Then I changed the definition of proc on target to delete data older than 1 week.
Goal achieved 🙂
What if the data on publisher gets updated(30 days retention) and if that row is not present on the subscriber ? Replication will fail as it could not find the data on subscriber( 1 week retention)

You would receive a replication error "Row not found" but you could create a customised replication procedure on the subscriber to cater for this if it became a problem.