September 4, 2009 at 7:17 am
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.
September 4, 2009 at 7:40 am
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
Consultant - Straight Path Solutions
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
September 4, 2009 at 9:01 am
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.
September 5, 2009 at 3:17 am
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
September 8, 2009 at 6:51 am
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 🙂
Rudy
September 8, 2009 at 6:56 am
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
Rudy
November 23, 2009 at 9:04 am
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
June 16, 2010 at 12:14 pm
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.
January 17, 2011 at 4:19 am
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.
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply