Install & configure NRDOT for MSSQL on Windows self-hosted: gMSA auth

Prerequisites

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

• A New Relic account with a valid license key.
• Network connectivity to the New Relic OTLP endpoint for your region.
• SQL Server requirements:
  • SQL Server 2017 or later.
  • Administrative access to SQL Server (sysadmin role or equivalent).
  • Network connectivity between the collector and SQL Server on port 1433 or a custom port.
  • SQL Server Management Studio (SSMS) or the sqlcmd utility.
• An Active Directory environment with gMSA support.
• A Windows host that's domain-joined and authorized to use the gMSA account.

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

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

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.

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

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

   bash

   Copiar

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

   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.

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 query sample, top query, query plan, and top procedure log collection
34
      events:
35
        db.server.query_sample:
36
          enabled: true
37
        db.server.top_query:
38
          enabled: true
39
        db.server.query_plan:
40
          enabled: true
41
        db.server.top_procedure:
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
        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
        collect_full_query_text: true
58
        allowed_comment_keys:
59
          - nr_service_guid
60

61
      # Top stored procedure collection configuration
62
      top_procedure_collection:
63
        max_procedure_sample_count: 1000
64
        top_procedure_count: 250
65
        collection_interval: 60s
66

67
      resource_attributes:
68
        db.system.version:
69
          enabled: true
70
        sqlserver.db.edition:
71
          enabled: true
72

73
processors:
74
    memory_limiter:
75
      check_interval: ${env:NR_MEM_LIMITER_CHECK_INTERVAL:-1s}
76
      limit_mib:       ${env:NR_MEM_LIMITER_LIMIT_MIB:-200}
77
      spike_limit_mib: ${env:NR_MEM_LIMITER_SPIKE_MIB:-50}
78

79
exporters:
80
    otlp:
81
      endpoint: https://otlp.nr-data.net:4318
82
      headers:
83
        api-key: YOUR_LICENSE_KEY
84
      tls:
85
        insecure: false
86
      compression: gzip
87

88
      sending_queue:
89
        enabled: true
90
        sizer: bytes
91
        queue_size: 100_000_000
92
        num_consumers: 10
93
        batch:
94
          sizer: bytes
95
          max_size: 1_000_000
96
          min_size: 0
97
          flush_timeout: 5s
98

99
service:
100
    telemetry:
101
      metrics:
102
        level: none
103

104
    pipelines:
105
      metrics:
106
        receivers: [nrsqlserver]
107
        processors: [memory_limiter]
108
        exporters: [otlp]
109

110
      logs:
111
        receivers: [nrsqlserver]
112
        processors: [memory_limiter]
113
        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

     Copiar

     $
     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

     Copiar

     $
     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

   Copiar

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

   Dica

   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:

$
net stop nrdot-collector
$
net start 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 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