Install & configure NRDOT for MSSQL monitoring with Linux RDS

Prerequisites

• A New Relic account with a valid license key.
• Network connectivity to the 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 a custom port.
• Linux system requirements:
  • The sqlcmd utility installed on your Linux system.

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 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 with any login that has permission to create logins and grant server-level permissions (for example, the sa login, or another account with the sysadmin role). 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 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 query sample, top query, query plan, and top procedure log collection
38
      events:
39
        db.server.query_sample:
40
          enabled: true
41
        db.server.top_query:
42
          enabled: true
43
        db.server.query_plan:
44
          enabled: true
45
        db.server.top_procedure:
46
          enabled: true
47

48
      # Top query collection configuration
49
      top_query_collection:
50
        lookback_time: 60s
51
        max_query_sample_count: 1000
52
        top_query_count: 250
53
        collection_interval: 60s
54
        collect_full_query_text: true
55
        allowed_comment_keys:
56
          - nr_service_guid
57

58
      # Query sample collection configuration
59
      query_sample_collection:
60
        max_rows_per_query: 100
61
        collect_full_query_text: true
62
        allowed_comment_keys:
63
          - nr_service_guid
64

65
      # Top stored procedure collection configuration
66
      top_procedure_collection:
67
        max_procedure_sample_count: 1000
68
        top_procedure_count: 250
69
        collection_interval: 60s
70

71
      resource_attributes:
72
        db.system.version:
73
          enabled: true
74
        sqlserver.db.edition:
75
          enabled: true
76

77
processors:
78
    memory_limiter:
79
      check_interval: ${env:NR_MEM_LIMITER_CHECK_INTERVAL:-1s}
80
      limit_mib:       ${env:NR_MEM_LIMITER_LIMIT_MIB:-200}
81
      spike_limit_mib: ${env:NR_MEM_LIMITER_SPIKE_MIB:-50}
82

83
exporters:
84
    otlp:
85
      endpoint: https://otlp.nr-data.net:4318
86
      headers:
87
        api-key: YOUR_LICENSE_KEY
88
      tls:
89
        insecure: false
90
      compression: gzip
91

92
      sending_queue:
93
        enabled: true
94
        sizer: bytes
95
        queue_size: 100_000_000
96
        num_consumers: 10
97
        batch:
98
          sizer: bytes
99
          max_size: 1_000_000
100
          min_size: 0
101
          flush_timeout: 5s
102

103
service:
104
    telemetry:
105
      metrics:
106
        level: none
107

108
    pipelines:
109
      metrics:
110
        receivers: [nrsqlserver]
111
        processors: [memory_limiter]
112
        exporters: [otlp]
113

114
      logs:
115
        receivers: [nrsqlserver]
116
        processors: [memory_limiter]
117
        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 the 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