November 8, 2010 at 6:47 am
I maintain an application that uses SQL 2005 / 2008 for data persistence.
Some clients use Merge Replication (pull or push) to replicate data.
Some of the tables contain information that should never be erased.
All versions of a row from such a table should be stored, somewhere in a database.
This means all updates and deletes to those rows should result in in 2 affected rows:
• a copy of the row before the change
• a row containing the change (or the deletion of the row in question)
I plan to achieve this using AFTER UPDATE,DELETE triggers on all the tables for which "versioning" is required.
The old versions of a row may be stored in the same database, an other database (or a database on a linked server).
I was hoping to do this by creating NOT FOR REPLICATION triggers on the published database.
The theory was:
All update and delete queries performed against a subscribed database should be replicated (for concurrency) to the publisher.
When the updates and deletes are replicated, the triggers on the published database will be fired, thus also updating the table containing the version rows.
There seem to be 2 problems with this approach:
1/
An update query against a table of the subscriber wil often result in mupltiple update queries performed against the published database during replication.
The merge triggers seem to generate multiple update statements.
2/
multiple updates of the same row in the subsciber database do not result in correct versioning. Only the end-result appears to be replicated. (this was unexpected because of the 'first to publisher wins' rule).
Right now, it seems that i will have to create versioning triggers on all subscribers and on the publisher. And keep diffirent versions of the rows in the same database as the source database, for maintability (database structure changes 2-4 times a year).
Anyway... since the requirement (keep all versions of rows for some of the table) seems like something you guys may have implemented... i am very curious as to how you guys would do this.
All thoughts, tips, .... would be appreciated.
Thx
November 8, 2010 at 8:15 am
What I've got for something like that is an XML-based "before" log.
If you look in the articles on this site for my Audit Trails and Logging piece, and look in the discussions on that, you'll see what there. The article is split in two parts, and the discussions are important on both.
Have you read those?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 8, 2010 at 8:20 am
I'm reading them now. Thanks for the pointer G Squared
November 8, 2010 at 8:30 am
I've read both parts of Audit Trails an Logging and learned a few things in the process. Specifically other view point on trigger code.
My problem is not writing the trigger scipts though.
The question i'am trying to answer is:
What is the best way to implement an active logging when a database used is in (merge) replication?
In this situation I have x amount of databases which may be updated by users.
Ideally, i would like to have 1 database with logging for all the changes applied to subscribers and the publisher.
November 9, 2010 at 6:33 am
I'd have to play with that a bit, but it seems to me the place to start would be to log at the publisher and the subscriber, and have the publisher merge in the subscriber logs to a table at the publisher.
Or, just log at the master copy of the database and you should get everything.
A log parser and passive logging at the master copy (publisher) should get you very reliable logging, though, and then you don't have to worry about trigger overhead or anything like that. Try out a log parser and see if it'll get you what you need. It's usually a more-than-good-enough solution.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply