May 2, 2006 at 2:38 pm
I am looking at replicating data from a production database to a reporting data. The twist is when a row is updated in production that row needs to be inserted into the corresponding table in reporting. The reporting version of the table schema will have an addition date-time column to maintain uniqueness. By inserting rows into a reporting table, a history of the table values will be maintained. The reporting database can't be older than 5 minutes for table updates from production.
Is this is possible with sql server 2005 or 2000?
Since an update can happen multiple times per second, each update will result in a new insert into reporting.
David Bird
May 2, 2006 at 2:48 pm
Yes, it is possible.......just not through the GUI wizard.....you have to build it out manually through script
set up the publications to do the replication through using procs, then modify the update proc to do the insert instead......I've used this methodology several times in different ways very successfully.
May 4, 2006 at 6:37 am
Alternatively you could capture the update in a publisher trigger and write to an audit table, and then replicate this table as per usual. Not as performant as the previous reply, but perhaps more maintainable.
Cheers,
Paul Ibison SQL Server MVP, http://www.replicationanswers.com
Paul Ibison
Paul.Ibison@replicationanswers.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply