Microsoft SQL Server Integration

The Microsoft SQL Server integration package allows you to search, observe, and visualize the SQL Server audit logs, as well as performance and transaction log metrics, through Elasticsearch.

The Microsoft SQL Server integration collects two types of data streams: logs and metrics.

Log data streams provide records of events happening in Microsoft SQL Server:

• audit: Events from the configured Windows event log channel, providing detailed auditing information. See SQL Server Audit.
• logs: Error logs created by the Microsoft SQL server for troubleshooting and system events.

Other log sources, such as files, are not supported.

Find more details in Logs.

Metrics data streams provide insights into SQL Server performance and health:

• performance: Comprehensive performance counters and objects available on the server.
• transaction_log: Usage statistics and space utilization metrics for transaction logs.
• availability_groups: Health and synchronization metrics for Always On Availability Groups.

Find more details in Metrics.

You need Elasticsearch for storing and searching your data and Kibana for visualizing and managing it. You can use our hosted Elasticsearch Service on Elastic Cloud, which is recommended, or self-manage the Elastic Stack on your hardware.

Before you can start sending data to Elastic, make sure you have the necessary Microsoft SQL Server permissions.

If you browse Microsoft Developer Network (MSDN) for the following tables, you will find a "Permissions" section that defines the permission needed for each table (for example, the "Permissions" section on the sys.dm_db_log_space_usage page).

1. transaction_log:
   • sys.databases
   • sys.dm_db_log_space_usage
   • sys.dm_db_log_stats (DB_ID) (Available on SQL Server (MSSQL) 2016 (13.x) SP 2 and later)
2. performance:
   • sys.dm_os_performance_counters
3. availability_groups:
   • sys.availability_groups
   • sys.dm_hadr_availability_group_states

Please make sure the user has the permissions to system as well as user-defined databases. For the particular user used in the integration, the following requirements are met:

User setup options:

• Grant specific permissions as mentioned in the MSDN pages above.
• Alteratively, use sysadmin role (includes all required permissions): This can be configured via SQL Server Management Studio (SSMS) in Server Roles. Read more about joining a role in the SQL Server documentation.

User Mappings (using SQL Server Management Studio (SSMS)):

• Open SSMS and connect to your server.
• Navigate to "Object Explorer" > "Security" > "Logins".
• Right-click the user and select "Properties".
• In the "User Mapping" tab, select the appropriate database and grant the required permissions.

For step-by-step instructions on how to set up any integration, refer to the Getting started guide.

Below you'll find more specific details on setting up the Microsoft SQL Server integration.

Microsoft SQL Server has a feature that allows running multiple databases on the same host (or clustered hosts) with separate settings. Establish a named instance connection by using the instance name along with the hostname (e.g. host/instance_name or host:named_instance_port) to collect metrics. Details of the host configuration are provided below.

The data can be visualized in Kibana by filtering based on the instance name and server name. The instance name can be filtered by mssql.metrics.instance_name and the server name by mssql.metrics.server_name fields.

As part of the input configuration, you need to provide the user name, password and host details. The host configuration supports both named instances or default (no-name) instances, using the syntax below.

Connecting to Default Instance (host):

• host (e.g. localhost (Instance name is not needed when connecting to default instance))
• host:port (e.g. localhost:1433)

Connecting to Named Instance (host):

• host/instance_name (e.g. localhost/namedinstance_01)
• host:named_instance_port (e.g. localhost:60873)

There are several levels of auditing for SQL Server, depending on government or standards requirements for your installation. The SQL Server Audit feature enables you to audit server-level and database-level groups of events and individual events.

For more information on the different audit levels, refer to SQL Server Audit Action Groups and Actions. Then to enable auditing for SQL Server, refer to these instructions.

Collects SQL Server audit events from the specified windows event log channel.

The SQL Server log contains user-defined events and certain system events you can use for troubleshooting.

Read more in View the SQL Server error log in SQL Server Management Studio.

Collects the performance counter metrics. The dynamic counter feature provides flexibility to collect metrics by providing the counter as an input. This input can be a regular expression which will filter results based on pattern. For example, if %grant% is given as input, it will enable metrics collection for all of the counters with names like 'Memory Grants Pending', 'Active memory grants count' etc. MSSQL supports a limited set of regular expressions. For more details, refer to Pattern Matching in Search Conditions.

The feature merge_results has been introduced in 8.4 beats which creates a single event by combining the metrics in a single event. For more details, refer to SQL module.

Read more in instructions about each performance counter metrics.

The system-level database transaction_log metrics for SQL Server instances are collected by default. Metrics for user-level databases can be collected by specifying a list of user databases or by enabling the Fetch from all databases toggle to collect metrics from all databases on the server.

Read more in instructions and the operations supported by transaction log.

To simplify the process of fetching metrics from all databases on the server, you can enable the Fetch from all databases toggle when configuring the integration. This field overrides manually entered database names in the Databases input and instead fetches the required transaction_log metrics from all databases, including system and user-defined databases.

Keep in mind that this feature is disabled by default and needs to be manually enabled to be activated.

When the password contains special characters, pass these special characters using URL encoding.

Collects metrics related to Always On Availability Groups, including replica status and synchronization health. This dataset queries the following SQL Server tables:

• sys.availability_groups
• sys.dm_hadr_availability_group_states

Prerequisites: To collect Availability Groups metrics, ensure the following:

1. Always On Availability Groups feature is enabled on the SQL Server instance.
2. The user account configured for the integration has VIEW SERVER STATE and VIEW ANY DEFINITION permissions. Additionally look at section Microsoft SQL Server permissions.

Read more in Monitor Availability Groups and Always On Availability Groups overview.

The SQL Server audit dataset provides events from the configured Windows event log channel. All SQL Server audit-specific fields are available in the sqlserver.audit field group.

ECS Field Reference

Please refer to the following document for detailed information on ECS fields.