diligentdba

This TSQL Tuesday post hosted by Cathrine Wilhelmson has to do with Monitoring.

During my many years as a DBA i have had a fair share of experience with monitoring tools – some of them as listed:

1 Spotlight for SQL Server – great tool for server level monitoring with very cool graphics. What i really liked about spotlight was that it was so very non-techie friendly – so whenever the director or VP chose to walk by and wants a peek into server health – all I needed to do was to pull up the screen and show him the dials whizzing around. On the flip side, spotlight did not help a whole lot with query tuning. It was also rather difficult to integrate with our ticketing system to generate tickets.

2 More recently, I have been working with Systems Center Operations Manager as our enterprise wide monitoring system. SCOM is a really complicated monitoring system to work with – but once you get familiar with it there is a lot it can do – in particular what I really liked was its ability to open tickets on its own within our ticketing system(with some customization of course), as well as close alerts on its own too when the alerting situation was resolved. SCOM calls for a dedicated admin  (which is part of my job), and a lot of tuning to get it down to where it only alerts on what you need without a whole lot of extra noise. SCOM is also NOT a tuning tool – it is only an alerting tool.

4 There are some situations that fall completely outside the realm of third party monitoring tools- for example, we need an alert whenever a user is added as a server role on some servers. We had to write a really simple script that would pull people who are on server roles, set it up as a job to email us every day in case something changed.

**Beginning of script – authored by Mala Mahadevan

SELECT role.name AS Name, member.name AS MemberName,
CASE WHEN (role.is_disabled) = 1 THEN ‘DISABLED’ WHEN (role.is_disabled) = 0 THEN ‘ENABLED’ END as     ‘Status’

INTO #temp_dbo
FROM sys.server_role_members

JOIN sys.server_principals AS role

ON sys.server_role_members.role_principal_id = role.principal_id

JOIN sys.server_principals AS member

ON sys.server_role_members.member_principal_id = member.principal_id AND

–filtering for logins that come from our domain
member.name LIKE ‘prod\%’

–Format email

SET @tableHTML =    N'<H1>Server Role members on Server A</H1>’ +

N'<table border=”1″>’ +

N'<tr><th>Name</th><th>Role</th><th>Status</th>’

+

CAST ((

SELECT  td = CAST(([Name]) AS nvarchar(100)),”,

td = CAST(([Membername]) AS nvarchar(100)),”,

td = CAST(([Status]) AS nvarchar(100)),”

FROM #temp_DBO

ORDER BY name

FOR XML PATH(‘tr’), TYPE) AS NVARCHAR(MAX) ) +    N'</table>’
SET @Results = isnull(@tableHTML, ”)

–Email the team on findings
EXEC msdb.dbo.sp_send_dbmail

@recipients =

‘mydbateam@mydba.com’,

@subject =

‘Server Roles on Server A’,    @importance = ‘Normal’,    @body =

@Results,    @body_format = ‘HTML’

DROP TABLE #temp_DBO