SolutionPack for Microsoft SQL Server 3.7 Summary Sheet – Service Assurance Suite 9.4.1

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.

The reports in the SolutionPack for Microsoft SQL Server give key information about status including:
  • performance and availability of constituent databases
  • datafile and logfile capacities
  • performance metrics such IO, Wait, Memory pressure, and Host performance details
  • End-to-End capacity reports (MS-SQL Server Application to LUN Detail)
Back to Top

Technical Specifications

SolutionPack version

3.7

Compatible EMC M&R versions

6.6u1

Supported Microsoft SQL versions

SQL Server 2008R2

SQL Server 2012

SQL Server 2014

Port

1433 (This is the default port on which the MS-SQL instance is running on the MS-SQL Server. It is configurable.)

Data collection method

SQL collector

SQL commands

This SolutionPack uses these commands:

Main reports

Support for Host Details report

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

User privilege requirements for SolutionPack for Microsoft SQL

The Microsoft SQL Server can be discovered with SQL Authentication or Windows Authentication.

Microsoft SQL authentication

SQL authentication works for users having either SYSADMIN privileges or an unprivileged account.
Note Image
An unprivileged account can be created and used to discover the Microsoft SQL Server. Configuring the SolutionPack with an unprivileged account

Windows authentication

A Windows user account must be imported into the Microsoft SQL Server with settings similar to these:
  • Server role of Public
  • Securable grants for Connect SQL, View any definitions, and View server state

Back to Top

Configuring the SolutionPack with an unprivileged account

Create a watch4net account with specific grants required by the Microsoft SQL SolutionPack collector.

The collector must connect to each instance of MS-SQL Server and perform SQL queries. You can use either an administrator equivalent system account or create a dedicated system account for the collector. If you want a dedicated system account, please ask the DBA administrator to create a watch4net account with specific grants for the collector.

Procedure

  1. Follow this example to create a watch4net account.
    The default database must be used as the [master] database in this procedure.
    USE [master]
    GO
    
    CREATE LOGIN [watch4net]
    WITH PASSWORD=N'<securepassword> ',
    DEFAULT_DATABASE=[master],
    DEFAULT_LANGUAGE=[us_english],
    CHECK_EXPIRATION=OFF,
    CHECK_POLICY=OFF
    GO
    
    EXECUTE master.sys.sp_MSforeachdb
    'USE [?];
    CREATE USER watch4net FOR LOGIN watch4net
    GO
    
    GRANT VIEW SERVER STATE TO watch4net
    GO
    
    GRANT VIEW ANY DEFINITION TO watch4net
    GO
    ;
    

Results

Your dedicated account is configured.

Back to Top

Installing the SolutionPack for Microsoft SQL

Before you begin

  • 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. The installation guide for your product provides more information.
  • Ensure you create a watch4net user prior to installing the SolutionPack. You may need this to ensure correct credentials are added to Microsoft SQL Server Devices Management during the installation process.

Procedure

  1. Log in to your product and click Administration > Centralized Management.
  2. Click SolutionPacks and select SOLUTIONPACK CENTER.
  3. Select the SolutionPack for Microsoft SQL Server in the Browse and Install SolutionPacks window.
  4. Read the summary information and click Install.
  5. Type the instance name.
  6. Assign a server for each of the three components.
  7. Click Next.
    The window displays the data collection details.
  8. Make a selection in the Data Collection drop-down menu.
    Select existing settings that have been specified for other components or select Add a new data collection.
  9. If you select Add a new Data collection, type information about the data collection. In the Hostname or IP address to send data field, use localhost on default port 2020.
    This is the Collector host where the Load Balancer Connector is installed.
  10. Select the Frontend Web Service option.
    Select existing settings that have been specified or choose Add a new Frontend Web Service.
  11. If you select Configure Collector advanced settings, you have the option to select different polling periods.
    The default polling period is 15 minutes. Consult with your database administrator to set the polling period based on the number of SQL Server instances that are being polled by this collector manager instance. For example, you have the option to select 5 minutes, 15 minutes, 30 minutes, or one hour.
  12. Click Next.
  13. If you select Add a new Web-Service Gateway, type information about the web-service gateway.
  14. In the Administration Web-Service instance, select an existing instance or create a custom instance.
  15. Click Install.
  16. Click OK.

    Monitor the installation logs to ensure that the installation completes successfully.

  17. Click Discovery Center > Device Management.
  18. Click Microsoft SQL Server.
  19. Click Add new device.
  20. Select the server and collector instance where you want to store the configuration details for this device.
  21. Set these values:
    • Enter the Hostname or IP address of the SQL server.
    • Specify the Database Port and Database Instance name. For example, type Default for the SQL Server Instance name or type the named instance.
    • Specify the mode of authentication used for the SQL Server: SQL Authentication or Windows Authentication
    • If using SQL Server authentication, type the username and password for the SQL Server.
    • If using Windows authentication, provide the Windows domain, username, and password. Use either the local or domain username.
  22. Click Test to check connectivity with the SQL Server.
  23. Click OK.
  24. Click Save.
Back to Top

Limitations for Microsoft SQL Server

If there are connection problems, find out whether SSL (Secure Socket Layer) is enabled.

Microsoft SQL Server is unable to connect to a Microsoft SQL Server that has SSL enabled. This is due to the inherent limitation of the OpenSource JTDS driver used to connect to the SQL Server.

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 nine 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 9 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 nine queries were executed, do the following:
    1. Wait a minimum of three polling cycles. By default, one polling cycle is fifteen 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 fifteen 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 > Logical Overview.
  3. Expand Collecting.
  4. Click the Collector-Manager for your collector instance.
    Collector-Manager::<Collector-Manager instance> - <host_ID>
  5. Expand Log Files and click the View File icon to review the error messages.
Back to Top