# Microsoft SQL Server

> Monitor Microsoft SQL Server performance, sessions, I/O operations, and logs with OpenTelemetry on Windows for comprehensive database observability

Source: https://last9.io/docs/integrations/databases/microsoft-sql-server/

Use OpenTelemetry to monitor Microsoft SQL Server performance and send telemetry data to Last9. This integration provides comprehensive monitoring of SQL Server performance metrics and error logs on Windows, including database I/O, buffer cache statistics, connections, and system resource utilization.

## Prerequisites

Before setting up Microsoft SQL Server monitoring, ensure you have:

- **Microsoft SQL Server**: SQL Server 2012 or higher installed and running on Windows
- **Windows Server**: Windows Server 2016 or higher, or Windows 10/11 for development
- **Administrative Access**: SQL Server administrator privileges and Windows admin access
- **Network Connectivity**: Outbound HTTPS access to Last9 endpoints
- **Last9 Account**: With OpenTelemetry integration credentials
- **OpenTelemetry Collector**: v0.118.0+ for base metrics, **v0.147.0+** for slow query insights (top queries, query samples, execution plans)

1. **Verify SQL Server Installation**

   Ensure SQL Server is installed and running:

   ```powershell
   # Check if SQL Server services are running
   Get-Service | Where-Object {$_.Name -like "*SQL*"}

   # Expected services:
   # - MSSQLSERVER (SQL Server Database Engine)
   # - SQLSERVERAGENT (SQL Server Agent)
   ```

   If services are not running, start them:

   ```powershell
   Start-Service MSSQLSERVER
   Start-Service SQLSERVERAGENT
   ```

2. **Configure SQL Server Network Access**

   Enable TCP/IP connections for SQL Server:

**SQL Server Configuration Manager**

   1. Open **SQL Server Configuration Manager**
   2. Navigate to **SQL Server Network Configuration** → **Protocols for MSSQLSERVER**
   3. Right-click **TCP/IP** → **Enable**
   4. Right-click **TCP/IP** → **Properties** → **IP Addresses** tab
   5. Set **TCP Port = 1433** for IPALL
   6. Click **OK** and restart SQL Server

**PowerShell Commands**

   Configure Windows Firewall and verify network settings:

   ```powershell
   # Add Windows Firewall rule for SQL Server
   netsh advfirewall firewall add rule name="SQL Server" dir=in action=allow protocol=TCP localport=1433

   # Restart SQL Server services
   Restart-Service MSSQLSERVER

   # Verify port is listening
   netstat -an | findstr 1433
   # Should show: TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING
   ```

3. **Enable Mixed Mode Authentication**

   Configure SQL Server to allow SQL Server authentication:

**SQL Server Management Studio**

   1. Open **SQL Server Management Studio (SSMS)**
   2. Connect to your SQL Server instance
   3. Right-click server name → **Properties**
   4. Go to **Security** tab
   5. Select **"SQL Server and Windows Authentication mode"**
   6. Click **OK** and restart SQL Server

**T-SQL Commands**

   ```sql
   -- Enable mixed mode authentication
   EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
       N'Software\Microsoft\MSSQLServer\MSSQLServer',
       N'LoginMode', REG_DWORD, 2;

   -- Restart required for changes to take effect
   ```

   After running this command, restart SQL Server:

   ```powershell
   Restart-Service MSSQLSERVER
   ```

4. **Create SQL Server Monitoring User**

   Create a dedicated user for OpenTelemetry monitoring with necessary permissions:

   ```sql
   -- Connect using SQL Server Management Studio as administrator

   -- Create login and user
   USE master;
   CREATE LOGIN otel_monitor WITH PASSWORD = 'SecurePassword123!';
   CREATE USER otel_monitor FOR LOGIN otel_monitor;

   -- Grant essential server-level permissions
   GRANT VIEW SERVER STATE TO otel_monitor;
   GRANT VIEW ANY DEFINITION TO otel_monitor;
   ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER otel_monitor;

   -- Grant permissions for SQL Agent job monitoring
   USE msdb;
   CREATE USER otel_monitor FOR LOGIN otel_monitor;
   GRANT SELECT ON dbo.sysjobs TO otel_monitor;
   GRANT SELECT ON dbo.sysjobhistory TO otel_monitor;
   GRANT SELECT ON dbo.sysjobactivity TO otel_monitor;

   -- Test the connection
   ```

   **Test the monitoring user connection:**

   ```powershell
   # Test connection with new user
   sqlcmd -S localhost -U otel_monitor -P SecurePassword123! -Q "SELECT @@VERSION"
   ```

5. **Configure Environment Variables for Security**

   Store sensitive credentials in environment variables:

**System-Level (Recommended)**

   Set system-level environment variables (requires Administrator privileges):

   ```powershell
   # Set system environment variable
   [Environment]::SetEnvironmentVariable("MSSQL_PASSWORD", "SecurePassword123!", "Machine")
   [Environment]::SetEnvironmentVariable("LAST9_OTLP_ENDPOINT", "your-last9-endpoint", "Machine")
   [Environment]::SetEnvironmentVariable("LAST9_OTLP_AUTH_HEADER", "your-auth-header", "Machine")

   # Verify variables are set
   [Environment]::GetEnvironmentVariable("MSSQL_PASSWORD", "Machine")
   ```

**User-Level**

   Set user-level environment variables:

   ```powershell
   # Set for current user
   [Environment]::SetEnvironmentVariable("MSSQL_PASSWORD", "SecurePassword123!", "User")
   [Environment]::SetEnvironmentVariable("LAST9_OTLP_ENDPOINT", "your-last9-endpoint", "User")
   [Environment]::SetEnvironmentVariable("LAST9_OTLP_AUTH_HEADER", "your-auth-header", "User")
   ```

6. **Install OpenTelemetry Collector**

   Download and install the OpenTelemetry Collector for Windows:

   ```powershell
   # Download the MSI installer
   Invoke-WebRequest -Uri "https://github.com/open-telemetry/opentelemetry-collector-releases/releases/download/v0.147.0/otelcol-contrib_0.147.0_windows_x64.msi" -OutFile "otelcol-contrib_0.147.0_windows_x64.msi"

   # Install using MSI
   msiexec /i otelcol-contrib_0.147.0_windows_x64.msi /quiet

   # Or double-click the MSI file to install using the GUI
   ```

   **Alternative download methods if GitHub is unreliable:**

   ```powershell
   # Using curl with redirect follow
   curl -L -o otelcol-contrib_0.147.0_windows_x64.msi "https://github.com/open-telemetry/opentelemetry-collector-releases/releases/download/v0.147.0/otelcol-contrib_0.147.0_windows_x64.msi"

   # With extended timeout
   Invoke-WebRequest -Uri "https://github.com/open-telemetry/opentelemetry-collector-releases/releases/download/v0.147.0/otelcol-contrib_0.147.0_windows_x64.msi" -OutFile "otelcol-contrib_0.147.0_windows_x64.msi" -UseBasicParsing -TimeoutSec 300
   ```

