Using System Monitoring Plug-in for Microsoft SQL Server

Purpose

This OBE describes how to use the system monitoring plug-in for Microsoft SQL Server in Oracle Enterprise Manager 10g Grid Control Release 2.

Time to Complete

Approximately 20 minutes

Topics

This OBE covers the following topics:

	Overview
	Prerequisite
	Viewing the Home Page
	Monitoring Availability and Performance
	Viewing SQL Server Reports
	Performing Configuration Management
	Using Enterprise Manager's Monitoring features for Microsoft SQL Server Monitoring
	Performing Root Cause Analysis (RCA)
	Summary
	Related information

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: Because this action loads all screenshots simultaneously, response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

The screenshots will not reflect the specific environment you are using. They are provided to give you an idea of where to locate specific functionality in the Oracle Enterprise Manager 10g Grid Control console.

Overview

The Enterprise Manager 10g Grid Control System Monitoring Plug-in for Microsoft SQL Server delivers comprehensive availability, performance and configuration information for Microsoft SQL Server. By combining Microsoft SQL Server monitoring with the richest and most comprehensive management of Oracle systems, Enterprise Manager significantly reduces the cost and complexity of managing IT environments that have a mix of Microsoft and Oracle technologies. Administrators running packaged and custom applications on top of Oracle systems and Microsoft can now centralize all of the monitoring information in the Grid Control Console, model and view the complete topology of their applications, and perform comprehensive root cause analysis.

Back to Topic List

Prerequisite

Before performing the tasks mentioned in the OBE, you should first download the Microsoft SQL Server Plug-in from OTN. Then import the plug-in into the management repository, deploy the plug-in to the agents, and then discover the Microsoft SQL Server target from Grid Control.

Back to Topic List

Viewing the Home Page

Administrators need a consistent and consolidated solution for managing the targets within their datacenter. Learning different interfaces or following different procedures for tasks across monitored targets is not a productive use of an administrator's time. Enterprise Manager addresses this problem by providing a consistent look-and-feel across all monitored targets - via target Home pages. Each of these Home pages provides an at-a-glance view of the health and performance of the corresponding target, enabling the administrator to truly manage by exception. Perform the following steps to view the home page for Microsoft SQL Server and to get an overview of the basic functionality:

1.	Open the browser and enter the following URL: http://<management service hostname>.<domain>:<port>/em/ The default port value on a clean machine is 7777. However, if there are other instances running on the machine, then the port may be different. The login page will be displayed. Enter the User Name and Password, and then click the Login button.
2.	Click the Targets tab.
3.	Click the All Targets subtab.
4.	Select Microsoft SQL Server in the Search dropdown, and click the Go button.
5.	Click the link for Microsoft SQL Server target.
6.	The Microsoft SQL Server home page provides you with the health of Microsoft SQL Server at the right grain and density, and complements your corporate strategy of operational excellence. It provides an at-a-glance view of the health and performance of Microsoft SQL Server. From this page, you obtain the target's status, availability over time, outstanding alerts, and access to additional SQL Server details via various drilldowns. Also from the Home page you can view alerts for the host on which Microsoft SQL Server resides, or you can drilldown to the host itself to obtain further detail on the host's availability and performance. Such integration enables you to easily and quickly correlate performance problems between the SQL Server target and the Microsoft operating system on which it runs. Note: The System Monitoring Plug-ins for Microsoft SQL Server also supports remote monitoring. In cases where the SQL Server target is monitored remotely, the Host section contains information about the host where the management agent is deployed.

Back to Topic List

Monitoring Availability and Performance

This portion of the lab will illustrate how the System Monitoring Plug-in for Microsoft SQL Server offers immediate value by automatically monitoring the availability and performance of the SQL Server targets. More than 170 out-of-box performance metrics are monitored for Microsoft SQL Server. In addition to such real-time monitoring of performance metrics, Enterprise Manager also stores the monitoring information in the management repository, thereby enabling you to analyze performance through various historical views and facilitating strategic tasks such as trend analysis and reporting. Perform the following steps to monitor the availability and performance of Microsoft SQL Server:

1.	To get an idea of the depth of performance monitoring Grid Control offers for Microsoft SQL Server, let's drilldown into All Metrics from the home page. Click the All Metrics link under the Related Links section.
2.	You can see the various metric categories that Grid Control monitors. Some of the key metrics collected include SQL Server Response, Memory / Cache / CPU Statistics, Locks and Processes, Alert Log and Events, Database Backups and Jobs, Space Usage, Users and Roles. Let's expand one of these categories, and look at one of the metrics in more detail. Click the plus sign next to Server Statistics to expand it
3.	Click the CPU Busy Ratio metric link.
4.	From the metric details page, you can see various statistics concerning this metric as well as a graphical view of its values in real-time, historical, or your own customized timeframe and how the values compare with defined thresholds. Alert history is also provided to track past problems and comments/details about them.

Back to Topic List

Viewing Microsoft SQL Server Reports

To aid you with critical tasks such as problem diagnosis, trend analysis and capacity planning, the System Monitoring Plug-in for Microsoft SQL Server includes eleven out-of-box reports, summarizing key information about SQL Server availability, performance, resource consumption and configuration. These reports are easily accessible from the SQL Server Home page in the Grid Control Console and from the Information Publisher (Enterprise Manager's powerful reporting framework), enabling administrators to schedule, share, and customize reports to fit their operations needs. Perform the following steps to view Microsoft SQL Server Reports:

1.	Navigate to the Microsoft SQL Server home page by clicking the Microsoft SQL Sever locator link.
2.	Click the Reports subtab.
3.	Here you see the Microsoft SQL Server Configuration report, which details SQL Server configuration information, such as server parameter, security, and registry settings. Click the View drop-down list. Here you see a list of reports that are provided out-of-box. They summarize key performance, usage, and configuration information. Let's look at the Microsoft SQL Server Space Usage report.
4.	This report presents information about space usage for each of the databases on the SQL Server, as well as graphically displays the space usage of the top five most utilized databases. View any other reports that you are interested in. Click the Home Subtab.
5.	In addition to being available on the SQL Server home page, these reports are also available from the Reports tab. You can take advantage of Enterprise Manager's powerful reporting capabilities, customize out-of-box reports to fit your operational needs, share reports with all types of business users, and schedule report generation. Click the Reports link under the Related Links section.
6.	Here you see all the reports for Microsoft SQL Server. In addition, you can take advantage of monitoring reports (such as Availability History (Target) or Outstanding Alerts and Policy Violations (Target)), to report on availability and health of the SQL Server environments. Click on the Availability History (Target) report in the Monitoring category and Availability History sub-category.
7.	Click the Continue button.
8.	You get to see the availability summary, chart and details for the SQL Server target.

Back to Topic List

Performing Configuration Management

Tracking configurations is one of the most time-consuming and difficult tasks administrators face on a daily basis. Being able to quickly view a detailed configuration snapshot, analyze historical changes and enforce standardization between systems is key to diagnostics, auditing, compliance, and making solid business decisions.

System Monitoring Plug-in for Microsoft SQL Server simplifies these tasks by automatically collecting detailed configuration information about Microsoft SQL Server, including: operating system details, clustering information, database and registry settings. This information is collected daily and stored in the management repository. In addition, Grid Control automatically tracks all changes to the Microsoft SQL Server configuration, helping administrators answer key questions about what changed and when the change was made. System Monitoring Plug-in for Microsoft SQL Server also enables enterprise-wide configuration comparisons of SQL Server instances, allowing administrators to quickly and easily pinpoint potential differences. This helps to keep systems synchronized and to reduce "configuration drift". In addition, it simplifies investigations into why systems that are presumed to be identical, are behaving differently. Perform the following steps to see how you can manage Microsoft SQL Server configuration using Grid Control:

1.	Click the Targets tab.
2.	Click the All Targets subtab.
3.	Select Microsoft SQL Server in the Search dropdown, and click the Go button.
4.	Click the link for Microsoft SQL Server target.
5.	Click the View Configuration link..
6.	Gird Control automatically collects configuration information for Microsoft SQL Server, including server configuration, database settings and registry settings. By default, this information is collected every 24 hours, but you can force the collection of new data by clicking the Refresh button on this page. You can view configuration history, compare configurations between SQL Server instances , and take a snapshot of the current configuration. Click the History button.
7.	This page shows all of the configuration changes that occurred on the SQL Server since Enterprise Manager started monitoring it. You can further drill down to see the details for each change.

Back to Topic List

Using Enterprise Manager's Monitoring features for Microsoft SQL Server monitoring

Microsoft SQL Server administrators need a powerful monitoring solution that will proactively notify them of availability and performance problems, automate routine tasks, enable standardization and reduce the cost and complexity associated with managing sets of systems. Enterprise Manager provides a comprehensive monitoring solution for Microsoft SQL Server. You can take advantage of the following key features:

Blackout Periods: prevent unnecessary alerts from being raised during scheduled maintenance operations, such as SQL Server backup or hardware upgrade.
Monitoring Templates: simplify the task of standardizing monitoring settings across the entire Microsoft SQL Server environment, by allowing administrators to specify the monitoring settings (metrics, thresholds, metric collection schedules and corrective actions) once and applying them to any number of SQL Server instances.
Corrective Actions: ensure that routine responses to alerts are automatically executed, thereby saving administrators time and ensuring problems are dealt with before they noticeably impact users.
Notification Rules, Methods and Schedules: define when and how administrators should be notified about critical problems with their applications, ensuring quicker problem resolution.
Groups / Systems: significantly simplify management of large numbers of components, allowing administrators to "manage many-as-one". By combining SQL Server instances in groups, or including them in heterogeneous groups or systems, administrators can benefit from a wealth of group management features, such as ability to proactively monitor availability and alerts of all group members via the System Monitoring Dashboard.

Perform the following steps to monitor your Microsoft SQL Server target:

1.	Click the Targets tab.
2.	Click the Groups subtab.
3.	Click the Plug-ins group, to go to its home page.
4.	This group contains a number of different plug-ins, including Microsoft SQL Server. The group home page presents administrators with a summary of the status, alerts and policy violations across all of the members of the group. Click the Charts subtab.
5.	When defining groups, administrators have the ability to include summary charts, which allow them to analyze collective performance of the group members. Here we can see different charts, displaying highest average/ lowest average/or statistical information across targets in the group. As you can see, metrics presented here are from different target types, such as Microsoft SQL Server and Juniper Netscreen firewall. Click the Launch Dashboard button.
6.	System Monitoring Dashboard provides administrators with a near real-time view of open alerts against members of the group. The color-coded interface highlights problems using universal colors of alarm - red for critical issues, yellow for warning alerts, and green for normal conditions. The System Monitoring Dashboard significantly reduces the complexity of monitoring groups of targets. As you can see, Microsoft SQL Server automatically appears on the System Dashboard. This allows administrators managing Oracle and Microsoft technologies to have a single view of all of the alerts on their environment. Here you looked at how Groups functionality can be applied to Microsoft SQL Server, just like to any other Enterprise Manager managed target. Similarly, you can use familiar monitoring features, such as alerts, notifications, blackouts, and templates for Microsoft SQL Server monitoring.

Back to Topic List

Performing Root Cause Analysis

Grid Control's Service Level Management functionality provides a comprehensive monitoring solution that helps IT organizations achieve high availability, performance, and optimized service levels for their business services. Administrators can monitor services from the end-users' perspective using service tests or synthetic transactions, model relationships between services and underlying IT components, diagnose root cause of service failure, and report on achieved service levels. The System Monitoring Plug-in for Microsoft SQL Server enables IT organizations running applications on top of Oracle and Microsoft SQL Server to derive greater value from Grid Control's Service Level Management features in a number of ways:

Enhanced Service Modeling: map relationships between services and the SQL Server instances that they rely on.
Complete Service Topology: include Microsoft SQL Server instances as part of the topology view of a service.
Comprehensive Root Cause Analysis: identify or exclude Microsoft SQL Server as the root cause of service failure.

Perform the following steps to see how you can use Microsoft SQL Server as part of the topology of a service:

1.	Click the Group: Plug-ins link on the dashboard.
2.	Click the Service subtab.
3.	Here you see a list of Services managed by Enterprise Manager. Let's look more closely at the Loan Application Service, which is down. Click the Loan Application Service link.
4.	On the home page, you can immediately see the Root Cause of service failure. The service is down because one of the databases that it depends on is down. Now let's look at the topology of this service. Click the Topology subtab on the service home page.
5.	As you can see, Loan Application Service is an aggregate service, consisting of multiple subservices. Here we also see all of the infrastructure components that individual subservices are relying on. In red, we see visual indication of the root cause of service failure. In the "Overview" section, use the zoom functionality and move the view selector to the left sub-service, to see it more clearly.
6.	The different components of the service are more clear. As you can see, plug-ins (such as SQL Server and Juniper Firewall) appear as part of the topology of the service. By bringing plug-ins into Enterprise Manager, you can now perform comprehensive service modeling and view the entire service topology. In addition, you can identify or exclude plug-ins as a cause of service failure. In this case, SQL Server is not the cause of service failure.

Back to Topic List

Summary

In this lesson, you learned how to:

	View the Home Page
	Monitor Availability and Performance
	View Microsoft SQL Server Reports
	Perform Configuration Management
	Use Enterprise Manager's monitoring features for Microsoft SQL Server monitoring
	Perform Root Cause Analysis (RCA)

Back to Topic List

Related Information

To ask a question about this OBE tutorial, post a query on the OBE Discussion Forum.

Back to Topic List