Linux self-hosted MSSQL monitoring with NRDOT

Prerequisites

• New Relic account with a valid license key
• New Relic OTLP endpoint for your region
• SQL Server requirements, you'll need:
  • SQL Server 2017 or later
  • Administrative access to SQL Server (sysadmin role or equivalent)
  • Network connectivity between collector and SQL Server on port 1433 or custom port
  • sqlcmd utility installed on your Linux system
• Network connectivity to New Relic OTLP endpoint

Enroll to preview

This integration is available as part of the New Relic public preview program. Contact your Organization Manager to opt in from the Previews & Trials page.

Install NRDOT Collector

Download and install the NRDOT package for your Linux distribution. Replace <NRDOT_VERSION> with the latest release tag from the nrdot-collector-releases page.

Create monitoring user

Run this script as root user to create the newrelic monitoring user and grant the necessary permissions for collecting SQL Server metrics.

1. Create a configuration file named as nr-grant-permission.sql.

2. Paste the following SQL script to create the newrelic monitoring user and replace <YOUR_PASSWORD> with your desired password:

   USE [master];
   GO
   CREATE LOGIN [newrelic] WITH PASSWORD = '<YOUR_PASSWORD>';
   GO
   -- Instance-level permissions
   GRANT VIEW SERVER STATE TO [newrelic];
   GRANT VIEW ANY DEFINITION TO [newrelic];
   GRANT VIEW ANY DATABASE TO [newrelic];
   GO
   -- Grant read access privileges to all user databases
   DECLARE @name SYSNAME;
   DECLARE db_cursor CURSOR READ_ONLY FORWARD_ONLY FOR
   SELECT [name]
   FROM [master].[sys].[databases]
   WHERE [name] NOT IN ('master', 'msdb', 'model', 'rdsadmin', 'distribution')
   AND [state] = 0; -- Only online databases
   OPEN db_cursor;
   FETCH NEXT FROM db_cursor INTO @name;
   WHILE @@FETCH_STATUS = 0
   BEGIN
     BEGIN TRY
       EXEC('USE [' + @name + '];
         IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''newrelic'')
         BEGIN
           CREATE USER [newrelic] FOR LOGIN [newrelic];
         END;
         GRANT VIEW DATABASE STATE TO [newrelic];');
     END TRY
     BEGIN CATCH
       PRINT 'Error on ' + @name + ': ' + ERROR_MESSAGE();
     END CATCH
     FETCH NEXT FROM db_cursor INTO @name;
   END
   CLOSE db_cursor;
   DEALLOCATE db_cursor;
   GO

   Copier

3. Execute the script using sqlcmd. Replace <YOUR_SA_PASSWORD> with your SQL Server administrator password:

   bash

   Copier

   $
   sqlcmd -S <YOUR_DB_ENDPOINT> -U sa -P '<YOUR_SA_PASSWORD>' -C -i nr-grant-permission.sql

4. (Optional) Verify the user is created successfully with the correct permissions:

   bash

   Copier

   $
   sqlcmd -S <YOUR_DB_ENDPOINT> -U sa -P '<YOUR_SA_PASSWORD>' -C -Q "SELECT sp.name AS [User], p.permission_name AS [Permission_Granted] FROM sys.server_permissions p JOIN sys.server_principals sp ON p.grantee_principal_id = sp.principal_id WHERE sp.name = 'newrelic';"

   Expected Output:

   bash

   Copier

   $
   User Permission_Granted
   $
   -------------------------------------------------------------------------------------
   $
   newrelic CONNECT SQL
   $
   newrelic VIEW ANY DEFINITION
   $
   newrelic VIEW ANY DATABASE
   $
   newrelic VIEW SERVER STATE

   This output confirms that your user has been created successfully with the required server-level permissions.

   Conseil

   To securely manage sensitive information, such as database credentials store them in secret management tools.

Configure NRDOT Collector

Choose your configuration option based on your monitoring requirements:

1. Create a configuration file named as mssql-config.yaml:

   bash

   Copier

   $
   sudo nano /etc/nrdot-collector/mssql-config.yaml

2. Add the following configuration to the mssql-config.yaml file created in the previous step:

   full configuration

   This minimal configuration focuses only on database monitoring without host infrastructure metrics. To enable with full-feature monitoring, refer to the configuration reference.

