December 23, 2013 at 6:05 am
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.
December 23, 2013 at 6:55 pm
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.
December 31, 2013 at 5:37 am
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.
December 31, 2013 at 5:54 am
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.
December 31, 2013 at 8:56 am
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)
January 2, 2014 at 8:30 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply