cppprogrammer (11/23/2014)
Hi,I'm investigating viable solutions to the following problem. A 3rd party is inserting data into a database we own. Currently I do not know if they have a method of calling a webservice instead (that would be preferred but is an unknown atm). On insertion of the data, we want to execute a web service call ourselves. This would trigger processing of said data. The data inserts into the table are potentially very frequent and the aim is to make this web service call appear near realtime.
Possibilities I am aware of (in no particular order):
1) xp_cmdshell call to an executable that can call the process. Heavily not preferred due to security implications.
2) CLR trigger. I am presuming this would be able to execute the web service call directly.
3) SQL jobs and SQL Server Agent. Jobs seem inappropriate for this due to lack of queuing. Basically, I'm having trouble getting my head around the scenario of "if the job is currently executing and a new record is inserted, what do I do to queue another execution of the job?".
4) Service Broker. Seems a bit of overkill considering how lightweight the web service call is.
Ideally, the external task inserting the record would just be able to call the web service and the job would be done.
Any opinions/avenues of investigation on this would be greatly appreciated!
Thanks,
SC
Hi there. What do you consider to be "frequent" inserts? How many rows in this table already, and how many projected to be there in the next year or two? How much load on the system (transactions per second, or even minute)?
I agree about not using xp_cmdshell or SQL Agent Jobs.
CLR is the most direct route, though could be a SQLCLR stored procedure (or function) that is called from a T-SQL trigger. That is often a little more flexible. But still not sure I would recommend it as you are tying an external resource to the transaction (i.e. the INSERT operation). If there is any delay in that external system, then the transaction is on hold, and that could cause blocking on other operations.
Service Broker does offer a nice separation there that would allow the transaction to complete and queue the message. Then the activation stored procedure kicked off automatically by Service Broker could call the SQLCLR proc or function to do the Web Service call.
Or, you can do a custom queue that you just feed into via an INSERT trigger. Then set up a SQL Agent Job to run every 30 seconds (or 1 minute; minimum is every 10 seconds) to pull from the queue. If the job runs over the next ideal time slot, it will wait for the next one in the pattern. So, if it runs every minute starting on the minute, and takes 1 minute and 20 seconds to run (hence now in the second minute), it will wait for the start of minute 3 to start again.
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR