I am just wondering what is an efficient method of invoking external code from SQL server. I do not want to load assemblies in SQL, I want to run something outside. So far options considered:
1. Windows Service - loop and check for a specified condition in database.
2. Web Service Listener - use SQL CLR to call the web service.
3. Queueing mechanism. Use MSMQ and widows service (similar 1) [probably reliant on CLR to send messages].
4. Service Broker - similar to 3. but message handling is SQL internal and in transaction.
All of the above are listeners in a way, could there be something that relies on event handlers - a stab in the dark.
Thank you for thoughts,
Lubomir
A lot depends on what you are trying to do. For option 1, consider using query notifications rather than looping and checking for a specific condition. Option 2 is often attractive unless invoking the external code is in the inner loop of an application. In that case, you might wish to consider rewriting the code so that instead of being external it is internal (using CLR inside the server). If this is a new application and not constrained by existing code, then open 4 seems more attractive than option 3 unless you need some feature specific to MSMQ. Look for information about External Activation in the Service Broker documentation. Other options you didn't mention would be using Biztalk Server or Indigo. You may find the discussion at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/BTS_2004WP/html/14bc36a8-69a9-48ed-8e4c-1c85202544c0.asp helpful.
sql
No comments:
Post a Comment