# 1. 开发环境搭建
# 创建开发环境数据库
CREATE DATABASE fgedudb_dev;
GO

# 配置开发环境参数
ALTER DATABASE fgedudb_dev SET COMPATIBILITY_LEVEL = 150;
GO

ALTER DATABASE fgedudb_dev SET RECOVERY SIMPLE;
GO

# 2. 测试环境搭建
# 创建测试环境数据库
CREATE DATABASE fgedudb_test;
GO

# 配置测试环境参数
ALTER DATABASE fgedudb_test SET COMPATIBILITY_LEVEL = 150;
GO

ALTER DATABASE fgedudb_test SET RECOVERY FULL;
GO

# 3. 预生产环境搭建
# 创建预生产环境数据库
CREATE DATABASE fgedudb_staging;
GO

# 配置预生产环境参数
ALTER DATABASE fgedudb_staging SET COMPATIBILITY_LEVEL = 150;
GO

ALTER DATABASE fgedudb_staging SET RECOVERY FULL;
GO

# 4. 生产环境搭建
# 创建生产环境数据库
CREATE DATABASE fgedudb_prod
ON PRIMARY (
NAME = ‘fgedudb_prod_data’,
FILENAME = ‘/sqlserver/fgdata/fgedudb_prod_data.mdf’,
SIZE = 100GB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10GB
)
LOG ON (
NAME = ‘fgedudb_prod_log’,
FILENAME = ‘/sqlserver/fgdata/fgedudb_prod_log.ldf’,
SIZE = 20GB,
MAXSIZE = 200GB,
FILEGROWTH = 5GB
);
GO

# 配置生产环境参数
ALTER DATABASE fgedudb_prod SET COMPATIBILITY_LEVEL = 150;
GO

ALTER DATABASE fgedudb_prod SET RECOVERY FULL;
GO

# 配置自动备份
USE msdb;
GO

EXEC dbo.sp_add_job
@job_name = N’Production Database Backup’,
@enabled = 1,
@description = N’Full backup of production database’;
GO

EXEC dbo.sp_add_jobstep
@job_name = N’Production Database Backup’,
@step_name = N’Full Backup’,
@subsystem = N’TSQL’,
@command = N’BACKUP DATABASE fgedudb_prod TO DISK = ”\fgedu1\backup\fgedudb_prod_full.bak” WITH INIT, COMPRESSION;’,
@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;
GO

EXEC dbo.sp_attach_schedule
@job_name = N’Production Database Backup’,
@schedule_name = N’Daily Full Backup’;
GO

EXEC dbo.sp_add_jobserver
@job_name = N’Production Database Backup’;
GO

# 1. 架构同步
# 使用SQL Server Data Tools (SSDT)进行架构同步
# 或使用Redgate SQL Compare等工具

# 2. 数据同步
# 使用SQL Server Integration Services (SSIS)进行数据同步

# 创建SSIS包同步数据
# 从生产环境同步到测试环境（脱敏处理）

# 3. 配置同步
# 使用PowerShell脚本同步配置

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

param(
[string]$SourceServer = “fgedu-prod”,
[string]$TargetServer = “fgedu-test”,
[string]$Database = “fgedudb”
)

# 同步数据库参数
$params = @(
“COMPATIBILITY_LEVEL”,
“IS_AUTO_CLOSE_ON”,
“IS_AUTO_SHRINK_ON”,
“IS_AUTO_UPDATE_STATISTICS_ON”,
“IS_PARAMETERIZATION_FORCED”,
“RECOVERY_MODEL”
)

foreach ($param in $params) {
$sourceValue = Invoke-Sqlcmd -ServerInstance $SourceServer -Database $Database -Query “SELECT DATABASEPROPERTYEX(‘$Database’, ‘$param’)”
$targetValue = Invoke-Sqlcmd -ServerInstance $TargetServer -Database $Database -Query “SELECT DATABASEPROPERTYEX(‘$Database’, ‘$param’)”

if ($sourceValue.Column1 -ne $targetValue.Column1) {
Write-Host “Syncing $param from $SourceServer to $TargetServer: $($sourceValue.Column1)”
# 生成并执行ALTER DATABASE语句
$sql = “ALTER DATABASE $Database SET $param = $($sourceValue.Column1)”
Invoke-Sqlcmd -ServerInstance $TargetServer -Database “master” -Query $sql
}
}

Write-Host “Configuration sync completed!”

# 4. 环境监控
# 使用PowerShell脚本监控各环境状态

# monitor_environments.ps1

$environments = @(
@{Name = “Development”; Server = “fgedu-dev”; Database = “fgedudb_dev”},
@{Name = “Testing”; Server = “fgedu-test”; Database = “fgedudb_test”},
@{Name = “Staging”; Server = “fgedu-staging”; Database = “fgedudb_staging”},
@{Name = “Production”; Server = “fgedu-prod”; Database = “fgedudb_prod”}
)

foreach ($env in $environments) {
try {
$status = Invoke-Sqlcmd -ServerInstance $env.Server -Database $env.Database -Query “SELECT DATABASEPROPERTYEX(‘$($env.Database)’, ‘Status’)”
Write-Host “$($env.Name) environment: $($status.Column1)”
} catch {
Write-Host “$($env.Name) environment: ERROR – $($_.Exception.Message)”
}
}

# 1. 开发环境部署
# 开发人员在开发环境中开发和测试

# 2. 测试环境部署
# 使用CI/CD工具部署到测试环境

# Azure DevOps pipeline示例
# yaml

# trigger:
# – main

# pool:
# vmImage: ‘windows-latest’

# steps:
# – task: SqlAzureDacpacDeployment@1
# inputs:
# azureSubscription: ‘FGEDU-Azure-Subscription’
# ServerName: ‘fgedu-test.database.windows.net’
# DatabaseName: ‘fgedudb_test’
# SqlUsername: ‘fgedu’
# SqlPassword: ‘$(SqlPassword)’
# DacpacFile: ‘$(Build.ArtifactStagingDirectory)/fgedudb.dacpac’
# Action: ‘Publish’

# 3. 预生产环境部署
# 手动审核后部署到预生产环境

# 4. 生产环境部署
# 手动审核后部署到生产环境

# 部署脚本示例
# deploy_database.ps1

param(
[string]$Environment = “dev”,
[string]$ScriptPath = “C:\scripts\database”
)

# 环境配置
$configs = @{
“dev” = @{Server = “fgedu-dev”; Database = “fgedudb_dev”; Username = “fgedu”; Password = “FgEdu123!@#”};
“test” = @{Server = “fgedu-test”; Database = “fgedudb_test”; Username = “fgedu”; Password = “FgEdu123!@#”};
“staging” = @{Server = “fgedu-staging”; Database = “fgedudb_staging”; Username = “fgedu”; Password = “FgEdu123!@#”};
“prod” = @{Server = “fgedu-prod”; Database = “fgedudb_prod”; Username = “fgedu”; Password = “FgEdu123!@#”}
}

$config = $configs[$Environment]

Write-Host “Deploying to $Environment environment…”

# 执行部署脚本
$scripts = Get-ChildItem -Path $ScriptPath -Filter “*.sql” | Sort-Object Name
foreach ($script in $scripts) {
Write-Host “Executing script: $($script.Name)”
Invoke-Sqlcmd -ServerInstance $config.Server -Database $config.Database -Username $config.Username -Password $config.Password -InputFile $script.FullName
}

Write-Host “Deployment to $Environment environment completed!”