Microsoft SQL Server monitoring integration

Our Microsoft SQL Server integration collects and sends inventory and metrics from your MS SQL Server environment to our platform, where you can see the health of your MS SQL Server environment. We collect both database and instance-level metrics so that you can pinpoint the source of any problems.

Read on to install the integration, and to see what data we collect.

Compatibility and requirements

Our integration requires Microsoft SQL Server 2008 R2 SP3 or higher, using mixed authentication mode.

Before installing the integration, make sure that you meet the following requirements:

• A New Relic account. Don't have one? Sign up for free! No credit card required.
• Windows distribution compatible with our infrastructure agent.
• The infrastructure agent installed.
• Requires SQL Server user or domain user with user privileges for both CONNECT and VIEW SERVER STATE, and READ access permissions.

Quick start

Instrument your MS SQL Server environment quickly and send your telemetry data with guided install. Our guided install creates a customized CLI command for your environment that downloads and installs the New Relic CLI and the infrastructure agent.

Ready to get started? Click one of these button to try it out.

Our guided install uses the infrastructure agent to set up the Microsoft SQL Server integration. Not only that, it discovers other applications and log sources running in your environment and then recommends which ones you should instrument.

The guided install works with most setups. But if it doesn't suit your needs, you can find other methods below to get started monitoring your MS SQL Server environment.

Install and activate

To install the Microsoft SQL Server integration:

1. Download the latest .MSI installer image from:

   32-bit Windows

   http://download.newrelic.com/infrastructure_agent/windows/integrations/nri-mssql/386/nri-mssql-386.msi

   Copy

   64-bit Windows

   http://download.newrelic.com/infrastructure_agent/windows/integrations/nri-mssql/nri-mssql-amd64.msi

   Copy

2. In an admin account, run the install script using an absolute path.

   32-bit Windows

   msiexec.exe /qn /i PATH\TO\nri-mssql-amd386.msi

   Copy

   64-bit Windows

   msiexec.exe /qn /i PATH\TO\nri-mssql-amd64.msi

   Copy

3. Rename C:\Program Files\New Relic\newrelic-infra\integrations.d\mssql-config.yml.sample to mssql-config.yml, and edit according to your instance.

4. Restart the infrastructure agent.

Additional notes:

• On-host integrations do not automatically update. For best results, regularly update the integration package and the infrastructure agent.

Configuration

Enabling your MS SQL Server

In the Microsoft SQL Server to be monitored, execute the following script to create a new user and grant CONNECT, VIEW SERVER STATE, and read access permissions to that user. Note that SQL Server must be using mixed mode authentication.

See the Microsoft documentation for details on creating logins and users in Microsoft SQL Server.

1. Use the following statements to create a new login and to grant CONNECT and VIEW SERVER STATE permissions to the login.

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

   Copy

2. Use the following statements to grant read 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

   Copy

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

   sqlcmd -U user_name -S host_name

   Copy

Configure the integration

An integration's YAML-format configuration is where you can place required login credentials and configure how data is collected. Which options you change depend on your setup and preference.

The configuration file has common settings applicable to all integrations like interval, timeout, inventory_source. To read all about these common settings refer to our Configuration Format document.

Instance settings

The SQL Server integration collects both Metrics(M) and Inventory(I) information. In the table, use the Applies to column for the settings available to each collection:

The values for these settings can be defined in several ways:

• Add the value directly to the configuration file. This is the most common way.
• Replace the values from environment variables using the {{}} notation. This requires infrastructure agent 1.14.0+. For more on this, see more on infrastructure agent passthrough environment variables.
• Use secrets management to protect sensible information, such as passwords, so that it's not exposed in plain text on the configuration file. For more information, see secrets management.

Labels and custom attributes

You can also decorate your metrics with labels. Labels allow you to add key/value pair attributes to your metrics so that you can query, filter, or group your metrics.

Even though our default sample configuration file includes examples of labels, they're optional. You can remove, modify, or add new ones.

labels:
   env: production
   role: load_balancer

Example configurations

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

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

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

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

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

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

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

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

Find and use data

To find your integration data go to one.newrelic.com > Infrastructure > Third-party services and select one of the Microsoft SQL Server integration links.

Microsoft SQL Server data is attached to the following event types:

• MssqlDatabaseSample
• MssqlInstanceSample
• MssqlWaitSample
• MssqlCustomQuerySample (if you running custom queries)

For more on how to find and use your data, see Understand integration data.

Metric data

The Microsoft SQL Server integration collects the following metric data attributes. Some metric name are prefixed with a category indicator and a period, such as asserts. or flush..

Database metrics

These attributes can be found by querying the MssqlDatabaseSample event.

Instance metrics

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

Wait metrics

These attributes can be found by querying the MssqlWaitSample event.

Inventory data

The Microsoft SQL Server integration captures the configuration parameters and current settings of the Microsoft SQL Server environment. It collects the results of the sp_configure stored procedure, as well as current running configuration settings from the sys.configurations table.

The data is available on the Inventory page, under the config/mssql source. For more about inventory data, see Understand integration data.

Check the source code

This integration is open source software. That means you can browse its source code and send improvements, or create your own fork and build it.