Lesson Learned #416: Unveiling User Activity Insights in Azure SQL Databases

[Jose_Manuel_Jurado]

Today, I got an interested service request where our customer needs to know the user activity for a very dense elastic database pool. During the troubleshooting I wrote, as an example, this Powershell script that tracks useful information about connectivity and execution. I hope that you could find of your interest.

Motivation:

Our customer needed a way to get a bird's-eye view of who's doing what in their packed elastic database pool. The challenge was clear: track user connections and activities swiftly and efficiently.

Enter the PowerShell Script:

1. Customization Made Easy: The script starts with a bunch of settings you can tweak. Put in your Azure SQL Server info, username, password, and more. 

2. Resilient Connections: The Open-DatabaseConnection function is all about robustness. It tries connecting, and if it stumbles, it tries again (up to your set retry count). 

3. Query Wisely: The Execute-Query function executes SQL queries. 

4. Tracking Loop: The main part of the script orchestrates everything and save the data in the log file.

Notes about the script: 

Before you use this script, remember to test any changes before deploying in the real world. 

Source code:

# Parameters
$ServerName = "servername.database.windows.net"
$Username = "username"
$Password = "pwd"
$ElasticPoolName = ""  # leave empty if you don't want to filter by Elastic Pool
$RetryCount = 3
$RetryDelaySeconds = 10
$MaxExecutionTime = 1200  # 20 minutes of collection data
$LoopInterval = 10  # seconds among executions
$LogFilePath = "C:\temp\log.txt"  # Default log file location


# Function to write to the log
function Write-Log {
    param([string]$message)
    try {
       $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
       $logMessage = "$timestamp - $message"
        $logMessage | Out-File -Append -FilePath $LogFilePath
         Write-Host $logMessage
       }
     catch {
         Write-Host $logMessage
       }
}

# Function to open database connection with retries
function Open-DatabaseConnection {
    param([string]$connectionString, [int]$retryCount, [int]$retryDelay)
    
    $retry = 0
    $connected = $false
    
    while ($retry -lt $retryCount -and !$connected) {
        try {
            $databaseConnection = New-Object System.Data.SqlClient.SqlConnection
            $databaseConnection.ConnectionString = $connectionString
            $databaseConnection.Open()
            $connected = $true
        } catch {
            Write-Log "Error opening database connection (Attempt $($retry + 1)): $($_.Exception.Message)"
            $retry++
            Start-Sleep -Seconds $retryDelay
        }
    }
    
    return $databaseConnection
}

# Function to execute query with retries
function Execute-Query {
    param([System.Data.SqlClient.SqlConnection]$connection, [string]$query, [ref]$readerRef, [int]$retryCount, [int]$retryDelay)
    
    $retry = 0
    $success = $false
    
    while ($retry -lt $retryCount -and !$success) {
        try {
            $command = $connection.CreateCommand()
            $command.CommandText = $query
            $reader = $command.ExecuteReader()
            $readerRef.Value = $reader  # Assign reader to the reference
            $success = $true
        } catch {
            Write-Log "Error executing query (Attempt $($retry + 1)): $($_.Exception.Message)"
            $retry++
            Start-Sleep -Seconds $retryDelay
        }
    }
}

# Main execution
$startTime = Get-Date
$endTime = $startTime.AddSeconds($MaxExecutionTime)

while ((Get-Date) -lt $endTime) {
    Write-Log "Execution started"
    
    # Attempt connection with retries
    $SqlConnection = Open-DatabaseConnection -connectionString ("Server=$ServerName;User Id=$Username;Password=$Password;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;") -retryCount $RetryCount -retryDelay $RetryDelaySeconds
    
    if (!$SqlConnection) {
        Write-Log "Failed to establish connection to the server. Exiting."
        exit
    }
    
    # Get list of non-system databases if ElasticPoolName is provided
    $databases = @()
    if (![string]::IsNullOrEmpty($ElasticPoolName)) {
        $databasesQuery = @"
        SELECT d.name
        FROM sys.databases d
        INNER JOIN sys.database_service_objectives slo ON d.database_id = slo.database_id
        WHERE d.database_id > 4 AND slo.elastic_pool_name ='$ElasticPoolName' AND d.state_desc = 'ONLINE'
"@
    } else {
        $databasesQuery = @"
        SELECT name
        FROM sys.databases
        WHERE database_id > 4 AND state_desc = 'ONLINE'
"@
    }

    $databasesCommand = $SqlConnection.CreateCommand()
    $databasesCommand.CommandText = $databasesQuery
    $databasesReader = $databasesCommand.ExecuteReader()
    while ($databasesReader.Read()) {
        $databases += $databasesReader["name"]
    }
    $databasesReader.Close()
    
    # Process each database
    foreach ($databaseName in $databases) {
        Write-Log ("Processing database: $databaseName")
        
        $connectionString = "Server=$ServerName;Database=$databaseName;User Id=$Username;Password=$Password;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
        
        try {
            $databaseConnection = Open-DatabaseConnection -connectionString $connectionString -retryCount $RetryCount -retryDelay $RetryDelaySeconds
            
            # Query for connection and request information
            $query = @"
            SELECT s.session_id, s.program_name, s.client_interface_name, s.client_version,s.host_name,s.host_process_id,
                   c.net_transport, s.login_time,
                   c.client_net_address,c.connect_time,c.protocol_version,c.net_transport,c.net_packet_size,c.connection_id,
                   r.start_time, r.cpu_time, r.total_elapsed_time, r.wait_type, r.wait_resource, r.command
            FROM sys.dm_exec_sessions s
            LEFT JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
            LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id 
            WHERE s.is_user_process=1
"@

            $reader = $null
            Execute-Query -connection $databaseConnection -query $query -readerRef ([ref]$reader) -retryCount $RetryCount -retryDelay $RetryDelaySeconds

            if ($reader) {
                while ($reader.Read()) {
                    # Process and log information
                    $sessionInfo = @{
                        SessionId = $reader["session_id"]
                        ProgramName = $reader["program_name"]
                        ClientInterfaceName = $reader["client_interface_name"]
                        ClientVersion = $reader["client_version"]
                        HostName = $reader["host_name"]
                        HostProcessId = $reader["host_process_id"]
                        NetTransport = $reader["net_transport"]
                        LoginTime = $reader["login_time"]
                        ClientAddress = $reader["client_net_address"]
                        ConnectTime = $reader["connect_time"]
                        ProtocolVersion = $reader["protocol_version"]
                        NetPacketSize = $reader["net_packet_size"]
                        ConnectionId = $reader["connection_id"]
                        StartTime = $reader["start_time"]
                        CpuTime = $reader["cpu_time"]
                        ElapsedTime = $reader["total_elapsed_time"]
                        WaitType = $reader["wait_type"]
                        WaitResource = $reader["wait_resource"]
                        Command = $reader["command"]
                    }

                    Write-Log ("Session Info: SessionId: {0}, Program: {1}, Client Interface: {2}, Client Version: {3}, Host Name: {4}, Host Process ID: {5}, Net Transport: {6}, Login Time: {7}, Client Address: {8}, Connect Time: {9}, Protocol Version: {10}, Net Packet Size: {11}, Connection ID: {12}, Start Time: {13}, CPU Time: {14}, Elapsed Time: {15}, Wait Type: {16}, Wait Resource: {17}, Command: {18}" -f `
                        $sessionInfo.SessionId, $sessionInfo.ProgramName, $sessionInfo.ClientInterfaceName, $sessionInfo.ClientVersion, $sessionInfo.HostName, $sessionInfo.HostProcessId, $sessionInfo.NetTransport, $sessionInfo.LoginTime, `
                        $sessionInfo.ClientAddress, $sessionInfo.ConnectTime, $sessionInfo.ProtocolVersion, $sessionInfo.NetPacketSize, $sessionInfo.ConnectionId, $sessionInfo.StartTime, $sessionInfo.CpuTime, `
                        $sessionInfo.ElapsedTime, $sessionInfo.WaitType, $sessionInfo.WaitResource, $sessionInfo.Command)
                }
                $reader.Close()
            }
            
            $databaseConnection.Close()
        } catch {
            Write-Log "Error processing database '$databaseName': $($_.Exception.Message)"
        }
    }
    
    $SqlConnection.Close()
    Write-Log "Execution completed"
    
    Start-Sleep -Seconds $LoopInterval
}