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

Prerequisites

Before monitoring your Microsoft SQL Server with NRDOT, 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.
  • Windows domain or SQL Server authentication.

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

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

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

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 query sample, top query, query plan, and top procedure log collection
37
      events:
38
        db.server.query_sample:
39
          enabled: true
40
        db.server.top_query:
41
          enabled: true
42
        db.server.query_plan:
43
          enabled: true
44
        db.server.top_procedure:
45
          enabled: true
46

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

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

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

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

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

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

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

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

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

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