Menu
DocumentationGrafana CloudMonitor infrastructureGrafana integrationsIntegrations referenceMicrosoft SQL Server
Grafana Cloud

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.

This integration includes 5 useful alerts and 2 pre-built dashboards to help monitor and visualize Microsoft SQL Server metrics and logs.

Grafana Alloy configuration

Before you begin

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

  1. In your Grafana Cloud stack, click Connections in the left-hand menu.
  2. Find Microsoft SQL Server and click its tile to open the integration.
  3. Review the prerequisites in the Configuration Details tab and set up Grafana Agent to send Microsoft SQL Server metrics and logs to your Grafana Cloud instance.
  4. Click Install to add this integration’s pre-built dashboards and alerts to your Grafana Cloud instance, and you can start monitoring your Microsoft SQL Server setup.

Configuration snippets for Grafana Alloy

Simple mode

These snippets are configured to scrape a single Microsoft SQL Server instance running locally with default ports.

First, manually copy and append the following snippets into your alloy configuration file.

Integrations snippets

river 
prometheus.exporter.mssql "integrations_mssql" {
	connection_string = "sqlserver://user:pass@127.0.0.1:1433"
}

discovery.relabel "integrations_mssql" {
	targets = prometheus.exporter.mssql.integrations_mssql.targets

	rule {
		target_label = "instance"
		replacement  = constants.hostname
	}

	rule {
		target_label = "job"
		replacement  = "integrations/mssql"
	}
}

prometheus.scrape "integrations_mssql" {
	targets    = discovery.relabel.integrations_mssql.output
	forward_to = [prometheus.remote_write.metrics_service.receiver]
	job_name   = "integrations/mssql"
}

Logs snippets

linux

river 
local.file_match "logs_integrations_integrations_mssql_linux" {
	path_targets = [{
		__address__ = "localhost",
		__path__    = "/var/opt/mssql/log/errorlog",
	}]
}

loki.process "logs_integrations_integrations_mssql_linux" {
	forward_to = [loki.write.grafana_cloud_loki.receiver]

	stage.multiline {
		firstline     = "^\\s*\\d{4}-\\d{2}-\\d{2}\\s+\\d{2}:\\d{2}:\\d{2}\\.\\d+"
		max_lines     = 0
		max_wait_time = "3s"
	}

	stage.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>.*)$"
	}

	stage.timestamp {
		source = "timestamp"
		format = "RFC3339Nano"
	}

	stage.static_labels {
		values = {
			instance = constants.hostname,
			job      = "integrations/mssql",
			log_type = "mssql_error",
		}
	}

	stage.labels {
		values = {
			component = null,
			message   = null,
		}
	}
}

loki.source.file "logs_integrations_integrations_mssql_linux" {
	targets    = local.file_match.logs_integrations_integrations_mssql_linux.targets
	forward_to = [loki.process.logs_integrations_integrations_mssql_linux.receiver]
}

windows

river 
local.file_match "logs_integrations_integrations_mssql_windows" {
	path_targets = [{
		__address__ = "localhost",
		__path__    = "/Program Files/Microsoft SQL Server/MSSQL.*/MSSQL/LOG/ERRORLOG",
	}]
}

loki.process "logs_integrations_integrations_mssql_windows" {
	forward_to = [loki.write.grafana_cloud_loki.receiver]

	stage.multiline {
		firstline     = "^\\s*\\d{4}-\\d{2}-\\d{2}\\s+\\d{2}:\\d{2}:\\d{2}\\.\\d+"
		max_lines     = 0
		max_wait_time = "3s"
	}

	stage.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>.*)$"
	}

	stage.timestamp {
		source = "timestamp"
		format = "RFC3339Nano"
	}

	stage.static_labels {
		values = {
			instance = constants.hostname,
			job      = "integrations/mssql",
			log_type = "mssql_error",
		}
	}

	stage.labels {
		values = {
			component = null,
			message   = null,
		}
	}
}

loki.source.file "logs_integrations_integrations_mssql_windows" {
	targets    = local.file_match.logs_integrations_integrations_mssql_windows.targets
	forward_to = [loki.process.logs_integrations_integrations_mssql_windows.receiver]
}

Advanced mode

The following snippets provide examples to guide you through the configuration process.

To instruct Grafana Alloy to scrape your Microsoft SQL Server instances, manually copy and append the snippets to your alloy configuration file, then follow subsequent instructions.

Advanced integrations snippets

river 
prometheus.exporter.mssql "integrations_mssql" {
	connection_string = "sqlserver://user:pass@127.0.0.1:1433"
}

discovery.relabel "integrations_mssql" {
	targets = prometheus.exporter.mssql.integrations_mssql.targets

	rule {
		target_label = "instance"
		replacement  = constants.hostname
	}

	rule {
		target_label = "job"
		replacement  = "integrations/mssql"
	}
}

prometheus.scrape "integrations_mssql" {
	targets    = discovery.relabel.integrations_mssql.output
	forward_to = [prometheus.remote_write.metrics_service.receiver]
	job_name   = "integrations/mssql"
}

This integrations uses the prometheus.exporter.mssql component to generate metrics from a Microsoft SQL Server instance.

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.

Also, rather than using SQL Server credentials to authenticate, it is possible to use Windows credentials by modifying the connecton_string accordingly (see the Authentication section of the Grafana Agent MSSQL Integration documentation).

The ability to pull in custom metrics (outside of the defaults) is also available for this integration. To do this, use the query_config parameter, under the integrations.mssql section of your configuration. After configuring to collect custom metrics, the default dashboards will have to be manually modified to include any reference to these new metrics.

For the full array of configuration options, refer to the prometheus.exporter.mssql component reference documentation.

This exporter must be linked with a discovery.relabel component to apply the necessary relabelings.

For each Microsoft SQL Server instance to be monitored you must create a pair of these components.

Configure the following properties within each discovery.relabel component:

  • instance label: constants.hostname sets the instance label to your Grafana Alloy server hostname. If that is not suitable, change it to a value uniquely identifies this Microsoft SQL Server instance. Make sure this label value is the same for all telemetry data collected for this instance.

You can then scrape them by including each discovery.relabel under targets within the prometheus.scrape component.

Advanced logs snippets

linux

river 
local.file_match "logs_integrations_integrations_mssql_linux" {
	path_targets = [{
		__address__ = "localhost",
		__path__    = "/var/opt/mssql/log/errorlog",
	}]
}

loki.process "logs_integrations_integrations_mssql_linux" {
	forward_to = [loki.write.grafana_cloud_loki.receiver]

	stage.multiline {
		firstline     = "^\\s*\\d{4}-\\d{2}-\\d{2}\\s+\\d{2}:\\d{2}:\\d{2}\\.\\d+"
		max_lines     = 0
		max_wait_time = "3s"
	}

	stage.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>.*)$"
	}

	stage.timestamp {
		source = "timestamp"
		format = "RFC3339Nano"
	}

	stage.static_labels {
		values = {
			instance = constants.hostname,
			job      = "integrations/mssql",
			log_type = "mssql_error",
		}
	}

	stage.labels {
		values = {
			component = null,
			message   = null,
		}
	}
}

loki.source.file "logs_integrations_integrations_mssql_linux" {
	targets    = local.file_match.logs_integrations_integrations_mssql_linux.targets
	forward_to = [loki.process.logs_integrations_integrations_mssql_linux.receiver]
}

To monitor your Microsoft SQL Server instance logs, you will use a combination of the following components:

  • local.file_match defines where to find the log file to be scraped. Change the following properties according to your environment:

    • __address__: The Microsoft SQL Server instance address
    • __path__: The path to the log file.
    • instance label: constants.hostname sets the instance label to your Grafana Alloy server hostname. If that is not suitable, change it to a value uniquely identifies this Microsoft SQL Server instance. Make sure this label value is the same for all telemetry data collected for this instance.

  • loki.process defines how to process logs before sending it to Loki.

  • loki.source.file sends logs to Loki.

windows

