December 24, 2007 at 12:53 pm
Good day. I am administering numerous servers with numerous SQL databases on each. I have gone through and set maintenance plans on all. I would like to write a script or ask for assistance writing a script that will go out to each of the servers, pull the status of the maintenance plans and drop in a text file or spreadsheet each morning.
Is this possible?
Is anyone presently doing this?
And alternate recommendations?
I am presently doing this manually and would a much more automated process.
Thank you!
December 24, 2007 at 4:52 pm
The maintenance plans runs as a job, so you need a job reporting system.
You can query the msdb database on each server in multiple ways, script a connection to each sever, set up linked servers, use an SSIS package on your main server to pull data from other servers, etc.
Pick a direction, and try to query for job status. We can help if you get stuck
December 26, 2007 at 9:06 pm
Can you explain the differences of the 3 options?
Which would be the optimal solution for an enterprise environment?
December 26, 2007 at 9:28 pm
Optimum is hard to determine. It's somewhat a philosophical debate on how you want to handle things.
Personally I don't like linked servers. Possible security holes could be used for other purposes that you don't expect and have been flaky in the past. SQL -> SQL are usually stable, but I personally don't like them if I can avoid it. Haven't done enough 2005 work to know if I'd change my opinion.
I prefer to have each server monitor itself. Build a small db that's standard on every server, and monitor the server locally. That way you could always get a standard report from every server. I then used to build a DTS package (would be SSIS now) that would query a list of servers and pull all that data back to a central server. It does make me dependent on that one server, but if it fails, I know where to look and I can always move the package since I have the same setup on every server.
Some people like scripting/programming and you could use SQLCMD to pull in the data. That way you can schedule it or run it one demand. THe issues I've had are that if one server has issues, my script sometimes fails. That might be my so-so programming skills, but I just prefer simpler set ups.
That help?
December 26, 2007 at 9:45 pm
Helps. I can see why the linked servers would be a problem and since i'm no programmer the last option is out.
Do you have any sample databases or sample information on how you monitor the databases?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply