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

[Guest 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

}

 

Continue reading...