SolutionPack for Microsoft SQL Server Summary Sheet – ViPR SRM 3.6

Table of Contents

Overview

Learn how to install and configure the SolutionPack for Microsoft SQL Server. This SolutionPack provides the unified monitoring of Microsoft SQL databases using SQL queries targeted at servers. It provides key information for database administrators, such as server host performance, database availability, and deadlocks. Both ViPR SRM and Service Assurance Suite use this SolutionPack.

Back to Top

Technical Specifications

SolutionPack version

1.0

Supported Microsoft SQL versions

SQL Server 2008R2

SQL Server 2012

Data collection method

SQL collector

SQL commands

This SolutionPack uses these commands:

User privilege requirements

SQL authentication requires that the user be a member of the SYSADMIN group.

The Windows user account requires that the user be a member of the Guests group or the Domain Guests group.

The Windows user account must be imported into the Microsoft SQL Server with settings similar to these:

The default database must be the master.

Main reports

Host Health (%), including the percentage of CPU and memory utilization of all hosts

Performance of SQL Server

Databases Overview

Performance Scorecard of storage usage, index contention, memory pressure, signal waits, SQL server hosts performance, and user connections and logins

Situations to Watch, including memory exceptions, SQL server locks, I/O botllenecks, CPU bottleneck, data files out of space, and actual and forecasted SLA values

Inventory of servers, databases, data files, and objects

Back to Top

Where to find the latest SolutionPack software

Install the latest core software update for your product suite. SolutionPacks distributed with core software have a 30-day free evaluation period. If you plan to use the software longer than 30 days, you must install a SolutionPack license before the trial period ends.

This 30-day free evaluation only applies to new installations and is not available for upgraded installations. If you upgrade the core software and want to try a new SolutionPack, you must request a license for that SolutionPack by completing a Support Request (SR) form, which is available on the EMC Online Support website at http://support.emc.com.

Back to Top

Installing the SolutionPack

Before you begin

  • Determine whether you need a SolutionPack license file by checking the feature names and expiration dates listed in Centralized Management > License Management. If the license is not listed, obtain one by completing a Support Request (SR) form, which you can find on the EMC Online Support website at http://support.emc.com.

  • Make sure the core modules, such as the Module-Manager, are up-to-date on all servers since not all module dependencies are validated during the SolutionPack installation. See the EMC M&R (Watch4net) Installation and Configuration Guide provides for more information.

Procedure

  1. Log in to Centralized Management.
  2. Select SOLUTIONPACK CENTER.
  3. Select the SolutionPack for Microsoft SQL Server in the Browse and Install SolutionPacks screen.
  4. Read the summary information and click Install.
  5. In the Collector field of the Select the components to install window, select the Collector host.
  6. In the Reports field, select the Frontend host.
  7. Click Next.
  8. Make a selection in the Data Collection field.
  9. If you select Add a new Data collection, enter information for the new data collection. In the Hostname or IP address to send data field, type localhost.
  10. Leave Enable alerting on data collected selected.
  11. Make a selection in the Alerting on data collection field.
    If you select Add a new Alerting on data collection, type information for the new alerting destination. In the Alerting Backend hostname or IP address field, select the Primary Backend host.
  12. Type information to identify the SQL Server.
    If you select Choose for Windows authentication instead of SQL Server authentication, type the Windows domain name.
  13. Set these values:
    • Fully qualified name of the SQL server
    • Port
    • Username
    • Password
  14. Select Use advanced settings to configure polling settings.
  15. Click Next.
  16. In the Administration Web-Service Instance field on the Reports window, select the Frontend host.
  17. Select Centralized Management > SolutionPack to verify the installed SolutionPack.
Back to Top

Confirming report creation

After you install a SolutionPack, you can view its reports.

To view the reports:

Procedure

  1. Go to User Interface > Report Library.
  2. Click the SolutionPack to view its reports.

Results

It may take up to an hour to display all relevant information in these reports.

Back to Top

Troubleshooting

Report display problems

Back to Top

What to do if data does not appear in reports

Procedure

  1. Do the following to check the Collector Manager logs:
    1. View /opt/APG/Collecting/Collector-Manager/microsoft-sqlserver/logs/collecting-0-0.log.
    2. Verify that the last message in the log states that a certain number of raw values were collected using 13 SQL queries. This message indicates the collector is working.
      For example: <instance_master> collect done: 2,735 new raw values collected in 0 seconds 527 ms using 13 SQL queries
    3. Check for any Java exceptions and warning messages.
  2. If collector errors exist, do the following:
    1. Ping the Microsoft SQL Server using the fully qualified domain name (FQDN).
    2. Replace the current user, which is the username and password set during SolutionPack installation to retrieve data from the Microsoft SQL sever, with a user with SYSADMIN rights.
    3. Use telnet to check if the Microsoft SQL port is accessible from the SQL collector server.
    4. Reconfigure the SolutionPack by navigating to Centralized Management > SolutionPacks > Microsoft SQL Server.
    5. Under SolutionPackBlock, click the pencil next to Collector to reconfigure the settings.
    6. To edit advanced connection settings, you may need to manually edit the SQL collector configuration file.
  3. If no errors exist and the last message in the log indicates that 13 queries were executed, do the following:
    1. Wait a minimum of three polling cycles. By default, one polling cycle is five minutes.
    2. Navigate to Centralized Management > Scheduled Tasks.
    3. Under the Backend category, run backend/Default/import_variable_status.task.
    4. Refresh the reports after five minutes.
  4. Make sure the Frontend is attached to the database used by the SQL collector.
Back to Top

Running a scheduled task to import data into reports

After you push a new configuration into a collector, a scheduled task runs and populates the reports with new data. You can manually run the scheduled task to import the data more quickly.

Before you begin

Allow at least three polling cycles to pass before manually running the scheduled task.

Procedure

  1. Click Administration.
  2. Click Centralized Management.
  3. Expand Scheduled Tasks.
  4. Click Database.
  5. Select the import-properties-Default task.
  6. Click Run Now.
  7. Confirm success in running the task in the Last Result and Last Result Time columns.
Back to Top

Viewing collector errors in the Collector-Manager log files

Review the Collector-Manager log files to troubleshoot problems with data collection.

Procedure

  1. Click Administration.
  2. Click Centralized Management.
  3. Expand Collecting.
  4. Click the Collector-Manager for your collector instance.
    Collector-Manager::<Collector-Manager instance> - <physical_host_ID>
  5. Expand Log Files and click the View File icon to review the configuration error messages.
Back to Top