Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cat_jesus
Aged Yak Warrior
547 Posts |
 Posted - 2008-01-30 : 11:05:39
|
| Hi all, I've been digging around the last few days, looking for the best way to monitor all the jobs on all my prod servers. Using EM or SSMS is BS. I've been thinking that perhaps the best strategy is to write or modify a script that gathers information from sysjobs and sysjobhistory on all servers and then inserts it into my main DBA database on my "admin" server. Of course this script would have to be on all servers and be in a scheduled job itself. I'm thinking that I should probably categorize every job in the company and break out my reports that way too. I'll probably throw all the data into a cube so I can do some at a glance checks to see that everything is running ok. I'd like to trend run times with the data too. Anyway as with most fun projects there are many different way to accomplish this. I've hit on one above and I've seen several others. What are some of the solutions you guys have come up with, what have you tried that didn't work so well and what are some pitfalls that I should avoid in setting this up?Cat |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2008-01-31 : 12:53:39
|
| You can have SERVER1 and then SERVER2 be the slaveRight click on SQL SERVER AGENT and multi administration, make this a master, make this a target |
 |
|
|
CShaw
Yak Posting Veteran
65 Posts |
Posted - 2008-01-31 : 13:36:07
|
| I have done this in one of two ways.1) There is a great product out there called SQL Server Sentry and I highly recommend it. Not only will it do what you ask but it will centralize your Job Notifications, and allow you to chain jobs across servers.2) I wrote a SQL Query that gets the run values from MSDB on the SQL Jobs. This SQL Script was then place in a script and then scheduled via a Windows scheduler. The results were dumped in a database that are then queried by Reporting Services, I have that report sent to me every day right before I get to work.I hope this helps. Option 2 requires more work on your side and is not as robust as Option 1. But it has nothing to install on the SQL Servers.Chris Shawwww.SQLonCall.com |
|
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2008-01-31 : 15:23:50
|
| TRACEYSQL -- It's amazing how many things there are about SQL Server that I still don't know about after all these years. Thanks, I'll play around with that in test and do some BOL reading.CShaw -- I'll look into Sentry but I'm already leaning toward #2. We have a DBA database on each server now to track database growth and I'm setting up index reporting as well. I was thinking I should just go ahead and continue down the path of using DBA on all servers and then dump the data daily into "my" server for reporting. At this point I'm trying to decide how I'm going to get the data to my server. I could use a linked server for each but I don't really like that too much. I was also thinking about using OPENDATASOURCE but I haven't used it much. I may just go with an SSIS package or I could be a sadist and use replication ;) I don't have any strong reasons to go with one method over another, which is why I asked in the first place.But now I have some new things to research and think about.Thanks! |
|
|
|
|
|
|
Monitoring jobs across servers/versions