Microsoft SQL Server

• Overview
• Setup
  • Prerequisites
  • Configuration
• Troubleshooting
• Metrics and Tags
  • Default Metrics
  • Default Metric Tags

Overview

This plugin collects runtime metrics from Microsoft SQL Server instances. It gathers information about resource usage and performance characteristics, including information for queries, on your database instance(s).

Setup

The sqlserver plugin is included with the SolarWinds Snap Agent by default, please follow the directions below to enable it for a agent instance.

Prerequisites

This plugin requires the connection credential to the Microsoft SQL Server instance to monitor, user must have public access role to all databases.

Configuration

The SolarWinds Snap Agent provides an example configuration file to help you get started quickly. It defines the plugin and task file to be loaded by the agent, but requires you to provide the correct settings for your database. To enable the plugin:

1. Make a copy of the Microsoft SQL Server example configuration file sqlserver.yaml.example, renaming it to sqlserver.yaml

On Windows, using Explorer or PowerShell:

Copy

> copy "C:\ProgramData\SolarWinds\Snap\plugins.d\sqlserver.yaml.example" "C:\ProgramData\SolarWinds\Snap\plugins.d\sqlserver.yaml"

On Linux using command line:

Copy

$ sudo cp /opt/SolarWinds/Snap/etc/plugins.d/sqlserver.yaml.example /opt/SolarWinds/Snap/etc/plugins.d/sqlserver.yaml

2. Update the sqlserver.yaml configuration file with settings specific to your database instance, for example:

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
collector:
  sqlserver:
    all:
      servers: |
        server=localhost;user id=appoptics;password=PLEASECHANGEME;app name=hostagent;log=1;
      query_version: 2
      # azuredb: false
      exclude_query: |
        DatabaseIO
        DatabaseProperties
        Schedulers
        SqlRequests
load:
  plugin: snap-plugin-collector-bridge-sqlserver.exe
  task: task-bridge-sqlserver.yaml

• servers: required setting that should set the connection information to the database instance. The credentials should be entered in the following format:

  Copy

  1
  2
  3
  servers:
    server={hostname\instance};user id={username};password={password};app name=AOHost
  

  or for multiple instances:

  Copy

  1
  2
  3
  4
  servers: |
    server={hostname\instance1};user id={username};password={password};app name=AOHost
    server={hostname\instance2};user id={username};password={password};app name=AOHost
  

• query_version: by default set to 2 and specifies version of metrics that are collected by plugin.

• azuredb: if it is true the collector will gather resource utilization metrics.

• exclude_query: allows for excluding metric groups from being collected. Possible choices are:

  • PerformanceCounters
  • WaitStatsCategorized
  • DatabaseIO
  • DatabaseProperties
  • CPUHistory
  • DatabaseSize
  • DatabaseStats
  • MemoryClerk
  • VolumeSpace
  • Schedulers
  • AzureDBResourceStats
  • AzureDBResourceGovernance
  • SqlRequests
  • ServerProperties

• include_query: a list of queries to include. If not specified, all the above listed queries are used.

3. Restart the agent:

Copy

> net stop swisnapd
> net start swisnapd

On Linux command line:

Copy

$ sudo service swisnapd restart

4. Enable the Microsoft SQL Server plugin in the AppOptics UI

On the Integrations Page you will see the Microsoft SQL Server plugin available if the previous steps were successful. If you do not see the plugin, see Troubleshooting Linux.

Select the Microsoft SQL Server plugin to open the configuration menu in the UI, and enable the plugin.

You should soon see the sqlserver metrics reported to your dashboard.

Troubleshooting

Required SQL Services:

• SQL Browser
• TCP/IP enabled in SQL Server Network Configuration

SQL User Permissions:

• SQL Authentication only

• User must have at least processadmin role, the sysadmin role is needed if you intend to collect data file metrics. If you do not have the correct permissions you will see the following error in the logs:

  "mssql: The user does not have permission to perform this action"

Testing the plugin:

Below is an example for running the plugin from CMD. Note the additional \ escape characters which are required:

Copy

1
2
C:\Program Files\SolarWinds\Snap\bin>snap-plugin-collector-bridge-sqlserver.exe --config "{\"servers\": \"server={localhost\\sqlexpress};user id=appoptics;password=Password123;app name=AOHost\"}"

Metrics and Tags

The tables below outline the default set of metrics collected by the Microsoft SQL Server plugin plugin along with the optional metrics available. You enable or disable individual metrics from the task-bridge-sqlserver.yaml file:

On Windows:

C:\ProgramData\SolarWinds\Snap\tasks.d\task-bridge-sqlserver.yaml

On Linux:

/opt/SolarWinds/Snap/etc/tasks.d/task-bridge-sqlserver.yaml

To enable all available metrics comment out existing metrics and add the following line to the metrics list in the file above:

Copy

1
2
3
4
5
workflow:
  collect:
    metrics:
      /sqlserver/*/all: {}

Default Metrics

Default Metric Tags

hostname, sql_instance, database_name, obejct, instance, counter

The scripts are not supported under any SolarWinds support program or service. The scripts are provided AS IS without warranty of any kind. SolarWinds further disclaims all warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The risk arising out of the use or performance of the scripts and documentation stays with you. In no event shall SolarWinds or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the scripts or documentation.