# 配置SQL Server Agent
# 1. 启用SQL Server Agent服务
USE master;
GO

EXEC xp_instance_regwrite
N’HKEY_LOCAL_MACHINE’,
N’SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent’,
N’Startup’,
REG_DWORD,
2; — 2=自动启动
GO

# 2. 配置SQL Server Agent服务账户
# 在SQL Server配置管理器中设置

# 3. 创建作业类别
USE msdb;
GO

EXEC dbo.sp_add_category
@class = N’JOB’,
@type = N’LOCAL’,
@name = N’FGEDU Maintenance’;
GO

# 4. 创建维护作业
EXEC dbo.sp_add_job
@job_name = N’FGEDU Database Backup’,
@enabled = 1,
@description = N’Full backup of all databases’,
@category_name = N’FGEDU Maintenance’;
GO

# 添加作业步骤
EXEC dbo.sp_add_jobstep
@job_name = N’FGEDU Database Backup’,
@step_name = N’Backup Databases’,
@subsystem = N’TSQL’,
@command = N’EXEC dbo.usp_backup_databases @backup_type = ”FULL”;’,
@database_name = N’master’;
GO

# 创建调度
EXEC dbo.sp_add_schedule
@schedule_name = N’Daily Full Backup’,
@freq_type = 4, — 每天
@freq_interval = 1,
@freq_subday_type = 1, — 一次
@freq_subday_interval = 0,
@active_start_time = 20000; — 2:00 AM
GO

# 附加调度到作业
EXEC dbo.sp_attach_schedule
@job_name = N’FGEDU Database Backup’,
@schedule_name = N’Daily Full Backup’;
GO

# 添加作业服务器
EXEC dbo.sp_add_jobserver
@job_name = N’FGEDU Database Backup’;
GO

# 使用PowerShell自动化管理
# 创建PowerShell脚本备份数据库

# 数据库备份脚本
# backup_databases.ps1
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

param(
[string]$ServerInstance = “fgedu1”,
[string]$BackupPath = “\\fgedu1\backup”,
[string]$BackupType = “FULL”
)

Import-Module SqlServer

# 获取所有用户数据库
$databases = Get-SqlDatabase -ServerInstance $ServerInstance | Where-Object { $_.Name -ne ‘master’ -and $_.Name -ne ‘model’ -and $_.Name -ne ‘msdb’ -and $_.Name -ne ‘tempdb’ }

# 备份每个数据库
foreach ($db in $databases) {
$backupFile = “$BackupPath\$($db.Name)_$($BackupType)_$(Get-Date -Format ‘yyyyMMdd_HHmmss’).bak”
Write-Host “Backing up database $($db.Name) to $backupFile”
Backup-SqlDatabase -ServerInstance $ServerInstance -Database $db.Name -BackupFile $backupFile -BackupAction $BackupType
}

Write-Host “Backup completed successfully!”

# 执行脚本
# PowerShell -File “C:\scripts\backup_databases.ps1” -ServerInstance “fgedu1” -BackupPath “\\fgedu1\backup” -BackupType “FULL”

# 使用SSIS设计自动化工作流
# 1. 创建SSIS项目
# 2. 添加数据流任务
# 3. 配置数据源和目标
# 4. 添加控制流任务
# 5. 配置错误处理
# 6. 部署SSIS包

# 使用PowerShell设计自动化工作流
# 自动化维护工作流脚本

# maintenance_workflow.ps1
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

param(
[string]$ServerInstance = “fgedu1”
)

Import-Module SqlServer

# 步骤1：检查数据库状态
Write-Host “Step 1: Checking database status…”
$databases = Get-SqlDatabase -ServerInstance $ServerInstance | Where-Object { $_.Name -ne ‘tempdb’ }
foreach ($db in $databases) {
Write-Host “Database $($db.Name): Status = $($db.Status)”
}

# 步骤2：执行数据库备份
Write-Host “Step 2: Performing database backups…”
& “C:\scripts\backup_databases.ps1” -ServerInstance $ServerInstance -BackupType “FULL”

# 步骤3：检查索引碎片
Write-Host “Step 3: Checking index fragmentation…”
$fragmentedIndexes = @()
foreach ($db in $databases) {
if ($db.Name -ne ‘master’ -and $db.Name -ne ‘model’ -and $db.Name -ne ‘msdb’) {
$tables = Get-SqlTable -ServerInstance $ServerInstance -Database $db.Name
foreach ($table in $tables) {
$indexes = Get-SqlIndex -ServerInstance $ServerInstance -Database $db.Name -Table $table.Name
foreach ($index in $indexes) {
$fragmentation = Get-SqlIndexFragmentation -ServerInstance $ServerInstance -Database $db.Name -Table $table.Name -Index $index.Name
if ($fragmentation.AvgFragmentationPercent -gt 30) {
$fragmentedIndexes += @{ Database = $db.Name; Table = $table.Name; Index = $index.Name; Fragmentation = $fragmentation.AvgFragmentationPercent }
}
}
}
}
}

# 步骤4：重建或重组索引
Write-Host “Step 4: Rebuilding or reorganizing indexes…”
foreach ($index in $fragmentedIndexes) {
if ($index.Fragmentation -gt 30) {
Write-Host “Rebuilding index $($index.Index) on table $($index.Table) in database $($index.Database)”
$query = “ALTER INDEX $($index.Index) ON $($index.Database).dbo.$($index.Table) REBUILD”
Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $query
} elseif ($index.Fragmentation -gt 5) {
Write-Host “Reorganizing index $($index.Index) on table $($index.Table) in database $($index.Database)”
$query = “ALTER INDEX $($index.Index) ON $($index.Database).dbo.$($index.Table) REORGANIZE”
Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $query
}
}

# 步骤5：更新统计信息
Write-Host “Step 5: Updating statistics…”
foreach ($db in $databases) {
if ($db.Name -ne ‘master’ -and $db.Name -ne ‘model’ -and $db.Name -ne ‘msdb’) {
Write-Host “Updating statistics for database $($db.Name)”
$query = “USE $($db.Name); EXEC sp_updatestats”
Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $query
}
}

Write-Host “Maintenance workflow completed successfully!”

# 自动化部署SQL Server数据库
# 1. 创建部署脚本

# deploy_database.ps1
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

param(
[string]$ServerInstance = “fgedu1”,
[string]$DatabaseName = “fgedudb”,
[string]$ScriptPath = “C:\scripts\database”,
[string]$BackupPath = “\\fgedu1\backup”
)

Import-Module SqlServer

# 步骤1：检查数据库是否存在
Write-Host “Step 1: Checking if database exists…”
$dbExists = Get-SqlDatabase -ServerInstance $ServerInstance | Where-Object { $_.Name -eq $DatabaseName }

if ($dbExists) {
# 步骤2：备份现有数据库
Write-Host “Step 2: Backing up existing database…”
$backupFile = “$BackupPath\$($DatabaseName)_predeploy_$(Get-Date -Format ‘yyyyMMdd_HHmmss’).bak”
Backup-SqlDatabase -ServerInstance $ServerInstance -Database $DatabaseName -BackupFile $backupFile
}

# 步骤3：执行部署脚本
Write-Host “Step 3: Executing deployment scripts…”
$scripts = Get-ChildItem -Path $ScriptPath -Filter “*.sql” | Sort-Object Name
foreach ($script in $scripts) {
Write-Host “Executing script: $($script.Name)”
Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $DatabaseName -InputFile $script.FullName
}

# 步骤4：验证部署
Write-Host “Step 4: Verifying deployment…”
$tables = Get-SqlTable -ServerInstance $ServerInstance -Database $DatabaseName
Write-Host “Tables in $DatabaseName: $($tables.Count)”

Write-Host “Deployment completed successfully!”

# 2. 创建CI/CD pipeline
# 在Azure DevOps中创建管道，配置以下步骤：
# – 拉取代码
# – 构建数据库项目
# – 部署到测试环境
# – 运行测试
# – 部署到生产环境