Microsoft SQL Server

The Microsoft SQL Server integration collects transaction, connection, and other metrics from your SQL Server instances.

For more information about SQL Server, see the SQL Server documentation.

Before you begin

The Ops Agent version 2.15.0 introduces support for a new set of SQL Server metrics using the workload.googleapis.com/ prefix. You must configure the agent to collect these metrics, as described in Example configuration.

The Ops Agent has always supported a limited set of SQL Servier metrics; these metrics use the agent.googleapis.com/mssql/ prefix. No additional configuration is needed to collect these metrics on Windows VMs.

This document refers to the agent-prefixed metrics as the v1 metrics and to the workload-prefixed metrics as the v2 metrics.

The v1 metrics include transaction-rate and connection-count information.
The v2 metrics include batch, lock, page, and user connection-count information at the instance level, and transaction and transaction-log information at the database level.

For tables of these metrics, see What is monitored.

Starting with the Ops Agent version 2.15.0, you can configure the agent to collect v1 metrics, v2 metrics, or both.

Note: If you have alerts or other configurations built on the v1 metrics and you switch to the v2 metrics, then you have to migrate your configurations as well. If you stop collecting v1 metrics, then alerts and other monitoring of those metrics will stop working. The following table shows the mapping of v1 metrics to v2 metrics:

v1 metric (`agent.googleapis.com/mssql/`)	v2 metric (`workload.googleapis.com/`)
`connections_user`	`sqlserver.user.connection.count`
`transaction_rate`	`sqlserver.transaction.rate`
`write_transaction_rate`	`sqlserver.transaction.write.rate`

Prerequisites

To collect SQL Server telemetry, you must install the Ops Agent:

For metrics v1, install version 1.0.0 or higher.
For metrics v2, install version 2.15.0 or higher.

This integration supports SQL Server versions 11.x, 12.x, 13.x, 14.x, and 15.x.

Configure the Ops Agent for SQL Server

When using the Ops Agent on a Microsoft Windows VM, the agent automatically collects SQL Server v1 metrics. No additional configuration is required.

Following the guide to configure the Ops Agent, add the required elements to collect telemetry from SQL Server instances, and restart the agent.

After setting up a new SQL Server instance, you might not immediately see metrics display in Metrics Explorer. The Ops Agent begins collecting SQL Server metrics only after there is database activity to report.

Example configuration

The following command creates the configuration to collect and ingest telemetry for SQL Server and restarts the Ops Agent.

$ErrorActionPreference = 'Stop'

# This config gets merged with the built-in Ops Agent config, which already
# includes the v1 receiver in the default pipeline.
# Because the v2 receiver here uses a unique ID, it does not overwrite
# the default receiver and so both receivers will be active simultaneously
# during the test.

# Create a back up of the existing file so existing configurations are not lost.
Copy-Item -Path 'C:\Program Files\Google\Cloud Operations\Ops Agent\config\config.yaml' -Destination 'C:\Program Files\Google\Cloud Operations\Ops Agent\config\config.yaml.bak'

# Configure the Ops Agent.
Add-Content 'C:\Program Files\Google\Cloud Operations\Ops Agent\config\config.yaml' "
metrics:
  receivers:
    mssql_v2:
      type: mssql
      receiver_version: 2
  service:
    pipelines:
      mssql_v2:
        receivers:
        - mssql_v2
"

# Stop-Service may fail if the service isn't in a Running state yet.
(Get-Service google-cloud-ops-agent*).WaitForStatus('Running', '00:03:00')
Stop-Service google-cloud-ops-agent -Force
Start-Service google-cloud-ops-agent*

Configure v1 metrics collection

The Ops Agent on a Microsoft Windows VM automatically collects SQL Server v1 metrics. No additional configuration is required. For information about collecting only v2 metrics, see Configure v2 metrics collection.

After you set up a new SQL Server instance, SQL Server might not immediately report any metrics. The Ops Agent begins collecting SQL Server metrics after there is database activity to report.

Configure v2 metrics collection

To ingest v2 metrics from SQL Server, you must create a receiver for the metrics that SQL Server produces and then create a pipeline for the new receiver.

This receiver does not support the use of multiple instances in the configuration, for example, to monitor multiple endpoints. All such instances write to the same time series, and Cloud Monitoring has no way to distinguish among them. The Ops Agent doesn't support SQL Server named instances.

To configure a receiver for your mssql metrics, specify the following fields:

Field	Default	Description
`collection_interval`	`60s`	A time.Duration value, such as `30s` or `5m`.
`receiver_version`		Version of the metrics collected; use `2` to collect v2 metrics.
`type`		This value must be `mssql`.

By default, the v1 metrics are collected automatically. You can either augment the v1 metrics by additionally collecting v2 metrics, or you can override the v1 metrics to collect only v2 metrics.

To collect only v2 metrics, do the following:
- Name your receiver mssql.
- Set the type field to mssql.
- Set the receiver_version field to 2.
- Use this receiver in your default_pipeline pipeline. This configuration overrides the built-in configuration of the mssql receiver.
To collect both v1 and v2 metrics, do the following:
- Name your receiver something other than mssql, like mssql_v2.
- Set the type field to mssql.
- Set the receiver_version to 2.
- Use this receiver in your new pipeline. This configuration adds a v2 receiver rather than overriding the built-in receiver. The script in Example configuration uses this approach.

What is monitored

Starting with version 2.15.0, the Ops Agent can collect two different sets of metrics:

The v1 metrics: agent.googleapis.com/mssql/.
The v2 metrics: workload.googleapis.com/.

V1 metrics

The following table provides the list of v1 metrics that the Ops Agent collects from the SQL Server instance.

The "metric type" strings in this table must be prefixed with agent.googleapis.com/mssql/. That prefix has been omitted from the entries in the table. When querying a label, use the metric.labels. prefix; for example, metric.labels.LABEL="VALUE".

Metric type ^{Launch stage} Display name
Kind, Type, Unit Monitored resources	Description Labels
`connections/user` ^GA SQL Server open connections
`GAUGE`, `DOUBLE`, `1` aws_ec2_instance gce_instance	Currently open connections to SQL server. Sampled every 60 seconds.
`transaction_rate` ^GA SQL Server transaction rate
`GAUGE`, `DOUBLE`, `1/s` aws_ec2_instance gce_instance	SQL server total transactions per second. Sampled every 60 seconds.
`write_transaction_rate` ^GA SQL Server write transaction rate
`GAUGE`, `DOUBLE`, `1/s` aws_ec2_instance gce_instance	SQL server write transactions per second. Sampled every 60 seconds.

Table generated at 2024-04-11 19:12:46 UTC.

V2 metrics

The following table provides the list of v2 metrics that the Ops Agent collects from the SQL Server instance.

Metric type
Kind, Type Monitored resources	Labels
`workload.googleapis.com/sqlserver.batch.request.rate`
`GAUGE`, `DOUBLE` gce_instance
`workload.googleapis.com/sqlserver.batch.sql_compilation.rate`
`GAUGE`, `DOUBLE` gce_instance
`workload.googleapis.com/sqlserver.batch.sql_recompilation.rate`
`GAUGE`, `DOUBLE` gce_instance
`workload.googleapis.com/sqlserver.lock.wait.rate`
`GAUGE`, `DOUBLE` gce_instance
`workload.googleapis.com/sqlserver.lock.wait_time.avg`
`GAUGE`, `DOUBLE` gce_instance
`workload.googleapis.com/sqlserver.page.buffer_cache.hit_ratio`
`GAUGE`, `DOUBLE` gce_instance
`workload.googleapis.com/sqlserver.page.checkpoint.flush.rate`
`GAUGE`, `DOUBLE` gce_instance
`workload.googleapis.com/sqlserver.page.lazy_write.rate`
`GAUGE`, `DOUBLE` gce_instance
`workload.googleapis.com/sqlserver.page.life_expectancy`
`GAUGE`, `INT64` gce_instance
`workload.googleapis.com/sqlserver.page.operation.rate`
`GAUGE`, `DOUBLE` gce_instance	`type`
`workload.googleapis.com/sqlserver.page.split.rate`
`GAUGE`, `DOUBLE` gce_instance
`workload.googleapis.com/sqlserver.transaction.rate`
`GAUGE`, `DOUBLE` gce_instance	`database`
`workload.googleapis.com/sqlserver.transaction.write.rate`
`GAUGE`, `DOUBLE` gce_instance	`database`
`workload.googleapis.com/sqlserver.transaction_log.flush.data.rate`
`GAUGE`, `DOUBLE` gce_instance	`database`
`workload.googleapis.com/sqlserver.transaction_log.flush.rate`
`GAUGE`, `DOUBLE` gce_instance	`database`
`workload.googleapis.com/sqlserver.transaction_log.flush.wait.rate`
`GAUGE`, `DOUBLE` gce_instance	`database`
`workload.googleapis.com/sqlserver.transaction_log.growth.count`
`CUMULATIVE`, `INT64` gce_instance	`database`
`workload.googleapis.com/sqlserver.transaction_log.percent_used`
`GAUGE`, `INT64` gce_instance	`database`
`workload.googleapis.com/sqlserver.transaction_log.shrink.count`
`CUMULATIVE`, `INT64` gce_instance	`database`
`workload.googleapis.com/sqlserver.user.connection.count`
`GAUGE`, `INT64` gce_instance

Verify the configuration

This section describes how to verify that you correctly configured the SQL Server receiver. It might take one or two minutes for the Ops Agent to begin collecting telemetry.

To verify that SQL Server metrics are being sent to Cloud Monitoring, do the following:

In the navigation panel of the Google Cloud console, select Monitoring, and then select Metrics explorer:
Go to Metrics explorer
In the toolbar of the query-builder pane, select the button whose name is either MQL or PromQL.
Verify that MQL is selected in the Language toggle. The language toggle is in the same toolbar that lets you format your query.

For v1 metrics, enter the following query in the editor, and then click Run query:
```
fetch gce_instance
| metric 'agent.googleapis.com/mssql/connections/user'
| every 1m
```

For v2 metrics, enter the following query in the editor, and then click Run:

fetch gce_instance
| metric 'workload.googleapis.com/sqlserver.user.connection.count'
| every 1m

View dashboard

To view your SQL Server metrics, you must have a chart or dashboard configured. The SQL Server integration includes one or more dashboards for you. Any dashboards are automatically installed after you configure the integration and the Ops Agent has begun collecting metric data.

You can also view static previews of dashboards without installing the integration.

To view an installed dashboard, do the following:

In the navigation panel of the Google Cloud console, select Monitoring, and then select Dashboards:
Go to Dashboards
Select the Dashboard List tab, and then choose the Integrations category.
Click the name of the dashboard you want to view.

If you have configured an integration but the dashboard has not been installed, then check that the Ops Agent is running. When there is no metric data for a chart in the dashboard, installation of the dashboard fails. After the Ops Agent begins collecting metrics, the dashboard is installed for you.

To view a static preview of the dashboard, do the following:

In the navigation panel of the Google Cloud console, select Monitoring, and then select Integrations:
Go to Integrations
Click the Compute Engine deployment-platform filter.
Locate the entry for SQL Server and click View Details.
Select the Dashboards tab to see a static preview. If the dashboard is installed, then you can navigate to it by clicking View dashboard.

For more information about dashboards in Cloud Monitoring, see Dashboards and charts.

For more information about using the Integrations page, see Manage integrations.

Install alerting policies

Alerting policies instruct Cloud Monitoring to notify you when specified conditions occur. The SQL Server integration includes one or more alerting policies for you to use. You can view and install these alerting policies from the Integrations page in Monitoring.

To view the descriptions of available alerting policies and install them, do the following:

In the navigation panel of the Google Cloud console, select Monitoring, and then select Integrations:
Go to Integrations
Locate the entry for SQL Server and click View Details.
Select the Alerts tab. This tab provides descriptions of available alerting policies and provides an interface for installing them.
Install alerting policies. Alerting policies need to know where to send notifications that the alert has been triggered, so they require information from you for installation. To install alerting policies, do the following:
1. From the list of available alerting policies, select those that you want to install.
2. In the Configure notifications section, select one or more notification channels. You have the option to disable the use of notification channels, but if you do, then your alerting policies fire silently. You can check their status in Monitoring, but you receive no notifications.
  
  For more information about notification channels, see Manage notification channels.
3. Click Create Policies.

For more information about alerting policies in Cloud Monitoring, see Introduction to alerting.

For more information about using the Integrations page, see Manage integrations.

What's next

For a walkthrough on how to use Ansible to install the Ops Agent, configure a third-party application, and install a sample dashboard, see the Install the Ops Agent to troubleshoot third-party applications video.