1
receivers:
2
    nrsqlserver:
3
      collection_interval: 15s
4
      # Direct connection to SQL Server
5
      username: newrelic
6
      password: YOUR_PASSWORD
7
      server: sqlserver.example.com
8
      port: 1433
9

10
      # Enable comprehensive database metrics
11
      metrics:
12
        sqlserver.database.count:
13
          enabled: true
14
        sqlserver.database.io:
15
          enabled: true
16
        sqlserver.database.latency:
17
          enabled: true
18
        sqlserver.database.operations:
19
          enabled: true
20
        sqlserver.database.tempdb.space:
21
          enabled: true
22
        sqlserver.database.tempdb.version_store.size:
23
          enabled: true
24
        sqlserver.deadlock.rate:
25
          enabled: true
26
        sqlserver.os.wait.duration:
27
          enabled: true
28
        sqlserver.processes.blocked:
29
          enabled: true
30
        sqlserver.memory.grants.pending.count:
31
          enabled: true
32
        sqlserver.database.file.size:
33
          enabled: true
34
        sqlserver.memory.area:
35
          enabled: true
36

37
      # Enable top query and query sample log collection
38
      events:
39
        db.server.query_sample:
40
          enabled: true
41
        db.server.top_query:
42
          enabled: true
43

44
      # Top query collection configuration
45
      top_query_collection:
46
        lookback_time: 60s
47
        max_query_sample_count: 1000
48
        top_query_count: 250
49
        collection_interval: 60s
50

51
      collect_full_query_text: true
52
      allowed_comment_keys:
53
        - nr_service_guid
54

55
      # Query sample collection configuration
56
      query_sample_collection:
57
        max_rows_per_query: 100
58

59
processors:
60
    memory_limiter:
61
      check_interval: ${env:NR_MEM_LIMITER_CHECK_INTERVAL:-1s}
62
      limit_mib:       ${env:NR_MEM_LIMITER_LIMIT_MIB:-200}
63
      spike_limit_mib: ${env:NR_MEM_LIMITER_SPIKE_MIB:-50}
64

65
    batch:
66

67
exporters:
68
    otlp:
69
      endpoint: https://otlp.nr-data.net:4318
70
      headers:
71
        api-key: YOUR_LICENSE_KEY
72
      tls:
73
        insecure: false
74
      compression: gzip
75

76
service:
77
    telemetry:
78
      metrics:
79
        level: none
80

81
    pipelines:
82
      metrics:
83
        receivers: [nrsqlserver]
84
        processors: [memory_limiter, batch]
85
        exporters: [otlp]
86

87
      logs:
88
        receivers: [nrsqlserver]
89
        processors: [memory_limiter, batch]
90
        exporters: [otlp]

Update NRDOT Collector configuration

After configuring your YAML file with the interactive inputs above, complete the NRDOT Collector setup:

1. Edit the service configuration file:

   bash

   Copier

   $
   sudo nano /etc/nrdot-collector/nrdot-collector.conf

2. Update the configuration path to point to your new mssql-config.yaml file:

   bash

   Copier

   $
   OTELCOL_CONFIG="/etc/nrdot-collector/mssql-config.yaml"

3. (Optional) Validate the NRDOT Collector configuration:

   bash

   Copier

   $
   sudo /usr/bin/nrdot-collector validate --config=/etc/nrdot-collector/mssql-config.yaml

   Conseil

   To correlate your application performance with database operations, you can set up database service identification. For more information, refer to database service identification setup guide.

Restart NRDOT Collector

After updating your configuration, restart the NRDOT Collector service:

$
sudo systemctl restart nrdot-collector

Find and use your data

Once your data is being collected, you can access comprehensive SQL Server database monitoring through New Relic UI.

To find your SQL Server database entity in New Relic:

1. Go to one.newrelic.com > All capabilities > Databases.

2. From the Entity type dropdown, select MSSQL instance, then click Apply.

3. Select your SQL Server database from the list of entities.

   After setting up SQL Server monitoring with NRDOT, you can:

   • Create custom dashboards to visualize your database metrics
   • Set up alerts for critical database performance thresholds
   • Explore your data using New Relic query capabilities