Last Saved: 4/6/2006 7:39:00 PM
Monitoring SQL Server with Microsoft
Operations Manager 2005
Objectives
After completing this lab, you will have had an opportunity to become familiar with several key SQL Management Pack features including:
• Management Pack Organization
• State Monitoring, Reports, Rules, Views, Tasks • Key Scenarios:
o Blocked SPID Monitoring o Client Side Monitoring
o Database Space Analysis and Threshold Monitoring • Leverage SQL Server management pack reports
This lab is designed to show students some of the main features of the SQL Server 2005 management pack for Microsoft Operations Manager 2005. In this lab we walk through the following main scenarios:
• Introduction to MOM 2005 state monitoring for SQL Server (10 minutes) • Monitoring a SQL Server blocking scenario (15 minutes)
• Implementing client side monitoring (15 minutes) • Monitoring database free space (15 minutes) • SQL Server MP Reports (5 minutes)
Estimated time to complete this lab: 60 minutes for all 5 exercises. Lab Setup
To complete this lab you will be using two Microsoft Virtual PC images. These images are configured as follows:
1. Virtual PC Image MOM2005Shared Servername: MOMSERVER Domain: n/a
Software Installed: Windows Server 2003 R2 Operations Manager 2005 SP1 SQL Server 2005
Visual Studio 2005 2. Logon credentials for the image is:
Exercise 1
SQL Server 2005 State Monitoring
Scenario
This exercise is designed to familiarize users with the MOM 2005 Operations Console, areas of the Operations Console, and how state monitoring works for SQL Server.
In this exercise, you will familiarize yourself with the Operations Console, how to find the SQL Server role, identify the SQL Server components for the role, and manipulate the health of the role.
Tasks Detailed Steps
1. Browsing SQL Server alerts a. To begin, open the Microsoft Operations Manager 2005 Operator
Console. To open the console, navigate to the desktop of the MOM virtual machine and run the MOM 2005 Operator Console via the Start Menu “Operator Console” icon.
b. When the operator console opens, you will notice several SQL Server alerts. Explorer the alerts, the alert details and the knowledge. For each alert examine the problem state and knowledge. The problem state indicates weather a particular alert is active or required investigation.
2. Exploring the SQL Server
state role
a. To explore the SQL state role, navigate to the state view. To navigate to the state view, select the “State” tree item from the navigation pane on the left hand side.
b. In the state view explorer each of the different “roles” which are outlined along the top (for example “SQL”, “MOM Agent”, “Disk”. View the components for each role by clicking on a role for a computer.
c. In the details pane you will see the components that make up each of the roles, as well as the instance. Explorer the SQL role and the components. Notice how the instance lists all of the SQL Server instances installed on a particular machine and health for all components in the instance. Explore the SQL Server Agent, Connectivity, DB Space, Log Space and SQL components. d. You can view the alerts which are causing a component to show red
(or yellow) by right clicking on the component and selecting “View Alerts”.
e. Select the SQL Server Agent component for the computer for the MOMSERVER computer. This is showing red. View the alerts which are causing this component to show red by right clicking on the components and selecting “View Alerts”.
Notice the alert has a problem state of “Active” which indicates that this is a problem that needs attention.
3. Run the “Run SQL Server
Management Studio” task
MOM 2005 includes tasks which allow you to automatically perform administrative actions or run administration tools directly from the MOM Console. The SQL Server management pack includes a number of tasks which are available in the Microsoft SQL Server group in the task pane.
computers)
b. Select the MOMSERVER computer and then expand the task tree under Microsoft SQL Server SQL Server 2005 and choose the “Run SQL Server Management Studio” task. Run this task by clicking it once. This will open the Run SQL Server Management Studio tool in context for the computer you have selected. c. Close SQL Server Management Studio
4. Correcting a problem
causing red state by using a task
As we identified in Task 2, the SQL Server agent is not running on our server and is currently an active problem. In MOM 2005 we can correct this by running a task. To start the SQL Server agent on the machine, the following steps should be followed:
a. Navigate to the top level Alerts view by selecting the “Alerts” tree item
from the navigation pane on the left hand side.
b. Select the alert with the name “SQL Server Agent Service
Availability”.
c. Navigate to the task pane and browse to the “Microsoft SQL Server”
node of the task tree and expand the “SQL Server 2005” node. Next click on the “Start SQL Agent” task to restart the SQL Agent service.
d. Navigate through the wizard (leaving the default settings). Notice that
the task is aware of all of the installed instances of SQL Server on an agent machine (eg: $Agent Name$ parameter is equal to the service name for the relevant service and instance). If multiple instances are installed you can check or uncheck instances when running a task.
e. Once the task completes, open the services snap in and verify that the
service has started. The service display name is: SQL Server Agent (MSSQLSERVER)
f. After verifying the service has started, return to the MOM console state
view. Examine the SQL Server role, and the Agent component for the MOMSERVER machine. This should have now turned green as the problem has been corrected by running a task.
g. Navigate to the alerts view and look at the “SQL Server Agent
Exercise 2
Monitoring a “Blocked” SPID
Scenario
Blocking situations may affect the performance of SQL Server. In this exercise we will generate a blocking scenario and use MOM to alert on this condition.
During the exercise you will create a blocked SPID, generate an alert in MOM, and then run the blocked SPID report.
Tasks Detailed Steps
1. Create a blocked SPID In order to monitor a blocking scenario it is necessary to create one. The following steps walk you through the simple process of creating a blocked SPID using SQL Server Management Studio. To create a process perform the following steps:
a. Open SQL Server Management Studio
b. When prompted for the server to connect to, enter “MOMSERVER” and Windows authentication. c. Choose File Open File and open the file
C:\SQL Lab\Blocking Script 1.sql.
d. Edit the “au_id” field in the query to be: 724-08-9931
e. Execute the query by selecting the green Execute Query button on the toolbar.
f. This query will be the blocking query for this scenario.
To create a blocked process condition:
a. In SQL Server Management Studio, open another file C:\SQL Lab\Blocking Script 2.sql
b. Execute the query
You will notice that the second query does not return, but the query continues to execute. This is because the query is blocked waiting for the first query to finish. Leave the query running.
2. Identify the blocked SPID in MOM
MOM monitors for blocked processes on a timed basis. After creating the blocked process, this will be identified by MOM is approximately 1 minute.
Examine the alert raised by the management pack. It will be called “SQL Server 2005 Block Analysis (MSSQLSERVER.pubs)”.
Note – you may see a blank description in the alert. This is a known issue that the SQL team are investigating and planning to include in their SQL 2005 SP1 MP update.
Client Side Monitoring
Scenario
The SQL Server Management Pack provides a simple way to verify remote connectivity to SQL Server and to verify that users can successful connect to remote SQL Server databases. While other
monitoring tools do not verify that the network stack of a server is functioning or that other network devices between the server and the end user are functioning, the SQL Server Management Pack provides client-side monitoring of remote SQL Server databases. This feature monitors the availability of SQL Server by querying the server remotely from client computers that you designate.
Tasks Detailed Steps
1. Browse the client side monitoring rules and parameters
The client side management monitoring features of the management pack are configured using a set of rules. It is important you understand how these rules work to configure your clients and settings. To browse the rules, open the MOM Administrator console and browse to the following rule path:
Microsoft SQL Server\SQL Server 2005\Client Side Monitoring\Event Processing Rules
The following table lists the client-side monitoring rules:
Processing Rule Default Value SQL Server Remote Connectivity Enabled SQL Server Remote Connectivity — Execution Failure Enabled SQL Server Remote Connectivity — Execution Time
Warning
Enabled
While there are several SQL Server remote connectivity rules, the first of these rules (SQL Server Remote Connectivity) stores the script parameters for remote connectivity monitoring. The following table lists the script parameters, descriptions, and default states for the SQL Server Remote Connectivity rule.
Script Parameter Description Default Value CheckInstances A comma-separated list of remote
databases to connect to. This list might include only computer names (for a default instance) or a computer and instance name (for example, Server\Instance10).
<blank>
DatabaseName The database to run the query against. By default, the database is set to Master.
Master
ExecutionTimeAlert The acceptable time for the specified query to execute. If the query takes longer than the specified time to execute, an alert is raised. This number can be a decimal number, for example 0.01. The default value is 5 seconds.
5
InformationEvent Logs an information event each time the script runs.
Query The Transact-SQL query that is run on the designated client computer to check connectivity to SQL Server databases specified in the CheckInstances script parameter. The default query is SELECT GETDATE.
SELECT GETDATE
2. Configuring Client Monitoring
Client monitoring of remote connectivity is configured by installing either SQL Server or SQL Server client tools on the designated client computers and then installing a MOM agent on the designated client computers. Once the prerequisites are defined it is necessary to add the client computer to the client computer group.
To add computers to the MS SQL Server client-side monitoring computer groups
1. In the MOM Administrator console, navigate to: Microsoft Operations Manager\Management Packs\Computer Groups
2. Right-click the “Microsoft SQL Server 2005 Client Side Monitoring” computer group, and then click Properties. 3. Click the Included Computers tab.
4. Click Add to add computers. From the available list select the computer:
WORKGROUP\MOMSERVER 5. Click Apply, and then OK.
To add computers to the SQL Server remote connectivity rule 1. In the MOM Administrator console, navigate to the Client Side
Monitoring rule group of the Microsoft SQL Management Pack (under SQL Server 2005), and then click Event Processing Rules. 2. Right-click the SQL Server Remote Connectivity rule, and then
click Properties.
3. Click the Responses tab, select SQL Server 2005 Remote Connectivity, and then click Edit.
4. In the Script parameters box, select CheckInstances, and then click Edit Script Parameters.
5. In the Value field, enter the name of the machine to connect to. For this lab this will be “MOMSERVER”.
6. Using the steps above, modify the following other properties a. DatabaseName - set this to Northwind
b. ExecutionTime - set this to 0.001
c. Query - modify this to “Select * From Orders Where ShipCountry =- ‘Australia’”
3. Alerting on client side failures
The SQL Server management pack will generate client side alerts in the event that the query fails to execute or takes longer than the specified threshold to execute.
Exercise 4
Database Space Analysis and Thresholds
Scenario
The SQL management pack monitors the space of all databases and transaction logs. Granular thresholds can be defined based on the role of the databases.
Tasks Detailed Steps
1. Browsing the Processing Rules
The database space analysis state monitoring rule “SQL Server Database Space Analysis” is in the Microsoft SQL Server\SQL Server 2005\State Monitoring and Service Discovery rule group.
This rule stores the script responses for database space monitoring. The following table lists the scripts, the database they are applied to, the threshold settings, and the default state. If the database indicated is General, then the script is applied to all databases except those specifically called out in the table, such as the System Databases, Transaction Logs, and TempDB databases. The Model database is excluded from monitoring.
Script Database Threshold type and value
Default state
DBErrorLevel General databases Error — 10% Enabled DBWarningLevel General databases Warning — 20% Enabled InformationEvent NA Information — no
value set
Disabled
LogErrorLevel General Transaction logs Error — 10% Enabled LogWarningLevel General Transaction logs Warning — 20% Enabled SysDBErrorLevel System Databases (MSDB
and Master)
Error — 10% Enabled
SysDBWarningLevel System Databases (MSDB and Master)
Warning — 20% Enabled
SysLogErrorLevel System Transaction Logs (MSDB and Master)
Error — 10% Enabled
SysLogWarningLevel System Transaction Logs (MSDB and Master)
Warning — 20% Enabled
TempDBErrorLevel TempDB Error — 10% Enabled TempDBWarningLevel TempDB Warning — 20% Enabled TempLogErrorLevel Temporary Transaction Logs Error — 10% Enabled TempLogWarningLevel Temporary Transaction Logs Warning — 20% Enabled
2. Understanding Free Space Monitoring and Autogrow
Database and Transaction log space monitoring intelligently monitors free space and takes into account database autogrow, multiple file groups, and files. The free space check runs every hour by default, and will check each installed databases on the server. When checking the database, each database and transaction log file group and associated file is enumerated.
3. Combining Database Space Monitoring with Disk Space Monitoring
It is recommended that database and transaction log free space monitoring is used in combination with disk space monitoring to ensure that databases that are set to automatically grow have sufficient disk space as they grow. The SQL Server Management Pack includes two public views which help to monitor disk space. The public views are in the following location:
Microsoft SQL Server\SQL Server 2005\Server Resource Utilization\Disk Capacity
The following disk space views are provided: 1. % Free Space
2. Free Megabytes
Both of these views assist in determining if sufficient free space is available on the disk sub system. In MOM 2005 you can also leverage the Disk state monitoring role which monitors the available free space on all drives for a computer.
4. Customizing Thresholds
The database and transaction log space monitoring thresholds can be customized. If customizing thresholds, it is recommended you work with database administrators to determine the appropriate warning and error threshold levels. By default the space thresholds do not require any customization.
To customize Database Space Analysis monitoring do the following:
1. In the MOM Administrator console, navigate to the SQL Server Database Space Analysis rule under the following location:
Microsoft SQL Server\SQL Server 2005\State Monitoring and Service Discovery
2. Right-click on the rule and click the Response tab. 3. Select the script and click Edit.
4. In the Script parameters box, modify a script parameter by selecting one and clicking the Edit Parameter.
5. This is where the configuration for the thresholds in the SQL Server Database Space Analysis rule is done from. Once any parameter is changed, click OK and then Apply.
Exercise 5
Viewing SQL Server MP Reports
Scenario
One of the benefits of monitoring SQL Server with MOM is that the SQL Management Pack collects a wide variety of performance and configuration metrics about the managed servers and stores it in the System Center Reporting database.
During this exercise you will view reports that show how SQL Server is configured and how it is performing.
Tasks Detailed Steps
1. Open Report Console From the Operator Console, select the Go Reporting Console menu to open it. This will activate Internet Explorer and navigate to the SQL Reporting Services web page.
2. Select SQL Reports Select the “Microsoft SQL Server” link to view the reports that are available out-of-the-box from the SQL Management Pack
3. View SQL Configuration To see the details of how a specific SQL Server Instance is configured you can run the “SQL Server Configuration” report by clicking that link.
• Select “WORKGROUP\MOMSERVER” as the Computer • Press the View Report button
This report displays the configuration of SQL Server instances installed on a specified server. You can see details on server version, service packs, and database sizes. The report is generated by data discovered by the SQL Server Management Pack.
4. View User Connections by Peak Hour
To return to the SQL Server reports page, click on the “Microsoft SQL Server” link near the top of the page.
To see performance information on the number of users accessing the SQL Server at different times during the day, select the “User
Connections by Peak Hour” report. If your report does not contain much data you may want to select the optional date ranges below.
• [Optionally] Enter Begin Date = 3/24/2006 • [Optionally] Enter End Date = 3/31/2006
• Select “WORKGROUP\MOMSERVER” as the Computer • Press the View Report button
