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.