Windows RDS MSSQL monitoring with NRDOT

Prerequisites

Before monitoring your Microsoft SQL Server with NRDOT using Windows Domain Authentication, make sure your environment meets these requirements:

• 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 on Amazon RDS
  • RDS master user credentials or credentials for a SQL Server login with rds_superuser role
  • RDS Security Group configured to allow connections on port 1433 or custom port
  • SQL Server Management Studio (SSMS) or sqlcmd utility
  • RDS instance associated with the AWS Active Directory service
• Windows Domain Authentication requirements:
  • Windows domain environment with Active Directory
  • Domain-joined Windows EC2 instance
  • RDS SQL Server configured to accept Windows Authentication
  • Domain user account that will be used by NRDOT Collector
• Network connectivity to New Relic OTLP endpoint
• Your RDS SQL Server endpoint URL, which can be found in your AWS RDS Console under Connectivity & security. For more information, refer to AWS RDS documentation.

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

Install the NRDOT Collector on a domain-joined EC2 Windows instance or a local Windows machine that is part of the same domain and can connect to your RDS SQL Server instance.

1. Download and install the NRDOT Collector using PowerShell:

   bash

   コピー

   $
   [Net.ServicePointManager]::SecurityProtocol = 'tls12, tls'; $NRDOT_VERSION = (Invoke-RestMethod -Uri "https://api.github.com/repos/newrelic/nrdot-collector-releases/releases/latest").tag_name; $WebClient = New-Object System.Net.WebClient; $WebClient.Headers.Add("User-Agent", "Mozilla/5.0"); $WebClient.DownloadFile("https://github.com/newrelic/nrdot-collector-releases/releases/download/$NRDOT_VERSION/nrdot-collector_${NRDOT_VERSION}_windows_x64.msi", "$env:TEMP\nrdot-collector.msi"); Start-Process msiexec.exe -ArgumentList "/i `"$env:TEMP\nrdot-collector.msi`" /qn /norestart /L*V `"$env:TEMP\nrdot_install.log`"" -Wait; Get-Service nrdot-collector -ErrorAction SilentlyContinue

Create Windows domain login

Connect to your RDS SQL Server instance using SQL Server Management Studio (SSMS) or sqlcmd with administrative privileges.

In your SQL Server Management Studio (SSMS), run the following script to create a login for your gMSA account and grant the necessary permissions. Replace all occurrences of <YOUR_DOMAIN>\<YOUR_DOMAIN_USER> with your actual domain and account name.

USE [master];
GO

CREATE LOGIN [<YOUR_DOMAIN>\<YOUR_DOMAIN_USER>] FROM WINDOWS;
GO

GRANT VIEW ANY DATABASE   TO [<YOUR_DOMAIN>\<YOUR_DOMAIN_USER>];
GRANT VIEW SERVER STATE TO [<YOUR_DOMAIN>\<YOUR_DOMAIN_USER>];
GRANT VIEW ANY DEFINITION TO [<YOUR_DOMAIN>\<YOUR_DOMAIN_USER>];
GO

-- Grant access to all databases (required for query monitoring and tempdb metrics)
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;
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 = ''<YOUR_DOMAIN>\<YOUR_DOMAIN_USER>'')
    BEGIN
      CREATE USER [<YOUR_DOMAIN>\<YOUR_DOMAIN_USER>] FOR LOGIN [<YOUR_DOMAIN>\<YOUR_DOMAIN_USER>];
    END;
    GRANT VIEW DATABASE STATE TO [<YOUR_DOMAIN>\<YOUR_DOMAIN_USER>];');
  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

Run as domain user

1. Configure the NRDOT Collector service to run as your domain user using PowerShell. Replace YOUR_DOMAIN\YOUR_USER_ACCOUNT with your domain and account name:

   bash

   コピー

   $
   # Configure service to use domain account
   $
   # The space after obj= and password= is required syntax for sc.exe
   $
   sc.exe config "nrdot-collector" obj= "YOUR_DOMAIN\YOUR_USER_ACCOUNT" password= "<YOUR_PASSWORD>"
   $
   
   $
   # Restart the service
   $
   Restart-Service nrdot-collector

2. Validate the service identity:

   bash

   コピー

   $
   Get-WmiObject Win32_Service -Filter "Name='nrdot-collector'" | Select Name, State, StartName
   $
   # Expected: StartName = YOUR_DOMAIN\YOUR_USER_ACCOUNT

Configure NRDOT Collector

1. Create a configuration file named as mssql-config.yaml in PowerShell as an Administrator::

   bash

   コピー

   $
   New-Item -Path "C:\Program Files\nrdot-collector\mssql-config.yaml" -ItemType File

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

1
receivers:
2
    nrsqlserver:
3
      collection_interval: 15s
4
      datasource: "server=mydb-instance.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com;port=1433;integrated security=true;encrypt=true;TrustServerCertificate=true;"
5

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

33
      # Enable top query and query sample log collection
34
      events:
35
        db.server.query_sample:
36
          enabled: true
37
        db.server.top_query:
38
          enabled: true
39

40
      # Top query collection configuration
41
      top_query_collection:
42
        lookback_time: 60s
43
        max_query_sample_count: 1000
44
        top_query_count: 250
45
        collection_interval: 60s
46

47
      collect_full_query_text: true
48
      allowed_comment_keys:
49
        - nr_service_guid
50

51
      # Query sample collection configuration
52
      query_sample_collection:
53
        max_rows_per_query: 100
54

55
processors:
56
    memory_limiter:
57
      check_interval: ${env:NR_MEM_LIMITER_CHECK_INTERVAL:-1s}
58
      limit_mib:       ${env:NR_MEM_LIMITER_LIMIT_MIB:-200}
59
      spike_limit_mib: ${env:NR_MEM_LIMITER_SPIKE_MIB:-50}
60

61
    batch:
62

63
exporters:
64
    otlp:
65
      endpoint: https://otlp.nr-data.net:4318
66
      headers:
67
        api-key: YOUR_LICENSE_KEY
68
      tls:
69
        insecure: false
70
      compression: gzip
71

72
service:
73
    telemetry:
74
      metrics:
75
        level: none
76

77
    pipelines:
78
      metrics:
79
        receivers: [nrsqlserver]
80
        processors: [memory_limiter, batch]
81
        exporters: [otlp]
82

83
      logs:
84
        receivers: [nrsqlserver]
85
        processors: [memory_limiter, batch]
86
        exporters: [otlp]

Update Windows Service Registry

1. Update the Windows Service Registry to use the new mssql-config.yaml file using one of the following methods:

   • For PowerShell: Run the following command as Administrator:

     bash

     コピー

     $
     Set-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\nrdot-collector" -Name "ImagePath" -Value '"C:\Program Files\nrdot-collector\nrdot-collector.exe" --config "C:\Program Files\nrdot-collector\mssql-config.yaml"'

   • For Command Prompt: If PowerShell isn't available, use Command Prompt instead and run the following command as Administrator:

     bash

     コピー

     $
     sc config "nrdot-collector" binPath= "\"C:\Program Files\nrdot-collector\nrdot-collector.exe\" --config \"C:\Program Files\nrdot-collector\mssql-config.yaml\""

2. Validate the NRDOT Collector configuration to ensure it's correctly formatted and will work properly:

   bash

   コピー

   $
   & "C:\Program Files\nrdot-collector\nrdot-collector.exe" validate --config="C:\Program Files\nrdot-collector\mssql-config.yaml"

Restart NRDOT Collector

After updating your configuration, restart the NRDOT Collector service:

$
net stop nrdot-collector
$
net start nrdot-collector

Verify the service is running under the domain account by checking Windows Services (services.msc). The Log On As column should show your domain account. For example, CORP\SQLMonitor.

Find and use your data

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

To find your SQL Server database entity in New Relic:

1. Go to https://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

Prerequisites

Before monitoring your Microsoft SQL Server with NRDOT using gMSA authentication, make sure you've:

• 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 on Amazon RDS
  • RDS master user credentials or credentials for a SQL Server login with rds_superuser role
  • RDS Security Group configured to allow connections on port 1433 or custom port
  • SQL Server Management Studio (SSMS) or sqlcmd utility
  • RDS instance associated with the AWS Active Directory service
• Your RDS SQL Server endpoint URL, which can be found in your AWS RDS Console under Connectivity & security. For more information, refer to AWS RDS documentation.
• Active Directory environment with gMSA support
• Domain-joined Windows EC2 instance authorized to use the gMSA account
• 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 Collector using PowerShell:

$
[Net.ServicePointManager]::SecurityProtocol = 'tls12, tls'; $NRDOT_VERSION = (Invoke-RestMethod -Uri "https://api.github.com/repos/newrelic/nrdot-collector-releases/releases/latest").tag_name; $WebClient = New-Object System.Net.WebClient; $WebClient.Headers.Add("User-Agent", "Mozilla/5.0"); $WebClient.DownloadFile("https://github.com/newrelic/nrdot-collector-releases/releases/download/$NRDOT_VERSION/nrdot-collector_${NRDOT_VERSION}_windows_x64.msi", "$env:TEMP\nrdot-collector.msi"); Start-Process msiexec.exe -ArgumentList "/i `"$env:TEMP\nrdot-collector.msi`" /qn /norestart /L*V `"$env:TEMP\nrdot_install.log`"" -Wait; Get-Service nrdot-collector -ErrorAction SilentlyContinue

Create monitoring user

Connect to your RDS SQL Server instance using SQL Server Management Studio (SSMS) or sqlcmd with administrative privileges.

In your SQL Server Management Studio (SSMS), run the following script to create a login for your gMSA account and grant the necessary permissions. Replace all occurrences of <YOUR_DOMAIN>\<YOUR_GMSA_USERNAME> with your actual domain and account name.

USE [master];
GO

CREATE LOGIN [<YOUR_DOMAIN>\<YOUR_GMSA_USERNAME>$] FROM WINDOWS;
GO

