April 27, 2010 at 12:56 am
I have a standard database which holds my vital data, and a historical database that contains all obsolete data (let`s say all entities of some kind older than 3 months). I would like to easily copy certain entities into historical database (one-way replication ONLY). What`s more, older entities will be deleted from primary database once a week. I don`t want those DELETE statements to be copied into historical database (what`s once copied into historical database, should be kept there forever - nothing should be deleted from this database).
Question is - what replication model should I choose? Will log shipping be good choice?
Thank you for advices
April 27, 2010 at 2:16 am
Replication/log shipping/mirroring is not the right option for data archival.
Recommend you to implement this through SSIS package.
April 27, 2010 at 3:22 am
How would you justify this? In other forum transactional replication was recommended to me and it really seems to require much less work than SSIS.
April 27, 2010 at 3:34 am
Log shipping wont help you as if you change the source database by deleting records the target records will also be deleted in the logs that get restored to the target archive database.
With transactional replication you can modify the scripts to not perform deletes on the target database, its a bit of a hack but it is possible.
April 27, 2010 at 3:52 am
Is it possible to instruct log shipping not to ship delete statements (or to instruct archive DB not to read them)?
April 27, 2010 at 4:14 am
As far as I know log shipping won't let you do this, log shipping is exactly what the name implies a copy of the logs shipped our to a new environment and restored. So in the logs your records will have been deleted. It will be far easier to modify the replication scripts.
Set up replication as normal, then review the scripts for the deletes and revise them (comment the drop statements out), test it to see if you've done it correctly before implementing in a production environment.
April 28, 2010 at 6:50 am
The HR system we have accomplishses this by way of triggers on the tables that are to be audited. It copies updates (before and after) as well as deletes into an archive database on the same server. I'm not sure how this would work in a multi-server scenario. Maybe a hybrid of db triggers + replication of the archive db?
April 28, 2010 at 8:10 am
kuzwamac9 (4/27/2010)
I have a standard database which holds my vital data, and a historical database that contains all obsolete data (let`s say all entities of some kind older than 3 months). I would like to easily copy certain entities into historical database (one-way replication ONLY). What`s more, older entities will be deleted from primary database once a week. I don`t want those DELETE statements to be copied into historical database (what`s once copied into historical database, should be kept there forever - nothing should be deleted from this database).Question is - what replication model should I choose? Will log shipping be good choice?
Thank you for advices
What you are describing is an archiving process, not a replication scenario.
Usually there is a date (for example G/L Post Date) from which you leave records in the system for a set period, and you use this to create a job to move them from an active table to another table that may or may not be viewable by general users of the database.
Pick a status and / or date where changes do not occur, and use this to design a package that you schedule to copy / append to the other database, and then delete from the source.
Be sure to have some audits in the process.
Forever is a long time. Usually there is some progression - production, archive, then to a long term media.
Greg E
April 28, 2010 at 8:29 am
I happen to agree that this is not a replication scenario but is an archive/audit scenario. It is likely possible with replication and transactional replication with custom sprocs was my first thought when I read this. But on reflection I am not so sure that is the right answer.
How many tables are we talking about?
What is the transaction volume?
What method is used to determine records to archive?
What are you trying to track? Inserts, Updates, Deletes? At the row or field level?
How many fields are typically updated at a time?
CEWII
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply