— 1. 自动化备份脚本
— 创建PowerShell备份脚本
cat > /sqlserver/scripts/backup.ps1 << 'EOF' #!/usr/bin/env pwsh # backup.ps1 # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn param( [string]$DatabaseName = "fgedudb", [string]$BackupPath = "\\fgedu-prod-01\Backup", [string]$BackupType = "Full" ) $date = Get-Date -Format "yyyyMMdd" $time = Get-Date -Format "HHmmss" $backupFile = "$BackupPath\$DatabaseName`_$BackupType`_$date`_$time.bak" try { Write-Host "开始备份数据库 $DatabaseName..." $query = "BACKUP DATABASE [$DatabaseName] TO DISK = '$backupFile' WITH INIT, COMPRESSION" Invoke-Sqlcmd -ServerInstance "localhost" -Database "master" -Query $query Write-Host "备份完成: $backupFile" # 清理旧备份（保留7天） $oldBackups = Get-ChildItem -Path $BackupPath -Filter "$DatabaseName`_$BackupType`_*.bak" | Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-7)} foreach ($backup in $oldBackups) { Write-Host "清理旧备份: $($backup.FullName)" Remove-Item -Path $backup.FullName -Force } } catch { Write-Host "备份失败: $($_.Exception.Message)" exit 1 } EOF -- 2. 自动化维护脚本 -- 创建PowerShell维护脚本 cat > /sqlserver/scripts/maintenance.ps1 << 'EOF' #!/usr/bin/env pwsh # maintenance.ps1 # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn param( [string]$DatabaseName = "fgedudb" ) try { Write-Host "开始维护数据库 $DatabaseName..." # 数据库完整性检查 Write-Host "执行数据库完整性检查..." $query = "DBCC CHECKDB('$DatabaseName') WITH NO_INFOMSGS" Invoke-Sqlcmd -ServerInstance "localhost" -Database "master" -Query $query # 更新统计信息 Write-Host "更新统计信息..." $query = "UPDATE STATISTICS fgedu.sales WITH FULLSCAN" Invoke-Sqlcmd -ServerInstance "localhost" -Database $DatabaseName -Query $query # 重建索引 Write-Host "重建索引..." $query = "ALTER INDEX ALL ON fgedu.sales REBUILD WITH (ONLINE = ON, FILLFACTOR = 80)" Invoke-Sqlcmd -ServerInstance "localhost" -Database $DatabaseName -Query $query Write-Host "维护完成" } catch { Write-Host "维护失败: $($_.Exception.Message)" exit 1 } EOF -- 3. 自动化监控脚本 -- 创建PowerShell监控脚本 cat > /sqlserver/scripts/monitor.ps1 << 'EOF' #!/usr/bin/env pwsh # monitor.ps1 # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn param( [string]$OutputFile = "/sqlserver/monitor/metrics.json" ) try { Write-Host "开始收集监控指标..." # 收集CPU使用率 $cpuUsage = (Get-WmiObject -Class win32_processor | Measure-Object -Property LoadPercentage -Average).Average # 收集内存使用率 $memory = Get-WmiObject -Class win32_operatingsystem $memoryUsage = ((($memory.TotalVisibleMemorySize - $memory.FreePhysicalMemory) / $memory.TotalVisibleMemorySize) * 100).ToString("0.00") # 收集磁盘使用率 $disk = Get-WmiObject -Class win32_logicaldisk -Filter "DeviceID='D:'" $diskUsage = ((($disk.Size - $disk.FreeSpace) / $disk.Size) * 100).ToString("0.00") # 收集数据库连接数 $query = "SELECT COUNT(*) AS connection_count FROM sys.dm_exec_sessions" $connectionCount = Invoke-Sqlcmd -ServerInstance "localhost" -Database "master" -Query $query | Select-Object -ExpandProperty connection_count # 收集慢查询 $query = "SELECT TOP 5 total_worker_time/1000 as total_time_ms, execution_count, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) as sql_text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st ORDER BY total_worker_time DESC" $slowQueries = Invoke-Sqlcmd -ServerInstance "localhost" -Database "master" -Query $query # 生成指标JSON $metrics = @{ timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss" cpu_usage = $cpuUsage memory_usage = $memoryUsage disk_usage = $diskUsage connection_count = $connectionCount slow_queries = $slowQueries | ForEach-Object { @{ total_time_ms = $_.total_time_ms execution_count = $_.execution_count sql_text = $_.sql_text } } } # 输出到文件 $metrics | ConvertTo-Json -Depth 3 | Out-File -FilePath $OutputFile -Force Write-Host "监控指标收集完成: $OutputFile" } catch { Write-Host "监控失败: $($_.Exception.Message)" exit 1 } EOF -- 4. SQL Server Agent作业 -- 创建备份作业 EXEC dbo.sp_add_job @job_name = N'AutomatedBackup'; EXEC dbo.sp_add_jobstep @job_name = N'AutomatedBackup', @step_name = N'Full Backup', @subsystem = N'PowerShell', @command = N'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -File "D:\SQLServer\Scripts\backup.ps1" -DatabaseName "fgedudb" -BackupPath "\\fgedu-prod-01\Backup" -BackupType "Full"', @database_name = N'master'; EXEC dbo.sp_add_schedule @schedule_name = N'DailyAt2AM', @freq_type = 4, @freq_interval = 1, @active_start_time = 020000; EXEC dbo.sp_attach_schedule @job_name = N'AutomatedBackup', @schedule_name = N'DailyAt2AM'; EXEC dbo.sp_add_jobserver @job_name = N'AutomatedBackup'; -- 创建维护作业 EXEC dbo.sp_add_job @job_name = N'AutomatedMaintenance'; EXEC dbo.sp_add_jobstep @job_name = N'AutomatedMaintenance', @step_name = N'Maintenance', @subsystem = N'PowerShell', @command = N'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -File "D:\SQLServer\Scripts\maintenance.ps1" -DatabaseName "fgedudb"', @database_name = N'master'; EXEC dbo.sp_add_schedule @schedule_name = N'WeeklyAt3AM', @freq_type = 8, @freq_interval = 1, @active_start_time = 030000; EXEC dbo.sp_attach_schedule @job_name = N'AutomatedMaintenance', @schedule_name = N'WeeklyAt3AM'; EXEC dbo.sp_add_jobserver @job_name = N'AutomatedMaintenance'; -- 创建监控作业 EXEC dbo.sp_add_job @job_name = N'AutomatedMonitor'; EXEC dbo.sp_add_jobstep @job_name = N'AutomatedMonitor', @step_name = N'Monitor', @subsystem = N'PowerShell', @command = N'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -File "D:\SQLServer\Scripts\monitor.ps1" -OutputFile "D:\SQLServer\Monitor\metrics.json"', @database_name = N'master'; EXEC dbo.sp_add_schedule @schedule_name = N'Every15Minutes', @freq_type = 4, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 15, @active_start_time = 000000; EXEC dbo.sp_attach_schedule @job_name = N'AutomatedMonitor', @schedule_name = N'Every15Minutes'; EXEC dbo.sp_add_jobserver @job_name = N'AutomatedMonitor';

执行结果：