Linux RDS MSSQL monitoring with NRDOT

Prerequisites

• New Relic account with a valid license key
• New Relic OTLP endpoint for your region
• AWS RDS SQL Server requirements:
  • AWS RDS SQL Server 2017 or later
  • RDS master username and password
  • Security group allowing inbound connections on port 1433 or custom port
• Linux system requirements:
  • 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 RDS SQL Server metrics.

1. Create a file named as nr-grant-permission-rds.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

   コピー

3. Execute the script using sqlcmd. Replace <YOUR-RDS-ENDPOINT> with your RDS endpoint and <YOUR_MASTER_PASSWORD> with your master password:

   bash

   コピー

   $
   sqlcmd -S <YOUR-RDS-ENDPOINT> -U <YOUR_MASTER_USER> -P '<YOUR_MASTER_PASSWORD>' -C -i nr-grant-permission-rds.sql

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

   bash

   コピー

   $
   sqlcmd -S <YOUR-RDS-ENDPOINT> -U <YOUR_MASTER_USER> -P '<YOUR_MASTER_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';"

   ヒント

   • Use your RDS master username and password to execute these commands. The script automatically excludes the rdsadmin database which is reserved by AWS.
   • 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

   コピー

   $
   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 configuration focuses on essential database monitoring for your RDS environment. To enable comprehensive monitoring with all available metrics, refer to the configuration reference.

1
receivers:
2
    nrsqlserver:
3
      collection_interval: 15s
4
      # Connection details - Use your RDS endpoint
5
      username: newrelic
6
      password: YOUR_PASSWORD
7
      server: mydb-instance.xxxxxxxxxxxx.us-east-1.rds.amazonaws.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 using the following command:

   bash

   コピー

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

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

   bash

   コピー

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

3. (Optional) Validate the NRDOT Collector configuration:

   bash

   コピー

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

   ヒント

   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