March 26, 2018 at 7:15 pm
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.
March 28, 2018 at 11:56 am
When you say "jobs to run", exactly what kind of job are you talking about? SQL Agent Job? Something else? Also, what is the overall goal here? It's not clear to me why you're presenting a choice between using SQL CLR to invoke a .Net program, and some other choice. Also, you are mentioning the passing of INSERT-ed data, so please be a lot more specific about what your goal is. We can't read your mind and know immediately what you're talking about.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 28, 2018 at 7:38 pm
Sure.
Essentially this is a legacy system and there is going to be, unfortunately, a band aid to move to a more modern platform.
There is a table that has a "job" entry. All custom. Contains things like the type of report to generate, report parameters, status of the job. This gets inserted from VFP or classic ASP end.
Currently the VFP code polls the table to get the jobs which are needing to run (I.e "Status" field = 0) and generates the report based on the data entry.
in .NET, I am thinking about placing the current row inserted (the report job to run) into an messaging queue and have SQL CLR write the inserted data (the report job to run) on the queue.
Another process would be running where the data on the queue gets dispatched to a receiver that will then do what needs to be done to generate the report.
I wouldn't like the SQL process to "wait" until a report is completed if I invoke using SQL CLR and have the .NET code generate the report in that call as the report may take some time to generate (depending on the type of report requested).
March 29, 2018 at 8:02 am
Ahmed Ilyas - Wednesday, March 28, 2018 7:38 PMSure.Essentially this is a legacy system and there is going to be, unfortunately, a band aid to move to a more modern platform.
There is a table that has a "job" entry. All custom. Contains things like the type of report to generate, report parameters, status of the job. This gets inserted from VFP or classic ASP end.
Currently the VFP code polls the table to get the jobs which are needing to run (I.e "Status" field = 0) and generates the report based on the data entry.in .NET, I am thinking about placing the current row inserted (the report job to run) into an messaging queue and have SQL CLR write the inserted data (the report job to run) on the queue.
Another process would be running where the data on the queue gets dispatched to a receiver that will then do what needs to be done to generate the report.I wouldn't like the SQL process to "wait" until a report is completed if I invoke using SQL CLR and have the .NET code generate the report in that call as the report may take some time to generate (depending on the type of report requested).
So... let me see if I have this straight... You've got an existing process that uses VFP to examine a "jobs" table, and it then kicks off actions that process that "jobs" table data, and do whatever that data says to do? SSIS seems like the most logical course of action, with a SQL Agent job scheduled to run frequently enough that it will catch new entries in that table and then "process" them accordingly. This leaves the larger question of what; exactly; are all the possible things that "jobs" table can direct to be done. You'd need to know SSIS fairly well, but there may be things that even SSIS could then delegate to other programs. I'm just not sure that an SSIS solution is some kind of band-aid, as it could easily be considered a comprehensive solution, which then begs the question of now needing to know exactly why this process needs some kind of band-aid. Is it broken? Are there performance or reliability issues? If the latter, what pieces, exactly, are unreliable? There is a LOT of information that would be needed to provide useful guidance, and all I really have to go on is that I think you have what amounts to a home-built "job" processor. which in most cases can be permanently replaced with SSIS and SQL Agent Jobs. As you continue to talk about .Net and CLR, I'm at a loss to understand what kind of guidance you're really looking for here, as there's been no detail on exactly what kind of work these various "jobs" are supposed to do, and that's pretty critical to understanding what a good solution would be, as is knowing where; exactly; the problems are with the existing process. I don't think I have anywhere near enough info to make an informed choice... I could also choose to build a SQL Agent job to replace each and every existing "job" entry in that table, and then apply an appropriate SQL Agent job schedule to each of them, but again, don't have enough information...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 29, 2018 at 8:32 am
Thanks.
I understand about not enough information - I am just relaying on the same amount of information I have 🙂
This is more of a PoC than anything. They are wanting to replace (gradual) VFP to .NET and SQL and one of the core pieces here is what I am going to be working on which essentially is to find a way to do almost the same thing as what the VFP is doing but... in .NET.
the .NET part will be the one to generate reports (RDLC binding) and then possibly send them off via email or drop in a storage location.
The .NET code needs to know what reports to generate. Instead of polling the table (perf, congestion etc...), my thought was to get SQL to invoke the .NET code to tell it "here is some data that just came in... go do your thing" - hence the .NET SQL CLR thought process.
March 29, 2018 at 10:04 am
Ahmed Ilyas - Thursday, March 29, 2018 8:32 AMThanks.I understand about not enough information - I am just relaying on the same amount of information I have 🙂
This is more of a PoC than anything. They are wanting to replace (gradual) VFP to .NET and SQL and one of the core pieces here is what I am going to be working on which essentially is to find a way to do almost the same thing as what the VFP is doing but... in .NET.
the .NET part will be the one to generate reports (RDLC binding) and then possibly send them off via email or drop in a storage location.
The .NET code needs to know what reports to generate. Instead of polling the table (perf, congestion etc...), my thought was to get SQL to invoke the .NET code to tell it "here is some data that just came in... go do your thing" - hence the .NET SQL CLR thought process.
I'm still not knowing what kinds of things those "jobs" are doing, and thus without that critical piece, I'm much more inclined to set up SQL Agent Jobs that use SSIS packages to do that work, Involving .Net in database work when it's not really needed is generally a bad idea, and is far too reliant on manually fixing things when they go bump in the night. A well designed set of SSIS packages would be much more appropriate to database tasks, and enforces a certain amount of discipline to the processes - something .Net usually can NOT do.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply