Documentation forAppOptics

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:

  1. 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
  2. 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
  3. Restart the agent:

    On Windows command line:

    net stop swisnapd
    net start swisnapd

    On Linux command line:

    sudo service swisnapd restart
  4. 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.    

Tags

hostname, sql_instance, database_name, obejct, instance, counter

Navigation Notice: When the APM Integrated Experience is enabled, AppOptics shares a common navigation and enhanced feature set with other integrated experience products. How you navigate AppOptics and access its features may vary from these instructions.

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.