Hi all,
I am looking for some guidance or advice on how best to move forward.
Essentially, there is a table that stores "jobs to run" along with parameters.
Sure, I can invoke SQL CLR and have it run a .NET code, passing it the data that has been inserted and then that .NET code can do what it wants.
Problem is, I don't like the idea of the SQL CLR dispatch process to "wait" until it's done as that process could take maybe 1 min or 5 hours, depending on whatever needs to be done. And it means that it will be hogging SQL resources as well as not being able for it to be scalable. Pretty much want a fire and forget thing here.
But, I do think it's the best way to dispatch some process outside of SQL to use SQL CLR rather than .NET code having to poll SQL.
What are your thoughts? I need to design a solution that is scalable and if I need more throughput, I can add more servers (from the perspective of the .NET stuff) to do the data processing.
can these processes (all/parts) in parallel async?
have you looked at other technologies, other than sql crl (ie hangfire)
Not particularly I guess (in terms of parallel). However I am leaning more towards WCF And MSMQ (I have done this before). I guess I want to push from SQL out to a message queue of some kind (MSMQ) that there is something that needs to be done and ideally when the record has been inserted successfully.
For the last part - is it better to do a notification on after trigger insert? How does one create a message and place it on MSMQ from within SQL?
we used audit columns (on the tables we wanted to keep track changes on) to trigger a Q. so a sql job runs in background every 5 minutes maybe and picks up records that changed in the last 5 minute comparing it to a logger table. poor mans version of Q. but I am sure there are better way of doing this.
for some reason I am not a big fan of triggers. I want to be in control. 
That's true.
Well what about running a SQL Job every 5 mins or whatever, and then for each record found to create a message to place it on the MSMQ?
I would do this fro outside sql server.
What you have is a job queue.
I assume there os some flag to say whether the job is available to run if there is a dependency between jobs.
I would have a stored procedure to get the job from the queue and another for the result - which also updated dependencies.
Then you can have any number of applications from any number of servers polling the system to get jobs.
The get SP would lock the job table and get the job to run and flag it as running with an ID for the app. It also needs to clear the running flag for any jobs that were being run by that app in case of a failure.
The done SP would just update the status of the job.
In that way you can have any number of apps run on however many servers you want.
I've also done this by having a task on the main server to call a shell on a remote server (whichever is available) to start a task to service the command but that sort of thing is dependent on how windows handles threads which is not always consistent across versions.
I would look at using a Service Broker - if the goal is to keep the core processing in SQL Server. The service broker is your queuing mechanism and it can then be configured to call out to anything you need.