})
The Sumo Logic App for Microsoft SQL Server is an unified logs and metrics app that provides insight into your SQL server performance metrics and errors. The App consists of predefined Dashboards, providing visibility into your environment for real-time or historical analysis on backup, latency, performance counter, restore, mirroring, database monitoring, general health and operations of your system.
This App has been tested with following SQL Server versions:
* Microsoft SQL Server 2012
## Collecting logs and metrics for the Microsoft SQL Server App
This section provides instructions for configuring a local file source to collect SQL Server ERRORLOG data, and a script source to collect SQL Server performance metrics. A sample log message is also provided.
### Collect Logs and Metrics
Sumo Logic supports collection of logs and metrics data from SQL Server in both Kubernetes and non-Kubernetes environments. Click on the appropriate tabs below based on the environment where your SQL Server clusters are hosted.
})
The first service in the pipeline is Telegraf. Telegraf collects metrics from SQL Server. Note that we are running Telegraf as a sidecar deployment in each pod from which we want to collect metrics. This means that Telegraf is running in the same pod as the containers it monitors. Telegraf uses the [SQL Server input plugin](https://github.com/influxdata/telegraf/tree/master/plugins/inputs/sqlserver) to obtain metrics. For simplicity, the diagram doesn’t show the input plugins.
The injection of the Telegraf sidecar container is done by the Telegraf Operator.
Prometheus pulls metrics from Telegraf and sends them to [Sumo Logic Distribution for OpenTelemetry Collector](https://github.com/SumoLogic/sumologic-otel-collector) which enriches metadata and sends metrics to Sumo Logic.
In the logs pipeline, Sumo Logic Distribution for OpenTelemetry Collector collects logs written to standard out and forwards them to another instance of Sumo Logic Distribution for OpenTelemetry Collector, which enriches metadata and sends logs to Sumo Logic.
Follow the below instructions to set up the metric collection:
1. Configure Metrics Collection
* Setup Kubernetes Collection with the Telegraf operator
* Add annotations on your SQL Server pods
2. Configure Logs Collection
* Configure logging in SQL Server.
* Add labels on your SQL Server pods to capture logs from standard output.
* Collecting SQL Server Logs from a Log file.
**Prerequisites**
It’s assumed that you are using the latest helm chart version. If not, upgrade using the instructions [here](/docs/send-data/kubernetes).
#### Step 1: Configure Metrics Collection
This section explains the steps to collect SQL Server metrics from a Kubernetes environment.
In Kubernetes environments, we use the Telegraf Operator, which is packaged with our Kubernetes collection. [Learn more](/docs/send-data/collect-from-other-data-sources/collect-metrics-telegraf/telegraf-collection-architecture). Follow the steps listed below to collect metrics from a Kubernetes environment:
1. **[Set up Kubernetes Collection with the Telegraf Operator](/docs/send-data/collect-from-other-data-sources/collect-metrics-telegraf/install-telegraf)**.
2. **Add annotations on your SQL Server pods**. Before you add annotations, you need to create a login on every SQL Server pod you want to monitor, with following script:
```sql
USE master;
GO
CREATE LOGIN [Username_ENV_TO_BE_CHANGED] WITH PASSWORD=N'Password_CHANGE_ME';
GO
GRANT VIEW SERVER STATE TO [Username_ENV_TO_BE_CHANGED];
GO
GRANT VIEW ANY DEFINITION TO [Username_ENV_TO_BE_CHANGED];
GO
```
On your SQL Server Pods, add the following annotations:
```sql
annotations:
telegraf.influxdata.com/class: sumologic-prometheus
prometheus.io/scrape: "true"
prometheus.io/port: "9273"
telegraf.influxdata.com/inputs: |+
[[inputs.sql server]]
servers = ["Server=[**Classic UI**](/docs/get-started/sumo-logic-ui-classic). In the main Sumo Logic menu, select **Manage Data > Logs > Field Extraction Rules**. 2. Click the **+ Add Rule** button on the top right of the table. 3. The **Add Field Extraction Rule** form will appear. 4. Enter the following options: * **Rule Name**. Enter the name as **App Observability - Proxy**. * **Applied At**. Choose **Ingest Time** * **Scope**. Select **Specific Data** * **Scope**. Enter the following keyword search expression: ```sumo pod_labels_environment=* pod_labels_component=database pod_labels_db_system=* pod_labels_db_cluster=* ``` * **Parse Expression**. Enter the following parse expression: ```sumo if (!isEmpty(pod_labels_environment), pod_labels_environment, "") as environment | pod_labels_component as component | pod_labels_db_system as db_system | if (!isEmpty(pod_labels_db_cluster), pod_labels_db_cluster, null) as db_cluster ``` 1. Click **Save** to create the rule.
})
Telegraf uses the [SQL Server input plugin](https://github.com/influxdata/telegraf/tree/master/plugins/inputs/sqlserver) to obtain SQL Server metrics and the Sumo Logic output plugin to send the metrics to Sumo Logic. Logs from SQL Server are collected by a [Local File source](/docs/send-data/installed-collectors/sources/local-file-source).
The process to set up collection for SQL Server data is done through the following steps:
1. Configure Logs Collection
* Configure logging in SQL Server
* Configure Sumo Logic Installed Collector
* Configure a local file source
* Save
2. Configure Metrics Collection
* Configure a Hosted Collector
* Configure an HTTP Logs and Metrics Source
* Install Telegraf
* Configure and start Telegraf
#### 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.
1. **Make sure logging is turned on in SQL Server.** Follow [this documentation](https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/scm-services-configure-sql-server-error-logs?view=sql-server-ver15) to enable it.
The Microsoft SQL Server App’s queries and dashboards depend on logs from the SQL Server `ERRORLOG`, which is typically found at: `C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG \`. The `ERRORLOG` is typically in UTF-16LE encoding, but verify the file encoding used in your SQL Server configuration. On Windows, this can be found by using a tool such as Notepad++.
2. **Configure an Installed Collector.** If you have not already done so, install and configure an installed collector for Windows by [following the documentation](/docs/send-data/installed-collectors/windows).
3. **Configure a local file source** to ingest the `ERRORLOG`.
* On the Collection Management screen, click Add, next to the collector, then select Add Source.
* Select Local File as the source type.
* Configure the Local File Source fields as follows:
* Name (Required). Enter a name for the source.
* Description (Optional).
* File Path (Required). Enter the path to your `ERRORLOG` and be sure to account for log rotations by using a wildcard: `C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG*`
* Collection should begin. Set this for how far back historically you want to start collecting.
* Source Host (Optional). Sumo Logic uses the hostname assigned by the OS unless you enter a different host name
* Source Category (Recommended). Be sure to follow the [Best Practices for Source Categories](/docs/send-data/best-practices). A recommended Source Category may be `Prod/DB/MSSQL/ERRORLOG.`
* Fields. Set the following fields:
* `component = database`
* `db_system = SQLserver`
* `db_cluster = })
### General Health
The **SQL Server - General Health** dashboard gives you the overall health of SQL Server. Use this dashboard to analyze server events including stopped/up servers, and corresponding down/uptime, monitor disk space percentage utilization, wait time trend, app-domain issues by SQL server.
Use this dashboard to:
* Analyze server events including stopped/up servers, and corresponding down/uptime.
* Monitor server events trends including SQL Server wait time.
* Get insight into app-domain and percentage disk utilization issues by SQL Server.
})
### I/O
The **SQL Server - I/O** dashboard provides read and write bytes throughput by SQL Server.
Use this dashboard to:
* Analyze performance of SQL server by monitoring read and write bytes throughput of your SQL server instance.
})
### Latency
The **SQL Server - Latency** dashboard provides read and write latency trend by SQL Server.
Use this dashboard to:
* Analyze performance of SQL server by monitoring read and write latency of your SQL server instance.
})
### Operations
The **SQL Server - Operations** displays recent server configuration changes, number & type of configuration updates, error and warnings, high severity error, and warning trends.
Use this dashboard to:
* Get insights into configuration changes and updates to SQL server instance.
* Monitor any errors and warnings.
})
### Performance Counters
The **SQL Server - Performance Counters** dashboard shows performance counters related to database activities, SQL statistics, and buffer cache.
Use this dashboard to:
* Get insights into database activities such as errors/sec, lock timeouts/sec, and wait/sec, deadlocks/sec, and write transactions/sec.
* Monitor important SQL statistics such as login/sec, logout/sec, sql compilations/sec, processes blocked and batch requests/sec.
})
### Replication
The **SQL Server - Replication** dashboard provides insights into SQL Server replication performance. Use this dashboard to monitor bytes sent and received from replica instance, transaction delays, and mirrored white transaction/sec.
Use this dashboard to:
* Get insights into bytes sent to and received from replica instance.
* Analyze transaction delays, and mirrored white transaction/sec.
})
### Backup Restore Mirroring
The **SQL Server - Backup Restore Mirroring** provides information about:
* Transaction log backup events
* Database backup events
* Restore activities
* Backup failures and reasons
* Mirroring errors
})
## Create monitors for Microsoft SQL Server app
import CreateMonitors from '../../reuse/apps/create-monitors.md';