Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replication with publication and subscription having different data Expand / Collapse
Author
Message
Posted Monday, December 23, 2013 6:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 7:28 AM
Points: 380, Visits: 899
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


-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1525508
Posted Monday, December 23, 2013 6:55 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:43 PM
Points: 1,282, Visits: 1,762
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.
Post #1525668
Posted Tuesday, December 31, 2013 5:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 7:28 AM
Points: 380, Visits: 899
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 :)


-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1526730
Posted Tuesday, December 31, 2013 5:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
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.
Post #1526732
Posted Tuesday, December 31, 2013 8:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 8:13 PM
Points: 124, Visits: 750
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)
Post #1526775
Posted Thursday, January 2, 2014 8:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
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.
Post #1527146
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse