Windows self-hosted MSSQL monitoring with NRDOT

Preview

We're still working on this feature, but we'd love for you to try it out!

This feature is currently provided as part of a preview pursuant to our pre-release policies.

Set up Microsoft SQL Server monitoring using the NRDOT Collector on Windows self-hosted environments including physical servers, virtual machines, and standalone Windows installations. To monitor in a Windows RDS environment, refer to Windows RDS instrumentation.

To install this through the New Relic UI instead, go to one.newrelic.com > Integrations & Agents > MSSQL (OpenTelemetry) . Follow the on-screen instructions to set up the integration. This page covers the CLI install path only.

Based on your SQL Server security configuration, you can choose from three authentication methods to connect the NRDOT Collector to your SQL Server instance:

Prerequisites

Before monitoring your Microsoft SQL Server with NRDOT, 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
- Administrative access to SQL Server (sysadmin role or equivalent)
- Network connectivity between collector and SQL Server on port 1433 or custom port
- SQL Server Management Studio (SSMS) or sqlcmd utility
- Windows domain or SQL Server authentication
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:

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 monitoring user

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

In your SQL Server Management Studio (SSMS), run the following script to create the newrelic monitoring user. Replace <YOUR_PASSWORD> with your desired password:

USE [master];
GO
CREATE LOGIN [newrelic] WITH PASSWORD = '<YOUR_PASSWORD>';
GO

GRANT VIEW SERVER STATE TO [newrelic];
GRANT VIEW ANY DEFINITION TO [newrelic];
GRANT VIEW ANY DATABASE TO [newrelic];
GO

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 = ''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

팁

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

Configure NRDOT Collector

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
```
Add your environment-specific values to the following configuration and copy it to the mssql-config.yaml file created in the previous step:
full configuration
This configuration focuses on essential database monitoring for your SQL Server environment. To enable comprehensive monitoring with all available metrics, refer to the configuration reference.

SQL Server Username

SQL Server Password

Server Hostname/IP

Server Port

New Relic OTLP Endpoint

New Relic License Key

mssql-config.yaml

1
receivers:
2
    nrsqlserver:
3
      collection_interval: 15s
4
      username: newrelic
5
      password: YOUR_PASSWORD
6
      server: sqlserver.example.com
7
      port: 1433
8

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

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

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

50
      collect_full_query_text: true
51
      allowed_comment_keys:
52
        - nr_service_guid
53

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

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

64
    batch:
65

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

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

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

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

Update Windows Service Registry

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\""

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"
```
팁
To correlate your application performance with database operations, you can set up database service identification. For more information, refer to set up database service identification.

Restart NRDOT Collector

After updating your configuration, restart the NRDOT Collector service:

bash

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

팁

Always restart the NRDOT Collector service after making configuration changes to ensure the new settings take effect.

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:

Go to https://one.newrelic.com > All Capabilities > Databases.
From the Entity type dropdown, select MSSQL instance, then click Apply.
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 Windows 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
- Administrative access to SQL Server (sysadmin role or equivalent)
- Network connectivity between collector and SQL Server on port 1433 or custom port
- SQL Server Management Studio (SSMS) or sqlcmd utility
Windows domain environment with Active Directory
SQL Server configured to accept Windows Authentication
Domain user account with appropriate SQL Server permissions
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:

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 monitoring user

Ensure your SQL Server is configured for Windows Authentication and verify connectivity.

Retrieve your Windows username:
```
SELECT SYSTEM_USER;
```

Run the following command to verify the necessary permissions are granted to your Windows user. Replace all occurrences of <ACCOUNT_USERNAME> with the username retrieved from step 1:

DECLARE @TargetUser SYSNAME = '<ACCOUNT_USERNAME>'; -- <-- Set your Windows user here

-- 1. Create the checklist of required permissions from your script
DECLARE @Checklist TABLE (Scope NVARCHAR(50), [Database] SYSNAME, [Permission] NVARCHAR(128));

INSERT INTO @Checklist VALUES
('Server', 'ALL', 'VIEW SERVER STATE'),
('Server', 'ALL', 'VIEW ANY DEFINITION'),
('Server', 'ALL', 'VIEW ANY DATABASE');

INSERT INTO @Checklist
SELECT 'Database Role', [name], 'db_datareader' FROM sys.databases WHERE [name] NOT IN ('master', 'msdb', 'model', 'rdsadmin', 'distribution') AND [state] = 0
UNION ALL
SELECT 'Database Explicit', [name], 'VIEW DATABASE STATE' FROM sys.databases WHERE [name] NOT IN ('master', 'msdb', 'model', 'rdsadmin', 'distribution') AND [state] = 0
UNION ALL
SELECT 'Database Explicit', [name], 'VIEW DEFINITION' FROM sys.databases WHERE [name] NOT IN ('master', 'msdb', 'model', 'rdsadmin', 'distribution') AND [state] = 0;

-- 2. Gather existing explicit permissions
WITH ExplicitPerms AS (
    SELECT 'Server' AS Scope, CAST('ALL' AS NVARCHAR(128)) COLLATE DATABASE_DEFAULT AS [Database], p.permission_name COLLATE DATABASE_DEFAULT AS [Permission]
    FROM sys.server_permissions p JOIN sys.server_principals l ON p.grantee_principal_id = l.principal_id WHERE l.name = @TargetUser
    UNION ALL
    SELECT 'Database Role', d.name COLLATE DATABASE_DEFAULT, r.name COLLATE DATABASE_DEFAULT
    FROM sys.databases d CROSS APPLY (SELECT dp_role.name FROM sys.database_role_members drm JOIN sys.database_principals dp_role ON drm.role_principal_id = dp_role.principal_id JOIN sys.database_principals dp_user ON drm.member_principal_id = dp_user.principal_id JOIN sys.server_principals sp ON dp_user.sid = sp.sid WHERE sp.name = @TargetUser) r WHERE d.state = 0
    UNION ALL
    SELECT 'Database Explicit', d.name COLLATE DATABASE_DEFAULT, p.permission_name COLLATE DATABASE_DEFAULT
    FROM sys.databases d CROSS APPLY (SELECT db_p.permission_name FROM sys.database_permissions db_p JOIN sys.database_principals dp_user ON db_p.grantee_principal_id = dp_user.principal_id JOIN sys.server_principals sp ON dp_user.sid = sp.sid WHERE sp.name = @TargetUser) p WHERE d.state = 0
)
-- 3. Output the exact verification matrix
SELECT
    chk.Scope,
    chk.[Database],
    chk.[Permission] AS [Required Permission],
    CASE
        WHEN exp.[Permission] IS NOT NULL THEN 'GRANTED'
        WHEN IS_SRVROLEMEMBER('sysadmin', @TargetUser) = 1 THEN 'PASSED (via SysAdmin Role)'
        ELSE 'MISSING'
    END AS [Verification Status]
FROM @Checklist chk
LEFT JOIN ExplicitPerms exp ON chk.Scope = exp.Scope AND chk.[Database] = exp.[Database] AND chk.[Permission] = exp.[Permission]
ORDER BY chk.Scope, chk.[Database], chk.[Permission];

(Optional) If you don't have the necessary permissions, from your SSMS or sqlcmd utility, run the following command to grant the required permissions to your Windows account. Replace all occurrences of <ACCOUNT_USERNAME> with your Windows username retrieved from step 1:

USE [master];
GO

-- 1. Grant Instance-level permissions to your Windows Account
GRANT VIEW SERVER STATE TO [<ACCOUNT_USERNAME>];
GRANT VIEW ANY DEFINITION TO [<ACCOUNT_USERNAME>];
GRANT VIEW ANY DATABASE TO [<ACCOUNT_USERNAME>];
GO

-- 2. Loop through all user databases and grant read/visibility permissions
DECLARE @name SYSNAME;
DECLARE @sql NVARCHAR(MAX);

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
        PRINT 'Granting permissions on database: ' + @name;

        -- Create the database user mapping if it doesn't exist, then grant roles
        SET @sql = '
            USE [' + @name + '];
            IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''<ACCOUNT_USERNAME>'')
            BEGIN
                CREATE USER [<ACCOUNT_USERNAME>] FOR LOGIN [<ACCOUNT_USERNAME>];
            END;
            GRANT VIEW DATABASE STATE TO [<ACCOUNT_USERNAME>];
            GRANT VIEW DEFINITION TO [<ACCOUNT_USERNAME>];
            ALTER ROLE db_datareader ADD MEMBER [<ACCOUNT_USERNAME>];';

        EXEC sp_executesql @sql;
        PRINT 'Success: ' + @name;
    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

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
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

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

Validate the service identity:

bash

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

팁

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

Configure NRDOT Collector

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
```
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 Windows SQL Server environment. To enable comprehensive monitoring with all available metrics, refer to the configuration reference.

SQL Server Hostname/IP

SQL Server Port

New Relic OTLP Endpoint

New Relic License Key

mssql-config.yaml

1
receivers:
2
    nrsqlserver:
3
      collection_interval: 15s
4
      datasource: "server=sqlserver.example.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]

팁

This configuration focuses on essential database monitoring for your Windows authentication environment. To enable comprehensive monitoring with all available metrics, refer to the configuration reference.

Update Windows Service Registry

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\""

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"
```
팁
To correlate your application performance with database operations, you can set up database service identification. For more information, refer to set up database service identification.

Restart NRDOT Collector

After updating your configuration, restart the NRDOT Collector service:

bash

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

팁

Always restart the NRDOT Collector service after making configuration changes to ensure the new settings take effect.

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:

Go to https://one.newrelic.com > All Capabilities > Databases.
From the Entity type dropdown, select MSSQL instance, then click Apply.
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 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
- Administrative access to SQL Server (sysadmin role or equivalent)
- Network connectivity between collector and SQL Server to port 1433 or custom port
- SQL Server Management Studio (SSMS) or sqlcmd utility
Active Directory environment with gMSA support
Windows host must be domain-joined and 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:

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 monitoring user

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 gMSA account name.

USE [master];
GO

-- Provision an engine login identity for the gMSA
CREATE LOGIN [<YOUR_DOMAIN>\<YOUR_GMSA_USERNAME>$] FROM WINDOWS;
GO

-- Grant monitoring permissions
GRANT VIEW SERVER STATE TO [<YOUR_DOMAIN>\<YOUR_GMSA_USERNAME>$];
GRANT VIEW ANY DEFINITION TO [<YOUR_DOMAIN>\<YOUR_GMSA_USERNAME>$];
GRANT VIEW ANY DATABASE TO [<YOUR_DOMAIN>\<YOUR_GMSA_USERNAME>$];
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_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

팁

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

Run as gMSA account

After installing the NRDOT Collector, configure the service to run as your gMSA account using PowerShell. Replace YOUR_DOMAIN\YOUR_gMSA_ACCOUNT$ with your domain and gMSA account name. You must include the $ at the end, as it is required for gMSA accounts.

bash

$# 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_ACCOUNT$" 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

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
```
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 Windows SQL Server environment. To enable comprehensive monitoring with all available metrics, refer to the configuration reference.

SQL Server Hostname/IP

SQL Server Port

New Relic OTLP Endpoint

New Relic License Key

mssql-config.yaml

1
receivers:
2
    nrsqlserver:
3
      collection_interval: 15s
4
      datasource: "server=sqlserver.example.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]

팁

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

Update Windows Service Registry

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\""

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:

bash

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

팁

Always restart the NRDOT Collector service after making configuration changes to ensure the new settings take effect.

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:

Go to https://one.newrelic.com > All Capabilities > Databases.
From the Entity type dropdown, select MSSQL instance, then click Apply.
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

Set up APM-database correlation

Learn how to correlate your application performance with database operations in New Relic.

Troubleshooting

Learn how to troubleshoot your MSSQL Windows monitoring setup in New Relic.

Metrics reference

Learn about the available metrics collected by the NRDOT Collector.

Windows self-hosted MSSQL monitoring with NRDOT

Preview

Prerequisites .css-21sua1{background:none;border:none;width:0;padding:0;}

Enroll to preview

Install NRDOT Collector

Create monitoring user

팁

Configure NRDOT Collector

full configuration

Update Windows Service Registry

팁

Restart NRDOT Collector

팁

Find and use your data

Prerequisites

Enroll to preview

Install NRDOT Collector

Create monitoring user

Collector on different Windows host

팁

Configure NRDOT Collector

full configuration

팁

Update Windows Service Registry

팁

Restart NRDOT Collector

팁

Find and use your data

Prerequisites

Enroll to preview

Install NRDOT Collector

Create monitoring user

팁

Run as gMSA account

Configure NRDOT Collector

full configuration

팁

Update Windows Service Registry

Restart NRDOT Collector

팁

Find and use your data

Related documentation

Set up APM-database correlation

Troubleshooting

Metrics reference

Prerequisites