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 is compatible with Microsoft SQL Server 2008 R2 SP3 or higher.

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

• Infrastructure Pro subscription or trial.
• New Relic Infrastructure installed on host.
• Windows distribution compatible with New Relic Infrastructure.
• Microsoft SQL Server user with user privileges for both CONNECT and VIEW SERVER STATE, and READ access permissions.

Microsoft SQL users and privileges

In the Microsoft SQL Server that is 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.

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;
       

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
       

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

       sqlcmd -U user_name -S host_name

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
       

   64-bit Windows

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

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
       

   64-bit Windows

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

3. Change 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.

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.

For an example configuration, see the example config file.

Commands

The mssql-config.yml file accepts the following commands:

• all_data: collects both inventory and metric data from the Microsoft SQL Server environment.

Arguments

The all_data command accepts the following arguments:

• username: username used to authenticate the MS SQL Server. To use Windows Authentication, specify this argument in the DOMAIN\username form. This field is required.

• password: password used to authenticate the MS SQL Server. This field is required.

• hostname: hostname or IP of the MS SQL Server installation. Default: 127.0.0.1.

• port: port number on which the MS SQL Server is listening. This is only required when instance is not specified.

• instance: instance the Microsoft SQL Server is connected to. instance can be used in place of port by enabling SQL Browser; if enabled, do not include port in the argument.

• enable_ssl: indicates whether SSL is used to connect to the MS SQL Server. Default: false.

• trust_server_certificate: if set to true, server certificate is not verified for SSL. If set to false, certificate will be verified against supplied certificate.

• certificate_location: certificate file to verify SSL encryption against.

• timeout: timeout for queries, in seconds. Default: 30.

• enable_buffer_metrics: enables the collection of buffer pool metrics. These can be resource intensive for large systems. Default: true.

• enable_database_reserve_metrics: enables the collection of database partition reserve space. These can be resource intensive for large systems. Default: true.

• custom_metrics_query: an SQL query with the required columns metric_name, metric_type, and metric_value. The metric_type can be gauge, rate, delta, or attribute. Additional columns collected with the query are added to the metric set as attributes.

Example configurations

Example mssql-config.yml file configuration:

Configuration for one query

integration_name: com.newrelic.mssql

instances:
  - name: mssql-server
    command: all_data
    arguments:
      hostname: mssql.localnet
      username: mssql_user
      password: mssql_password
      port: 1433
      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
    labels:
      env: production
      role: mssql

Configuration for multiple queries

If you need multiple SQL queries, add them to mssql-custom-query.yml, and then reference that file.

integration_name: com.newrelic.mssql

instances:
  - name: mssql-server
    command: all_data
    arguments:
      hostname: mssql.localnet
      username: mssql_user
      password: mssql_password
      port: 1433
      custom_metrics_config: full_path_to_file
    labels:
      env: production
      role: mssql              

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        

For more about the general structure of on-host integration configuration, see Configuration.

Find and use data

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

In New Relic Insights, Microsoft SQL Server data is attached to the following Insights event types:

• MssqlDatabaseSample
• MssqlInstanceSample
• MssqlWaitSample

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 types in Insights.

Instance metrics

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

Wait metrics

These attributes can be found by querying the MssqlWaitSample event types in Insights.

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 Infrastructure Inventory page, under the config/mssql source. For more about inventory data, see Understand integration data.

For more help