river 
local.file_match "logs_integrations_integrations_mssql_windows" {
	path_targets = [{
		__address__ = "localhost",
		__path__    = "/Program Files/Microsoft SQL Server/MSSQL.*/MSSQL/LOG/ERRORLOG",
	}]
}

loki.process "logs_integrations_integrations_mssql_windows" {
	forward_to = [loki.write.grafana_cloud_loki.receiver]

	stage.multiline {
		firstline     = "^\\s*\\d{4}-\\d{2}-\\d{2}\\s+\\d{2}:\\d{2}:\\d{2}\\.\\d+"
		max_lines     = 0
		max_wait_time = "3s"
	}

	stage.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>.*)$"
	}

	stage.timestamp {
		source = "timestamp"
		format = "RFC3339Nano"
	}

	stage.static_labels {
		values = {
			instance = constants.hostname,
			job      = "integrations/mssql",
			log_type = "mssql_error",
		}
	}

	stage.labels {
		values = {
			component = null,
			message   = null,
		}
	}
}

loki.source.file "logs_integrations_integrations_mssql_windows" {
	targets    = local.file_match.logs_integrations_integrations_mssql_windows.targets
	forward_to = [loki.process.logs_integrations_integrations_mssql_windows.receiver]
}

To monitor your Microsoft SQL Server instance logs, you will use a combination of the following components:

  • local.file_match defines where to find the log file to be scraped. Change the following properties according to your environment:

    • __address__: The Microsoft SQL Server instance address
    • __path__: The path to the log file.
    • instance label: constants.hostname sets the instance label to your Grafana Alloy server hostname. If that is not suitable, change it to a value uniquely identifies this Microsoft SQL Server instance. Make sure this label value is the same for all telemetry data collected for this instance.

  • loki.process defines how to process logs before sending it to Loki.

  • loki.source.file sends logs to Loki.

Grafana Agent configuration

Before you begin

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

  1. In your Grafana Cloud stack, click Connections in the left-hand menu.
  2. Find Microsoft SQL Server and click its tile to open the integration.
  3. Review the prerequisites in the Configuration Details tab and set up Grafana Agent to send Microsoft SQL Server metrics and logs to your Grafana Cloud instance.
  4. Click Install to add this integration’s pre-built dashboards and alerts to your Grafana Cloud instance, and you can start monitoring your Microsoft SQL Server setup.

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.

Also, rather than using SQL Server credentials to authenticate, it is possible to use Windows credentials by modifying the connecton_string accordingly (see the Authentication section of the Grafana Agent MSSQL Integration documentation).

The ability to pull in custom metrics (outside of the defaults) is also available for this integration. To do this, use the query_config parameter, under the integrations.mssql section of your configuration. After configuring to collect custom metrics, the default dashboards will have to be manually modified to include any reference to these new metrics.

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).

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

sh 
sudo usermod -a -G mssql grafana-agent

Configuration snippets for Grafana Agent

Below integrations, insert the following lines and change the URLs according to your environment:

yaml 
  mssql:
    enabled: true
    scrape_interval: 60s
    scrape_integration: true
    instance: '<your-instance-name>'
    connection_string: 'sqlserver://user:pass@127.0.0.1:1433' # replace with your connection string

Below logs.configs.scrape_configs, insert the following lines according to your environment.

yaml 
    - job_name: integrations/mssql-linux
      static_configs:
        - targets:
          - localhost
          labels:
            __path__: /var/opt/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:
    - 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:

Full example configuration for Grafana Agent

Refer to the following Grafana Agent configuration for a complete example that contains all the snippets used for the Microsoft SQL Server integration. This example also includes metrics that are sent to monitor your Grafana Agent instance.

yaml 
integrations:
  prometheus_remote_write:
  - basic_auth:
      password: <your_prom_pass>
      username: <your_prom_user>
    url: <your_prom_url>
  agent:
    enabled: true
    relabel_configs:
    - action: replace
      source_labels:
      - agent_hostname
      target_label: instance
    - action: replace
      target_label: job
      replacement: "integrations/agent-check"
    metric_relabel_configs:
    - action: keep
      regex: (prometheus_target_sync_length_seconds_sum|prometheus_target_scrapes_.*|prometheus_target_interval.*|prometheus_sd_discovered_targets|agent_build.*|agent_wal_samples_appended_total|process_start_time_seconds)
      source_labels:
      - __name__
  # Add here any snippet that belongs to the `integrations` section.
  # For a correct indentation, paste snippets copied from Grafana Cloud at the beginning of the line.
  mssql:
    enabled: true
    scrape_interval: 60s
    scrape_integration: true
    instance: '<your-instance-name>'
    connection_string: 'sqlserver://user:pass@127.0.0.1:1433' # replace with your connection string
logs:
  configs:
  - clients:
    - basic_auth:
        password: <your_loki_pass>
        username: <your_loki_user>
      url: <your_loki_url>
    name: integrations
    positions:
      filename: /tmp/positions.yaml
    scrape_configs:
      # Add here any snippet that belongs to the `logs.configs.scrape_configs` section.
      # For a correct indentation, paste snippets copied from Grafana Cloud at the beginning of the line.
    - job_name: integrations/mssql-linux
      static_configs:
        - targets:
          - localhost
          labels:
            __path__: /var/opt/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:
    - 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:
metrics:
  configs:
  - name: integrations
    remote_write:
    - basic_auth:
        password: <your_prom_pass>
        username: <your_prom_user>
      url: <your_prom_url>
    scrape_configs:
      # Add here any snippet that belongs to the `metrics.configs.scrape_configs` section.
      # For a correct indentation, paste snippets copied from Grafana Cloud at the beginning of the line.
  global:
    scrape_interval: 60s
  wal_directory: /tmp/grafana-agent-wal

Dashboards

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

  • MSSQL overview
  • MSSQL pages

Microsoft SQL Server overview dashboard (1/3).

Microsoft SQL Server overview dashboard (1/3).

Microsoft SQL Server overview dashboard (2/3).

Microsoft SQL Server overview dashboard (2/3).

Microsoft SQL Server overview dashboard (3/3).

Microsoft SQL Server overview dashboard (3/3).

Alerts

The Microsoft SQL Server integration includes the following useful alerts:

AlertDescription
MSSQLHighNumberOfDeadlocksWarning: There are deadlocks ocurring in the database.
MSSQLModerateReadStallTimeWarning: There is a moderate amount of IO stall for database reads.
MSSQLHighReadStallTimeCritical: There is a high amount of IO stall for database reads.
MSSQLModerateWriteStallTimeWarning: There is a moderate amount of IO stall for database writes.
MSSQLHighWriteStallTimeCritical: There is a high amount of IO stall for database writes.

Metrics

The most important metrics provided by the Microsoft SQL Server integration, which are used on the pre-built dashboards and Prometheus alerts, are as follows:

  • mssql_available_commit_memory_bytes
  • 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
  • mssql_server_target_memory_bytes
  • mssql_server_total_memory_bytes
  • up

Changelog

md 
# 1.0.3 - April 2024

* Added cluster selector to dashboards for kubernetes support
* Added default cluster label to agent config
* Bump version to 1.0.3

# 1.0.2 - March 2024

* Updates MSSQL integration with documentation for Windows authentication options

# 1.0.1 - December 2023

* Updates MSSQL integration with documentation for new query_config parameter
* Fixes prometheus metrics list to show new metrics added in 1.0.0

# 1.0.0 - December 2023

* Updates MSSQL integration to use newest MSSQL mixin

# 0.0.4 - September 2023

* Update Grafana Agent configuration snippets to include filtered metrics used in gauge panels

# 0.0.3 - September 2023

* New Filter Metrics option for configuring the Grafana Agent, which saves on metrics cost by dropping any metric not used by this integration. Beware that anything custom built using metrics that are not on the snippet will stop working.
* New hostname relabel option, which applies the instance name you write on the text box to the Grafana Agent configuration snippets, making it easier and less error prone to configure this mandatory label.

# 0.0.2 - August 2023

* Add regex filter for logs datasource

# 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.