请求Azure VM上数据库环境状态定时报告的分步制作指导

一、前期准备 #

• 安装并导入必要PowerShell模块：
  • Azure管理模块：Install-Module -Name Az -AllowClobber -Force，执行Connect-AzAccount完成账号登录
  • SQL Server操作模块：Install-Module -Name SqlServer -Force，执行Import-Module SqlServer导入
• 确保执行脚本的账号拥有：Azure VM读取权限、SQL Server sysadmin或对应权限（查看作业、性能计数器、磁盘信息等）

二、磁盘可用空间采集 #

1. Azure托管磁盘空间信息 #

获取VM的系统盘、数据盘在Azure存储层面的已用/可用空间：

# 替换为你的VM名称列表和资源组名
$vmNames = @("DB-VM-01", "DB-VM-02")
$resourceGroupName = "DB-Resource-Group"

$diskInfo = foreach ($vm in $vmNames) {
    $vmObj = Get-AzVM -ResourceGroupName $resourceGroupName -Name $vm
    # 遍历数据盘
    foreach ($disk in $vmObj.StorageProfile.DataDisks) {
        $diskDetails = Get-AzDisk -ResourceGroupName $resourceGroupName -DiskName $disk.Name
        [PSCustomObject]@{
            VMName              = $vm
            DiskName            = $disk.Name
            DiskSizeGB          = $diskDetails.DiskSizeGB
            UsedSpaceGB         = $diskDetails.DiskSizeGB - $diskDetails.DiskSizeRemainingGB
            AvailableSpaceGB    = $diskDetails.DiskSizeRemainingGB
            DiskType            = $diskDetails.Sku.Name
        }
    }
    # 获取系统盘信息
    $osDisk = $vmObj.StorageProfile.OsDisk
    $osDiskDetails = Get-AzDisk -ResourceGroupName $resourceGroupName -DiskName $osDisk.Name
    [PSCustomObject]@{
        VMName              = $vm
        DiskName            = $osDisk.Name
        DiskSizeGB          = $osDiskDetails.DiskSizeGB
        UsedSpaceGB         = $osDiskDetails.DiskSizeGB - $osDiskDetails.DiskSizeRemainingGB
        AvailableSpaceGB    = $osDiskDetails.DiskSizeRemainingGB
        DiskType            = $osDiskDetails.Sku.Name
    }
}

2. VM内部磁盘可用空间 #

通过远程执行命令获取VM操作系统内的磁盘剩余空间：

$diskSpaceScript = @"
Get-Volume | Where-Object { `$_.DriveType -eq 'Fixed' } | Select-Object DriveLetter, FileSystemLabel, @{Name='SizeGB'; Expression={[math]::Round(`$_.Size/1GB,2)}}, @{Name='FreeSpaceGB'; Expression={[math]::Round(`$_.FreeSpace/1GB,2)}}, @{Name='FreePercent'; Expression={[math]::Round((`$_.FreeSpace/`$_.Size)*100,2)}}
"@

$internalDiskInfo = foreach ($vm in $vmNames) {
    $result = Invoke-AzVMRunCommand -ResourceGroupName $resourceGroupName -VMName $vm -CommandId 'RunPowerShellScript' -ScriptString $diskSpaceScript
    $output = $result.Value[0].Message | ConvertFrom-Json
    $output | Add-Member -MemberType NoteProperty -Name 'VMName' -Value $vm
    $output
}

三、数据库核心信息采集 #

获取数据库状态、空间使用情况：

# 替换为你的SQL实例地址（默认实例可直接填VM名称）
$sqlInstance = "DB-VM-01,1433"
# 可通过Azure Key Vault自动化获取凭证，避免明文输入
$sqlCred = Get-Credential

$dbInfo = Invoke-SqlCmd -ServerInstance $sqlInstance -Credential $sqlCred -Query @"
SELECT 
    name AS DatabaseName,
    state_desc AS DatabaseState,
    recovery_model_desc AS RecoveryModel,
    CAST(SUM(size)*8/1024.0 AS DECIMAL(10,2)) AS TotalSizeMB,
    CAST(SUM(size - used_space)*8/1024.0 AS DECIMAL(10,2)) AS FreeSpaceMB,
    CAST((SUM(size - used_space)/SUM(size))*100 AS DECIMAL(5,2)) AS FreePercent
FROM sys.master_files
WHERE type_desc = 'ROWS'
GROUP BY name, state_desc, recovery_model_desc
ORDER BY name
"@

四、SQL Server性能指标采集 #

抓取核心性能指标，识别潜在瓶颈：

$perfInfo = Invoke-SqlCmd -ServerInstance $sqlInstance -Credential $sqlCred -Query @"
-- CPU使用率（最近5分钟）
SELECT 
    'CPU使用率' AS Metric,
    CAST(100 - (cntr_value * 1.0 / 100) AS DECIMAL(5,2)) AS Value,
    '%' AS Unit
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Idle Time' AND object_name LIKE '%Processor%'

UNION ALL

-- 缓冲池命中率
SELECT 
    '缓冲池命中率' AS Metric,
    CAST((1 - (cntr_value * 1.0 / (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Page lookups/sec'))) * 100 AS DECIMAL(5,2)) AS Value,
    '%' AS Unit
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page reads/sec' AND object_name LIKE '%Buffer Manager%'

UNION ALL

-- TOP5等待类型
SELECT TOP 5
    wait_type AS Metric,
    wait_time_ms AS Value,
    'ms' AS Unit
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('SLEEP_TASK', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'LAZYWRITER_SLEEP')
ORDER BY wait_time_ms DESC
"@

五、失败作业检测 #

查询最近7天内SQL Agent的失败作业：

$failedJobs = Invoke-SqlCmd -ServerInstance $sqlInstance -Credential $sqlCred -Query @"
SELECT 
    j.name AS JobName,
    CONVERT(VARCHAR(10), CAST(h.run_date AS VARCHAR(8)), 120) AS RunDate,
    STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS RunTime,
    h.message AS FailureMessage
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
WHERE h.run_status = 0 -- 0表示作业失败
    AND h.run_date >= CONVERT(VARCHAR(8), GETDATE()-7, 112)
ORDER BY h.run_date DESC, h.run_time DESC
"@

六、生成HTML报告 #

将采集到的所有信息整合成可视化报告：

$reportPath = "C:\DB_Status_Report_$(Get-Date -Format 'yyyyMMddHHmm').html"
$htmlHeader = @"
<html>
<head>
    <title>Azure VM数据库环境状态报告</title>
    <style>
        table { border-collapse: collapse; width: 100%; margin: 10px 0; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
        th { background-color: #f2f2f2; }
        .section-title { font-size: 1.2em; font-weight: bold; margin-top: 20px; }
    </style>
</head>
<body>
    <h1>Azure VM数据库环境状态报告 - $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')</h1>
"@

$htmlFooter = "</body></html>"
$htmlContent = $htmlHeader

# 拼接磁盘信息
$htmlContent += "<div class='section-title'>一、磁盘空间信息</div>"
$htmlContent += "<h3>Azure托管磁盘</h3>" + ($diskInfo | ConvertTo-Html -Fragment)
$htmlContent += "<h3>VM内部磁盘</h3>" + ($internalDiskInfo | ConvertTo-Html -Fragment)

# 拼接数据库信息
$htmlContent += "<div class='section-title'>二、数据库状态信息</div>" + ($dbInfo | ConvertTo-Html -Fragment)

# 拼接性能指标
$htmlContent += "<div class='section-title'>三、核心性能指标</div>" + ($perfInfo | ConvertTo-Html -Fragment)

# 拼接失败作业
$htmlContent += "<div class='section-title'>四、最近7天失败作业</div>"
if ($failedJobs.Count -eq 0) {
    $htmlContent += "<p>无失败作业记录</p>"
} else {
    $htmlContent += ($failedJobs | ConvertTo-Html -Fragment)
}

$htmlContent += $htmlFooter
$htmlContent | Out-File -FilePath $reportPath -Encoding UTF8

七、配置定时任务 #

通过Windows任务计划程序实现自动执行：

1. 打开「任务计划程序」，点击「创建基本任务」
2. 命名任务（如「数据库状态定时报告」），设置触发周期（如每天凌晨2点）
3. 选择「启动程序」，程序/脚本填powershell.exe，添加参数填-ExecutionPolicy Bypass -File "C:\Path\To\Your\Script.ps1"
4. 勾选「使用最高权限运行」，确保脚本拥有足够权限

内容的提问来源于stack exchange，提问作者Army