Troubleshooting Guide

🔧 Common Issues and Solutions

This guide covers the most common issues encountered during the Azure Stream Analytics Workshop and their solutions.

🚨 Stream Analytics Job Issues

Issue: Job Won’t Start

Symptoms:

Job remains in “Starting” state for more than 5 minutes
Error message: “Job failed to start”

Solutions:

Check Input Connections:

# Test Event Hub connection
az eventhubs eventhub show --resource-group rg-streamanalytics-workshop --namespace-name YOUR_NAMESPACE --name telemetry-data

Verify Output Connections:
- Test blob storage access permissions
- Check SQL Database firewall rules
- Verify Power BI authentication
Review Query Syntax:
- Use Query Test feature to validate syntax
- Check for missing TIMESTAMP BY clause
- Verify input/output aliases match

Issue: No Data Processing

Symptoms:

Input events showing 0
No output data being generated

Solutions:

Check Data Source:

# Verify Event Hub has data
az monitor metrics list --resource YOUR_EVENTHUB_RESOURCE_ID --metric IncomingMessages --interval PT1M

Verify Input Configuration:
- Consumer group not conflicting with other consumers
- Correct Event Hub name and namespace
- Valid connection string
Check Event Hub Data:
- Navigate to Event Hub in portal
- Use “Process Data” → “Explore” to see sample events
- Verify JSON format is correct

Issue: High Streaming Unit (SU) Usage

Symptoms:

SU% consistently above 80%
Job performance degraded

Solutions:

Optimize Query:

-- ❌ Avoid high cardinality grouping
GROUP BY deviceId, DATEPART(second, timestamp)
   
-- ✅ Use lower cardinality grouping
GROUP BY SUBSTRING(deviceId, 1, 6), TumblingWindow(minute, 5)

Reduce Window Overlap:

-- ❌ High overlap causing performance issues
GROUP BY HoppingWindow(minute, 10, 1)  -- 90% overlap
   
-- ✅ Reasonable overlap
GROUP BY HoppingWindow(minute, 10, 5)  -- 50% overlap

Scale Up:
- Increase Streaming Units in job settings
- Monitor performance after scaling

📊 Data Quality Issues

Issue: Missing or NULL Values

Symptoms:

Aggregations returning unexpected results
NULL values in output

Solutions:

Handle NULLs in Queries:

-- ❌ NULLs will affect aggregations
SELECT AVG(temperature) FROM [input]
   
-- ✅ Explicitly handle NULLs
SELECT 
    AVG(CASE WHEN temperature IS NOT NULL THEN temperature END) AS AvgTemp,
    COUNT(temperature) AS ValidReadings,
    COUNT(*) - COUNT(temperature) AS MissingReadings
FROM [input]

Data Validation Query:

SELECT 
    deviceId,
    COUNT(*) AS TotalEvents,
    COUNT(temperature) AS ValidTemp,
    COUNT(humidity) AS ValidHumidity,
    COUNT(pressure) AS ValidPressure,
    (COUNT(temperature) * 100.0) / COUNT(*) AS TempCompleteness
FROM [telemetry-input] TIMESTAMP BY timestamp
GROUP BY deviceId, TumblingWindow(minute, 15)

Issue: Incorrect Data Types

Symptoms:

Type conversion errors
Unexpected query results

Solutions:

Safe Type Conversions:

-- ❌ Unsafe conversion
SELECT CAST(deviceId AS bigint) FROM [input]
   
-- ✅ Safe conversion
SELECT TRY_CAST(RIGHT(deviceId, 3) AS bigint) AS DeviceNumber
FROM [input]
WHERE deviceId LIKE 'device-___'

Validate Input Data:

SELECT 
    deviceId,
    timestamp,
    temperature,
    CASE 
        WHEN TRY_CAST(temperature AS decimal) IS NULL THEN 'Invalid'
        ELSE 'Valid'
    END AS TemperatureValidation
FROM [telemetry-input] TIMESTAMP BY timestamp

🔗 Connectivity Issues

Issue: SQL Database Connection Failures

Symptoms:

“Login failed” errors
Connection timeout errors

Solutions:

Check Firewall Rules:

# Add current IP to firewall
az sql server firewall-rule create \
  --resource-group rg-streamanalytics-workshop \
  --server YOUR_SQL_SERVER \
  --name AllowMyIP \
  --start-ip-address YOUR_IP \
  --end-ip-address YOUR_IP

Verify Credentials:
- Double-check username and password
- Ensure SQL Authentication is enabled
- Test connection with SQL Server Management Studio

Connection String Format:

Server=your-server.database.windows.net,1433;Database=StreamAnalyticsDB;User ID=sqladmin;Password=your-password;Encrypt=true;Connection Timeout=30;

Issue: Event Hub Authentication Errors

Symptoms:

“Unauthorized” errors
“Access denied” messages

Solutions:

Verify Connection String:

# Get correct connection string
az eventhubs namespace authorization-rule keys list \
  --resource-group rg-streamanalytics-workshop \
  --namespace-name YOUR_NAMESPACE \
  --name RootManageSharedAccessKey

Check Permissions:
- Ensure connection string has Send/Listen permissions
- Verify namespace and Event Hub names are correct

📈 Performance Issues

Issue: Slow Query Performance

Symptoms:

High latency between input and output
Backlog of unprocessed events

Solutions:

Optimize Joins:

-- ❌ Avoid unnecessary joins
SELECT * FROM input1 JOIN input2 ON input1.id = input2.id
   
-- ✅ Use specific columns and appropriate join conditions
SELECT input1.deviceId, input1.temperature, input2.metadata
FROM input1 TIMESTAMP BY timestamp1
JOIN input2 TIMESTAMP BY timestamp2
ON input1.deviceId = input2.deviceId
AND DATEDIFF(second, input1, input2) BETWEEN 0 AND 60

Reduce Output Volume:

-- Add HAVING clauses to filter small groups
SELECT deviceId, COUNT(*), AVG(temperature)
FROM [telemetry-input] TIMESTAMP BY timestamp
GROUP BY deviceId, TumblingWindow(minute, 5)
HAVING COUNT(*) >= 3  -- Only output groups with sufficient data

Issue: Power BI Not Updating

Symptoms:

Dashboard shows old data
Tiles not refreshing

Solutions:

Check Stream Analytics Output:
- Verify Power BI output is configured correctly
- Check output events metric in Stream Analytics

Power BI Dataset Limits:

Each streaming dataset limited to 200,000 rows

Implement data retention strategy:

-- Add row limit in Stream Analytics query
SELECT TOP 1000 
  deviceId,
  timestamp,
  temperature
INTO [powerbi-output]
FROM [telemetry-input] TIMESTAMP BY timestamp
ORDER BY timestamp DESC

Refresh Power BI Authentication:
- Re-authenticate Power BI connection in Stream Analytics
- Check dataset permissions in Power BI

🔍 Debugging Techniques

Debug Query Issues

Use Query Test Feature:

-- Start with simple query
SELECT * FROM [telemetry-input] TIMESTAMP BY timestamp
   
-- Gradually add complexity
SELECT deviceId, temperature FROM [telemetry-input] TIMESTAMP BY timestamp
WHERE temperature IS NOT NULL

Add Debug Output:

-- Add debug information to output
SELECT 
    deviceId,
    temperature,
    System.Timestamp() AS ProcessedTime,
    'DEBUG' AS MessageType
INTO [debug-output]
FROM [telemetry-input] TIMESTAMP BY timestamp

Monitor Resource Usage

Check Streaming Units:
- Monitor SU% metric
- Scale up if consistently above 80%
Monitor Input/Output Rates:
- Input Events per second
- Output Events per second
- Data Conversion Errors

Set Up Alerts:

# Create alert for high SU usage
az monitor metrics alert create \
  --name "High SU Usage" \
  --resource-group rg-streamanalytics-workshop \
  --scopes YOUR_STREAM_ANALYTICS_RESOURCE_ID \
  --condition "avg SU% > 80" \
  --description "Stream Analytics job using too many SUs"

📋 Diagnostic Queries

Check Event Arrival Patterns

SELECT 
    System.Timestamp() AS WindowEnd,
    COUNT(*) AS EventCount,
    MIN(timestamp) AS EarliestEvent,
    MAX(timestamp) AS LatestEvent,
    DATEDIFF(second, MIN(timestamp), MAX(timestamp)) AS TimeSpanSeconds,
    AVG(DATEDIFF(second, timestamp, System.Timestamp())) AS AvgLatencySeconds
FROM [telemetry-input] TIMESTAMP BY timestamp
GROUP BY TumblingWindow(minute, 5)

Identify Data Quality Issues

SELECT 
    deviceId,
    COUNT(*) AS TotalEvents,
    SUM(CASE WHEN temperature IS NULL THEN 1 ELSE 0 END) AS MissingTemperature,
    SUM(CASE WHEN temperature < -50 OR temperature > 100 THEN 1 ELSE 0 END) AS OutOfRangeTemperature,
    AVG(CASE WHEN temperature IS NOT NULL THEN temperature END) AS AvgTemperature
FROM [telemetry-input] TIMESTAMP BY timestamp
GROUP BY deviceId, TumblingWindow(hour, 1)

Monitor Output Health

SELECT 
    'SQL_OUTPUT' AS OutputType,
    System.Timestamp() AS CheckTime,
    COUNT(*) AS RecordCount
INTO [health-check-output]
FROM [telemetry-input] TIMESTAMP BY timestamp
GROUP BY TumblingWindow(minute, 1)

🆘 Getting Help

Azure Support Channels

Azure Portal:
- Navigate to your Stream Analytics job
- Click “Help + support” → “New support request”
Azure Documentation:
- Stream Analytics Troubleshooting
- Common Query Patterns
Community Forums:
- Microsoft Q&A
- Stack Overflow

Workshop-Specific Help

Check Prerequisites:
- Verify all Azure resources are created
- Confirm proper permissions and access
Validate Sample Data:
- Use provided sample JSON files
- Test with known-good data before using simulators
Step-by-Step Verification:
- Complete each lab in sequence
- Verify success criteria before proceeding

📞 Emergency Procedures

If Everything Stops Working

Stop Stream Analytics Job
Check Resource Health in Azure Portal
Verify All Connection Strings and credentials
Test Each Component Individually:
- Event Hub → Test with portal
- Stream Analytics → Test query with sample data
- SQL Database → Test connection with SSMS
- Power BI → Verify authentication
Restart in Minimal Configuration:
- Simple pass-through query
- Single output (blob storage)
- Verify basic functionality
Gradually Add Complexity:
- Add SQL output
- Add aggregations
- Add Power BI output
- Add alert logic

📊 Health Check Checklist

Stream Analytics job is running
Input events > 0 in last 5 minutes
Output events > 0 in last 5 minutes
Data conversion errors = 0
SU% < 80%
SQL Database has recent data
Power BI dashboard updating
No failed requests in Activity Log
All resource health checks passed

Remember: Most issues are related to configuration or connectivity. Start with the basics and work your way up to complex scenarios.