Find the New Relic Microsoft SQL Server plugin in the New Relic storefront
Find the New Relic Microsoft SQL Server plugin in Plugin Central
- A New Relic account. Sign up for a free account here
- .NET 3.5
- Windows 7/Server 2008 or later
- SQL Server 2005 or later
- Network access to New Relic
This plugin can be installed one of the following ways:
The New Relic Platform Installer (NPI) is a simple, lightweight command line tool that helps you easily download, configure and manage New Relic Platform Plugins. If you're interested in learning more simply go to our forum category and checkout the 'Getting Started' section. If you have any questions, concerns or feedback, please do not hesitate to reach out through the forums as we greatly appreciate your feedback!
Once you've installed the NPI tool, run the following command:
npi install com.newrelic.platform.microsoft.sqlserver
This command will take care of the creation of newrelic.json and plugin.json configuration files. See the configuration information section for more information.
The latest version of the plugin can be downloaded here. Once the plugin is on your box, extract it to a location of your choosing.
Check out the configuration information section for details on configuring your plugin.
To run the plugin, execute the following command from a terminal or command window (assuming you are in the directory where the plugin was extracted):
plugin.exe
Step 3 showed you how to run the plugin; however, there are several problems with running the process directly in the foreground (For example, when the machine reboots the process will not be started again). That said, there are several common ways to keep a plugin running, but they do require more advanced knowledge or additional tooling. We highly recommend considering using the New Relic Platform Installer as it will take care of most of the heavy lifting for you.
If you prefer to be more involved in the maintaince of the process, you can use the following tool to create a Windows service.
You will need to modify two configuration files in order to set this plugin up to run. The first (newrelic.json) contains configurations used by all Platform plugins (e.g. license key, logging information, proxy settings) and can be shared across your plugins. The second (plugin.json) contains data specific to each plugin such as a list of hosts and port combination for what you are monitoring. Templates for both of these files should be located in the 'config' directory in your extracted plugin folder.
The plugin.json file has a provided template in the config directory named plugin.template.json. If you are installing manually, make a copy of this template file and rename it to plugin.json (the New Relic Platform Installer will automatically handle creation of configuration files for you).
Below is an example of the plugin.json file's contents, you can add multiple objects to the "agents" array to monitor different instances:
{
"agents": [
{
"type" : "sqlserver",
"name" : "Production Database",
"connectionString" : "Server=hostname\\instanceName;Database=master;Trusted_Connection=True;",
"includeSystemDatabases" : "false",
"includes" : [
{
"name": "AdventureWorks",
"displayName": "My AdventureWorks Database"
}
],
"excludes" : [
{
"name": "nameOfDatabaseToExclude"
}
]
},
{
"type" : "azure",
"name" : "Azure Cloud Database",
"connectionString" : <Your SQL Azure connection string>
}
]
}
note - Notice you must escape '' characters in your connection strings.
note - Set the "name" attribute to identify each MS SQL host, e.g. "Production" as this will be used to identify specific instances in the New Relic UI.
note - Each JSON object in the 'agents' array should have a type of either 'sqlserver' or 'azure'.
note - Get your SQL Azure connection string from the Azure Portal.
The newrelic.json file also has a provided template in the config directory named newrelic.template.json. If you are installing manually, make a copy of this template file and rename it to newrelic.json (again, the New Relic Platform Installer will automatically handle this for you).
The newrelic.json is a standardized file containing configuration information that applies to any plugin (e.g. license key, logging, proxy settings), so going forward you will be able to copy a single newrelic.json file from one plugin to another. Below is a list of the configuration fields that can be managed through this file:
Your New Relic license key is the only required field in the newrelic.json file as it is used to determine what account you are reporting to. If you do not know what your license key is, you can learn about it here.
Example:
{
"license_key": "YOUR_LICENSE_KEY_HERE"
}
By default Platform plugins will have their logging turned on; however, you can manage these settings with the following configurations:
log_level - The log level. Valid values: [debug, info, warn, error, fatal]. Defaults to info.
log_file_name - The log file name. Defaults to newrelic_plugin.log.
log_file_path - The log file path. Defaults to logs.
log_limit_in_kbytes - The log file limit in kilobytes. Defaults to 25600 (25 MB). If limit is set to 0, the log file size would not be limited.
Example:
{
"license_key": "YOUR_LICENSE_KEY_HERE",
"log_level": "debug",
"log_file_path": "C:\\Logs"
}
If you are running your plugin from a machine that runs outbound traffic through a proxy, you can use the following optional configurations in your newrelic.json file:
proxy_host - The proxy host (e.g. webcache.example.com)
proxy_port - The proxy port (e.g. 8080). Defaults to 80 if a proxy_host is set
proxy_username - The proxy username
proxy_password - The proxy password
Example:
{
"license_key": "YOUR_LICENSE_KEY_HERE",
"proxy_host": "proxy.mycompany.com",
"proxy_port": 9000
}
By default, most services will be installed as Local Service. Use the Services MMC to change the user when using a trusted connection.
SQL Server - Trusted Connection
When Trusted_Connection=True is used in the connection string, the plugin connects to SQL using the credentials from the Windows/domain user configured to run the Windows Service. In SQL Server, ensure a login is configured for this Windows/domain user. Finally, grant the minimum rights to the user to perform the queries.
For example, if the user were THE_DOMAIN\NewRelic, run the following SQL:
USE [master];
GO
GRANT VIEW SERVER STATE TO [THE_DOMAIN\NewRelic];
GO
Ensure the user has access to each database.
SQL Server - SQL Login
When using a SQL Login in the connection string, ensure the login is configured with the correct rights.
For example, add a login named NewRelic. Grant the rights it needs to make the queries.
USE [master];
GO
CREATE LOGIN NewRelic WITH PASSWORD=N'AnyPhraseHere',
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF,
CHECK_POLICY=ON;
GO
GRANT VIEW SERVER STATE TO NewRelic;
GO
Then create a user for each database to monitor. For example, with the database "TestData":
USE TestData;
GO
CREATE USER NewRelicUser FOR LOGIN NewRelic;
GO
Windows Azure SQL Database
Azure SQL is configured in two separate connections to the database. Use SSMS to access the master database.
First, create the login in the master database for your Azure SQL Server and the user to query for service interuption metrics.
CREATE LOGIN NewRelic WITH password='AnyPhraseHere';
GO
CREATE USER NewRelicUser FROM LOGIN NewRelic;
GO
In a new connection to each individual Azure SQL Database:
CREATE USER NewRelicUser FROM LOGIN NewRelic;
GO
GRANT VIEW DATABASE STATE TO NewRelicUser
GO
Permissions Issues in the Database
For each database to be monitored, execute a sample query to confirm the correct rights have been applied. Do this by launching SQL Server Management Studio (SSMS) as the user configured to host the service.
-
When using Windows authentication, launch SSMS as the user. This can be done but holding down Ctrl+Shift when right-clicking on the SSMS shortcut. In the context menu is an entry for 'Run as a * different user' that prompts for credentials. Enter the username and password of the Windows user that will host the service.
-
When using SQL authentication or Azure, launch SSMS normally. When prompted, select 'SQL Server Authentication' and enter the credentials supplied in your config file.
In each database, confirm the following query executes without failure:
SQL Server
SELECT
d.Name AS DatabaseName,
COUNT(c.connection_id) AS NumberOfConnections,
ISNULL(SUM(c.num_reads), 0) AS NumberOfReads,
ISNULL(SUM(c.num_writes), 0) AS NumberOfWrites
FROM sys.databases d
LEFT JOIN sys.sysprocesses s ON s.dbid = d.database_id
LEFT JOIN sys.dm_exec_connections c ON c.session_id = s.spid
WHERE (s.spid IS NULL OR c.session_id >= 51)
GROUP BY d.Name
Azure SQL
SELECT
ROW_NUMBER() OVER (ORDER BY [wait_time_ms] DESC) AS [RowNum],
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitSeconds],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceSeconds],
[signal_wait_time_ms] / 1000.0 AS [SignalSeconds],
[waiting_tasks_count] AS [WaitCount],
[wait_time_ms] * 100 / SUM([wait_time_ms]) OVER () AS [Percentage]
FROM sys.dm_db_wait_stats
Find a bug? Post it to http://support.newrelic.com.
The New Relic Platform uses an extensible architecture that allows you to define new metrics beyond the provided defaults. To expose more data about your MS SQL servers, fork this repository, create a new GUID, add the metrics you would like to collect to the code and then build summary metrics and dashboards to expose your newly collected metrics.
You are welcome to send pull requests to us - however, by doing so you agree that you are granting New Relic a non-exclusive, non-revokable, no-cost license to use the code, algorithms, patents, and ideas in that code in our products if we so choose. You also agree the code is provided as-is and you provide no warranties as to its fitness or correctness for any purpose.
The New Relic Microsoft SQL Server plugin was originally authored by Ed Chapel, Jesse Stromwick, and Mike Merrill. Subsequent updates and support are provided by New Relic.