7. **Create OpenTelemetry Collector Configuration**

   Create the collector configuration file:

   ```powershell
   # Create the configuration file
   New-Item -Path "C:\Program Files\OpenTelemetry Collector\config.yaml" -ItemType File -Force
   notepad "C:\Program Files\OpenTelemetry Collector\config.yaml"
   ```

   Add the following comprehensive configuration:

   ```yaml
   receivers:
     # SQL Server Error Log Collection
     filelog:
       include:
         - 'C:\Program Files\Microsoft SQL Server\MSSQL*.MSSQLSERVER\MSSQL\Log\ERRORLOG*'
       include_file_path: true
       # SQL Server ERRORLOG uses UTF-16 LE encoding
       encoding: utf-16le
       retry_on_failure:
         enabled: true

     # System Metrics Collection
     hostmetrics:
       collection_interval: 60s
       scrapers:
         cpu:
           metrics:
             system.cpu.logical.count:
               enabled: true
         memory:
           metrics:
             system.memory.utilization:
               enabled: true
             system.memory.limit:
               enabled: true
         disk:
         filesystem:
           metrics:
             system.filesystem.utilization:
               enabled: true
         network:
         paging:
         load:
         process:
           mute_process_user_error: true
           mute_process_io_error: true
           mute_process_exe_error: true
           metrics:
             process.cpu.utilization:
               enabled: true
             process.memory.utilization:
               enabled: true

     # SQL Server Performance Metrics
     sqlserver:
       server: localhost
       port: 1433
       username: otel_monitor
       password: ${env:MSSQL_PASSWORD} # Using environment variable
       collection_interval: 60s

       # Explicit metrics allowlist — only keys in metadata.yaml at your version
       # are valid here. Any unlisted key causes a hard startup failure.
       metrics:
         sqlserver.user.connection.count:
           enabled: true
         sqlserver.processes.blocked:
           enabled: true
         sqlserver.batch.request.rate:
           enabled: true
         sqlserver.batch.sql_compilation.rate:
           enabled: true
         sqlserver.batch.sql_recompilation.rate:
           enabled: true
         sqlserver.transaction.rate:
           enabled: true
         sqlserver.transaction.write.rate:
           enabled: true
         sqlserver.lock.wait.rate:
           enabled: true
         sqlserver.lock.wait_time.avg:
           enabled: true
         sqlserver.deadlock.rate:
           enabled: true
         sqlserver.page.buffer_cache.hit_ratio:
           enabled: true
         sqlserver.page.life_expectancy:
           enabled: true
         sqlserver.page.split.rate:
           enabled: true
         sqlserver.page.lazy_write.rate:
           enabled: true
         sqlserver.page.checkpoint.flush.rate:
           enabled: true
         sqlserver.page.operation.rate:
           enabled: true
         sqlserver.transaction_log.growth.count:
           enabled: true
         sqlserver.transaction_log.shrink.count:
           enabled: true
         sqlserver.transaction_log.flush.rate:
           enabled: true
         sqlserver.transaction_log.flush.wait.rate:
           enabled: true
         sqlserver.transaction_log.usage:
           enabled: true
         sqlserver.transaction_log.flush.data.rate:
           enabled: true
         sqlserver.database.latency:
           enabled: true
         sqlserver.database.operations:
           enabled: true

       # Slow query insights (requires v0.147.0+)
       # Emitted as logs — top N slowest queries with SQL text, execution plan,
       # CPU time, reads/writes, and real-time in-flight query snapshots.
       events:
         db.server.top_query:
           enabled: true
         db.server.query_sample:
           enabled: true
       top_query_collection:
         lookback_time: 120s # Look back 2 minutes for finished queries
         max_query_sample_count: 2000 # Delta tracking cache size
         top_query_count: 50 # Top 50 slowest per scrape
         collection_interval: 60s # Collect every 60 seconds
       query_sample_collection:
         max_rows_per_query: 100 # Max in-flight queries per snapshot

   processors:
     batch:
       timeout: 30s
       send_batch_size: 10000
       send_batch_max_size: 10000

     # System resource detection
     resourcedetection/system:
       detectors: ["system"]
       system:
         hostname_sources: ["os"]
         resource_attributes:
           host.ip:
             enabled: true
           host.cpu.model.name:
             enabled: true

     # Cloud resource detection (uncomment your cloud provider's detector)
     # Valid AWS detectors: ec2, ecs, eks, lambda (NOT "aws")
     resourcedetection/cloud:
       detectors: ["env"]
       timeout: 2s
       override: false

     # Log transformation and enrichment
     transform/logs:
       flatten_data: true
       log_statements:
         - context: log
           statements:
             - set(observed_time, Now())
             - set(time_unix_nano, observed_time_unix_nano) where time_unix_nano == 0
             - set(resource.attributes["service.name"], "mssql-server")
             - set(resource.attributes["deployment.environment"], "production")
             - set(resource.attributes["database.system"], "mssql")

     # Tag slow query and query sample logs for filtering
     transform/query_tags:
       log_statements:
         - context: log
           conditions:
             - attributes["event.name"] == "db.server.top_query"
           statements:
             - set(attributes["slow_query"], "true")
         - context: log
           conditions:
             - attributes["event.name"] == "db.server.query_sample"
           statements:
             - set(attributes["query_sample"], "true")

     # Metrics transformation and enrichment
     transform/metrics:
       metric_statements:
         - context: datapoint
           statements:
             - set(resource.attributes["service.name"], "mssql-server")
             - set(resource.attributes["deployment.environment"], "production")
             - set(resource.attributes["database.system"], "mssql")

     # Host metrics enrichment
     transform/hostmetrics:
       metric_statements:
         - context: datapoint
           statements:
             - set(attributes["host.name"], resource.attributes["host.name"])
             - set(attributes["cloud.account.id"], resource.attributes["cloud.account.id"])
             - set(attributes["cloud.availability_zone"], resource.attributes["cloud.availability_zone"])
             - set(attributes["cloud.platform"], resource.attributes["cloud.platform"])
             - set(attributes["cloud.provider"], resource.attributes["cloud.provider"])
             - set(attributes["cloud.region"], resource.attributes["cloud.region"])

   exporters:
     otlp/last9:
       endpoint: "${env:LAST9_OTLP_ENDPOINT}"
       headers:
         "Authorization": "${env:LAST9_OTLP_AUTH_HEADER}"
     debug:
       verbosity: detailed

   service:
     pipelines:
       # Log pipeline for SQL Server error logs + slow query insights
       logs:
         receivers: [filelog, sqlserver]
         processors:
           [
             batch,
             resourcedetection/system,
             resourcedetection/cloud,
             transform/logs,
             transform/query_tags,
           ]
         exporters: [otlp/last9]

       # Metrics pipeline for SQL Server performance metrics
       metrics/sqlserver:
         receivers: [sqlserver]
         processors:
           [
             batch,
             resourcedetection/system,
             resourcedetection/cloud,
             transform/metrics,
           ]
         exporters: [otlp/last9]

       # Metrics pipeline for host system metrics
       metrics/host:
         receivers: [hostmetrics]
         processors:
           [
             batch,
             resourcedetection/system,
             resourcedetection/cloud,
             transform/hostmetrics,
           ]
         exporters: [otlp/last9]
   ```

