Microsoft SQL Server monitoring integration

1. Create a new login and grant CONNECT, VIEW SERVER and VIEW ANY DEFINITION permissions:

   USE master;
     CREATE LOGIN newrelic WITH PASSWORD = MY_TEMPORAL_PASSWORD; --insert new password here
     GRANT CONNECT SQL TO newrelic;
     GRANT VIEW SERVER STATE TO newrelic;
     GRANT VIEW ANY DEFINITION TO newrelic;

   コピー

2. Use the following statements to grant public DB access privileges to the user:

   DECLARE @name SYSNAME
       DECLARE db_cursor CURSOR
       READ_ONLY FORWARD_ONLY
       FOR
       SELECT NAME
       FROM master.sys.databases
       WHERE NAME NOT IN ('master','msdb','tempdb','model','rdsadmin','distribution')
       OPEN db_cursor
       FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0
       BEGIN
           EXECUTE('USE "' + @name + '"; CREATE USER newrelic FOR LOGIN newrelic;' );
           FETCH next FROM db_cursor INTO @name
       END
       CLOSE db_cursor
       DEALLOCATE db_cursor

   コピー

3. Run the following command to verify that the user was successfully created:

   sqlcmd -U USERNAME -S HOST_NAME

   コピー

1. Create a new login and grant CONNECT, VIEW and VIEW ANY DEFINITION permissions:

   USE master;
     CREATE LOGIN [DomainName\DomainUser] FROM windows;
     GRANT CONNECT SQL TO [Domain\User];
     GRANT VIEW SERVER STATE TO [Domain\User];
     GRANT VIEW ANY DEFINITION TO [Domain\User];

   コピー

2. Use the following statements to grant public DB access privileges to the user:

   DECLARE @name SYSNAME
       DECLARE db_cursor CURSOR
       READ_ONLY FORWARD_ONLY
       FOR
       SELECT NAME
       FROM master.sys.databases
       WHERE NAME NOT IN ('master','msdb','tempdb','model','rdsadmin','distribution')
       OPEN db_cursor
       FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0
       BEGIN
           EXECUTE('USE "' + @name + '"; CREATE USER [Domain\User] FOR LOGIN [Domain\User];' );
           FETCH next FROM db_cursor INTO @name
       END
       CLOSE db_cursor
       DEALLOCATE db_cursor

   コピー

This is the basic configuration used to collect metrics and inventory from your localhost. It uses default connection on port 1433:

integrations:
  - name: nri-mssql
    env:
      HOSTNAME: localhost
      PORT: 1433
      USERNAME: mssql_user
      PASSWORD: mssql_password
    interval: 15s
    labels:
      environment: production
    inventory_source: config/mssql

This is the basic configuration used to collect metrics and inventory from your localhost. In this case we use a domain user for authentication:

integrations:
  - name: nri-mssql
    env:
      HOSTNAME: localhost
      PORT: 1433
      USERNAME: domain_name\domain_user
      PASSWORD: domain_user_password
    interval: 15s
    labels:
      environment: production
    inventory_source: config/mssql

This configuration collects metrics every 15 seconds and inventory every 60 seconds:

integrations:
  - name: nri-mssql
    env:
      METRICS: true
      HOSTNAME: localhost
      PORT: 1433
      USERNAME: mssql_user
      PASSWORD: mssql_password
    interval: 15s
    labels:
      environment: production

  - name: nri-mssql
    env:
      INVENTORY: true
      HOSTNAME: localhost
      PORT: 1433
      USERNAME: mssql_user
      PASSWORD: mssql_password
    interval: 60s
    labels:
      environment: production
    inventory_source: config/mssql

This configuration monitors the mssql_instance1 and mssql_instance2 instances on the same SQL Server. When connecting directly to an instance you'll need to remove the PORT setting. The SQL Browser service also needs to be running on the server:

integrations:
  - name: nri-mssql
    env:
      HOSTNAME: localhost
      USERNAME: mssql_user
      PASSWORD: mssql_password
      INSTANCE: mssql_instance1
    interval: 15s
    labels:
      environment: production
    inventory_source: config/mssql

  - name: nri-mssql
    env:
      HOSTNAME: localhost
      USERNAME: mssql_user
      PASSWORD: mssql_password
      INSTANCE: mssql_instance2
    interval: 15s
    labels:
      environment: production
    inventory_source: config/mssql

Use to connect to MS SQL using SSL without validation of the certificate:

integrations:
  - name: nri-mssql
    env:
      METRICS: true
      HOSTNAME: localhost
      PORT: 1433
      USERNAME: mssql_user
      PASSWORD: mssql_password
      ENABLE_SSL: true
      TRUST_SERVER_CERTIFICATE: true
    interval: 15s
    labels:
      environment: production

If the integration causes some overhead on your SQL server, disabling the Buffer Pool and Database Partition Reserve metrics can help to optimize the integration's performance. In this example, we also increased the sampling interval to 30 seconds to reduce the sampling frequency:

integrations:
  - name: nri-mssql
    env:
      HOSTNAME: localhost
      PORT: 1433
      USERNAME: mssql_user
      PASSWORD: mssql_password
      ENABLE_BUFFER_METRICS: false
      ENABLE_DATABASE_RESERVE_METRICS: false
    interval: 30s
    labels:
      environment: production
    inventory_source: config/mssql

Keep in mind the following:

• If you enable ENABLE_BUFFER_METRICS, a query starts running involving the sys.sysdatabases and sys.dm_os_buffer_descriptors internal tables to obtain the buffer's pool size for each database. This query could cause overhead on some SQL Servers. If you disable ENABLE_BUFFER_METRICS, the metric bufferpool.sizePerDatabaseInBytes won't be reported in MssqlDatabaseSample and buferpool.sizeInBytes won't be reported in MssqlInstanceSample.
• If you enable ENABLE_DATABASE_RESERVE_METRICS, the reserved size is queried for each database and may cause some load on your server, depending on its size and usage. When it's disabled, both pageFileTotal and pageFileAvailable metrics stop being reported in MssqlDatabaseSample.

You can use custom a custom query to collect additional metrics. Custom metrics will be added to the MssqlCustomQuerySample event sample.

integrations:
  - name: nri-mssql
    env:
      METRICS: true
      HOSTNAME: mssql_host1
      PORT: 1433
      USERNAME: mssql1_user
      PASSWORD: mssql1_password
      CUSTOM_METRICS_QUERY: >-
        SELECT
          'instance_buffer_pool_size' AS metric_name,
          Count_big(*) * (8*1024) AS metric_value,
          'gauge' as metric_type,
          database_id
        FROM sys.dm_os_buffer_descriptors WITH (nolock)
        GROUP BY database_id
    interval: 15s
    labels:
      environment: production

If you need multiple custom SQL queries, add them to mssql-custom-query.yml, and reference that file on your configuration. For more examples of custom queries, check our sample file on github.

integrations:
  - name: nri-mssql
    env:
      METRICS: true
      HOSTNAME: mssql_host1
      PORT: 1433
      USERNAME: mssql1_user
      PASSWORD: mssql1_password
      CUSTOM_METRICS_CONFIG: 'C:\path\to\mssql-custom-query.yml'
    interval: 15s
    labels:
      environment: production

• Here's an example mssql-custom-query.yml.

queries:
    # Example for metric_name / metric_type specified in this config
  - query: SELECT count(*) AS 'metric_value' FROM sys.databases
    metric_name: dbCount
    metric_type: gauge

    # Example for metric_name from query, metric_type auto-detected, additional attribute 'category_type'
  - query: SELECT CONCAT('category_', category_id) AS metric_name, name AS metric_value, category_type FROM syscategories
    database: msdb

    # Example for stored procedure 'exec dbo.sp_server_info @attribute_id = 2'
  - query: dbo.sp_server_info @attribute_id = 2

These attributes can be found by querying the MssqlDatabaseSample event.

The Microsoft SQL Server integration collects the following instance metrics. These attributes can be found by querying the MssqlInstanceSample event.

These attributes can be found by querying the MssqlWaitSample event.