GRANT VIEW ANY DATABASE TO [<YOUR_DOMAIN>\<YOUR_GMSA_USERNAME>$];
GRANT VIEW SERVER STATE TO [<YOUR_DOMAIN>\<YOUR_GMSA_USERNAME>$];
GRANT VIEW ANY DEFINITION TO [<YOUR_DOMAIN>\<YOUR_GMSA_USERNAME>$];
GO
-- Grant access to all 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;
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 = ''<YOUR_DOMAIN>\<YOUR_GMSA_USERNAME>$'')
    BEGIN
      CREATE USER [<YOUR_DOMAIN>\<YOUR_GMSA_USERNAME>$] FOR LOGIN [<YOUR_DOMAIN>\<YOUR_GMSA_USERNAME>$];
    END;
    GRANT VIEW DATABASE STATE TO [<YOUR_DOMAIN>\<YOUR_GMSA_USERNAME>$];');
  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

Run as gMSA account

Configure the NRDOT Collector service to run as your gMSA account using PowerShell. Replace <YOUR_DOMAIN>\<YOUR_GMSA_USERNAME>$ with your domain and gMSA account name. You must include the $ at the end, as it is required for gMSA accounts:

$
# Stop the NRDOT Collector service
$
Stop-Service nrdot-collector
$

$
# Configure service to use gMSA account
$
# Use cmd /c to avoid PowerShell treating $ as a variable
$
cmd /c 'sc config nrdot-collector obj= "<YOUR_DOMAIN>\<YOUR_GMSA_USERNAME>$" password= ""'
$

$
# Verify the configuration
$
Get-WmiObject Win32_Service -Filter "Name='nrdot-collector'" | Select Name, StartName
$

$
# Start the NRDOT Collector service 
$
Start-Service nrdot-collector

Configure NRDOT Collector

1. Create a configuration file named as mssql-config.yaml in PowerShell as an Administrator::

   bash

   コピー

   $
   New-Item -Path "C:\Program Files\nrdot-collector\mssql-config.yaml" -ItemType File

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

1
receivers:
2
    nrsqlserver:
3
      collection_interval: 15s
4
      datasource: "server=mydb-instance.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com;port=1433;integrated security=true;encrypt=true;TrustServerCertificate=true;"
5

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

33
      # Enable top query and query sample log collection
34
      events:
35
        db.server.query_sample:
36
          enabled: true
37
        db.server.top_query:
38
          enabled: true
39

40
      # Top query collection configuration
41
      top_query_collection:
42
        lookback_time: 60s
43
        max_query_sample_count: 1000
44
        top_query_count: 250
45
        collection_interval: 60s
46

47
      collect_full_query_text: true
48
      allowed_comment_keys:
49
        - nr_service_guid
50

51
      # Query sample collection configuration
52
      query_sample_collection:
53
        max_rows_per_query: 100
54

55
processors:
56
    memory_limiter:
57
      check_interval: ${env:NR_MEM_LIMITER_CHECK_INTERVAL:-1s}
58
      limit_mib:       ${env:NR_MEM_LIMITER_LIMIT_MIB:-200}
59
      spike_limit_mib: ${env:NR_MEM_LIMITER_SPIKE_MIB:-50}
60

61
    batch:
62

63
exporters:
64
    otlp:
65
      endpoint: https://otlp.nr-data.net:4318
66
      headers:
67
        api-key: YOUR_LICENSE_KEY
68
      tls:
69
        insecure: false
70
      compression: gzip
71

72
service:
73
    telemetry:
74
      metrics:
75
        level: none
76

77
    pipelines:
78
      metrics:
79
        receivers: [nrsqlserver]
80
        processors: [memory_limiter, batch]
81
        exporters: [otlp]
82

83
      logs:
84
        receivers: [nrsqlserver]
85
        processors: [memory_limiter, batch]
86
        exporters: [otlp]

Update Windows Service Registry

1. Update the Windows Service Registry to use the new mssql-config.yaml file using one of the following methods:

   • For PowerShell: Run the following command as Administrator:

     bash

     コピー

     $
     Set-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\nrdot-collector" -Name "ImagePath" -Value '"C:\Program Files\nrdot-collector\nrdot-collector.exe" --config "C:\Program Files\nrdot-collector\mssql-config.yaml"'

   • For Command Prompt: If PowerShell isn't available, use Command Prompt instead and run the following command as Administrator:

     bash

     コピー

     $
     sc config "nrdot-collector" binPath= "\"C:\Program Files\nrdot-collector\nrdot-collector.exe\" --config \"C:\Program Files\nrdot-collector\mssql-config.yaml\""

2. Validate the NRDOT collector configuration to ensure it's correctly formatted and will work properly:

   bash

   コピー

   $
   & "C:\Program Files\nrdot-collector\nrdot-collector.exe" validate --config="C:\Program Files\nrdot-collector\mssql-config.yaml"

Restart NRDOT collector

After updating your configuration, restart the NRDOT Collector service on your domain-joined EC2 Windows instance:

$
net stop nrdot-collector
$
net start nrdot-collector

Verify that the service is running under the gMSA account by checking Windows Services (services.msc). The Log On As column should show your gMSA account, for example, CORP\SQL-gMSA$.

Find and use your data

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

To find your SQL Server database entity in New Relic:

1. Go to https://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