Configuring Monitoring for Microsoft SQL Server with GermainUX: Queries, KPIs, SLAs, and Alerts

⚙️ Configure Monitoring for Microsoft SQL Server

Configure GermainUX monitoring for Microsoft SQL Server to collect database metrics, execute custom SQL queries, create KPIs, define SLAs, configure alerts, and monitor application-specific database conditions.

🔍 Configure a Database Query Monitor

GermainUX Database Query Monitors execute SQL queries on a defined schedule and process the returned data for monitoring and analytics.

Navigate to:

Germain Workspace → Left Menu → Wizards → Database Query Monitor Deployment

Use the Wizard to create a SQL Server Query Monitor.

🗄️ Select the SQL Server Data Source

Select the SQL Server database connection configured during deployment.

Verify that the selected data source:

• Connects to the correct SQL Server instance

• Uses the appropriate credentials

• Has access to the required database

• Has permission to execute the monitoring query
  

✏️ Configure the SQL Query

Enter the SQL query that GermainUX should execute.

Queries can retrieve information from SQL Server system views or other tables accessible to the monitoring account.

For example, Query Monitors can be configured for:

• SQL Server performance metrics

• Active sessions

• Connections

• Application-specific database conditions

• Operational data

• Custom database KPIs

GermainUX is not limited to SQL Server system tables. Queries can monitor other database tables when the configured account has the required access.

🎯 Configure the KPI

Define the KPI associated with the data returned by the Query Monitor.

The KPI determines how the monitored information is represented and analyzed within GermainUX.

Configure the KPI according to the database condition or metric being monitored.

Examples might include:

• Query response time

• Active connection count

• Database condition

• Application transaction count

• Custom operational metric
  

  

📏 Configure the SLA

Create an SLA when the SQL Server KPI should be evaluated against an expected condition or threshold.

For a database KPI, configure a:

Fact-based SLA

Define the conditions that determine when the KPI is operating normally or requires attention.

The SLA can then be used to detect SQL Server conditions that require an operational response.

🔔 Configure Actions

Select the actions GermainUX should execute when the SLA condition is met.

Depending on your GermainUX configuration, actions can include:

• Alerts

• Reports

• Scripts

• Other configured operational actions

This allows SQL Server monitoring to move from detection to operational response.

For example:

Query Monitor → KPI → SLA → Alert / Report / Script

⚙️ Select the Monitoring Node & Engine

Configure where the SQL Server Query Monitor will execute.

Select:

Monitoring Node
The GermainUX Node from which the Engine operates.

Engine
The GermainUX Engine that will execute the monitor.

The selected Engine must have connectivity to the SQL Server instance.

⏱️ Configure the Schedule

Define how frequently GermainUX should execute the SQL query.

Choose a schedule based on:

• How quickly the condition needs to be detected

• How frequently the underlying data changes

• The operational importance of the KPI

• The cost of executing the query

Avoid unnecessarily frequent execution of resource-intensive queries.

✅ Verify the Monitor

After deploying the Query Monitor, verify that it is running successfully.

Locate the monitor in:

Germain State

Confirm that:

• The monitor is running

• SQL Server connectivity is successful

• The query executes successfully

• Data is being collected

• The KPI receives the expected data

• SLA evaluation operates as expected

📊 Configure SQL Server Performance Monitoring

Create Query Monitors and KPIs for the SQL Server performance information required by your environment.

For example:

• CPU-related database performance

• Memory utilization

• Disk I/O

• SQL Server performance counters

• Active sessions

• Connection activity

SQL Server performance information can be collected from system views such as:

sys.dm_os_performance_counters

and:

sys.dm_exec_sessions

provided the GermainUX monitoring account has the required permissions.

⏳ Configure Query Performance Monitoring

Configure monitoring for SQL queries that are important to application performance.

Depending on your monitoring requirements, analyze:

• Query execution

• Query response time

• Slow-running queries

• Execution plans

• Index usage

Create KPIs and SLAs for query conditions requiring proactive detection.

🔗 Configure Connection Monitoring

Monitor SQL Server sessions and database connection activity to identify conditions such as:

• Excessive active connections

• Unexpected connection growth

• Connection utilization

• Potential connection issues

Configure appropriate KPIs and thresholds according to the expected workload of the SQL Server environment.

📦 Configure Storage Monitoring

Configure monitoring for SQL Server database and filegroup storage.

Monitor conditions such as:

• Disk utilization

• Filegroup utilization

• Available capacity

• Data file growth

• Log file capacity

Configure alerts before storage reaches levels that could affect database operation.

💾 Configure Backup Monitoring

Configure SQL queries and KPIs to monitor SQL Server backup activity.

Monitor conditions such as:

• Backup completion

• Backup failure

• Last successful backup

• Backup frequency

• Recovery-related information

Configure SLAs and alerts according to your organization's backup and recovery requirements.

🚨 Configure Error Monitoring

Configure monitoring for SQL Server errors, warnings, or other database conditions requiring attention.

Error monitoring can be combined with:

• KPIs

• SLAs

• Alerts

• Reports

• Scripts

Focus alerts on database conditions requiring operational action rather than every informational SQL Server event.

🔍 Configure Application-Specific Database Monitoring

GermainUX can monitor SQL Server tables beyond the standard system tables and views.

This allows SQL Server monitoring to be extended to application-specific or business conditions.

For example:

Application Table → SQL Query → KPI → SLA → Alert / Action

This can be used to monitor application transactions, processing states, queues, failures, volumes, or other information stored within SQL Server.

Grant the GermainUX database account only the permissions required to access the selected data.

📊 Configure Dashboards & Reports

Use the SQL Server telemetry collected by GermainUX to create real-time dashboards and automated reports.

Examples include:

• SQL Server health

• Availability

• Query performance

• Connections

• Storage

• Backups

• Errors

• Custom KPIs

• SLA status

• Historical trends

Where appropriate, combine SQL Server telemetry with application and infrastructure data to provide end-to-end operational context.

🧪 Validate the Configuration

After configuring SQL Server monitoring, verify that:

• Queries execute successfully

• Expected data is collected

• KPIs are populated

• SLA conditions evaluate correctly

• Alerts and actions trigger as expected

• Monitoring executes according to schedule

• Query execution does not create unnecessary load on SQL Server

Adjust queries, schedules, KPIs, and thresholds as necessary for your environment.

ℹ️ Advanced Configuration

GermainUX SQL Server monitoring can be extended by creating additional Database Query Monitors for system views, application tables, and other database information.

This allows organizations to build monitoring specific to their SQL Server architecture and applications while using GermainUX analytics, dashboards, SLAs, alerts, and automation to operationalize the collected data.

Please contact us for any help.