Polling changes on a db, and copying changes to another db

Question

Polling changes on a db, and copying changes to another db

sherif_omar

SSC Enthusiast

Points: 100
More actions
April 3, 2008 at 1:12 pm

#123275

Hi all, I'm in the process of cleaning up a very VERY poorly implemented system. One of the problems I'm tackling consists of :
1 - Database A
2 - Database B (different schema than A)
3 - A task which regularly polls A, checks for updated/new records and inserts them (with some transformation) into B
As you can imagine, the overhead of the polling is killing the system. What I want to know is :
What is the best way for A to notify B whenever there are changes to a particular table on A ?
I'm running 2000 but we will most probably upgrade to 2005.
thanks in advance to everybody
Omar

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

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

ksullivan SSCrazy Points: 2346 More actions · Answer 1

Add triggers to the A tables. Depending on how the tables are set up and how many changes they get, and so forth, you could have the triggers write to 1 table with something like primary key, name/id of table, activity date, type of change (insert, update, delete) then poll that 1 table. Or, make one trigger log per table with roughly the same data (obviously you can skip name/id of table here) and poll all of them.

sherif_omar SSC Enthusiast Points: 100 More actions · Answer 2

Thanks, that definitely sounds like the way to go. So the polling process would be something like :

1 - Poll change log table

2 - Read last, say, 100 rows

3 - Process those rows

4 - Mark the rows as consumed / delete the rows

Does this sound ok ? I am worried about the cost of step 4.

ksullivan SSCrazy Points: 2346 More actions · Answer 3

Mind you, this sort of thing is a band-aid, quick and dirty fix and should not be used on critical stuff. If you are going to be stuck with the A and B sets for the forseeable future, then use SQL Server's replication. Keeping tables in synch is what it is designed for. In fact, look into replication before rolling your own code.

1 - Poll change log table
2 - Read last, say, 100 rows
3 - Process those rows
4 - Mark the rows as consumed / delete the rows

Instead of arbitrarily reading the last 100 rows or so, keep track of the times when the polling happens and read everything since the last poll. Simple table** will suffice listing log read, stop and start datetimes. Don't use getdate() in all of your select/delete code. Set a variable for @now and pass it along otherwise the last parts of your code may process records that are added after the process starts. Depending on how much data you can tolerate and how much lag time is ok, you could set up a job for #4 that deletes "old" records, preferably based on the ** table once a day, once a week, whatever. Avoid updating the log records as consumed because that may take too much time. Delete based on the date added fields. In essence you have 3 parts operating independently:

add records to logs

polling the logs

deleting from the logs

sherif_omar SSC Enthusiast Points: 100 More actions · Answer 4

Hi,

I probably should add that this process occurs only on 2 tables in A, not the whole db.

I was considering transaction replication, but since it's not a direct 1-to-1 replication, I am afraid of the overhead of the additional data transformation. How would you recommend doing the transformation when combined with a replication ? DTS ?

Thanks for the ideas on the polling, definitely better than what we have now.

regards

Sherif

ksullivan SSCrazy Points: 2346 More actions · Answer 5

How would you recommend doing the transformation when combined with a replication ? DTS ?

For "homegrown" replication, I would do the transformations in stored procedures. That's more out of personal preference and the mixed environment in which I work than anything else. For true replication, offhand, I do not recall what transformation options are available.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

If you can wait until Sql2005, than I would recommend a very similar approach, except use asynchronous triggers writing to Service Broker queues. This will take care of most of the decoupling, queuing/dequeuing and other tricky stuff for you and the performance is great.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]