June 21, 2010 at 11:28 am
This is technically a replication question but I don't see a replication forum for 2008, so I will put it here.
We have an unusual request put to us here, and I am not sure what is the best path to take (if any even exists). Basically the application team wants to have real-time replication to a report server. In theory this would be an easy task, with straightforward transactional replication. One wrench in the works, though -- they archive old data periodically, and they want to prevent these archived changes from replicating. So basically they would have the entire history of the DB including the most recent changes realtime, and replication has to be smart enough not to replicate the archive process (unknown to the DBA group what that looks like).
Obviously off-the-shelf replication isn't sufficient to do this, but is there any third party product that could do this? Or will this require some form of painful custom programming to replication to achieve?
June 21, 2010 at 12:32 pm
There are ways to do this with replication (Transactional).
1. You can choose in your replication properties *NOT* to replicated deleted transactions for that article/object.
The disadvantage in using this if there is a delete done by the application not related to archiving process, you will not delete it.
2. You can modify the replication stored proc for deletes on the subscriber to check for some flag before applying the delete.
For example you add a column to the table called archive flag. In the archive process, you update all records to be archived with the Archive flag and then delete. In subscriber, modify your replication stored proc for delete to check for the flag before applying.
3. Create an archive table in the publisher and subscriber and let the archiving process move old records to that table.
Is this what you were asking for?
-Roy
June 21, 2010 at 12:39 pm
It helps, it confirms my thinking. 1) and 3) are not options here -- they want to use the existing schema for all reporting (since this is a report source) and don't want to reprogram their tasks, and the app can do deletes, so I can't just disable deletes. I sort of figured 2) would be the only out-of-the-box option, but I don't know if that will be feasible or not. Also checking to see if there was any third party solution. Given the expectations and their timeline, we may simply have to tell them that they can't do it without adding extra time for custom programming that they probably aren't expecting to do.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply