database Integration
sql-server (en)


ServicePilot database-sql-server


# SQL Server Database

Overview

This package monitors a Microsoft SQL Server Database.

Description

This package automatically configures the ServicePilot Agent in order to monitor a Microsoft SQL Server Database by using SQL requests.

The statistics gathered in this way allow system and instances performance management.

Requirements

  • Network Flows - It is is necessary to ensure network connectivity between ServicePilot and the monitored device. In case of a network infrastructure using a firewall, all the ports below must be opened:

    • ServicePilot Manager Web server access (by default TCP/80 when using HTTP or TCP/443 when using HTTPS although this port is configurable): Between ServicePilot Agent and ServicePilot Manager
    • TCP/1433 (Default) (Microsoft SQL): Between ServicePilot Agent and Microsoft SQL Server
    • UDP/1434 (SQL Server Browser service): Between ServicePilot Agent and Microsoft SQL Server. Note that if firewalls are being implemented then it is preferable to set a fixed port for Microsoft SQL database instance access that is on TCP/1433 by default.
    • If Windows domain authentication is being used then connectivity between the ServicePilot Agent and Windows Domain Controllers for authentication, time synchronization, group policy, etc.
  • Third Party Configuration

    • Created an SQL Server Authentication Login for the Microsoft SQL Server database instance or a Windows domain account used by the ServicePilot Agent
    • Created and associated a database User with read permissions in the databases monitored to the Login
    • Allowed IP remote access to the Microsoft SQL Server database instance and associated firewall rules
  • ServicePilot Requirements

    • ServicePilot Manager minimum version: 8.5
    • ServicePilot Agent Minimum version 8.5: installed and configured

Installation

Before adding a resource to monitor, make certain that all pre-requisites are in place and if a ServicePilot Agent is required, that it is communicating correctly with the ServicePilot Manager.

Add resource using Views Configuration web interface

  1. As an administrative user of ServicePilot, open the ServicePilot web interface.
  2. Navigate to Administration. The Configuration > Views web page will open.
  3. Click on the view in which to place the new resource in the Views hierarchy on the left of the interface. The View editor section will show the existing view contents.
  4. From the Packages list on the right of the interface, click and drag the database-sql-server package into the View editor and let go.
  5. The Package properties dialog box will open to allow resource configuration.
  6. Click OK to close the Package properties dialog box. Note that the dialog box will not close if required parameters are not set.
  7. Click Save to apply the new resource to ServicePilot configuration.

Key field notes

  1. In the Monitoring Policies tab, specify the policy or policies to apply to the package

  2. Connection tab:

    1. Instance to monitor: Specify the database instance to monitor in the format Host\Instance
    2. Authentication Type: Select the authentication type (Windows or SQL) to be used by ServicePilot to connect to the SQL Server Database
    3. Database Login: Specify the username of the SQL Server Login Account
    4. Database Password: Specify the password of the SQL Server Login Account
  3. Monitoring Options tab:

    1. Polling Interval (sec): Specify the time interval (in seconds) at which ServicePilot will poll the Microsoft Database
  4. Filters tab:

    1. Databases Included Filter: Specify the Databases to be included in the monitoring, separated by | e.g. "Database1|Database*"
    2. Databases Excluded Filter: Specify the Databases to exclude from the monitoring, separated by | e.g. "Database1|Database*"

      Note: Each element of the Databases Filter list is a regular expression as defined by Like Operator (Visual Basic)

Note: Database Login and Password are only required for SQL type authentication

Notes

This package uses the master.sys.dm_os_performance_counters table from SQL Server.