SQL Server
The SQL Server Integration allows you to ingest and monitor the performance and health metrics of your Microsoft SQL Server databases. This integration is useful for tracking batch requests, query compilations, lock waits, page operations, transactions, and more.
Prerequisites
Middleware Host Agent (MW Agent) must be installed on your local machine. To install the MW Agent, see our Installation Guide.
Configuration
To set up the SQL Server Integration, you can configure the receiver using a Setting base approach. You may use this configuration approach to securely store your credentials and connection parameters.
Setting-Based Authentication
To set up the SQL Server integration, enter the required connection details directly in the provided form fields.
Required Fields
- Host - The machine name or IP address where SQL Server is running.
- Username - The username used to authenticate with SQL Server.
- Password - The password for the provided username.
- Server - The server IP (e.g., 0.0.0.0 for all interfaces).
- Port - The port SQL Server is listening on (default: 1433).
- Instance Name (Optional) - The name of the SQL Server instance, if applicable.
- Collection Interval (Optional) - The frequency at which data is collected. The default interval is already set to 10s.
- Computer Name (Optional) - The machine name where SQL Server is running. These fields must be entered in the UI to enable the SQL Server integration.
User-Based Authentication
Overview
To collect SQL Server metrics securely, a non-root user with specific permissions must be created. This guide outlines the steps to install MSSQL Server and SQLCMD using Docker and configure user-based authentication with appropriate permissions.
MSSQL Docker Configuration
We will deploy MSSQL Server and MSSQL Tools in a Docker container.
# Create a docker-compose.yml file with the following configuration:
version: '3.1'
services:
mssql:
container_name: mssql-db
hostname: mssql-db-host
image: mcr.microsoft.com/mssql/server:2022-latest
environment:
ACCEPT_EULA: "Y"
MSSQL_SA_PASSWORD: "<YourSecurePassword>"Deploy the container using:
# Start the MSSQL container docker-compose up -d
NOTE: Sometimes MSSQL server fails to start due to insufficient asynchronous I/O contexts. You can increase the contexts by:
sudo sysctl -w fs.aio-max-nr=1048576
User Authentication Setup
To enforce security, a dedicated non-root user (mw_user) will be created with restricted access.
Access MSSQL Container
# Execute the following command to enter the container as root: > docker exec -it --user root mssql-db bash
Step 1: Create a User
Execute the following command to create a new login (mw_user) and associate it with a database user:
/opt/mssql-tools18/bin/sqlcmd -S mssql-db -U sa -P '<YourSecurePassword>' -Q "USE master; CREATE LOGIN mw_user WITH PASSWORD = '<YourSecurePassword>'; CREATE USER mw_user FOR LOGIN mw_user;" -b -C
NOTE: The -C flag is used to trust the server certificate. Without it, you may encounter the error: "SSL Provider: certificate verify failed: self-signed certificate."
Verify the user creation:
/opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P '<YourSecurePassword>' -Q "SELECT 1" -C -b
If successful, you will see the following output:
1
Step 2: Grant Permissions to mw_user
Assign necessary permissions to mw_user:
/opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P '<YourSecurePassword>' -Q "USE master; GRANT VIEW SERVER STATE TO mw_user;" -C -b /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P '<YourSecurePassword>' -Q "USE master; GRANT SELECT ON sys.dm_exec_requests TO mw_user;" -C -b
Step 3: Verify User Permissions
Confirm that mw_user has the necessary access by running:
/opt/mssql-tools18/bin/sqlcmd -S localhost -U mw_user -P '<YourSecurePassword>' -C -Q "USE master; SELECT * FROM sys.dm_exec_requests;" -C -b
If the command executes successfully without errors, the user has the correct permissions.
Access Integrations
Step 1: Access SQL Server Integration
Log in to Middleware, navigate to the Installations Page in the bottom left corner, select All Integration and click SQL Server:
Step 2: Configure the integration SQL Server
To configure the integration of your Nginx Web Server with our platform.
To configure the integration of your SQL Server with our platform, update the configuration as follows:
- Collection Interval (Optional): Specify the interval at which telemetry data is collected. In this configuration, two intervals are defined—10s and 5s.
- Computer Name (Optional): Set the computer name for the SQL Server instance. Here, it is CustomServer.
- Instance Name (Optional): Define the specific SQL Server instance. Here, it is CustomInstance.
- Authentication: Provide authentication details (username and password). In this case, the username is sqlserver and the password is sqlserver.
- Server Address: Specify the server IP or hostname. Here, it is 0.0.0.0.
- Port: Define the port used by SQL Server. The default port is 1433.
Conclusion
This setup ensures that SQL Server metrics can be securely collected using a non-root user while maintaining the necessary permissions for observability. By following this guide, you establish a secure and controlled authentication mechanism for MSSQL in a Docker environment.
Visualize Analytics
Default SQL Server Dashboard
Once the SQL Server integration setup is complete, a new SQL Server-specific dashboard will appear in the Dashboard Builder. This default dashboard serves as a jumping off point for visualizing and analyzing SQL Server data.
Create SQL Server Widget
You can add SQL Server data to dashboards as a custom widget. Follow these steps to configure your widget:
- Select Widget Dashboard Navigate to the Create Widget screen.
- Configure Widget Profile On the Widget Profile screen, define the widget name and description.
- Set Up Configuration In the Widget Configuration screen, customize data settings, apply filters, and finalize widget preferences.
Alerts
Alerts can be configured for any SQL Server metrics. When creating a new rule, select the Database detection method and SQL Server database type for available metrics to appear in the Metrics dropdown list. Select the desired metric and continue configuring the alert conditions.
Metrics Collected
| Metric | Description |
|---|---|
sqlserver.batch.request.rate | Number of batch requests received by SQL Server. |
sqlserver.batch.sql_compilation.rate | Number of SQL compilations needed. |
sqlserver.batch.sql_recompilation.rate | Number of SQL recompilations needed. |
sqlserver.lock.wait.rate | Number of lock requests resulting in a wait. |
sqlserver.lock.wait_time.avg | Average wait time for all lock requests that had to wait. |
sqlserver.page.buffer_cache.hit_ratio | Pages found in the buffer pool without having to read from disk. |
sqlserver.page.checkpoint.flush.rate | Number of pages flushed by operations requiring dirty pages to be flushed. |
sqlserver.page.lazy_write.rate | Number of lazy writes moving dirty pages to disk. |
sqlserver.page.life_expectancy | Time a page will stay in the buffer pool. |
sqlserver.page.operation.rate | Number of physical database page operations issued. |
sqlserver.page.split.rate | Number of pages split as a result of overflowing index pages. |
sqlserver.transaction.rate | Number of transactions started for the database (not including XTP-only transactions). |
sqlserver.transaction.write.rate | Number of transactions that wrote to the database and committed. |
sqlserver.transaction_log.flush.data.rate | Total number of log bytes flushed. |
sqlserver.transaction_log.flush.rate | Number of log flushes. |
sqlserver.transaction_log.flush.wait.rate | Number of commits waiting for a transaction log flush. |
sqlserver.transaction_log.growth.count | Total number of transaction log expansions for a database. |
sqlserver.transaction_log.shrink.count | Total number of transaction log shrinks for a database. |
sqlserver.transaction_log.usage | Percent of transaction log space used. |
sqlserver.user.connection.count | Number of users connected to the SQL Server. |
sqlserver.database.count | The number of databases. |
sqlserver.database.io | The number of bytes of I/O on this file. |
sqlserver.database.latency | Total time that the users waited for I/O issued on this file. |
sqlserver.database.operations | The number of operations issued on the file. |
sqlserver.processes.blocked | The number of processes that are currently blocked. |
sqlserver.resource_pool.disk.throttled.read.rate | The number of read operations that were throttled in the last second. |
sqlserver.resource_pool.disk.throttled.write.rate | The number of write operations that were throttled in the last second. |
Troubleshooting
Missing Integrations Menu
If you do not see the Integrations Menu in Middlware, that means your account has not been granted Installation permissions. Contact your system administrator to add the Installation permission to your user role in Settings.
Next Steps
- How to Create Alerts
- Dashboard Basics & Customization
- Custom Telemetry Ingestion
- Getting Started With Real User Monitoring (RUM)
- Data Ingestion APIs
Need assistance or want to learn more about Middleware? Contact our support team at [email protected].