March 31, 2023 at 10:42 am
Hello folks,
Even writing the title, it sounds oxymoronic but bear with me.
We have a third party vendor application with it's own front end the business uses.
Specific tables from the vendors database are replicated into our SQL server via a vendor managed process. (AWS Data Sync)
Historically we have made use of the data via views for our in house application (GIS).
We have upgraded the in house GIS application to another vendor supplied solution and we're reworking the "glue ware".
The new application has its own, very specific SQL database, including its own schema.
As it stands the existing views we had been using won't work as they are not owned by the new application's Schema owner.
I've been considering the idea of instantiating the views as tables in the new database under the application's schema.
The rub is the data updates need to be near immediate(within the second). This is the current performance.
I have considered triggers but as mentioned, we can't modify the source database tables by adding triggers.
I'm racking my brain and Google looking for a mechanism I can latch onto to trigger a simple (Truncate/Select into) script when the vendor database is updated that doesn't involve touching the tables themselves.
Simpler terms I'm not fussed by the specifics of a transaction, just that a transaction (insert/update/delete) has occured and I can use that event to initiate an update in another database.
Any help appreciated
Andy.
March 31, 2023 at 1:45 pm
Well, honestly, this is a tough one. However, I'd probably put Extended Events to work to watch for object changes. However, you could also look to Event Notifications, although that's build on Trace, which will automatically mean a higher impact on performance on the server. The events you're most interested in are Object Created, Object Deleted and Object Altered. I have a blog post highlighting some of this behavior.
"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
April 2, 2023 at 1:20 am
Thanks Grant.
I did some reading and others and I see where Extended Events and Event Notifications might help.
The examples I looked at were wrapped around analysis of process but I couldn't find a good example with a more "trigger like" response to a logged event.
They all seemed to revolve around reading a system table and I'm not sure that's going to help me.
There didn't seem to be a way to direct output to a non-system table or indeed directly execute a script as a result of the Extended Event occurring.
I realise this was not the core design for EE but maybe I missed something and you could clarify?
I did find this quite comprehensive document with bot EE and EN examples http://www.cherrycreekeducation.com/bbk/b/wpd_sql_extevtandnotif_us_sw_01112012_1.pdf
Further reading from the PDF cites this document https://www.sqlskills.com/blogs/jonathan/event-notifications-vs-extended-events/
Based on this careful implementation of EN looks closer to my challenge.
I'm having trouble adapting the examples I've read. Assuming one of the many events/notifications is the right event is there a way to run a simple script or are he documented examples the only way to latch onto these Events/Notifications?
Thanks
Andy
April 3, 2023 at 12:26 pm
No, to use Extended Events for this, you'd have to have a second process that monitors the events for the action in question and then respond from there. There is no way, simply within ExEvents, to do anything like a trigger or response.
Event Notifications are probably a better solution, but they have added overhead too. In order to use them, you'll have to write the code that consumes the vent, then based on whatever filter criteria you have, responds in some way. About half the work you'd have to do to use ExEvents for the same thing.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply