Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replication with publication and subscription having different data


Replication with publication and subscription having different data

Author
Message
S_Kumar_S
S_Kumar_S
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 1062
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
Andrew G
Andrew G
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2003 Visits: 2227
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
S_Kumar_S
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 1062
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 Smile

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
MysteryJimbo
MysteryJimbo
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 15327
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
deep_kkumar
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 756
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 Smile


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
MysteryJimbo
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 15327
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 Smile


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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search