8. **Configure OpenTelemetry Collector Service**

   Configure the Windows service to use the correct configuration:

   ```powershell
   # Stop the service if it's running
   Stop-Service otelcol-contrib -ErrorAction SilentlyContinue

   # Configure the service with correct config file path and feature flags
   sc.exe config otelcol-contrib binPath= "`"C:\Program Files\OpenTelemetry Collector\otelcol-contrib.exe`" --config `"C:\Program Files\OpenTelemetry Collector\config.yaml`" --feature-gates transform.flatten.logs"

   # Set service to start automatically
   Set-Service -Name otelcol-contrib -StartupType Automatic

   # Verify service configuration
   sc.exe qc otelcol-contrib
   ```

   **Important:** Note the space after `binPath=` - this is required by `sc.exe` syntax!

9. **Start and Verify OpenTelemetry Service**

   Start the service and verify it's working:

   ```powershell
   # Start the service
   Start-Service otelcol-contrib

   # Check service status
   Get-Service otelcol-contrib

   # Expected output: Status should be "Running"
   ```

   **If the service fails to start, run in foreground mode for debugging:**

   ```powershell
   # Run collector in foreground to see detailed errors
   & "C:\Program Files\OpenTelemetry Collector\otelcol-contrib.exe" --config "C:\Program Files\OpenTelemetry Collector\config.yaml" --feature-gates transform.flatten.logs
   ```

## Understanding SQL Server Monitoring

The integration collects comprehensive SQL Server telemetry:

### SQL Server Performance Metrics

- **Database I/O Statistics**: Read/write operations, latency, and throughput per database
- **Buffer Cache Statistics**: Buffer cache hit ratio, page life expectancy, memory pressure
- **Batch Requests**: Number of batch requests per second (key performance indicator)
- **Active Connections**: Current user connections and session counts
- **Lock Statistics**: Lock waits, deadlocks, and lock wait time
- **Wait Statistics**: Types of waits and their duration (identifying performance bottlenecks)
- **Transaction Metrics**: Transactions per second, transaction log usage

### SQL Server Error Logs

- **Startup and Shutdown Events**: Service lifecycle events
- **Error Messages**: Application errors, I/O errors, and system messages
- **Authentication Events**: Failed login attempts and security-related events
- **Backup and Restore Operations**: Job completion and failure notifications
- **Configuration Changes**: Dynamic configuration updates

### Host System Metrics

- **CPU Utilization**: Processor usage and load from Windows host
- **Memory Usage**: Available memory, used memory, and memory pressure
- **Disk I/O**: Read/write operations, latency, and throughput
- **Filesystem Metrics**: Disk space utilization and available capacity
- **Network Throughput**: Bytes sent/received and network errors

### Slow Query Insights (v0.147.0+)

When `events` are enabled in the collector configuration, the `sqlserver` receiver emits two types of log events for query-level visibility:

**Top Queries (`db.server.top_query`)** — collected every 60 seconds from `sys.dm_exec_query_stats`:

| Log Attribute                    | Description                           |
| -------------------------------- | ------------------------------------- |
| `db.query.text`                  | SQL query text (obfuscated)           |
| `sqlserver.query_plan`           | Execution plan (obfuscated)           |
| `sqlserver.total_elapsed_time`   | Total elapsed time (delta, seconds)   |
| `sqlserver.total_worker_time`    | Total CPU time (delta, seconds)       |
| `sqlserver.execution_count`      | Execution count (delta)               |
| `sqlserver.total_logical_reads`  | Logical reads (delta)                 |
| `sqlserver.total_logical_writes` | Logical writes (delta)                |
| `sqlserver.total_physical_reads` | Physical reads (delta)                |
| `sqlserver.total_rows`           | Total rows returned (delta)           |
| `sqlserver.query_hash`           | Query hash for deduplication          |
| `sqlserver.procedure_name`       | Stored procedure name (if applicable) |

**Query Samples (`db.server.query_sample`)** — real-time snapshot from `sys.dm_exec_requests`:

| Log Attribute                          | Description                                |
| -------------------------------------- | ------------------------------------------ |
| `db.query.text`                        | Currently executing SQL text (obfuscated)  |
| `sqlserver.total_elapsed_time`         | Elapsed time (seconds)                     |
| `sqlserver.cpu_time`                   | CPU time (seconds)                         |
| `sqlserver.wait_type`                  | Current wait type (e.g., `PAGEIOLATCH_SH`) |
| `sqlserver.wait_time`                  | Wait duration                              |
| `sqlserver.blocking_session_id`        | Blocking session (0 if none)               |
| `sqlserver.reads` / `sqlserver.writes` | Physical I/O                               |
| `sqlserver.logical_reads`              | Logical reads                              |
| `sqlserver.row_count`                  | Rows affected                              |
| `sqlserver.request_status`             | `running`, `suspended`, etc.               |

These events are emitted as **logs** through the logs pipeline. Ensure the `sqlserver` receiver is included in your logs pipeline receivers.

:::note[Collector Version]
Slow query insights require **otel-collector-contrib v0.147.0 or later**. Earlier versions only collect performance counter metrics. The `events`, `top_query_collection`, and `query_sample_collection` config keys are not available in older versions.
:::

## Verification and Testing

1. **Check Service Status**

   Verify all services are running properly:

   ```powershell
   # Check OpenTelemetry service status
   Get-Service otelcol-contrib

   # Check SQL Server service status
   Get-Service MSSQLSERVER, SQLSERVERAGENT

   # All services should show "Running" status
   ```

2. **Test SQL Server Connectivity**

   Verify the monitoring user can connect to SQL Server:

   ```powershell
   # Test connection with monitoring user
   sqlcmd -S localhost -U otel_monitor -P SecurePassword123! -C -Q "SELECT @@VERSION"

   # Should return SQL Server version information without errors
   ```

3. **View OpenTelemetry Collector Logs**

   Check the collector logs for any issues:

   ```powershell
   # View recent logs in Event Viewer
   Get-EventLog -LogName Application -Source otelcol-contrib -Newest 20

   # Or open Event Viewer GUI
   eventvwr.msc
   # Navigate to: Windows Logs > Application > Filter by Source: otelcol-contrib
   ```

   **What to look for:**

   - ✅ "Everything is ready. Begin running and processing data."
   - ✅ No SQL Server connection errors
   - ✅ No authentication failures
   - ✅ Successful data export to Last9 endpoint

4. **Generate Test Data**

   Create test activity to verify monitoring:

**Generate Test Logs**

   ```sql
   -- Generate a test error log entry
   RAISERROR('OTEL TEST: Verification test entry for Last9', 16, 1) WITH LOG;
   ```

**Generate Database Activity**

   ```sql
   -- Create test database activity
   SELECT COUNT(*) FROM sys.tables;
   SELECT COUNT(*) FROM sys.databases;
   SELECT COUNT(*) FROM sys.dm_exec_sessions;

   -- Check current connections
   SELECT
       DB_NAME(database_id) as database_name,
       COUNT(*) as connection_count
   FROM sys.dm_exec_sessions
   WHERE database_id > 0
   GROUP BY database_id;
   ```

**Test Failed Login**

   ```powershell
   # Generate failed login event (creates security log entries)
   sqlcmd -S localhost -U wrong_user -P WrongPassword! -C
   ```

5. **Verify Data in Last9**

   Log into your Last9 account and verify data is being received:

   1. Navigate to [Last9 Dashboard](https://app.last9.io)
   2. Check **Metrics** section for:
      - `sqlserver.*` metrics (e.g., `sqlserver.batch.request.rate`)
      - `system.*` metrics (e.g., `system.cpu.utilization`)
   3. Check **Logs** section for:
      - SQL Server ERRORLOG entries
      - Service name: "mssql-server"

   **Expected timeline:** Data should appear within 1-2 minutes of starting the service.

## Key Metrics to Monitor

### Critical Performance Indicators

| Metric                                  | Description                            | Alert Threshold          |
| --------------------------------------- | -------------------------------------- | ------------------------ |
| `sqlserver.batch.request.rate`          | Batch requests per second              | Baseline ±50%            |
| `sqlserver.user.connection.count`       | Active user connections                | > 80% of max connections |
| `sqlserver.page.buffer_cache.hit_ratio` | Buffer cache hit percentage            | < 90%                    |
| `sqlserver.page.life_expectancy`        | Page life expectancy in seconds        | < 300 seconds            |
| `sqlserver.lock.wait_time.avg`          | Average lock wait time in milliseconds | > 500ms average          |

### Capacity Planning

| Metric                                           | Description             | Monitoring Focus  |
| ------------------------------------------------ | ----------------------- | ----------------- |
| `system.cpu.utilization`                         | CPU usage percentage    | Sustained > 80%   |
| `system.memory.utilization`                      | Memory usage percentage | > 80% utilization |
| `system.filesystem.utilization`                  | Disk space usage        | > 85% full        |
| `sqlserver.database.latency` (`direction=read`)  | Database read latency   | > 20ms average    |
| `sqlserver.database.latency` (`direction=write`) | Database write latency  | > 10ms average    |

### Health Monitoring

| Metric                        | Description             | Alert Condition    |
| ----------------------------- | ----------------------- | ------------------ |
| `sqlserver.database.count`    | Number of databases     | Unexpected changes |
| `sqlserver.deadlock.rate`     | Deadlocks per second    | > 0                |
| `sqlserver.processes.blocked` | Blocked processes count | > 0                |
| `system.disk.io`              | Disk I/O operations/s   | Increasing trend   |

## Troubleshooting

### Service Issues

**OpenTelemetry Service Won't Start:**

```powershell
# Check Event Viewer for specific errors
Get-EventLog -LogName Application -Source otelcol-contrib -Newest 10

