Automating Azure SQL Maintenance with Azure Automation

Keeping Your SQL Databases Healthy: The Power of Automation

In the realm of database management, maintaining optimal performance and storage efficiency for your SQL databases is critical. SQL databases are the backbone of countless business applications, supporting everything from transaction processing to analytics. However, as they grow over time, they can face challenges like bloated storage, performance issues, and cost inefficiencies. That’s where the automated SQL maintenance script comes in.

This blog will explore what this script does, why it is crucial for good database health, and why every database administrator should consider such automated solutions. Our future blog will be about Azure SQL Backup leveraging the same Azure Automation Account.

What Does This Script Do?

This SQL maintenance script is designed to be executed on Azure Automation Accounts, which can use either Azure workers or hybrid workers. If the environment lacks Virtual Network (VNet) integration, the script automatically adds a temporary firewall rule to allow access. This flexibility makes it easy to run in various configurations, ensuring secure and seamless database maintenance.

The SQL maintenance script is designed to automate the labor-intensive process of optimizing SQL database storage and ensuring elastic pools have enough space to accommodate dynamic workloads. It performs several key functions:

1. Elastic Pool Management: The script assesses the current utilization of your Azure SQL Elastic Pool and makes necessary adjustments. It ensures the pool has adequate free space for future growth while avoiding unnecessary costs. If the available space is low, it will resize the pool to add additional storage, keeping your databases efficient.
2. Database Shrinking and Resizing: The script checks each database’s used space and compares it to its allocated storage. If the unused log space exceeds 20% of the allocated storage, the script uses direct SQL queries to perform a shrink operation to recover wasted space. If a database requires more space based on its usage, the script also resizes it accordingly, aiming to keep 50% more space than currently used.
3. Firewall Adjustments: The script adds a temporary firewall rule for the executing machine’s IP address to ensure that it can interact securely with the SQL server. Once the operations are complete, the rule is removed, maintaining a secure environment.
4. Reporting: After performing these maintenance tasks, the script compiles a comprehensive report detailing the changes made, including database shrinks, resizes, and elastic pool adjustments. This report is then sent via email to the designated recipients, providing a complete audit trail of all actions taken.

Why Is This Important for Database Health?

Managing storage and ensuring that databases are operating efficiently is essential for several reasons:

• Performance: Over-allocated databases with significant unused space can slow down performance. By automatically resizing databases and ensuring they have just the right amount of space, the script helps maintain optimal performance levels.
• Cost Efficiency: Cloud storage can be costly if not managed properly. Resizing elastic pools and databases based on actual needs ensures you are not overpaying for unused capacity. Conversely, under-provisioning can lead to service disruptions. This script helps balance both aspects, providing cost-effective storage without risking performance issues.
• Scalability: As your application usage grows, your storage requirements will change. Automating this resizing process allows your databases to scale dynamically without requiring manual intervention. It’s about keeping pace with growth seamlessly.
• Security: By automatically adding and removing firewall rules, the script ensures that database interactions remain secure, reducing the risk of unauthorized access.

A Note on Backups

While this maintenance script focuses on optimizing database performance and storage, it’s also important to ensure your databases are backed up properly. A separate upcoming blog will focus on automating SQL backups to Azure Storage Accounts, providing a robust and reliable backup solution for disaster recovery and compliance purposes. Stay tuned for more details!

Conclusion

Database maintenance is a time-consuming yet necessary task. Automating processes like storage adjustments, database resizing, and firewall configuration ensures that your SQL databases stay lean, cost-efficient, and high-performing without requiring manual oversight. This script is a powerful tool that helps database administrators achieve these goals, allowing them to focus more on strategic tasks rather than routine maintenance.

Stay tuned for the upcoming post on automated (off-platform) database backups to Azure Storage Accounts!

<#
.SYNOPSIS
    Requires PowerShell 7.2.

    A script to automate Azure SQL maintenance tasks using Azure Automation.

.DESCRIPTION
    This script automates the maintenance of Azure SQL databases. It is designed to:
    - Adjust Azure SQL Elastic Pool storage based on usage.
    - Shrink log files if they exceed 20% of allocated storage using direct SQL queries.
    - Resize databases dynamically based on usage requirements.
    - Manage firewall rules for Azure worker access when Virtual Network (VNet) integration is unavailable.
    - Send a detailed report via email of all maintenance actions taken.
    The script can be executed in Azure Automation Accounts, supporting both Azure workers and hybrid workers.

.NOTES
    Author: Prof-IT Services
    Contact: [email protected]
    Date: 2024.12.02
    Version: 1.5
#>


# Import required modules
Import-Module Az.Sql
Import-Module Az.Resources
Import-Module Az.Monitor

# Define variables
$resourceGroupName = "YourResourceGroupName"  # Replace with your resource group name
$serverName = "YourServerName"  # Replace with your Azure SQL Server name
$minimumDBsize = 10  # Minimum database size in GB
$elasticpoolbuffer = 100  # Buffer for elastic pool adjustment in GB
$dbspacecalc = 1.50 # calculation to configure storage for databases, usedspace + 50%

# Define parameters for elastic pool
$elasticPoolName = "YourElasticPoolName"  # Replace with your elastic pool name
$userName = "YourAdminUsername"  # SQL Server admin username
$password = "YourSecurePassword"  # SQL Server admin password (replace with secure value)

# SMTP parameters for sending the report
$smtpServer = "mail.smtp2go.com"
$smtpFrom = "[email protected]"
$smtpTo = "[email protected]"
$smtpSubject = "SQL Server Storage Adjustment Report"
$smtpUsername = "YourSMTPUsername"
$smtpPassword = "YourSMTPPassword"

# Define connection details for SQL server
$serverFqdn = "$serverName.database.windows.net"  # Fully qualified domain name of the SQL server
$connectionString = "Server=$serverFqdn; Database=master; User ID=$userName; Password=$password; Encrypt=True; TrustServerCertificate=False;"  # Connection string for SQL server

# Define ArrayLists to store logs and database changes
$logEntries = [System.Collections.ArrayList]@()  # Stores log entries for actions performed
$databaseChanges = [System.Collections.ArrayList]@()  # Stores log entries for database changes

# Counters for actions
$shrinkCount = 0  # Counter for databases shrunk
$resizeCount = 0  # Counter for databases resized
$elasticPoolResizeCount = 0  # Counter for elastic pool resizes
$elasticPoolTotalChangeGB = 0  # Total elastic pool size changes in GB
$elasticPoolTotalSize = 0  # Total size of the elastic pool
$errorcount = 0  # Counter for errors

# Function to log actions and statistics
function Log-Action {
    param (
        [string]$Object,  # Object being acted upon
        [string]$Action,  # Action performed
        [string]$Details  # Details of the action
    )
    $logEntry = [PSCustomObject]@{
        Timestamp = Get-Date  # Timestamp of action
        Object = $Object  # Object name
        Action = $Action  # Action name
        Details = $Details  # Details of the action
    }
    [void]$logEntries.Add($logEntry)  # Add log entry to ArrayList
    Write-Output $logEntry  # Output log entry
}

# Function to log database changes
function Log-DatabaseChange {
    param (
        [string]$DatabaseName,  # Name of the database
        [string]$ChangeType,  # Type of change (e.g., resize, shrink)
        [int]$ChangeAmountGB  # Amount of change in GB
    )
    $changeEntry = [PSCustomObject]@{
        Timestamp = Get-Date  # Timestamp of change
        DatabaseName = $DatabaseName  # Database name
        ChangeType = $ChangeType  # Type of change
        ChangeAmountGB = $ChangeAmountGB  # Amount of change in GB
    }
    [void]$databaseChanges.Add($changeEntry)  # Add change entry to ArrayList
    Write-Output $changeEntry  # Output change entry
}


# Function to determine if an IP address is private
function Is-PrivateIP($ipAddress) {
    $privateRanges = @(
        "10.0.0.0/8",  # Class A private range
        "172.16.0.0/12",  # Class B private range
        "192.168.0.0/16",  # Class C private range
        "169.254.0.0/16",  # Link-local range
        "127.0.0.0/8"  # Loopback range
    )

    foreach ($range in $privateRanges) {
        if (Test-Connection -ComputerName $ipAddress -Count 1 -Quiet) {
            if ([IPAddress]::Parse($ipAddress).IsInSubnet($range)) {
                return $true  # Return true if IP is within a private range
            }
        }
    }
    return $false  # Return false if IP is not private
}

