Before you download Microsoft SQL Server Management Pack and import it into Operations Manager, it helps to ask one simple question: what will it actually watch for me? Microsoft released this pack at the time I wrote this note, so let me answer that in plain words.
In short, the pack lets Operations Manager find your SQL Server 2000, 2005 and 2008 installations on its own and then keep an eye on them, mostly for availability and performance. Under the hood it is a blend of scripts and native Operations Manager monitoring.
What it keeps an eye on
I like to think of the coverage in layers, starting with the server and working down to the query that is stuck.
- Services and roles: the key SQL Server services, plus the Database Engine, Reporting Services, Analysis Services and Integration Services.
- Inside each instance: databases, SQL Server Agent and Agent jobs, all discovered for you.
- Database health: database status, database free space, transaction log free space, and log shipping on both the source and the destination.
- Workload trouble: SPID blocking that refuses to go away, plus a long list of SQL Server events and performance data.
When something goes wrong, you get an alert. I like that the alert does not just shout at you. It also explains the likely impact and what you might do about it, which is very welcome at 3 AM when your brain is still loading.
What you get to look at
Watching is only half of the job. The pack also adds more than 20 views, for things like database free space, performance, alerts and the state of each role and component it found. For the long view, reports cover lock analysis, the most deadlocked databases, service pack levels across servers and user connection activity. The generic reports in Microsoft’s Generic Report Library also work against the same servers, so you can follow availability and performance over longer stretches of time.
Each role and component also comes with tasks. In most cases they let you open common tools, pull triage details or apply a fix without leaving the Operations Console.
Coverage is wide: Enterprise, Standard and Express editions on 32 bit, 64 bit and ia64, including clusters and servers with several instances. The guide that comes with the pack lists every supported setup. The feature details here come from Microsoft’s own description of the pack.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
