SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Failed Jobs Monitoring System


A Failed Jobs Monitoring System

Author
Message
Mark.L.Jones
Mark.L.Jones
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 155
Good solution, but in a world of non perfect trusted domains the linked server solution simply has too many issues to be viabale. Because you only open a single process within a stored procedure that runs agaginst all servers in your set, if a problem is encountered running remote SQL through linked servers the entire process will fail and end. for example:

If you have 10 servers you will query if server 2 fails, then you will miss the remaining 8 servers.

You can get around this by using SQLCMD to run remote SQL queries rather than linked servers. This opens new process to every server you wish to connect to, SQLCMD is easily runable from a TSQL query by using xp_cmdshell, port the results into a table by using an INSERT command embedded in your remote sql statement.
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18562 Visits: 14898
Mark,

Good point, but enabling xp_cmdshell once again expands the surface area of your SQL Server. Which one is the lesser of 2 evils?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Fred Panos
Fred Panos
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 13
One thing we have done in addition to the above is to create a web page that lists the job results and then have a 3rd Party alert system check that web page for key words. Then if it finds it can email (which is redundant) but it can also automatically call someone. The monitor we use is called AlertSite and it can call and progressively call more and more people if the job isn't being resolved. This was crucial for us given that jobs needed to finish at night and we didn't want to have a 24/7 staff presence for the failures.
Chris Howarth-536003
Chris Howarth-536003
SSC-Addicted
SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)

Group: General Forum Members
Points: 407 Visits: 1163
Mark Jones (9/4/2009)
Good solution, but in a world of non perfect trusted domains the linked server solution simply has too many issues to be viabale. Because you only open a single process within a stored procedure that runs agaginst all servers in your set, if a problem is encountered running remote SQL through linked servers the entire process will fail and end. for example:

If you have 10 servers you will query if server 2 fails, then you will miss the remaining 8 servers.



Not necessarily, you could use sp_testlinkedserver and a bit of error handling to trap any connectivity issues before firing any queries at the linked server.

http://msdn.microsoft.com/en-us/library/ms189809.aspx

Personally I prefer an Integration Services approach, using a package variable populated from a lookup table to loop through and connect to each server in turn.

Chris
Rudy Panigas
Rudy Panigas
SSC Eights!
SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)

Group: General Forum Members
Points: 996 Visits: 1311
The code provide is not perfect, as everyone has there own idea on "perfect" code, but it does try to link to a server and if not able to connect, it produces an error and continues onto the next server for its link connection. If not, I will gladly update the code as I have continued to may changes to it.

The intent here was to show that there are may ways to get the job done and that it isn't necessary to always purchase software. Although we do use tools from Quest and RedGate to help on our duties, sometimes times the in-house developed tool does the trick. As anyone can tell you each company's network has its own characteristics thus needing a personalized touch.

Glad to see that many people have at least looked at this article and have posted a reply.

Rudy

PS. Please remember that I never said that this was a perfect solution and if you think you can create something better, then do so and post it at ServerCentral.com. We would love to see it Smile



Rudy Panigas
Rudy Panigas
SSC Eights!
SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)SSC Eights! (996 reputation)

Group: General Forum Members
Points: 996 Visits: 1311
Hello everyone,

Please ignore my last comment as it was meant for another forum...Opps!

See DBAs are not always right Smile

But to comment on this forum, if you create a loop that would cycle thru the connection to a linked server you could then add any linked failure to a table for review later and therefore you could continue to link to the remaining servers.

Rudy



alen teplitsky
alen teplitsky
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2752 Visits: 4674
great article i just found

in my case i can't use linked servers in all cases to monitor jobs so i'm going to change the code to use SSIS and maybe dump all the data into a central database. we don't have a central monitoring sql server and we're going to use a production server with a separate database to monitor this
michael.morse
michael.morse
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 200
I was trying to test this solution out but I get an error stating I am missing the sp_SMTPMail object. Any chance you could script the one you have? I have tried to search online but non of the examples provide the @query parameter. Thank you.
SQL Pete
SQL Pete
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 177
I implemented a very similar solution a while back and find it works well. I also have a SSRS front end to view my data. The monitoring solution also grabs data from all databases, last backup date/time etc. It also keeps an inventory with version numbers, collation etc etc.
I have also managed to get disk usage too, though the process is a little dirty.
I did it because I want complete control of what I monitor/report and I too didn't trust SQL Server Mail, though db mail is much beter these days.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search