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.
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.
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.
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.