December 1, 2020 at 12:21 am
I have a SQL 20XX database in an on-premise data center that has a table, call it acct.trans_history, with about 1 million rows in it generated per day (and truncated at the end of the day). It is a table written by an old accounting application.
This is a table that looks like an accounting ledger. Records are added to the table for each accounting event to these columns: date (datetime), event_type (int), is_debit (bit), transaction_value (decimal 18,2). Records are never deleted or updated once they have been inserted. At the end of the day, as part of a nightly scheduled job, the table is truncated to allow the next day's entries to populate.
What I want to do is write an app that will raise an event in an Azure Event Hub for each entry written. So, when the accounting app writes a row, some magic app pushes a payload to an Azure Event Hub so various downstream consumers of that hub can respond to the event. I would like this in as close to real-time as possible -- meaning a table write to acct.trans_history might raise an event in this Event Hub within a few minutes.
What would be the best way of getting table writes in an on-premise SQL server up to an Event Hub? Would I write an app that queries the table and raises the events? If so, what do I do if the table is truncated before the app finishes querying the table? Is there an Azure technology I should be looking at instead? Is this a change data capture table sort of thing?
Any tips offered would certainly go along away and would be much appreciated.
December 1, 2020 at 3:11 pm
I haven't set up something like this, so let's get that out of the way up front.
First thing that comes to mind is simple INSERT triggers. However, generally, I don't like triggering behaviors. Instead, querying usually works better and isn't masked. If it only needs to be near real time, and you have the datetime of the records going in, run a query every five minutes to capture the data and then fire the results into the Event Hub (or every 3 or 2, testing & experimentation here). You can even correlate and control this in conjunction with the truncate, so you don't lose the info. It will provide less overhead and a more directly controlled mechanism that trying to do this through table triggers or using something like Service Broker (the other option that comes to mind).
CDC is not what you're looking for here. That's much more oriented to capturing updates & deletes to existing data, not simply inserts.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 1, 2020 at 4:32 pm
First thing that comes to mind is simple INSERT triggers. However, generally, I don't like triggering behaviors. Instead, querying usually works better and isn't masked.
This seems like one of the times where triggers are the right mechanism. There's no complex logic in the trigger, there's nothing complicated happening to the table, updates/delete etc.... that might trip up triggers. It's just a simple move of the data on insert.
December 1, 2020 at 7:48 pm
This seems like one of the times where triggers are the right mechanism. There's no complex logic in the trigger, there's nothing complicated happening to the table, updates/delete etc.... that might trip up triggers. It's just a simple move of the data on insert.
I won't argue. It still makes me uncomfortable.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 1, 2020 at 8:23 pm
I think this fits in with service broker much more than using triggers. The service broker would monitor the table and generate a message for every insert - and call out to a service to send the data to the event hub.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply