Learn about the Wavefront Microsoft SQL Server Integration.

Microsoft SQL Server Integration

Note: This integration provides updated setup instructions and dashboard for SQL server. For the previous setup instructions, see the SQL Server (Archived) integration in the Archived section.

SQL Server by Microsoft is a popular enterprise RDBMS. This integration installs and configures Telegraf to send SQL server metrics into Wavefront. Telegraf is a light-weight server process capable of collecting, processing, aggregating, and sending metrics to a Wavefront proxy.

In addition to setting up the metrics flow, this integration also installs a dashboard. Here’s the performance counters section of a dashboard displaying MSSQL metrics: images/sqlserver-perfcounters.png

Microsoft SQL Server Setup

This integration uses the MSSqlServer input plugin for Telegraf.

This integration is supported only on Windows.

Note: If you use vRealize Operations, the application proxy agent sets up the integration for you. See the setup instructions. Otherwise, follow the setup steps on this page.

Step 1: Set up a Wavefront Proxy

If you do not have a Wavefront proxy installed on your network, install a proxy.

Step 2: Install the Telegraf Agent

  1. Download wavefront-telegraf-64-setup.exe. Only 64-bit Windows is supported.

  2. Double click the wavefront-telegraf-64-setup.exe file and follow the instructions in the setup dialog to install Telegraf.

    Note: Steps 3-5 are only required if the Wavefront proxy is installed on a different host.

  3. Edit the telegraf.conf file located in Program Files\Telegraf.

    Warning: Do not use notepad to modify the file. Use an editor that supports Unix style line endings, such as Notepad++ or EditPlus.

  4. Configure the outputs section to communicate with your Wavefront proxy: 
    [[outputs.wavefront]]
   host = "WAVEFRONT_PROXY_HOSTNAME"
   port = 2878

    Note: In the default telegraf.conf, the host property is set to localhost.

  5. Restart the Telegraf service using the Windows Services Management Console or from the command prompt: 
    net stop telegraf
net start telegraf
  6. Check Program Files\Telegraf\telegraf.log to verify the installation.

Step 3. Configure MSSQLServer Input Plugin

Create an SQL user with the required permissions on every SQL Server from which you plan to collect metrics. Use the following script after connecting to your SQL Server.

USE master;
GO
CREATE LOGIN [telegraf] WITH PASSWORD = N'mystrongpassword';
GO
GRANT VIEW SERVER STATE TO [telegraf];
GO
GRANT VIEW ANY DEFINITION TO [telegraf];
GO

Edit the telegraf.conf file located in Program Files\Telegraf\ and enter the following snippet:

[[inputs.sqlserver]]
# Specify instances to monitor with a list of connection strings.
# To collect metrics from more than 1 server, run the SQL script from Step 2 on each server and provide the connection string in the "servers" section.
# All connection parameters are optional.
# By default, the host is localhost, listening on default port (TCP/1433)
#    for more information about the plugin, visit https://github.com/influxdata/telegraf/tree/master/plugins/inputs/sqlserver
#    See https://github.com/denisenkom/go-mssqldb for detailed connection parameters.

servers = [
"Server=<servername>;Port=1433;User Id=telegraf;Password=<'mystrongpassword' from step 2>;app name=telegraf;log=1;",
"Server=<servername>;Port=1433;User Id=telegraf;Password=<'mystrongpassword' from step 2>;app name=telegraf;log=1;"
]
query_version = 2
name_prefix = "MSSQL2."

Step 4. Restart Telegraf

Use the Windows Services Management Console or execute the following from the command prompt:

net stop telegraf
net start telegraf
integrations list