Microsoft SQL Server

Overview

This integration 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 monitoring is accomplished by bridge plugin which is included with the SolarWinds Snap Agent by default. Follow the directions below to enable it for an agent instance. The bridge plugin utilize Telegraf SQL Server plugin.

Prerequisites

This integration requires the connection credential to the Microsoft SQL Server instance to monitor, user must have the processadmin role and public access role to all databases. The sysadmin role is needed if you intend to collect data file metrics

Configuration

The agent provides an example task file to help you get started quickly, but requires you to provide the correct settings for your SQL Server installation. To enable the task:

Make a copy of the Microsoft SQL Server example task file task-bridge-sqlserver.yaml.example, renaming it to task-bridge-sqlserver.yaml

On Windows, using Explorer or PowerShell:

copy "C:\ProgramData\SolarWinds\Snap\tasks-autoload.d\task-bridge-sqlserver.yaml.example" "C:\ProgramData\SolarWinds\Snap\tasks-autoload.d\task-bridge-sqlserver.yaml"

On Linux using command line:

sudo cp -p /opt/SolarWinds/Snap/etc/tasks-autoload.d/task-bridge-sqlserver.yaml.example /opt/SolarWinds/Snap/etc/tasks-autoload.d/task-bridge-sqlserver.yaml

Edit the task file with settings specific to your Microsoft SQL Server install:

If you wish to also collect logs for this service, uncomment the last section in the example task file. For more information on collecting logs, see the logs collector docs.

---
version: 2

schedule:
    type: cron
    interval: "0 * * * * *"

plugins:
  - plugin_name: bridge

    config:
      sqlserver:
        ## Specify instances to monitor.
        ## All connection parameters are optional.
        ## By default, the host is localhost, listening on default port, TCP 1433.
        ##   for Windows, the user is the currently running AD user (SSO).
        ##   See https://github.com/denisenkom/go-mssqldb for detailed connection
        ##   parameters.
        servers:
          - "server=localhost;user id=appoptics;password=PLEASECHANGEME;app name=hostagent;log=1;"

        ## Optional parameter, setting this to 2 will use a new version
        ## of the collection queries.
        query_version: 2

        ## If you are using AzureDB, setting this to true will gather resource utilization metrics
        # azuredb: false

        ## A list of queries to include. If not specified, all the above listed queries are used.
        # include_query:
        #   - PerformanceCounters

        ## If you would like to exclude some of the metrics queries, list them here
        ## Possible choices:
        ## - PerformanceCounters
        ## - WaitStatsCategorized
        ## - DatabaseIO
        ## - DatabaseProperties
        ## - CPUHistory
        ## - DatabaseSize
        ## - DatabaseStats
        ## - MemoryClerk
        ## - VolumeSpace
        ## - Schedulers
        ## - AzureDBResourceStats
        ## - AzureDBResourceGovernance
        ## - SqlRequests
        ## - ServerProperties
        exclude_query:
          - DatabaseIO
          - DatabaseProperties
          - Schedulers
          - SqlRequests

    publish:
      - plugin_name: publisher-appoptics

## If you want to gather logs for this integration, uncomment the following section.
#  - plugin_name: log-files
#    config:
#      file_paths:
#        - /var/opt/mssql/log/errorlog
#        ## For windows:
#        - C:\\Program Files\Microsoft SQL Server\MSSQL.<version>\MSSQL\LOG\ERRORLOG

#    publish:
#      - plugin_name: loggly-http-bulk

Restart the agent:

On Windows command line:

net stop swisnapd
net start swisnapd

On Linux command line:

sudo service swisnapd restart

Enable the Microsoft SQL Server integration in AppOptics

On the Integrations Page you will see Microsoft SQL Server integration available if the previous steps were successful. It may take a couple minutes before the Microsoft SQL Server integration is identified. Select the Microsoft SQL Server integration to open the configuration menu in the UI, and enable it. If you do not see it, see Troubleshooting Linux.

Testing Integration

To check if and what metrics can be collected with given configuration, run bridge plugin in debug mode:

On Windows command line:

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

On Linux command line:

/opt/SolarWinds/Snap/bin/snap-plugin-collector-bridge --debug-mode --plugin-config "{\"sqlserver\": {\"servers\": [\"server=localhost;user id=appoptics;password=Password123;app name=AOHost\"]}}"

Troubleshooting

Required SQL Services:

SQL Browser
TCP/IP enabled in SQL Server Network Configuration

SQL User Permissions:

SQL Authentication only
The user must have the correct roles. See Prerequisites. 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"

Specifying a server and port:

To specify a port number, add port=XXXX to the Servers definition. Ex: server=localhost;port=XXXX;

Metrics and Tags

Metrics

Namespace	Description
azure_db	AzureDB resource utilization from sys.dm_db_resource_stats.
azure_db_resource_statsm	stats from sys.dm_db_resource_stats.
azure_db_resource_governance	stats from sys.dm_user_db_resource_governance.
database_io	IO stats from sys.dm_io_virtual_file_stats.
database_properties	Databases properties	state and recovery model	from sys.databases.
memory_clerk	Memory clerk breakdown from sys.dm_os_memory_clerks most clerks have been given a friendly name.
performance_counters	A select list of performance counters from sys.dm_os_performance_counters.
server_properties	Number of databases in all possible states (online offline suspect) cpu_count physical_memory sql_server_service_uptime and sql_server_version.
sql_requests	snapshot of dm_exec_requests and dm_exec_sessions that gives running requests as well as wait types and blocking sessions.
wait_stats	wait_time_in_ms number_of_waiting_tasks resource_wait_time signal_wait_time max_wait_time_in_ms wait_type and wait_category. The waits are categorized using the same categories used in Query Store.
azure_managed_instances	Stats from sys.server_resource_stats.
schedulers	scheduler stats from sys.dm_os_schedulers.

Search SolarWinds Support