Microsoft SQL Server integration for Grafana Cloud

Microsoft SQL Server is a relational database that can be used to store and retrieve data. The Microsoft SQL Server integration uses the Grafana Agent to collect metrics for monitoring a MSSQL instance, including aspects such as active connections, read/write latency, buffer cache hit rate, and page faults. Accompanying dashboards are provided to visualize these metrics, as well as recommended alerting policies.

This integration supports SQL Server 2017, SQL Server 2019, and SQL Server 2022.

Pre-install configuration for the Microsoft SQL Server integration

In order to scrape Microsoft SQL Server metrics, you must have a user configured with the following grants:

GRANT VIEW ANY DEFINITION TO <MONITOR_USER>;
GRANT VIEW SERVER STATE TO <MONITOR_USER>;

Install Microsoft SQL Server integration for Grafana Cloud

In your Grafana instance, click Integrations and Connections (lightning bolt icon)
Navigate to the Microsoft SQL Server tile and review the prerequisites. Then click Install integration.
Once the integration is installed, follow the steps on the Configuration Details page to setup Grafana Agent and start sending Microsoft SQL Server metrics to your Grafana Cloud instance.

Post-install configuration for the Microsoft SQL Server integration

In the agent configuration file, the agent must be provided a connection string with the username, password, and a host:port pair. To configure this, modify the connection_string in the configuration, under the integrations.mssql section.

This integration supports both metrics and logs from an MSSQL instance. In order to see these signals correlated on the same dashboard, the job and instance labels must match for the metrics scrape config and the logs scrape config in the Agent configuration file (replace <your-instance-name> with an identifier for your instance):

For the logs section, you may remove the job that does not correspond to your OS (e.g. remove the integrations/mssql-linux job on Windows systems).

integrations:
  mssql:
    enabled: true
    scrape_interval: 60s
    scrape_integration: true
    instance: '<your-instance-name>'
    connection_string: 'sqlserver://<USER>:<PASS>@<HOST>:<PORT>'

logs:
  configs:
    scrape_configs:
      - job_name: integrations/mssql-linux
        static_configs:
          - targets:
              - localhost
            labels:
              # Default log path on Linux
              __path__: /var/opt/mssql/log/errorlog
          - targets:
              - localhost
            labels:
              # Default log path on Windows
              __path__: '/Program Files/Microsoft SQL Server/MSSQL.*/MSSQL/LOG/ERRORLOG'
        pipeline_stages:
          - multiline:
              firstline: '^\s*\d{4}-\d{2}-\d{2}\s+\d{2}:\d{2}:\d{2}\.\d+'
          - regex:
              expression: '^(?s)(?P<timestamp>\s*\d{4}-\d{2}-\d{2}\s+\d{2}:\d{2}:\d{2}\.\d+)\s+(?P<component>\w+)\s+(?P<message>.*)$'
          - timestamp:
              format: RFC3339Nano
              source: timestamp
          - static_labels:
              instance: '<your-instance-name>'
              job: integrations/mssql
              log_type: mssql_error
          - labels:
              message:
      - job_name: integrations/mssql-windows
        static_configs:
          - targets:
              - localhost
            labels:
              __path__: '/Program Files/Microsoft SQL Server/MSSQL.*/MSSQL/LOG/ERRORLOG'
        pipeline_stages:
          - multiline:
              firstline: '^\s*\d{4}-\d{2}-\d{2}\s+\d{2}:\d{2}:\d{2}\.\d+'
          - regex:
              expression: '^(?s)(?P<timestamp>\s*\d{4}-\d{2}-\d{2}\s+\d{2}:\d{2}:\d{2}\.\d+)\s+(?P<component>\w+)\s+(?P<message>.*)$'
          - timestamp:
              format: RFC3339Nano
              source: timestamp
          - static_labels:
              instance: '<your-instance-name>'
              job: integrations/mssql
              log_type: mssql_error
          - labels:
              message:
              component:

On Linux, you will also need to add the grafana-agent user to the mssql group to get logs:

sudo usermod -a -G mssql grafana-agent

Dashboards

The Microsoft SQL Server integration installs the following dashboards in your Grafana Cloud instance to help monitor your metrics.

MSSQL overview
MSSQL pages

Microsoft SQL Server overview dashboard

Microsoft SQL Server pages dashboard.

Alerts

This integration includes the following useful alerts:

Group: MSSQLAlerts

Alert	Description
MSSQLHighNumberOfDeadlocks	Warning: There are deadlocks ocurring in the database.
MSSQLModerateReadStallTime	Warning: There is a moderate amount of IO stall for database reads.
MSSQLHighReadStallTime	Critical: There is a high amount of IO stall for database reads.
MSSQLModerateWriteStallTime	Warning: There is a moderate amount of IO stall for database writes.
MSSQLHighWriteStallTime	Critical: There is a high amount of IO stall for database writes.

Metrics

The following metrics are automatically written to your Grafana Cloud instance by connecting your Raspberry Pi instance through this integration:

mssql_batch_requests_total
mssql_buffer_cache_hit_ratio
mssql_build_info
mssql_checkpoint_pages_sec
mssql_connections
mssql_deadlocks_total
mssql_io_stall_seconds_total
mssql_kill_connection_errors_total
mssql_log_growths_total
mssql_os_memory
mssql_os_page_file
mssql_page_fault_count_total

Changelog

# 0.0.1 - February 2023

- Initial release

Cost

By connecting your Microsoft SQL Server instance to Grafana Cloud you might incur charges. To view information on the number of active series that your Grafana Cloud account uses for metrics included in each Cloud tier, see Active series and dpm usage and Cloud tier pricing.