September 21, 2012 at 6:20 am
Hi
We have a tableA with around 100 million records and it is being replicated to table B.
Now since the size has grown significantly, we plan to archive the tableA for data older than 1 year.
Which Approach will be better:
1.
Put all data of tableA into ARC_tableA. Delete data from ARC_tableA which is just 1 year old so that this table just have data older than 1 year.
Then create a proc with delete statement in btaches of 50000. Replicate this proc to destiniation server. Purpose of replicating the proc is to send the execution of proc over network to destination server instead of several delete commands.This way we won't be sending the batches of delete statements and only execute statement will be replicated and executed over destination server.
2.Break the replication. Run delete statemets on individual servers. Set up the replication again.My intention is to break the replication as last rsort.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
September 21, 2012 at 7:51 am
S_Kumar_S (9/21/2012)
HiWe have a tableA with around 100 million records and it is being replicated to table B.
Now since the size has grown significantly, we plan to archive the tableA for data older than 1 year.
Which Approach will be better:
1.
Put all data of tableA into ARC_tableA. Delete data from ARC_tableA which is just 1 year old so that this table just have data older than 1 year.
Then create a proc with delete statement in btaches of 50000. Replicate this proc to destiniation server. Purpose of replicating the proc is to send the execution of proc over network to destination server instead of several delete commands.This way we won't be sending the batches of delete statements and only execute statement will be replicated and executed over destination server.
2.Break the replication. Run delete statemets on individual servers. Set up the replication again.My intention is to break the replication as last rsort.
Option 1. Replicate execution of the procedure is your best option as either way you need to clean up the subscriber. At least this way it is done in a managed way with no down time.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply