November 6, 2008 at 11:04 am
We are looking into some different methods to do some processing on our data, and I was wondering if I can get someones advice.
Right now, we have an application (.NET app) that runs every x number of seconds and kicks off a program which does 'stuff' (creates a file.. updates a status, that kinda thing). And while it has been mostly stable, it has on occasion crashed and stuff just stops, meaning I've needed a seperate monitor app to check on it from time to time.
We have a new process coming up where I'll have to check on some tables in a database on a particular server, and if there are entries, I need to update a status, and then update a status in a table in a database on a different server.
My options right now are:
Add this process to my existing application -- But I'm worried about processing time.
Create a new process which is called from my existing application -- Its an additional app which could be prone to error (atleast at first)
Create a new process which does nothing but monitor my tables and does the updates, similar to my existing application -- Again, new app, and I run the risk of it crashing, so I need a monitor app
Move the select/update's to a scheduled job and let the SQL Server 2005 Agent run it once a minute for me.
I'm leaning towards the last option, seems the simplest, and if the Agent goes down.. I'm gonna know about it anyway..
But.. my boss wants me to check around and make sure that I wont run the risk of wrapping something around the axle.. the process MUST run, its business critical, and we dont currently use SQL Server Agent for anything like this.
The job would be on a central server (where the final update is done) -- and will use linked servers to get the data from the tables, and perform an update.
Can anyone see why it would be a problem to do through SQL Server agent? Anyone have any experience in using something like this?
Any advice at all would be appreciated.
Thanks!
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
November 6, 2008 at 12:47 pm
Hi Kevin,
I feel quite comfortable to say that SQL Server Agent is robust and I can't say I have seen many cases (less than handful in 10 years) where the Agent itself has crashed . Now, of course you could set it up to run any job and nothing prevents that job from bogging down your server completely.
You say that ".. have to check on some tables in a database on a particular server, and if there are entries...". Do you mean that as soon as there is an INSERT something should happen or are you checking for rows that fulfill certain conditions?
If you need to react to every INSERT, have a look at TRIGGERs. Otherwise I think you can safely rely on SQL Server Agent to run your job. It is also cluster aware if you should choose to cluster your SQL Server for availability.
HTH!
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 6, 2008 at 1:45 pm
Thanks Elisabeth
I don't want to do it on a trigger.. once a minute will be fine, i'm looking for set rows that match a certain status. sounds like my thought about sql server agent would work fine.
as for being able to bog the server down.. well.. i'd be running the same sql in the .net app.. so i guess if i bog it down, it'd be bogged down in the .net too..
appreciate your input 🙂
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply