Archive data through replication

Question

Archive data through replication

Gopi S

Ten Centuries

Points: 1091
More actions
May 5, 2015 at 5:44 pm

#317298

Hi DBA's,
I'm looking for a process to archive data through replication. I have nightly job that purge records in few source tables(publisher) retaining only 3 yrs data. I have archive database (subscriber) that contains prior 3 yrs data and current 3 yrs data. Before nightly job DELETES records in Source table i want to STOP replication so that the delete is not replicated in archive database. After the job completes i would like to TURN ON replication so that any new inserts and updates in Source will ONLY be replicated in archive database.
My DBA tested this but after last step of turning replication back ON archive database is sync'd with source table.
Please suggest if this is possible or any other best way to do.
There are around 70 tables where 30 of them are transactional tables that needs record purge. Developing ETL process is possible but tedious.

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

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Gopi S (5/5/2015)
Hi DBA's,
I'm looking for a process to archive data through replication. I have nightly job that purge records in few source tables(publisher) retaining only 3 yrs data. I have archive database (subscriber) that contains prior 3 yrs data and current 3 yrs data. Before nightly job DELETES records in Source table i want to STOP replication so that the delete is not replicated in archive database. After the job completes i would like to TURN ON replication so that any new inserts and updates in Source will ONLY be replicated in archive database.
My DBA tested this but after last step of turning replication back ON archive database is sync'd with source table.
Please suggest if this is possible or any other best way to do.
There are around 70 tables where 30 of them are transactional tables that needs record purge. Developing ETL process is possible but tedious.

Quick thought, you can horizontally partition the tables at the subscriber, pushing everything older than 3 years into an archive set, either by a trigger or a job. No need then to flip the replication on and off.

😎

happycat59 One Orange Chip Points: 29387 More actions · Answer 2

You could try changing the definition of the articles so that DELETE commands are not replicated. This may or may not be what you actually need - e.g. what should you do with DELETEs for "current" data.

Gopi S Ten Centuries Points: 1091 More actions · Answer 3

Hi,

I need DELETE to be replicated only for current 3 yrs data. Replication should not happen when the nightly job DELETE data older than 3 yrs. So what i did is when configuring publication i added filter like SELECT <published_columns> FROM [dbo].[transactiontable] WHERE Created >= DATEADD(mm,-12,getdate()).

This worked fine for the first time when i delete record older than 12 months and record retained in archive database. But when i delete record created after 12 months it clears all records older than 12 months + the record i deleted newly.

I'm using Transactional replication option when configuring Publication with above filter condition. Is there other specific settings i have to use so that records that gets INSERT/UPDATE/DELETE for above filter gets replicated and records prior to 12 months is retained.

Gopi S Ten Centuries Points: 1091 More actions · Answer 4

I finally made it to work by using filters when defining table lists to be replicated.

Thanks for information.

happycat59 One Orange Chip Points: 29387 More actions · Answer 5

Another option could be to implement a stored procedure for the delete commands - the logic for when to apply the delete commands would be in the stored procedure. Replication will still think that the delete is being done for the old transactions but the stored proc will simply do nothing with them