Our Microsoft SQL Server integration collects and sends inventory and metrics from your MS SQL Server environment to our platform, where you can see the health of your MS SQL Server environment. We collect both database and instance-level metrics so that you can pinpoint the source of any problems.
Read on to install the integration, and to see what data we collect.
Compatibility and requirements
Our integration is compatible with Microsoft SQL Server 2008 R2 SP3 or higher.
Before installing the integration, make sure that you meet the following requirements:
- Install the infrastructure agent.
- Windows distribution compatible with the infrastructure agent.
- Microsoft SQL Server user with user privileges for both
CONNECT
andVIEW SERVER STATE
, andREAD
access permissions.
Microsoft SQL users and privileges
In the Microsoft SQL Server that is to be monitored, execute the following script to create a new user and grant CONNECT
, VIEW SERVER STATE
, and read access permissions to that user.
See the Microsoft documentation for details on creating logins and users in Microsoft SQL Server.
The Microsoft SQL Server Integration currently only supports SQL Authentication.
- Use the following statements to create a new login and to grant
CONNECT
andVIEW SERVER STATE
permissions to the login.USE master; CREATE LOGIN newrelic WITH PASSWORD = 'tmp_password'; --insert new password here GRANT CONNECT SQL TO newrelic; GRANT VIEW SERVER STATE TO newrelic; GRANT VIEW ANY DEFINITION TO newrelic;
- Use the following statements to grant read access privileges to the user.
DECLARE @name SYSNAME DECLARE db_cursor CURSOR READ_ONLY FORWARD_ONLY FOR SELECT NAME FROM master.sys.databases WHERE NAME NOT IN ('master','msdb','tempdb','model','rdsadmin','distribution') OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN EXECUTE('USE "' + @name + '"; CREATE USER newrelic FOR LOGIN newrelic;' ); FETCH next FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor
- Run the following command to verify that the user was successfully created.
sqlcmd -U user_name -S host_name
Install and activate
To install the Microsoft SQL Server integration:
-
Download the latest .MSI installer image from:
-
In an admin account, run the install script using an absolute path.
- 32-bit Windows
-
msiexec.exe /qn /i PATH\TO\nri-mssql-amd386.msi
- 64-bit Windows
-
msiexec.exe /qn /i PATH\TO\nri-mssql-amd64.msi
- Rename
C:\Program Files\New Relic\newrelic-infra\integrations.d\mssql-config.yml.sample
tomssql-config.yml
, and edit according to your instance. - Restart the infrastructure agent.
Additional notes:
- On-host integrations do not automatically update. For best results, regularly update the integration package and the infrastructure agent.
Configure the integration
An integration's YAML-format configuration is where you can place required login credentials and configure how data is collected. Which options you change depend on your setup and preference.
For an example configuration, see the example config file.
With secrets management, you can configure on-host integrations with New Relic Infrastructure's agent to use sensitive data (such as passwords) without having to write them as plain text into the integration's configuration file. For more information, see Secrets management.
Commands
The mssql-config.yml
file accepts the following commands:
all_data
: collects both inventory and metric data from the Microsoft SQL Server environment.
Arguments
The all_data
command accepts the following arguments:
-
username
: username used to authenticate the MS SQL Server. To use Windows Authentication, specify this argument in theDOMAIN\username
form. This field is required. -
password
: password used to authenticate the MS SQL Server. This field is required. -
hostname
: hostname or IP of the MS SQL Server installation. Default:127.0.0.1
. -
port
: port number on which the MS SQL Server is listening. This is only required wheninstance
is not specified. -
instance
: instance the Microsoft SQL Server is connected to.instance
can be used in place ofport
by enablingSQL Browser
; if enabled, do not includeport
in the argument. -
enable_ssl
: indicates whether SSL is used to connect to the MS SQL Server. Default:false
. -
trust_server_certificate
: if set totrue
, server certificate is not verified for SSL. If set tofalse
, certificate will be verified against supplied certificate. -
certificate_location
: certificate file to verify SSL encryption against. -
timeout
: timeout for queries, in seconds. Default:30
. -
enable_buffer_metrics
: enables the collection of buffer pool metrics. These can be resource intensive for large systems. Default:true
. -
enable_database_reserve_metrics
: enables the collection of database partition reserve space. These can be resource intensive for large systems. Default:true
. custom_metrics_query
: an SQL query with the required columnsmetric_name
,metric_type
, andmetric_value
. Themetric_type
can begauge
,rate
,delta
, orattribute
. Additional columns collected with the query are added to the metric set as attributes.
If both port and instance are omitted, the default port of 1433 is used.
Example configurations
Example mssql-config.yml
file configuration:
- Configuration for one query
-
integration_name: com.newrelic.mssql instances: - name: mssql-server command: all_data arguments: hostname: mssql.localnet username: mssql_user password: mssql_password port: 1433 custom_metrics_query: >- SELECT 'instance_buffer_pool_size' AS metric_name, Count_big(*) * (8*1024) AS metric_value, 'gauge' as metric_type, database_id FROM sys.dm_os_buffer_descriptors WITH (nolock) GROUP BY database_id labels: env: production role: mssql
- Configuration for multiple queries
-
If you need multiple SQL queries, add them to
mssql-custom-query.yml
, and then reference that file.integration_name: com.newrelic.mssql instances: - name: mssql-server command: all_data arguments: hostname: mssql.localnet username: mssql_user password: mssql_password port: 1433 custom_metrics_config: full_path_to_file labels: env: production role: mssql
Here's an example
mssql-custom-query.yml
.queries: # Example for metric_name / metric_type specified in this config - query: SELECT count(*) AS 'metric_value' FROM sys.databases metric_name: dbCount metric_type: gauge # Example for metric_name from query, metric_type auto-detected, additional attribute 'category_type' - query: SELECT CONCAT('category_', category_id) AS metric_name, name AS metric_value, category_type FROM syscategories database: msdb # Example for stored procedure 'exec dbo.sp_server_info @attribute_id = 2' - query: dbo.sp_server_info @attribute_id = 2
For more about the general structure of on-host integration configuration, see Configuration.
Find and use data
To find your integration data go to one.newrelic.com > Infrastructure > Third-party services and select one of the Microsoft SQL Server integration links.
Microsoft SQL Server data is attached to the following event types:
For more on how to find and use your data, see Understand integration data.
Metric data
The Microsoft SQL Server integration collects the following metric data attributes. Some metric name are prefixed with a category indicator and a period, such as asserts.
or flush.
.
Database metrics
These attributes can be found by querying the MssqlDatabaseSample
event.
Metric | Description |
---|---|
|
The size of the buffer pool per database. |
|
Wait time of stall since last restart, in milliseconds. |
|
Total number of times the transaction log for the database has been expanded since the last restart. |
|
Available page file size, in bytes. |
|
Total page file size, in bytes. |
Instance metrics
The Microsoft SQL Server integration collects the following instance metrics. These attributes can be found by querying the MssqlInstanceSample
event.
Metric | Description |
---|---|
|
The number of page splits per second. |
|
The number of active connections. |
|
The ratio of data pages found and read from the buffer cache over all data page requests. |
|
The number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed. |
|
The number of batch requests per second on the buffer pool. |
|
The life expectancy of a page in the buffer pool, in milliseconds. |
|
The size of the buffer pool, in bytes. |
|
The number of background processes on the instance. |
|
The number of blocked processes on the instance. |
|
The amount of disk space on the instance, in bytes. |
|
The number of dormant processes on the instance. |
|
The number of forced parameterizations per second on the instance. |
|
The number of preconnect processes on the instance. |
|
The number of runnable processes on the instance. |
|
The number of runnable tasks on the instance. |
|
The number of running processes on the instance. |
|
The number of sleeping processes on the instance. |
|
The number of suspended processes on the instance. |
|
The number of transactions per second on the instance. |
|
The available physical memory, in bytes. |
|
The total physical memory, in bytes. |
|
The percentage of memory utilization. |
|
The number of user connections. |
|
The number of lock requests per second that resulted in a deadlock since the last restart. |
|
The number of kill connection errors per second since the last restart. |
|
The number of times per second that MS SQL Server is unable to retain a lock right away for a resource. |
|
The number of MS SQL compilations per second. |
|
The number of MS SQL re-compilations per second. |
|
The number of user errors per second since the last restart. |
|
The percentage of buffer pools hits on the instance. |
|
The number of milliseconds per second spent waiting across the instance. |
Wait metrics
These attributes can be found by querying the MssqlWaitSample
event.
Metric | Description |
---|---|
|
Total wait time for this wait type, in milliseconds. This time is inclusive of |
|
The number of waits on this wait type, in milliseconds. This counter is incremented at the start of each wait. |
Inventory data
The Microsoft SQL Server integration captures the configuration parameters and current settings of the Microsoft SQL Server environment. It collects the results of the sp_configure
stored procedure, as well as current running configuration settings from the sys.configurations
table.
The data is available on the Inventory page, under the config/mssql source. For more about inventory data, see Understand integration data.
Check the source code
This integration is open source software. That means you can browse its source code and send improvements, or create your own fork and build it.