Best practices for event driven processing when data inserted into table

Question

Best practices for event driven processing when data inserted into table

cppprogrammer

Right there with Babe

Points: 797
More actions
November 23, 2014 at 7:24 pm

#292726

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
The external call, I believe, will not yield its thread and hence will tie up one of the schedulers. This is another concern regarding potentially long-running calls. Even if the call normally takes < 30 ms, if the connection timeout is 60 seconds and there is a connection problem, you are tying up an entire thread for that time, not just the transaction. So maybe set any timeouts to relatively low values.
Only so many network handles can be open and it is not many. Hence you need to be very diligent about closing all WebRequests, Streams, etc. It is very important to ensure this happens in finally blocks, etc such that all exceptions will always close the connections. While I have not experienced this myself, I have heard from a few folks that when doing network calls, they can make a few (literally, as in: 3) and then they start getting errors that can only be resolved by restarting the SQL Server service.

If you have those 2 areas covered then you should be ok.

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

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 1

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

cppprogrammer Right there with Babe Points: 797 More actions · Answer 2

Thank you very much for your response.

Solomon Rutzky (11/23/2014)
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)?

Well it would be low load for a day but it might insert in bursts. E.g. 100 rows but if say 5 go in within 100ms, we'd want the triggered process to complete it's task in less than a second ideally. That is from insert into the table, the trigger firing, the executable or CLR proc completing work. In practice, from xp_cmdshell, the executable completion is < 30ms so the bottleneck seems very much to be the mechanism queuing/firing the executable.

Solomon Rutzky (11/23/2014)
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.

Our external call I was thinking could be done async. The system is designed so that if that external process fails, it is retried by a timed mechanism so we don't impact the insert or trigger operation. Not that we expect it to fail but in case it does...

Solomon Rutzky (11/23/2014)
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.

Yes it does sound like it ticks a lot of the boxes. Hesitant to go down this path as if this becomes "core" to our solution, it is another component that requires configuration on deployments. Short-term manual config. Hopefully automated in medium to long term. Not sure about the responsiveness of the Service Broker as well...

Solomon Rutzky (11/23/2014)
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.

Was a little worried about setting the job queue execution so tight - we would likely have to configure this approach to 10s. Seems quite "busy loop" approach? And is not edge triggered so lower preference to go with this approach. Potentially over 10s of wait time for external process to trigger as well.

Thank you again for your analysis/response. The CLR proc, deployment requirements aside, seems like it would or could be the most performant choice at this stage so we'll prototype and see how it goes.

Thanks,

SC

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 3

cppprogrammer (11/24/2014)
Thank you very much for your response.
Solomon Rutzky (11/23/2014)
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.
Our external call I was thinking could be done async. The system is designed so that if that external process fails, it is retried by a timed mechanism so we don't impact the insert or trigger operation. Not that we expect it to fail but in case it does...

Do you mean an async call from within the CLR trigger? If so, I'm not sure that is such a great idea. It is putting a lot of risk into that INSERT operation. Whatever you do, just make sure not to impact the timely and successful completion of that trigger. If the external call enters a record into a queue table such that the process doesn't need to wait for anything and the item in the queue can be picked up whenever, then that would be ok.

Solomon Rutzky (11/23/2014)
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.
Yes it does sound like it ticks a lot of the boxes. Hesitant to go down this path as if this becomes "core" to our solution, it is another component that requires configuration on deployments. Short-term manual config. Hopefully automated in medium to long term. Not sure about the responsiveness of the Service Broker as well...

I have not tested it for performance, but my understanding is that Service Broker is quite responsive and fast.

Solomon Rutzky (11/23/2014)
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.
Was a little worried about setting the job queue execution so tight - we would likely have to configure this approach to 10s. Seems quite "busy loop" approach? And is not edge triggered so lower preference to go with this approach. Potentially over 10s of wait time for external process to trigger as well.

Understood. I was just throwing out options ;-).

Thank you again for your analysis/response. The CLR proc, deployment requirements aside, seems like it would or could be the most performant choice at this stage so we'll prototype and see how it goes.

No problem. And as I mentioned above, just make sure you don't impede a quick and successful completion of the trigger.

Please do post back to let us know how it turned out.

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

cppprogrammer Right there with Babe Points: 797 More actions · Answer 4

Yes sorry I should have clarified. The initial execution of the external process via the trigger was to be a "fire once" operation that returned immediately and designed to ignore whether that external process execution was successful or not. That would maintain the timeliness and speed of the trigger operation and allow it to commit quickly.

The "retry" mechanism was not going to be done within the trigger/CLR proc at all. We have a Windows Service ("scheduler service") that can perform this role on a schedule. That way, in ideal scenario, the CLR proc does the job. If the external process fails for whatever reason, the scheduler service will mop up later.

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 5

cppprogrammer (12/3/2014)
Yes sorry I should have clarified. The initial execution of the external process via the trigger was to be a "fire once" operation that returned immediately and designed to ignore whether that external process execution was successful or not. That would maintain the timeliness and speed of the trigger operation and allow it to commit quickly.
The "retry" mechanism was not going to be done within the trigger/CLR proc at all. We have a Windows Service ("scheduler service") that can perform this role on a schedule. That way, in ideal scenario, the CLR proc does the job. If the external process fails for whatever reason, the scheduler service will mop up later.

That sounds reasonable. Just be aware of the risks of sticking an external call into a trigger. Don't get me wrong, I am all for doing cool and useful stuff like this. In fact, I sell a library of SQLCLR functions and procs, SQL#[/url], and one of the functions is a generic HttpRequest that is used pretty heavily by some customers. I just don't think anyone is using it in a trigger ;-).

So the main things to watch out for are: