SolutionPack for Microsoft SQL Server Summary Sheet
Table of Contents
Overview
This article explains 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.
This SolutionPack is one of many provided with Service Assurance Suite.
Technical Specifications
Compatible Watch4net versions
- 6.3u1 and later
Supported Microsoft SQL versions
- SQL Server 2008R2
- SQL Server 2012
Data collection method
- SQL collector
SQL commands
These commands are used by the SolutionPack:
- SELECT
- CONNECT
- INSERT
- CREATE TABLE (creates temporary tables)
- EXEC (executes store procedure that lists disks and their available free space)
- DROP (removes temporary tables)
User privilege requirements
- SQL authentication requires that the user be a member of the SYSADMIN group
- Windows user account requires the user be a member of the Guests group or the Domain Guests group
- Windows user account must be imported into the Microsoft SQL Server with settings similar to these:
- Server roles of public
- Securable grants for Connect SQL, View any definitions, and View server state
- 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
Where to find the latest SolutionPack software
SolutionPack software updates
- Install the latest core software update, such as 6.4. 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.
- Upload a SolutionPackCenter Update.zip file from . Since new SolutionPacks distributed in the zip file do not include a 30-day trial license, you must install a license file before installing the SolutionPack.
Installing the SolutionPack
Before you begin
Determine whether you need a SolutionPack license file by checking the feature names and expiration dates listed in http://support.emc.com.
. If the license is not listed, obtain one by completing a Support Request (SR) form, which you can find on the EMC support website- 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
- Log in to Centralized Management.
- Select SOLUTIONPACK CENTER.
- Select the SolutionPack for Microsoft SQL Server in the Browse and Install SolutionPacks screen.
- Read the summary information and click
Install.
You have the option of selecting Windows authentication instead of SQL Server authentication for this SolutionPack.
- For both Windows and SQL Server authentication, set these values:
- Fully qualified name of the SQL server
- The port to connect to
- For SQL Sever authentication, set these values:
- Username
- Password
- For Windows authentication, do the following:
- Check Choose for Windows authentication instead of SQL Server authentication.
- Type the Windows domain name.
- Set values for Username and > Password.
After you finish
After the installation is complete, select SolutionPack.
to verify the installedConfirming report creation
After you install a SolutionPack, you can view its reports.
To view the reports:
Procedure
- Go to .
- Click the SolutionPack to view its reports.
Results
Troubleshooting
Report display problems
What to do if data does not appear in reports
Procedure
- Do the following to check the Collector Manager logs:
- View /opt/APG/Collecting/Collector-Manager/microsoft-sqlserver/logs/collecting-0-0.log.
- 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
- Check for any Java exceptions and warning messages.
- If collector errors exist, do the following:
- Ping the Microsoft SQL Server using the fully qualified domain name (FQDN).
- 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.
- Use telnet to check if the Microsoft SQL port is accessible from the SQL collector server.
- Reconfigure the SolutionPack by navigating to .
- Under SolutionPackBlock, click the pencil next to > Collector to reconfigure the settings.
- To edit advanced connection settings, you may need to manually edit the SQL collector configuration file.
- If no errors exist and the last message in the log indicates that 13 queries were executed, do the following:
- Wait a minimum of three polling cycles. By default, one polling cycle is five minutes.
- Navigate to .
- Under the Backend category, run backend/Default/import_variable_status.task.
- Refresh the reports after five minutes.
- Make sure the Frontend is attached to the database used by the SQL collector.
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
- Click Administration.
- Click Centralized Management.
- Expand Scheduled Tasks.
- Click Database.
- Select the import-properties-Default task.
- Click Run Now.
- Confirm success in running the task in the Last Result and Last Result Time columns.
Viewing collector errors in the Collector-Manager log files
Review the Collector-Manager log files to troubleshoot problems with data collection.
Procedure
- Click Administration.
- Click Centralized Management.
- Expand Collecting.
- Click the Collector-Manager for your collector instance.
Collector-Manager::<Collector-Manager instance> - <physical_host_ID>
- Expand Log Files and click the > View File icon to review the configuration error messages.