# Test configuration in foreground mode
& "C:\Program Files\OpenTelemetry Collector\otelcol-contrib.exe" --config "C:\Program Files\OpenTelemetry Collector\config.yaml" --feature-gates transform.flatten.logs

# Verify config file syntax and path
Test-Path "C:\Program Files\OpenTelemetry Collector\config.yaml"
```

### SQL Server Connection Issues

**Cannot Connect to SQL Server:**

```powershell
# Verify SQL Server is running
Get-Service MSSQLSERVER

# Check if TCP/IP is enabled and port 1433 is listening
netstat -an | findstr 1433

# Test connection manually
sqlcmd -S localhost -U otel_monitor -P SecurePassword123! -C -Q "SELECT @@VERSION"
```

**Authentication Failures:**

```sql
-- Verify Mixed Mode authentication is enabled
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS 'Windows Authentication Only';
-- Should return 0 for Mixed Mode

-- Check if monitoring user exists
SELECT name, is_disabled FROM sys.server_principals WHERE name = 'otel_monitor';

-- Verify user permissions
SELECT
    p.state_desc,
    p.permission_name,
    s.name
FROM sys.server_permissions p
    LEFT JOIN sys.server_principals s ON p.grantee_principal_id = s.principal_id
WHERE s.name = 'otel_monitor';
```

### Log Collection Issues

**SQL Server Logs Not Being Collected:**

```powershell
# Verify ERRORLOG file path and permissions
Get-ChildItem "C:\Program Files\Microsoft SQL Server\MSSQL*.MSSQLSERVER\MSSQL\Log\ERRORLOG*"