# Function to add a firewall rule for Azure worker
function Add-FirewallRuleForAzureWorker {
    # Get the public IP address of the current machine
    $publicIp = (Invoke-RestMethod -Uri "http://ipinfo.io/ip").Trim()
    $firewallRuleName = "TempFirewallRule-$($publicIp.Replace('.', '-'))"

    # Check if the IP address is already present in the firewall rules
    $existingRule = Get-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName -ServerName $serverName -FirewallRuleName $firewallRuleName

    if (-not $existingRule) {
        # Add a firewall rule to allow the current public IP address to access the SQL server
        New-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName -ServerName $serverName -FirewallRuleName $firewallRuleName -StartIpAddress $publicIp -EndIpAddress $publicIp
        Write-Output "Firewall rule added for IP: $publicIp"
    } else {
        $firewallRuleName = $existingRule.FirewallRuleName
        Write-Output "Firewall rule already exists for IP: $publicIp"
    }
}

# Function to remove firewall rule for Azure worker
function Remove-FirewallRuleForAzureWorker {
    $publicIp = (Invoke-RestMethod -Uri "http://ipinfo.io/ip").Trim()
    $firewallRuleName = "TempFirewallRule-$($publicIp.Replace('.', '-'))"
    Remove-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName -ServerName $serverName -FirewallRuleName $firewallRuleName
    Write-Output "Firewall rule removed for IP: $publicIp"
}

# Function to execute a SQL query
function Invoke-SqlQuery {
    param (
        [string]$ConnectionString,  # SQL Server connection string
        [string]$SqlQuery,  # SQL query to execute
        [string]$Object,  # Object name for logging purposes
        [int]$CommandTimeout = 30000  # Command timeout in seconds
    )

    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $ConnectionString

    try {
        $connection.Open()  # Open SQL connection
        $command = $connection.CreateCommand()
        $command.CommandText = $SqlQuery  # Set SQL query
        $command.CommandTimeout = $CommandTimeout  # Set command timeout

        $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
        $dataTable = New-Object System.Data.DataTable
        $adapter.Fill($dataTable) | Out-Null  # Execute query and fill results into DataTable

        Log-Action -Object $Object -Action "SQL Query Execution" -Details "Successfully executed SQL query: $SqlQuery"
        return $dataTable  # Return results
    }
    catch {
        Log-Action -Object $Object -Action "SQL Query Execution" -Details "Error executing SQL query: $_"
    }
    finally {
        $connection.Close()  # Close SQL connection
    }
}

# Function to check and adjust storage in elastic pool
function Adjust-ElasticPoolStorage {
    param (
        [string]$ResourceGroupName,  # Resource group name
        [string]$ServerName,  # SQL server name
        [string]$ElasticPoolName,  # Elastic pool name
        [int]$SpaceRequired  # Space required in GB
    )
    Log-Action -Object "ElasticPool" -Action "Elastic Pool Adjustment" -Details "Adjusting storage for elastic pool: $ElasticPoolName"
    
    # Get the elastic pool resource
    try {
        $elasticPool = Get-AzSqlElasticPool -ResourceGroupName $ResourceGroupName -ServerName $ServerName -ElasticPoolName $ElasticPoolName -ErrorAction Stop
    } catch {
        Log-Action -Object "ElasticPool" -Action "Elastic Pool Retrieval" -Details "Failed to retrieve elastic pool $ElasticPoolName $_"
        return
    }

    # Calculate used space and available space in the elastic pool
    $metric = Get-AzMetric -ResourceId $elasticPool.ResourceId -MetricName 'allocated_data_storage' -ErrorAction SilentlyContinue

    if ($metric.Data.Count -gt 0) {
        $usedSpaceObject = $metric.Data | Select-Object -Last 1
        $usedSpaceBytes = $usedSpaceObject.Average
        $usedSpaceGB = [math]::Floor($usedSpaceBytes / 1GB)  # Convert used space to GB
    } 
    if ($usedSpaceGB -le 10) {
        Log-Action -Object "ElasticPool" -Action "Metric Retrieval" -Details "Failed to retrieve allocated data storage metric for elastic pool $ElasticPoolName."
        return
    }
    
    $availableSpaceGB = [math]::Floor($elasticPool.MaxSizeBytes / 1GB) - $usedSpaceGB  # Calculate available space
    $currentgoal = $usedSpaceGB + $elasticpoolbuffer
    $newgoal = $usedSpaceGB + $elasticpoolbuffer + $SpaceRequired
    $global:elasticPoolTotalSize = $newgoal

    $spacedifference = $newgoal - $currentgoal

    Log-Action -Object "ElasticPool" -Action "Elastic Pool Usage" -Details "Current Usage in Elastic Pool: $usedSpaceGB GB (Available: $availableSpaceGB GB)"

    # Adjust elastic pool storage if required
    if ($spacedifference -lt 10 -or $spacedifference -gt 10) {
        $newSizeGB = $newgoal
        Log-Action -Object "ElasticPool" -Action "Elastic Pool Adjustment" -Details "Adjusting storage for elastic pool $ElasticPoolName to $newSizeGB GB."

        try {
            Log-Action -Object "ElasticPool" -Action "Elastic Pool Adjustment" -Details "Elastic pool $ElasticPoolName storage adjusted successfully to $newSizeGB GB."
            Log-DatabaseChange -DatabaseName "ElasticPool" -ChangeType "Elastic Pool Adjustment" -ChangeAmountGB $spacedifference 
            $elasticPoolResizeCount++
            $elasticPoolTotalChangeGB = $elasticPoolTotalChangeGB + $spacedifference

            Set-AzSqlElasticPool -ResourceGroupName $ResourceGroupName -ServerName $ServerName -ElasticPoolName $ElasticPoolName -StorageMB ($newSizeGB * 1024)
        } catch {
            Log-Action -Object "ElasticPool" -Action "Elastic Pool Adjustment" -Details "Failed to resize elastic pool $ElasticPoolName $_"
        }
    } else {
        Log-Action -Object "ElasticPool" -Action "Elastic Pool Adjustment" -Details "Elastic pool $ElasticPoolName already in target range of $newgoal GB."
    }
}

