Skip to main content

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.

Requirements

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.

Note. For more information regarding Microsoft SQL Server Installation, click the link (https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server?view=sql-server-ver16) for more info.

Microsoft SQL Server permissions

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.

  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

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.

Setup

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

Named Instance

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.

Query by Instance Name or Server Name in Kibana

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.

Host Configuration

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.

Note: This integration supports collecting metrics from a single host. For multi-host metrics, each host can be run as a new integration.

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)

 

If you need further assistance, kindly contact our support at info@cytechint.com for prompt assistance and guidance.

Back to top