# Check if service has read access
icacls "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log"

# Generate test log entry
sqlcmd -S localhost -U otel_monitor -P SecurePassword123! -C -Q "RAISERROR('Test log entry', 16, 1) WITH LOG;"
```

### Network and Firewall Issues

**No Data Reaching Last9:**

```powershell
# Test outbound connectivity to Last9
Test-NetConnection -ComputerName your-last9-endpoint-hostname -Port 443

# Check Windows Firewall rules
Get-NetFirewallRule -DisplayName "*SQL*" | Select-Object DisplayName, Enabled, Direction

# Verify environment variables are set
Get-ChildItem Env: | Where-Object {$_.Name -like "*LAST9*"}
```

### Environment Variables Issues

**Environment Variables Not Working:**

```powershell
# Verify environment variables are set at Machine level
[Environment]::GetEnvironmentVariable("MSSQL_PASSWORD", "Machine")
[Environment]::GetEnvironmentVariable("LAST9_OTLP_ENDPOINT", "Machine")

# Restart service to reload environment variables
Restart-Service otelcol-contrib

# Test with hardcoded values temporarily (remove after testing)
```

## Advanced Configuration

### Multi-Instance Monitoring

Monitor multiple SQL Server instances:

```yaml
receivers:
  sqlserver/instance1:
    server: localhost
    port: 1433
    instance: MSSQLSERVER
    username: otel_monitor
    password: ${env:MSSQL_PASSWORD}

  sqlserver/instance2:
    server: localhost
    port: 1434
    instance: SQL2019
    username: otel_monitor
    password: ${env:MSSQL_PASSWORD}

service:
  pipelines:
    metrics/sql1:
      receivers: [sqlserver/instance1]
      processors: [batch, transform/metrics]
      exporters: [otlp/last9]
    metrics/sql2:
      receivers: [sqlserver/instance2]
      processors: [batch, transform/metrics]
      exporters: [otlp/last9]
```

### Custom Log Parsing

Parse SQL Server log formats:

```yaml
processors:
  transform/parse_sql_logs:
    log_statements:
      - context: log
        conditions:
          - body matches ".*Error.*"
        statements:
          - set(attributes["log.level"], "ERROR")
          - set(attributes["sql.error"], "true")
      - context: log
        conditions:
          - body matches ".*Login failed.*"
        statements:
          - set(attributes["log.level"], "WARN")
          - set(attributes["sql.login_failed"], "true")
```

### Performance Optimization

Optimize for high-volume environments:

```yaml
receivers:
  sqlserver:
    collection_interval: 30s # More frequent collection

processors:
  batch:
    timeout: 10s
    send_batch_size: 5000
    send_batch_max_size: 8000

  memory_limiter:
    limit_mib: 512
    spike_limit_mib: 128
```

## Best Practices

### Security

- **Environment Variables**: Store all sensitive credentials in environment variables
- **Least Privilege**: Grant monitoring user only necessary permissions
- **Network Security**: Use Windows Firewall to restrict SQL Server access
- **Audit Access**: Monitor monitoring user access through SQL Server audit logs

### Performance

- **Collection Intervals**: Balance monitoring frequency with performance impact
- **Resource Limits**: Set appropriate memory limits for the collector
- **Batch Processing**: Optimize batch sizes for efficient data transmission
- **Log Rotation**: Monitor SQL Server ERRORLOG rotation and retention

### Monitoring Strategy

- **Baseline Metrics**: Establish performance baselines for alerting
- **Multi-Layer Monitoring**: Monitor application, database, and system layers
- **Proactive Alerts**: Set up alerts for critical metrics before problems occur
- **Capacity Planning**: Use trends for proactive capacity planning

### Windows Administration

- **Service Management**: Configure services for automatic startup and recovery
- **Event Log Monitoring**: Regular review of Windows Event Logs
- **System Updates**: Keep Windows and SQL Server updated with security patches
- **Backup Strategy**: Ensure monitoring doesn't interfere with backup operations

## Need Help?

If you encounter any issues or have questions:

- Join our [Discord community](https://discord.com/channels/652153247672729619/652153247672729621) for real-time support
- Contact our support team at [support@last9.io](mailto:support@last9.io)