# Authenticate with Azure
try {
    $AzureContext = Connect-AzAccount -Identity -ErrorAction Stop  # Authenticate using managed identity
    Log-Action -Action "Authentication" -Details "Successfully authenticated to Azure."  -Object "Authentication"
} catch {
    Log-Action -Action "Authentication" -Details "Failed to authenticate to Azure: $_" -Object "Authentication"
    exit
}

# Load System.Data.SqlClient
Add-Type -AssemblyName "System.Data"

# Get all SQL Databases
$databases = Get-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName

# Check if resolved IP addresses are private or public
$privateip = $false
try {
    $ipAddresses = [System.Net.Dns]::GetHostAddresses($serverFqdn)  # Get IP addresses for the SQL server
} catch {
    Write-Host "Failed to resolve $serverFqdn. Error: $_"
    return
}

foreach ($ip in $ipAddresses) {
    if (Is-PrivateIP $ip.IPAddressToString) {
        $privateip = $true
    }
}

# If the server has a public IP, add a firewall rule to allow current machine's IP
if ($privateip -eq $false) {
    Add-FirewallRuleForAzureWorker
    start-sleep 600  # Sleep for 10 minutes to allow for firewall rule propagation
}

# Log start of database maintenance process
Log-Action -Action "Process Start" -Details "Starting Database Maintenance for $($databases.Count) databases." -Object "" 

#Processing Log Shrink actions first
foreach ($db in $databases) {
    # Skip system databases like 'master'
    if ($db.DatabaseName -in @("master", "model", "msdb", "tempdb")) {
        continue
    }
    $databaseName = $db.DatabaseName

    Log-Action -Object $databaseName -Action "Database Check" -Details "Checking DBCC storage"

    # SQL command to get space allocated in MB and space allocated unused in MB
    $sqlCommand = @"
    SELECT DB_NAME() as DatabaseName,
           SUM(size/128.0) AS DatabaseDataSpaceAllocatedInMB,
           SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) AS DatabaseDataSpaceAllocatedUnusedInMB
    FROM sys.database_files
    GROUP BY type_desc
    HAVING type_desc = 'ROWS';
"@

    $connectionStringDb = "Server=$serverFqdn; Database=$databaseName; User ID=$userName; Password=$password; Encrypt=True; TrustServerCertificate=False;"
    $databaseMetrics = Invoke-SqlQuery -ConnectionString $connectionStringDb -SqlQuery $sqlCommand -Object $databaseName 

    $allocated = [double]($databaseMetrics.DatabaseDataSpaceAllocatedInMB | Select-Object -First 1)
    $unused = [double]($databaseMetrics.DatabaseDataSpaceAllocatedUnusedInMB | Select-Object -First 1)
    
    $databaseMetrics.DatabaseDataSpaceAllocatedInMB
    $databaseMetrics.DatabaseDataSpaceAllocatedUnusedInMB

    write-output "DBCC allocated: $allocated"
    write-output "DBCC unused: $unused"

    $unusedPercentage = ($unused / $allocated) * 100

    Log-Action -Action "Database DBCC Usage" -Details "unused percentage: $unusedPercentage%" -Object $databaseName 
    # If the unused space is more than 20% of the total space, and more than 1000MB, run the shrink command
    if ($unusedPercentage -gt 20 -and $unused -ge 1000) {
        Log-Action -Action "Database DBCC Usage" -Details "Shrinking DBCC database" -Object $databaseName 
        $shrinkCommand = "DBCC SHRINKDATABASE ([$databaseName], 20) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);"
        Invoke-SqlQuery -ConnectionString $connectionStringDb -SqlQuery $shrinkCommand -Object $databaseName 
        $shrinkCount++
        Log-DatabaseChange -DatabaseName $databaseName -ChangeType "Shrink DBCC" -ChangeAmountGB $unused / 1024
    } else {
        Log-Action -Action "Database DBCC Usage" -Details "Shrink not needed" -Object $databaseName 
    }
}

#Remove Firewall Rule for public ip address
Remove-FirewallRuleForAzureWorker

#short sleep so metric can catch up with shrink actions
if ($shrinkCount -gt 0) {
    Start-Sleep -Seconds 500
}

# Loop through all databases and resize databases
foreach ($db in $databases) {
    # Skip system databases like 'master'
    if ($db.DatabaseName -in @("master", "model", "msdb", "tempdb")) {
        continue
    }
    $databaseName = $db.DatabaseName

    Log-Action -Object $databaseName -Action "Database Check" -Details "Checking storage"

    # Get the resource for the database
    $db_resource = Get-AzResource -ResourceId $db.ResourceId

    # Retrieve the latest 'storage' metric from Azure Monitor
    try {
        $db_metric_storage = $db_resource | Get-AzMetric -MetricName 'storage' -WarningAction SilentlyContinue
        $db_UsedSpace = $db_metric_storage.Data.Maximum | Select-Object -Last 1
        $usedSpaceGB = [math]::Floor($db_UsedSpace / 1GB)  # Use Floor to ensure we get the integer value without rounding up
    } catch {
        Log-Action -Action "Metric Retrieval" -Details "Failed to retrieve metric information for database" -Object $databaseName 
        continue
    }

    $maxSizeGB = [math]::Floor($db.MaxSizeBytes / 1GB)  # Use Floor to get an integer value
    $availableSpaceGB = $maxSizeGB - $usedSpaceGB

    #calculate target disk space with % set in define variables
    $GoalSpaceGB = $usedSpaceGB * $dbspacecalc

    if ($GoalSpaceGB -le $minimumDBsize) {
        $GoalSpaceGB = $minimumDBsize
    }

    $differenceGB = $GoalSpaceGB - $maxSizeGB
    $differenceGB = [int]$differenceGB

    Log-Action -Action "Database Usage" -Details "Current Usage: $usedSpaceGB GB of $maxSizeGB GB (Available: $availableSpaceGB GB, Goal: $GoalSpaceGB, Difference: $differenceGB)" -Object $databaseName 

    # Resize database if significant change is required
    if ($differenceGB -gt 1 -or $differenceGB -lt -1) {
        $newSizeGB = [int]$GoalSpaceGB
        
        try {
            if ($differenceGB -gt 10) {
                Log-Action -Action "Database Resize" -Details "Requesting elastic space for $($differenceGB) increase" -Object $databaseName 
                Adjust-ElasticPoolStorage -ResourceGroupName $resourceGroupName -ServerName $serverName -ElasticPoolName $elasticPoolName -SpaceRequired $differenceGB
            }
            Log-Action -Action "Database Resize" -Details "Resizing database to $newSizeGB GB" -Object $databaseName 
            Set-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $db.DatabaseName -MaxSizeBytes ($newSizeGB * 1GB) -ErrorAction Stop
            Log-Action -Action "Database Resize" -Details "Database resized successfully to $newSizeGB GB." -Object $databaseName 
            $resizeCount++
            Log-DatabaseChange -DatabaseName $databaseName -ChangeType "Increase" -ChangeAmountGB ($newSizeGB - $maxSizeGB)
        } catch {
            Log-Action -Action "Database Resize" -Details "Failed to resize database" -Object $databaseName 
        }
    } else {
        Log-Action -Action "Database Check" -Details "Database has sufficient space within the desired range." -Object $databaseName 
    }
}

#short sleep so metric can catch up with shrink actions
if ($resizeCount -gt 0 -or $shrinkCount -gt 0) {
    Start-Sleep -Seconds 500
}

# Always adjust elastic pool storage at the end to ensure target free space is met
Adjust-ElasticPoolStorage -ResourceGroupName $resourceGroupName -ServerName $serverName -ElasticPoolName $elasticPoolName -SpaceRequired 0

Log-Action -Action "Process Completion" -Details "Database size check and adjustment completed." -Object "" 


# Function to send report via SMTP
function Send-Report {
    # Count errors in log entries
    foreach ($log in $logEntries) {
        if ($log.Details -match 'failed|error') { 
            $errorcount++
        } 
    }
    if ($errorcount -ge 1) {
        $rowStyle = 'style="background-color: red;"'  # Set row style to red if errors are found
    }
    
    # Create summary of changes
    $summary = @"
        <h2>Summary of Changes</h2>
        <ul>
            <li><b>ElasticPool Size: $elasticPoolTotalSize GB<b></li>
            <li>Total LOG Databases Shrunk: $shrinkCount</li>
            <li>Total Azure Databases Resized: $resizeCount</li>
            <li>Total Elastic Pool Resizes: $elasticPoolResizeCount</li>
            <li>Total Elastic Pool Size Change: $elasticPoolTotalChangeGB GB</li>
            <li $rowStyle>Total Error Count: $errorcount</li>
        </ul>
"@

    # Convert database changes to HTML table
    $databaseChangesContent = @"
    <h2>Database Changes Report</h2>
    <table>
        <tr>
            <th>Timestamp</th>
            <th>Database Name</th>
            <th>Change Type</th>
            <th>Change Amount (GB)</th>
        </tr>
"@

    foreach ($change in $databaseChanges) {
        $databaseChangesContent += "<tr><td>$($change.Timestamp)</td><td>$($change.DatabaseName)</td><td>$($change.ChangeType)</td><td>$($change.ChangeAmountGB)</td></tr>"
    }

    $databaseChangesContent += @"
    </table>
"@

    # Convert log entries to HTML table
    $logEntriesContent = @"
    <h2>Elastic Pool Storage Adjustment Report</h2>
    <table>
        <tr>
            <th>Timestamp</th>
            <th>Object</th>
            <th>Action</th>
            <th>Details</th>
        </tr>
"@

    foreach ($log in $logEntries) {
        $rowStyle = if ($log.Details -match 'failed|error') { 'style="background-color: red;"' } else { '' }
        $logEntriesContent += "<tr $rowStyle><td>$($log.Timestamp)</td><td>$($log.Object)</td><td>$($log.Action)</td><td>$($log.Details)</td></tr>"
    }

    $logEntriesContent += @"
    </table>
    <br><br>
    <div style='text-align: center;'>
        <img src='data:image/png;base64,' alt='Powered by'>
    </div>
    </body>
    </html>
"@

    # Combine all content for HTML report
    $htmlContent = "<html><head><style>table { width: 100%; border-collapse: collapse; } th, td { border: 1px solid black; padding: 8px; text-align: left; } th { background-color: #4CAF50; color: white; } img { max-width: 100px; }</style></head><body>" + $summary + $databaseChangesContent + $logEntriesContent + "</body></html>"

    # Send email using SMTP client
    $smtpClient = New-Object System.Net.Mail.SmtpClient($smtpServer)
    $smtpClient.Credentials = New-Object System.Net.NetworkCredential($smtpUsername, $smtpPassword)
    $smtpClient.EnableSsl = $true
    $smtpClient.Port = 2525

    $mailMessage = New-Object System.Net.Mail.MailMessage
    $mailMessage.From = $smtpFrom
    $mailMessage.To.Add($smtpTo)
    $mailMessage.Subject = $smtpSubject
    $mailMessage.Body = $htmlContent
    $mailMessage.IsBodyHtml = $true

    try {
        $smtpClient.Send($mailMessage)  # Send the email
        Log-Action -Action "Report Sent" -Details "Report successfully sent to $smtpTo." -Object "MAIL"
    } catch {
        Log-Action -Action "Report Sending Failed" -Details "Failed to send report: $_" -Object "MAIL"
    }
}

# Send report via SMTP
Send-Report