Skip to main content
Sumo Logic

Collect Microsoft SQL Server Logs and Metrics for Non-Kubernetes environments

This page provides instructions to Collect Microsoft SQL Server Logs and Metrics for Non-Kubernetes environments.

We use the Telegraf operator for SQL Server metric collection and Sumo Logic Installed Collector for collecting SQL Server logs. The diagram below illustrates the components of the SQL Server collection in a non-Kubernetes environment. Telegraf runs on the same system as SQL Server, and uses the SQL Server input plugin to obtain SQL Server metrics, and the Sumo Logic output plugin to send the metrics to Sumo Logic. Logs from SQL Server on the other hand are sent to either a Sumo Logic Local File source or Syslog source.

This section provides instructions for configuring metrics collection for the Sumo Logic App for SQL Server. Follow the below instructions to set up the metric collection:

  1. Configure Metrics Collection

    1. Configure a Hosted Collector

    2. Configure an HTTP Logs and Metrics Source

    3. Install Telegraf

    4. Configure and start Telegraf

  2. Configure Logs Collection

    1. Configure logging in SQL Server

    2. Configure Sumo Logic Installed Collector

Step 1 Configure Metrics Collection

  1. Configure a Hosted Collector

To create a new Sumo Logic hosted collector, perform the steps in the Configure a Hosted Collector section of the Sumo Logic documentation.

  1. Configure an HTTP Logs and Metrics Source

Create a new HTTP Logs and Metrics Source in the hosted collector created above by following these instructions. Make a note of the HTTP Source URL.

  1. Install Telegraf

Use the these steps to install Telegraf.

  1. Configure and start Telegraf

As part of collecting metrics data from Telegraf, we will use the SQLserver input plugin to get data from Telegraf and the Sumo Logic output plugin to send data to Sumo Logic. 

Before you configure telegraf, you will need to create a login on every SQL Server instance you want to monitor, with the following script. This will create a user at SQL Server, which will be used as input to telegraf configuration.

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

Create or modify telegraf.conf and copy and paste the text below:  

[[inputs.sql server]]
    servers = [ "Server=<IP_CHANGE_ME>;Port=<Port_CHANGE_ME| default 1433>;User Id=<Username_CHANGE_ME>;Password=<Password_CHANGE_ME>;app name=telegraf;log=1;",]
   database_type = "SQLServer"
   exclude_query = [ 'SQLServerSchedulers' , 'SQLServerRequests' ]
            tagexclude = ['physical_filename','volume_mount_point']  
     [inputs.sqlserver.tags]
    environment="prod"
    component="database"
    db_cluster="sqlserver_on_premise"
    db_system = "sqlserver"
[[outputs.sumologic]]
  url = "<URL Created in Step 3>”
  data_format = "prometheus"
 [outputs.sumologic.tagpass]
    proxy_cluster=["sqlserver_on_premise"]

Please enter values for the following parameters (marked in bold above):

  • In the input plugins section, which is [[inputs.sqlserver]]:
    • servers - The URL to the sql server. Please see this doc for more information on additional parameters for configuring the SQL Server input plugin for Telegraf.
  • In the tags section, which is [inputs.sqlserver.tags]
    • environment - This is the deployment environment where the SQL Server cluster identified by the value of servers resides. For example: dev, prod or qa. While this value is optional we highly recommend setting it. 
    • db_cluster - Enter a name to identify this SQLserver cluster. This cluster name will be shown in the Sumo Logic dashboards. 
  • In the output plugins section, which is [[outputs.sumologic]] : 
    • url - This is the HTTP source URL created in step 3. Please see this doc for more information on additional parameters for configuring the Sumo Logic Telegraf output plugin.

Here’s an explanation for additional values set by this Telegraf configuration that we request you please do not modify as they will cause the Sumo Logic apps to not function correctly.

  • data_format - “prometheus” In the output plugins section, which is [[outputs.sumologic]]. Metrics are sent in the Prometheus format to Sumo Logic.
  • Component: “database” - In the input plugins section, which is [[inputs.SQLserver]] - This value is used by Sumo Logic apps to identify application components.
  • db_system: “sqlserver” - In the input plugins sections.In other words, this value identifies the messaging system.

For all other parameters please see this doc for more properties that can be configured in the Telegraf agent globally.

Once you have finalized your telegraf.conf file, you can start or reload the telegraf service using instructions from the doc.

At this point, SQL Server metrics should start flowing into Sumo Logic.

Step 2 Configure Logs Collection

This section provides instructions for configuring log collection for SQL Server running on a non-kubernetes environment for the Sumo Logic App for SQL Server. 

Based on your infrastructure and networking setup choose one of these methods to collect SQL Server logs and follow the instructions below to set up log collection:

  1. Configure logging in SQL Server

The Microsoft SQL Server App is based on SQL Server ERRORLOG, which is typically found at:  C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG.

The ERRORLOG typically uses UTF-16LE encoding, but you should confirm the file encoding used on your SQL Server for this configuration.

To check your ERRORLOG encoding on a Mac, use the following commands:

user$ file -I ERRORLOG*ERRORLOG*
ERRORLOGERRORLOG: text/plain; charset=utf-16le
ERRORLOG.1: text/plain; charset=utf-16le
ERRORLOG.2: text/plain; charset=utf-16le

On Windows, you can also check ERRORLOG encoding with Notepad ++.

For the most part, UTF-16 and UCS-2 are the same thing. There is no difference.

What it means is that each character is two bytes wide. "LE" stands for little endian, i.e. each two-byte character is stored with the low byte first.

  1. Configuring a Collector

If you have not already done so, install and configure an installed collector for Windows, using the instructions in Install a Collector on Windows.

  1. Configuring a Source

To collect logs directly from your SQL Server machine, use an Installed Collector and a Local File Source. 

  1. Add a Local File Source.

  2. Configure the Local File Source fields as follows:

  • Name. (Required)

  • Description. (Optional)

  • File Path (Required). Enter the path to your ERRORLOG. The files are typically located in C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG. If you are using a customized path, check the For further details on ERRORLOG location refer this doc.

  • Source Host. Sumo Logic uses the hostname assigned by the OS unless you enter a different host name

  • Source Category. Enter any string to tag the output collected from this Source, such as SQLserver/Logs. (The Source Category metadata field is a fundamental building block to organize and label Sources. For details see Best Practices.)

  • Fields. Set the following fields:

    • component = database

    • db_system = SQLserver

    • db_cluster = <Your_SQLserver_Cluster_Name>

    • environment = <Environment_Name>, such as Dev, QA or Prod.

  1. Configure the Advanced section:

  • Enable Timestamp Parsing. Select Extract timestamp information from log file entries.

  • Time Zone. Choose the option, Ignore time zone from log file and instead use, and then select your SQL Server Server’s time zone.

  • Timestamp Format. The timestamp format is automatically detected.

  • Encoding. Select UTF-16LE

  • Enable Multiline Processing. Detect messages spanning multiple lines

    • Infer Boundaries - Detect message boundaries automatically

  1. Click Save.

At this point, SQL Server logs should start flowing into Sumo Logic.