SQL Server 2005 needs the support of a number of Windows services to function
reliably. Microsoft is updating and rebranding its systems management products,
Microsoft Systems Management Server (SMS) and Microsoft Operations Manager (MOM),
in its System Center product family. The enhanced capabilities in the new System
Center products, particularly Microsoft System Center Operations Manager 2007,
which is available now; Microsoft System Center Data Protection Manager (DPM)
2007, which was in beta at publication time; and Microsoft System Center Configuration
Manager 2007, also in beta at press time, will support SQL Server 2005 in a
number of ways. (Note that Operations Manager 2007 can also monitor SQL Server
2000.) Let's take a tour of these products and see what they offer to help SQL
Server DBAs keep a closer watch on their systems. (For a quick look at all the
components that comprise the new System Center, see "What's
in Microsoft System Center 2007?".)
Operations Manager 2007
Operations Manager 2007 is based around IT service models. For the first time,
it's possible to manage an IT service from an end user's perspective (i.e.,
as a single service) instead of as the separate components (e.g., server, application,
disk space, Active Directory—AD) that the service uses. Operations Manager
still monitors these components individually but groups all required components
for a particular service together, so that, for example, if AD were unavailable,
any service that relied on AD would be shown as having a problem.
In most environments, Operations Manager runs an agent on each monitored server
or workstation (an agentless operation mode is also available). The agent basically
only stores and relays information back to the Operations Manager server. The
true power of Operations Manager lies in its management packs, which are product-
or feature-specific packs of knowledge that are installed on servers that Operations
Manager monitors. The management packs installed on a server depend on the software
or features it's running; for example, on a domain controller (DC) running DNS,
you'd install the base OS, AD, and DNS management packs. Management packs are
available for many Microsoft products; you can find a complete list of Operations
Manager management packs at http://www.microsoft.com/technet/prodtechnol/mom/catalog/catalog.aspx?vs=2005.
(You can download the Microsoft SQL Server Management Pack for MOM 2005 at http://www.microsoft.com/downloads/details.aspx?FamilyID=79f151c7-4d98-4c2bbf72-ec2b4ae69191&DisplayLang=en.)
The management pack contains large amounts of information about the product
(basically everything about the product that's in the Microsoft Knowledge Base)
and its use of the environment and tells the agent performance counters to watch
for, registry values (which are important in configuration monitoring), and
any other factors that could cause the agent to preemptively alert IT and take
steps to avoid potential problems detected by the management pack.
Although Operations Manager provides the standard "x is down, go fix it" reporting,
to which it generally will add information about how to fix the problem, the
product's ability to warn preemptively is its most useful feature. You can configure
a granular level of alerting. For example, you could configure Operations Manager
to send alerts related to your SQL Server service to the DBA team and IT administrators.
Or, you could set up Operations Manager to initially alert the Help desk, then,
if the problem hasn't been resolved after a certain time period, escalate the
alerts by paging another group, and so on until the problem is fixed.
The SQL Server Management Pack
The SQL Server Management Pack provides a discovery component that lets Operations
Manager examine instances, databases, file groups, files, agent jobs, and SQL
Server roles in a SQL Server environment. You can fully customize the
aspects you want to monitor and actions to perform. The management pack's event-analysis
function monitors all the key aspects of the SQL Server environment, such as
clustering, log shipping, backup, SQL Server Agent, and, as mentioned, SQL Server
roles (e.g., replication). In addition to providing overall views of the SQL
Server system's health, the management pack also provides in-depth views
of databases, the database engine, SQL Server Reporting Services (SSRS), SQL
Server 2005 Integration Services (SSIS), and other SQL Server components. The
main monitoring screen in Figure 1 provides
a high-level status view of the computers that Operations Manager is monitoring.
In the Computers section, you can add different columns to the components being
monitored, such as database functionality. (In this example, I've added the
column SQL DB Engine.) You select the columns to add to the main monitoring
screen by right-clicking the column heading.
After the SQL Server Management Pack has identified all the attributes and
components that need to be watched, the management pack with Operations Manager
can start monitoring the environment. The management pack provides three core
types of monitoring:
- Availability monitoring—At a basic level, the agent verifies that
the database can be contacted by creating a synthetic database-connection
transaction. The agent then checks the status of the services that SQL Server
uses, agent jobs, the state of any backups, and replication state. The agent
looks at around 400 different SQL Server events and any other occurrences
that might affect availability.
- Performance monitoring—Operations Manager monitors core items such
as caching ratio, user connections, processor utilization per instance, database
and log size and growth (both in percentage and absolute terms), and response
times to client requests.
- Configuration monitoring—The management pack understands the recommended
best practices and applies this knowledge to the SQL Server systems being
monitored. Operations Manager will generate alerts when best practices aren't
being followed. For example, Operations Manager will provide an alert if it
sees database configurations such as Auto Close or Auto Shrink enabled.
Another feature that will appeal to both SQL Server novice users and experienced
DBAs are the new ways to access functions that can aid in running SQL Server.
As Figure 2 shows, if, for example,
in the Microsoft Management Console (MMC) System Center Operations Manager 2007
snap-in you select SQL DB Engine Tasks in the Actions pane, you'll see actions
related to the database—for example, access to SQL Server tools, configuration
options, and control of SQL Server services. Not only does Operations
Manager let you see what's happening, it gives actions for the relevant component.
This applies to any item you select; for example, if you select a computer and
not a component, you'll see options to list active sessions, processes, and
other information relevant to the computer. As you can see, Operations Manager
is very much a management solution and not only a monitoring tool.
Prev. page
[1]
2
next page 
