Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

A Failed Jobs Monitoring System Expand / Collapse
Author
Message
Posted Friday, September 4, 2009 7:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, May 17, 2013 8:23 AM
Points: 226, 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.

Post #782877
Posted Friday, September 4, 2009 7:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:07 AM
Points: 11,157, Visits: 12,899
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

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
Post #782897
Posted Friday, September 4, 2009 9:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 19, 2013 4:09 PM
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.
Post #782995
Posted Saturday, September 5, 2009 3:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 3:33 AM
Points: 278, Visits: 1,068
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
Post #783289
Posted Tuesday, September 8, 2009 6:51 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:37 PM
Points: 312, Visits: 1,098
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 :)





Post #784195
Posted Tuesday, September 8, 2009 6:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:37 PM
Points: 312, Visits: 1,098
Hello everyone,

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

See DBAs are not always right :)

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



Post #784203
Posted Monday, November 23, 2009 9:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:34 PM
Points: 1,414, Visits: 4,539
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


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #823320
Posted Wednesday, June 16, 2010 12:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 3, 2011 10:07 AM
Points: 179, 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.
Post #938474
Posted Monday, January 17, 2011 4:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:35 AM
Points: 6, Visits: 162
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.
Post #1048685
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse