Linux 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 Linux self-hosted environments including physical servers, virtual machines, and containerized Linux deployments.

Prerequisites

Ensure your environment meets our compatibility and prerequisites.

Set up the NRDOT Collector

Install the NRDOT Collector on your Linux flavour:

중요

It is recommended to install the NRDOT Collector on the same host as your SQL Server instance to view infrastructure-level metrics for query performance monitoring.

Debian / Ubuntu

For AMD64 (x86_64) Architecture:

bash

$NRDOT_VERSION=$(curl -s https://api.github.com/repos/newrelic/nrdot-collector-releases/releases/latest | grep '"tag_name":' | awk -F'"' '{print $4}') && curl -L "https://github.com/newrelic/nrdot-collector-releases/releases/download/${NRDOT_VERSION}/nrdot-collector_${NRDOT_VERSION}_linux_amd64.deb" --output nrdot-collector.deb && sudo dpkg -i nrdot-collector.deb

For ARM64 Architecture:

bash

$NRDOT_VERSION=$(curl -s https://api.github.com/repos/newrelic/nrdot-collector-releases/releases/latest | grep '"tag_name":' | awk -F'"' '{print $4}') && curl -L "https://github.com/newrelic/nrdot-collector-releases/releases/download/${NRDOT_VERSION}/nrdot-collector_${NRDOT_VERSION}_linux_arm64.deb" --output nrdot-collector.deb && sudo dpkg -i nrdot-collector.deb

RHEL / CentOS

For AMD64 (x86_64) Architecture:

bash

$NRDOT_VERSION=$(curl -s https://api.github.com/repos/newrelic/nrdot-collector-releases/releases/latest | grep '"tag_name":' | awk -F'"' '{print $4}') && curl -L "https://github.com/newrelic/nrdot-collector-releases/releases/download/${NRDOT_VERSION}/nrdot-collector_${NRDOT_VERSION}_linux_x86_64.rpm" --output nrdot-collector.rpm && sudo rpm -ivh nrdot-collector.rpm

For ARM64 Architecture:

bash

$NRDOT_VERSION=$(curl -s https://api.github.com/repos/newrelic/nrdot-collector-releases/releases/latest | grep '"tag_name":' | awk -F'"' '{print $4}') && curl -L "https://github.com/newrelic/nrdot-collector-releases/releases/download/${NRDOT_VERSION}/nrdot-collector_${NRDOT_VERSION}_linux_arm64.rpm" --output nrdot-collector.rpm && sudo rpm -ivh nrdot-collector.rpm

Configure database user

Run the following script as a root user/sysadmin to create the newrelic monitoring user and grant global read permissions.

To create a newrelic monitoring user in Linux, create a file named nr-grant-permission.sql on your Linux instance with the following content:
```
USE [master];
GO
CREATE LOGIN [newrelic] WITH PASSWORD = '<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'')
      CREATE USER [newrelic] FOR LOGIN [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 execute the script, run the following command. Replace <Your_SA_Password> with your SQL Server administrator password:
bash
```
$sqlcmd -S localhost -U sa -P '<Your_SA_Password>' -C -i nr-grant-permission.sql
```

(Optional) To verify that the user was created successfully and has the correct permissions, run the following command. Replace <Your_SA_Password> with your SQL Server administrator password:

bash

$sqlcmd -S localhost -U sa -P '<Your_SA_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';"

Expected Output:

User Permission_Granted
-------------------------------------------------------------------------------------
<your_username> CONNECT SQL
<your_username> VIEW ANY DEFINITION
<your_username> VIEW ANY DATABASE
<your_username> VIEW SERVER STATE

This output confirms that your user has been created successfully with the required server-level permissions.

Configure NRDOT Collector

Select your configuration type based on your monitoring needs:

This configuration focuses on essential database monitoring with core metrics.

Create the configuration file. The filename must be mssql-config.yaml:
bash
```
$sudo nano /etc/nrdot-collector/mssql-config.yaml
```
Add the configuration content below to the mssql-config.yaml file you created in the previous step.

receivers:
  nrsqlserver:
    collection_interval: 15s
    # Direct connection to SQL Server
    username: <YOUR_USERNAME>
    password: <YOUR_PASSWORD>
    server: <YOUR_SERVER>
    port: 1433

    # Enable comprehensive database metrics
    metrics:
      sqlserver.database.count:
        enabled: true
      sqlserver.database.io:
        enabled: true
      sqlserver.database.latency:
        enabled: true
      sqlserver.database.operations:
        enabled: true
      sqlserver.database.tempdb.space:
        enabled: true
      sqlserver.database.tempdb.version_store.size:
        enabled: true
      sqlserver.deadlock.rate:
        enabled: true
      sqlserver.os.wait.duration:
        enabled: true
      sqlserver.processes.blocked:
        enabled: true
      sqlserver.memory.grants.pending.count:
        enabled: true
      sqlserver.database.file.size:
        enabled: true
      sqlserver.memory.area:
        enabled: true

    # Enable top query and query sample log collection
    events:
      db.server.query_sample:
        enabled: true
      db.server.top_query:
        enabled: true

    # Top query collection configuration
    top_query_collection:
      lookback_time: 60s
      max_query_sample_count: 1000
      top_query_count: 250
      collection_interval: 60s

    collect_full_query_text: true
    allowed_comment_keys:
      - nr_service_guid

    # Query sample collection configuration
    query_sample_collection:
      max_rows_per_query: 100

processors:
  memory_limiter:
    check_interval: ${env:NR_MEM_LIMITER_CHECK_INTERVAL:-1s}
    limit_mib:       ${env:NR_MEM_LIMITER_LIMIT_MIB:-200}
    spike_limit_mib: ${env:NR_MEM_LIMITER_SPIKE_MIB:-50}

  batch:

exporters:
  otlphttp:
    endpoint: <YOUR_OTLP_ENDPOINT>
    headers:
      api-key: <YOUR_LICENSE_KEY>
    tls:
      insecure: false
    compression: gzip

service:
  telemetry:
    metrics:
      level: none

  pipelines:
    metrics:
      receivers: [nrsqlserver]
      processors: [memory_limiter, batch]
      exporters: [otlphttp]

    logs:
      receivers: [nrsqlserver]
      processors: [memory_limiter, batch]
      exporters: [otlphttp]

This configuration provides comprehensive database monitoring with all available metrics.

Create the configuration file. The filename must be mssql-config.yaml:
bash
```
$sudo nano /etc/nrdot-collector/mssql-config.yaml
```
Add the configuration content below to the mssql-config.yaml file you created in the previous step.

중요

If you're an existing NRDOT Collector user, ensure to merge the configuration with your existing config.yaml file. This includes adding the new receivers, processors, and exporters for MSSQL Database monitoring while retaining your current configurations.

receivers:
  nrsqlserver:
    collection_interval: 15s
    username: <YOUR_USERNAME>
    password: <YOUR_PASSWORD>
    server: <YOUR_SERVER>
    port: <YOUR_PORT_NUMBER>

    metrics:
      # Database
      sqlserver.database.count:
        enabled: true
      sqlserver.database.file.size:
        enabled: true
      sqlserver.database.io:
        enabled: true
      sqlserver.database.latency:
        enabled: true
      sqlserver.database.operations:
        enabled: true
      sqlserver.database.page_file.size:
        enabled: true
      sqlserver.database.transactions.active:
        enabled: true
      sqlserver.database.backup_or_restore.rate:
        enabled: true
      sqlserver.database.execution.errors:
        enabled: true
      sqlserver.database.full_scan.rate:
        enabled: true
      sqlserver.database.tempdb.space:
        enabled: true
      sqlserver.database.tempdb.version_store.size:
        enabled: true
      # Security / Principals
      sqlserver.database.principals.count:
        enabled: true
      sqlserver.database.principals.old:
        enabled: true
      sqlserver.database.principals.orphaned_users:
        enabled: true
      sqlserver.database.principals.recently_created:
        enabled: true
      sqlserver.database.role.members.count:
        enabled: true
      sqlserver.database.role.memberships.count:
        enabled: true
      sqlserver.database.role.permission.risk_level:
        enabled: true
      sqlserver.database.role.roles.count:
        enabled: true
      sqlserver.database.security.role_membership.count:
        enabled: true
      sqlserver.server.security.principal.count:
        enabled: true
      sqlserver.server.security.role_membership.count:
        enabled: true
      # Locks
      sqlserver.lock.by_mode.count:
        enabled: true
      sqlserver.lock.by_resource.count:
        enabled: true
      sqlserver.lock.timeout.rate:
        enabled: true
      sqlserver.lock.wait.count:
        enabled: true
      sqlserver.lock.wait.rate:
        enabled: true
      sqlserver.deadlock.rate:
        enabled: true
      # Transactions
      sqlserver.transaction.delay:
        enabled: true
      sqlserver.transaction.mirror_write.rate:
        enabled: true
      sqlserver.transaction.longest_running_time:
        enabled: true
      sqlserver.transaction.version_cleanup.rate:
        enabled: true
      sqlserver.transaction.version_generation.rate:
        enabled: true
      # Memory
      sqlserver.memory.area:
        enabled: true
      sqlserver.memory.cache.object.count:
        enabled: true
      sqlserver.memory.grants.pending.count:
        enabled: true
      sqlserver.memory.page.count:
        enabled: true
      sqlserver.memory.target:
        enabled: true
      sqlserver.memory.usage:
        enabled: true
      # OS
      sqlserver.os.disk.size:
        enabled: true
      sqlserver.os.memory.usage:
        enabled: true
      sqlserver.os.memory.utilization:
        enabled: true
      sqlserver.os.scheduler.runnable_tasks.count:
        enabled: true
      sqlserver.os.wait.duration:
        enabled: true
      sqlserver.os.wait.tasks.count:
        enabled: true
      # Pages / Buffer
      sqlserver.page.buffer_cache.hit_ratio:
        enabled: true
      sqlserver.page.buffer_cache.free_list.stalls.rate:
        enabled: true
      sqlserver.page.life_expectancy:
        enabled: true
      sqlserver.page.lookup.rate:
        enabled: true
      # Batch / Compilation
      sqlserver.batch.request.rate:
        enabled: true
      sqlserver.batch.sql_compilation.rate:
        enabled: true
      sqlserver.batch.sql_recompilation.rate:
        enabled: true
      sqlserver.batch.compilation.utilization:
        enabled: true
      sqlserver.batch.page_split.utilization:
        enabled: true
      sqlserver.recompilation.ratio:
        enabled: true
      sqlserver.parameterization.rate:
        enabled: true
      sqlserver.plan.execution.rate:
        enabled: true
      sqlserver.attention.rate:
        enabled: true
      # Processes / Connections
      sqlserver.process.count:
        enabled: true
      sqlserver.processes.blocked:
        enabled: true
      sqlserver.user.connection.count:
        enabled: true
      sqlserver.login.rate:
        enabled: true
      sqlserver.logout.rate:
        enabled: true
      # Thread Pool
      sqlserver.thread_pool.tasks.count:
        enabled: true
      sqlserver.thread_pool.workers.count:
        enabled: true
      sqlserver.thread_pool.workers.max:
        enabled: true
      sqlserver.thread_pool.workers.utilization:
        enabled: true
      # TempDB
      sqlserver.tempdb.allocation.wait_time.total:
        enabled: true
      sqlserver.tempdb.contention.waiters.count:
        enabled: true
      sqlserver.tempdb.data_files.count:
        enabled: true
      sqlserver.tempdb.file.size:
        enabled: true
      sqlserver.tempdb.space.usage:
        enabled: true
      # Latch
      sqlserver.latch.superlatch.count:
        enabled: true
      sqlserver.latch.superlatch.transition.rate:
        enabled: true
      sqlserver.latch.wait.rate:
        enabled: true
      sqlserver.latch.wait_time.avg:
        enabled: true
      sqlserver.latch.wait_time.total:
        enabled: true
      # Index
      sqlserver.index.search.rate:
        enabled: true
      # Resource Pool
      sqlserver.resource_pool.disk.operations:
        enabled: true
      sqlserver.resource_pool.disk.throttled.read.rate:
        enabled: true
      sqlserver.resource_pool.disk.throttled.write.rate:
        enabled: true
      # Replica / Always-On
      sqlserver.replica.data.rate:
        enabled: true
      sqlserver.failover_cluster.ag.cluster_type:
        enabled: true
      sqlserver.failover_cluster.ag.failure_condition_level:
        enabled: true
      sqlserver.failover_cluster.ag.health_check_timeout:
        enabled: true
      sqlserver.failover_cluster.ag.required_sync_secondaries:
        enabled: true
      sqlserver.failover_cluster.replica.database.queue_size:
        enabled: true
      sqlserver.failover_cluster.replica.database.redo.rate:
        enabled: true
      sqlserver.failover_cluster.replica.flow_control_time:
        enabled: true
      sqlserver.failover_cluster.replica.role:
        enabled: true
      sqlserver.failover_cluster.replica.synchronization_health:
        enabled: true
      # Server Properties
      sqlserver.computer.uptime:
        enabled: true
      sqlserver.cpu.count:
        enabled: true
      # Tables
      sqlserver.table.count:
        enabled: true
      # Errors
      sqlserver.kill_connection.error.rate:
        enabled: true

    events:
      db.server.query_sample:
        enabled: true
      db.server.top_query:
        enabled: true

    top_query_collection:
      lookback_time: 60s
      max_query_sample_count: 1000
      top_query_count: 250
      collection_interval: 60s

    collect_full_query_text: true
    allowed_comment_keys:
      - nr_service_guid

    query_sample_collection:
      max_rows_per_query: 100

processors:
  memory_limiter:
    check_interval: ${env:NR_MEM_LIMITER_CHECK_INTERVAL:-1s}
    limit_mib:       ${env:NR_MEM_LIMITER_LIMIT_MIB:-500}
    spike_limit_mib: ${env:NR_MEM_LIMITER_SPIKE_MIB:-50}

  batch:


exporters:
  otlphttp:
    endpoint: <YOUR_OTLP_ENDPOINT>
    headers:
      api-key: <YOUR_LICENSE_KEY>
    tls:
      insecure: false
    compression: gzip

service:
  telemetry:
    metrics:
      level: none

  pipelines:
    metrics:
      receivers: [nrsqlserver]
      processors: [memory_limiter, batch]
      exporters: [otlphttp]

    logs:
      receivers: [nrsqlserver]
      processors: [memory_limiter, batch]
      exporters: [otlphttp]

팁

Note: The max_concurrent_queries option controls how many metric queries the receiver executes in parallel against SQL Server during each scrape cycle.

receivers:
  nrsqlserver:
    max_concurrent_queries: 4  # default is 4 if omitted or set to 0

Configuration parameters

The following table describes the key configuration parameters for the nrsqlserver receiver:

Parameter	Description
`<YOUR_USERNAME>`	Enter your database username for authentication
`<YOUR_PASSWORD>`	Enter your database password for authentication
`<YOUR_SERVER>`	Enter your SQL Server hostname or IP address
`port`	Enter your SQL Server port number. The default value is 1433.
`<YOUR_OTLP_ENDPOINT>`	Enter the New Relic OTLP endpoint. For more information, refer to New Relic OTLP endpoints documentation.
`<YOUR_LICENSE_KEY>`	Enter your New Relic license key.
`collection_interval`	Interval in seconds to collect database metrics. Default: 15s

팁

Note: If you enable all available metrics, increase the limit_mib value (e.g., 1000) to prevent the memory limiter from dropping data.

Update the collector configuration file
Run the following command to edit the service configuration:
bash
```
$sudo nano /etc/nrdot-collector/nrdot-collector.conf
```
Update the config path to point to your new mssql-config.yaml file:
bash
```
$OTELCOL_CONFIG="/etc/nrdot-collector/mssql-config.yaml"
```
Validate NRDOT Collector Configuration
Validate the NRDOT Collector configuration to ensure it's correctly formatted and will work properly:
bash
```
$sudo /usr/bin/nrdot-collector validate --config=/etc/nrdot-collector/mssql-config.yaml
```

Restart NRDOT collector

After updating your configuration, restart the NRDOT Collector service:

bash

$sudo systemctl restart nrdot-collector

팁

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

Next steps

After setting up Linux self-hosted monitoring:

Linux self-hosted MSSQL monitoring with NRDOT

Preview

Prerequisites

Set up the NRDOT Collector

중요

Configure database user

Configure NRDOT Collector

Standard configuration

Full-feature configuration

중요

팁

Configuration parameters

팁

Restart NRDOT collector

팁

Next steps

Linux self-hosted MSSQL monitoring with NRDOT

Preview

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

Set up the NRDOT Collector

중요

Configure database user

Configure NRDOT Collector

Full-feature configuration

팁

Configuration parameters

팁

Restart NRDOT collector

팁

Next steps

Prerequisites