October 31, 2005 at 9:07 am
So, I'm looking into using an MSX/TSX setup to help manage and monitor jobs, etc.
I took two test boxes and set one up as a Master and one as a Target and got it working...was able to push a job out and it ran and reported back, etc.
So, that's all great, but this was one small limited test. I'm curious to know if there are any gotchas that some of you have experienced that you would be willing to share with me to help prevent me from experiencing the same issues, etc.
I have about 40 instances that I'm considering using this MSX/TSX setup with, the remaining 15 instances would be monitored by something like Quest. Can a single MSX handle jobs on 40 instances? I already have maintenance plans and jobs set up on all the remote servers. Is there a way to transfer their ownership up to the MSX so they can be monitored from there? So far I haven't seen a way to do that.
*ponders*
Actually, one thing I just realized is that I could set up a job that looked for failed jobs. If I pushed that job out to all the TSX servers that could be used to alert me that something failed on a TSX box. That would actually be a great way to do it the more I think about it...Can anyone think of a reason why that is a bad idea?
Thanks!
November 1, 2005 at 9:26 am
i have more than 100 servers running with MSX-TSX relationship, works fine. All the remote server maintenance jobs are created on the MSX are downloaded by the TSX with in couple of seconds.
You can monitor the job status from the MSX Server very easily.
you can verify the last poll time and status of the servers too.
November 1, 2005 at 11:12 am
I have had about 60 Servers managed from a single MSX server. The jobs included database maintenance and application jobs.
As there were 2 sites (Chicago and LA) and the WAN did hickup about once a month, each site was given its own MSX server.
The server was HP DL360 G2 with 1Gb memory and 2 Drives in a Raid 1. SQL Mail was installed on MSX server but not on any other SQL Server.
Here is a SQL statement to get all failing jobs that occurred yesterday or today.
SELECT CAST( SERVERPROPERTY ('MachineName') as nvarchar(128) )AS JobMachineName
,COALESCE ( CAST( SERVERPROPERTY ('InstanceName') as nvarchar(128) ) , 'Default')AS JobInstanceName
, sj.Serveras TargetServerName
,sj.name as JobName
,sjh.step_id as StepId
, sjh.step_nameas StepName
,sjh.run_date
,sjh.run_time
,sjh.sql_message_id
,sjh.sql_severity
,sjh.message
FROM msdb.dbo.sysjobhistory sjh
join msdb.dbo.sysjobs sj
onsj.job_id = sjh.job_id
where sjh.Step_Id > 0
AND sjh.run_status = 0
and sjh.run_date >
CAST( CONVERT(char(10) , current_timestamp - 1 , 112) as integer)
SQL = Scarcely Qualifies as a Language
November 1, 2005 at 11:57 am
I have 25 servers and 400+ databases all in a MSX/TSX environment presently (I've been using it since v7.0 !!!). One 'gotcha is that the authentication used for MSX/TSX was changed by MS bwtween SP2 and SP3 (they are basically incompatable). The only other minor nuisance can be your network. Occassionally I have to defect/enlist a TSX server becasue things 'get behind' due to network 'hiccups'. Other than that, it's one of the best managment features in SQL Server.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
November 1, 2005 at 6:13 pm
I agree with Rudy, I had the same problem too because of the network. I defect all the servers and enlisted again. I could not able to enlist the target servers using the scripts. For each and every server i have to put in the login name and pwd.
I have added the remote servers to the job using the script.
regards
kumar
November 1, 2005 at 6:38 pm
Has anyone had success with alert forwarding to the MSX/TSX server ?
I have set this up 3 times, it works for a while and then the alerts will not forward on a consistent basis.
SQL = Scarcely Qualifies as a Language
November 2, 2005 at 7:02 am
So, it doesn't sound like this works "perfectly"...
If the plan is to check the jobs on a daily basis, have any of you considered writing a webpage to go out and collect the stats on all the servers? It seems that would prevent the enlisting/re-enlisting that some have dealt with, etc...
Mike
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply