Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2008
    Posts
    11

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

    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

  2. #2
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    Quick method would be to use a trigger to write the change to a change_log table, and then have your task pickup the changes from the change_log table. You could also look into replication.

    -A

  3. #3
    Join Date
    Apr 2008
    Posts
    11
    wouldn't adding another table add an extra level of reads and writes ?

    I was hoping to do away with the polling task completely.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    WShen you upgrade to 2k5, you might want to look into database snapshots. It can be an ideal solution, depending on your specific requirements.
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    Quote Originally Posted by thebends
    wouldn't adding another table add an extra level of reads and writes ?

    I was hoping to do away with the polling task completely.
    If you're having concurrency issues on that table; moving the changes to a separate table would at least offload the reads for the constant polling. If that's not acceptable either, then I would recommend looking into SQL Server replication, but sounds like it may be overkill if this is the only table that needs to be synced up.

    -A

  6. #6
    Join Date
    Apr 2008
    Posts
    11
    Thanks, I guess the change table method this is the way to go. Would you recommend writing the whole record to the change table, or just the PK ?

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by thebends
    Would you recommend writing the whole record to the change table, or just the PK ?
    What are your business requirements?
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2008
    Posts
    11
    Here's maybe a clearer overview :

    1. Database A pulls data from an external source (oracle) and stores the data as-is
    2. Database B is a normalized form of database A
    3. Any inserts, updates or deletes in A should be reflected in B as soon as possible
    4. The row relationship from A to B is many-to-one
    5. Database B is queried by application clients

    Note that we are only dealing with one table here on both A and B, not the whole database.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Nice username btw - Radiohead fan perchance?
    Quote Originally Posted by thebends
    Database B is queried by application clients
    If you were to only store the primary key values, how do you intend on querying for other information? linked servers?
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I assumed he meant only store the PK in the "auditing" table and link that to the Table in database A when populating database B. Given the requirement I would say that is fine.

    If there are relatively few updates between polls (less than ~3% of the table), you could have a an indexed date field in the table instead. Have the trigger update this everytime something changes. Select rows from the table where this date is between theLastUpdate and GETDATE(), storing the value of GETDATE() so you can use it the next time you poll the table.

    Just another option. Storing the PK in a second table is effectively just flagging rows for moving across servers so is arguably more than you require.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    Quote Originally Posted by thebends
    Thanks, I guess the change table method this is the way to go. Would you recommend writing the whole record to the change table, or just the PK ?
    If you're having concurrency issues with Table A; then I would recommend writing out the PK from Table A, along with the columns you want to monitor. That way the Polling procedure doesn't have to perform a read operation on Table A to perform the Update on Database B.

    -A

  12. #12
    Join Date
    Apr 2008
    Posts
    11
    Quote Originally Posted by georgev
    Nice username btw - Radiohead fan perchance?

    If you were to only store the primary key values, how do you intend on querying for other information? linked servers?
    Very big Radiohead fan, but not of most of their new stuff unfortunately

  13. #13
    Join Date
    Apr 2008
    Posts
    11
    Thanks for the suggestions - I think I'll go with writing all the required fields to the shadow table to avoid loading A.

    Does anybody have good polling strategies ? The most straightforward probably is :

    1 - read, say, 100 records
    2 - process the records
    3 - mark the records as processed.

    This sounds quite expensive though.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by thebends
    Very big Radiohead fan, but not of most of their new stuff unfortunately
    I've not heard anything off their new album; I really should get it off their website (I assume they're still doing that whole "give us what you think it's worth" thing?).

    And I have to thank you; your username was enough to make me dust one of their albums off and enjoy
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •