# 1. 服务器准备
# 检查服务器配置
Get-WmiObject -Class Win32_ComputerSystem | Select-Object Name, NumberOfProcessors, TotalPhysicalMemory
GO

Name NumberOfProcessors TotalPhysicalMemory
—- —————— ——————-
dev-fgedu-sql01 4 17179869184

# 2. 安装SQLServer
# 下载SQLServer安装包
Invoke-WebRequest -Uri “https://go.microsoft.com/fwlink/?linkid=2215158” -OutFile “SQLServer2019-DEV.exe”

# 运行安装程序
Start-Process -FilePath “.\SQLServer2019-DEV.exe” -ArgumentList “/ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT=”NT Service\MSSQLSERVER” /SQLSYSADMINACCOUNTS=”BUILTIN\Administrators” /AGTSVCACCOUNT=”NT Service\SQLAgent$MSSQLSERVER” /IACCEPTSQLSERVERLICENSETERMS”

# 3. 配置开发环境
# 创建开发数据库
sqlcmd -S localhost -U sa -P “Fgedu@2026#Dev” -Q “CREATE DATABASE dev_fgedudb”
GO

# 创建开发用户
sqlcmd -S localhost -U sa -P “Fgedu@2026#Dev” -Q “CREATE LOGIN dev_fgedu WITH PASSWORD=’Fgedu@2026#Dev’, DEFAULT_DATABASE=dev_fgedudb”
GO

# 授予权限
sqlcmd -S localhost -U sa -P “Fgedu@2026#Dev” -Q “USE dev_fgedudb; CREATE USER dev_fgedu FOR LOGIN dev_fgedu; ALTER ROLE db_owner ADD MEMBER dev_fgedu”
GO

# 4. 配置连接字符串
# 开发环境连接字符串
Server=dev-fgedu-sql01;Database=dev_fgedudb;User ID=dev_fgedu;Password=Fgedu@2026#Dev;TrustServerCertificate=True;

# 5. 验证环境
# 连接测试
sqlcmd -S dev-fgedu-sql01 -U dev_fgedu -P “Fgedu@2026#Dev” -Q “SELECT @@SERVERNAME, DB_NAME()”
GO

———————— ————
dev-fgedu-sql01 dev_fgedudb

(1 rows affected)

# 1. 服务器准备
# 检查服务器配置
Get-WmiObject -Class Win32_ComputerSystem | Select-Object Name, NumberOfProcessors, TotalPhysicalMemory
GO

Name NumberOfProcessors TotalPhysicalMemory
—- —————— ——————-
test-fgedu-sql01 8 34359738368

# 2. 安装SQLServer
# 下载SQLServer安装包
Invoke-WebRequest -Uri “https://go.microsoft.com/fwlink/?linkid=2215158” -OutFile “SQLServer2019-ENT.exe”

# 运行安装程序
Start-Process -FilePath “.\SQLServer2019-ENT.exe” -ArgumentList “/ACTION=Install /FEATURES=SQL,AS,RS /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT=”NT Service\MSSQLSERVER” /SQLSYSADMINACCOUNTS=”BUILTIN\Administrators” /AGTSVCACCOUNT=”NT Service\SQLAgent$MSSQLSERVER” /IACCEPTSQLSERVERLICENSETERMS”

# 3. 配置测试环境
# 创建测试数据库
sqlcmd -S localhost -U sa -P “Fgedu@2026#Test” -Q “CREATE DATABASE test_fgedudb”
GO

# 创建测试用户
sqlcmd -S localhost -U sa -P “Fgedu@2026#Test” -Q “CREATE LOGIN test_fgedu WITH PASSWORD=’Fgedu@2026#Test’, DEFAULT_DATABASE=test_fgedudb”
GO

# 授予权限
sqlcmd -S localhost -U sa -P “Fgedu@2026#Test” -Q “USE test_fgedudb; CREATE USER test_fgedu FOR LOGIN test_fgedu; ALTER ROLE db_owner ADD MEMBER test_fgedu”
GO

# 4. 配置连接字符串
# 测试环境连接字符串
Server=test-fgedu-sql01;Database=test_fgedudb;User ID=test_fgedu;Password=Fgedu@2026#Test;TrustServerCertificate=True;

# 5. 验证环境
# 连接测试
sqlcmd -S test-fgedu-sql01 -U test_fgedu -P “Fgedu@2026#Test” -Q “SELECT @@SERVERNAME, DB_NAME()”
GO

———————— ————
test-fgedu-sql01 test_fgedudb

(1 rows affected)

# 1. 创建数据库项目
# 在Visual Studio中创建SQL Server数据库项目

# 2. 导入开发环境架构
# 右键项目 → 导入 → 数据库
# 连接到dev_fgedu-sql01/dev_fgedudb
# 导入架构到项目中

# 3. 同步到测试环境
# 右键项目 → 发布
# 连接到test-fgedu-sql01/test_fgedudb
# 生成发布脚本并执行

# 4. 验证同步结果
# 比较两个环境的架构
sqlcmd -S dev-fgedu-sql01 -U dev_fgedu -P “Fgedu@2026#Dev” -Q “SELECT name FROM sys.tables ORDER BY name”
GO

name
————————–
dev_fgedu_test

(1 rows affected)

sqlcmd -S test-fgedu-sql01 -U test_fgedu -P “Fgedu@2026#Test” -Q “SELECT name FROM sys.tables ORDER BY name”
GO

name
————————–
dev_fgedu_test

(1 rows affected)

# 使用PowerShell脚本同步架构
# 创建同步脚本
$sourceServer = “dev-fgedu-sql01”
$sourceDatabase = “dev_fgedudb”
$targetServer = “test-fgedu-sql01”
$targetDatabase = “test_fgedudb”
$sourceUser = “dev_fgedu”
$sourcePassword = “Fgedu@2026#Dev”
$targetUser = “test_fgedu”
$targetPassword = “Fgedu@2026#Test”

# 生成架构脚本
$schemaScript = “C:\Scripts\schema_sync.sql”
sqlcmd -S $sourceServer -U $sourceUser -P $sourcePassword -d $sourceDatabase -Q “SELECT ‘USE ‘ + QUOTENAME(DB_NAME())” -o $schemaScript
sqlcmd -S $sourceServer -U $sourceUser -P $sourcePassword -d $sourceDatabase -Q “SELECT definition FROM sys.sql_modules WHERE object_id IN (SELECT object_id FROM sys.objects WHERE type IN (‘P’, ‘V’, ‘FN’, ‘IF’, ‘TF’))” -o $schemaScript -a 32767 -h -1 -s “”

# 执行架构脚本
sqlcmd -S $targetServer -U $targetUser -P $targetPassword -d $targetDatabase -i $schemaScript

# 1. 配置数据源
# 源数据库：dev_fgedu-sql01/dev_fgedudb
# 目标数据库：test-fgedu-sql01/test_fgedudb

# 2. 选择要同步的表
# 选择需要同步的表和数据

# 3. 生成同步脚本
# 生成数据同步脚本

# 4. 执行同步
# 执行数据同步脚本

# 使用PowerShell脚本同步数据
# 创建数据同步脚本
$sourceServer = “dev-fgedu-sql01”
$sourceDatabase = “dev_fgedudb”
$targetServer = “test-fgedu-sql01”
$targetDatabase = “test_fgedudb”
$sourceUser = “dev_fgedu”
$sourcePassword = “Fgedu@2026#Dev”
$targetUser = “test_fgedu”
$targetPassword = “Fgedu@2026#Test”

# 生成数据同步脚本
$tables = @(“dev_fgedu_test”)
foreach ($table in $tables) {
$dataScript = “C:\Scripts\data_sync_$table.sql”
sqlcmd -S $sourceServer -U $sourceUser -P $sourcePassword -d $sourceDatabase -Q “SELECT ‘DELETE FROM $table;'” -o $dataScript
sqlcmd -S $sourceServer -U $sourceUser -P $sourcePassword -d $sourceDatabase -Q “SELECT ‘INSERT INTO $table (name, create_time) VALUES (”’ + name + ”’, ”’ + CONVERT(varchar, create_time, 120) + ”’);’ FROM $table” -o $dataScript -a 32767 -h -1 -s “”
sqlcmd -S $targetServer -U $targetUser -P $targetPassword -d $targetDatabase -i $dataScript
}

# 验证数据同步
sqlcmd -S dev-fgedu-sql01 -U dev_fgedu -P “Fgedu@2026#Dev” -Q “SELECT * FROM dev_fgedu_test”
GO

id name create_time
———– ———- ———————–
1 测试数据1 2026-04-08 10:30:00.000
2 测试数据2 2026-04-08 10:30:00.000

(2 rows affected)

sqlcmd -S test-fgedu-sql01 -U test_fgedu -P “Fgedu@2026#Test” -Q “SELECT * FROM dev_fgedu_test”
GO

id name create_time
———– ———- ———————–
1 测试数据1 2026-04-08 10:30:00.000
2 测试数据2 2026-04-08 10:30:00.000

(2 rows affected)

# 1. 创建监控作业
# 在开发环境创建监控作业
USE [msdb]
GO
EXEC dbo.sp_add_job
@job_name = N’Monitor_Dev_Environment’,
@enabled = 1,
@description = N’Monitor development environment’
GO

# 添加作业步骤
EXEC dbo.sp_add_jobstep
@job_name = N’Monitor_Dev_Environment’,
@step_name = N’Check Database Status’,
@subsystem = N’TSQL’,
@command = N’EXEC dbo.CheckDatabaseStatus’,
@database_name = N’dev_fgedudb’
GO

# 添加调度
EXEC dbo.sp_add_jobschedule
@job_name = N’Monitor_Dev_Environment’,
@name = N’Hourly Schedule’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 8,
@freq_subday_interval = 1
GO

# 2. 创建监控存储过程
USE [dev_fgedudb]
GO
CREATE PROCEDURE dbo.CheckDatabaseStatus
AS
BEGIN
SET NOCOUNT ON;

— 检查数据库状态
SELECT
name,
state_desc,
recovery_model_desc,
log_reuse_wait_desc
FROM sys.databases;

— 检查连接数
SELECT
COUNT(*) AS connection_count
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

— 检查阻塞
SELECT
session_id,
blocking_session_id,
wait_type,
wait_time
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;
END
GO

# 3. 配置邮件告警
# 启用数据库邮件
EXEC sp_configure ‘show advanced options’, 1;
RECONFIGURE;
EXEC sp_configure ‘Database Mail XPs’, 1;
RECONFIGURE;

# 创建邮件配置文件
EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘SQL Server Alert Profile’,
@description = ‘Profile for SQL Server alerts’;

# 创建邮件账户
EXEC msdb.dbo.sysmail_add_account_sp
@account_name = ‘SQL Server Alert Account’,
@email_address = ‘alerts@fgedu.net.cn’,
@mailserver_name = ‘smtp.fgedu.net.cn’;

# 关联邮件账户和配置文件
EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘SQL Server Alert Profile’,
@account_name = ‘SQL Server Alert Account’,
@sequence_number = 1;

# 4. 配置告警作业
USE [msdb]
GO
EXEC dbo.sp_add_alert
@name = N’Database Status Alert’,
@message_id = 0,
@severity = 16,
@enabled = 1,
@delay_between_responses = 300,
@include_event_description_in = 1;

# 配置告警通知
EXEC dbo.sp_add_notification
@alert_name = N’Database Status Alert’,
@operator_name = N’DBA Team’,
@notification_method = 1;

# 1. 创建监控报告存储过程
USE [master]
GO
CREATE PROCEDURE dbo.GenerateEnvironmentReport
AS
BEGIN
SET NOCOUNT ON;

— 创建临时表存储监控数据
CREATE TABLE #EnvironmentStatus (
Environment VARCHAR(50),
ServerName VARCHAR(128),
DatabaseName VARCHAR(128),
Status VARCHAR(50),
ConnectionCount INT,
BlockingCount INT,
LastBackup DATETIME
);

— 收集开发环境数据
INSERT INTO #EnvironmentStatus
EXEC (‘
SELECT
”Development” AS Environment,
@@SERVERNAME AS ServerName,
name AS DatabaseName,
state_desc AS Status,
(SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1) AS ConnectionCount,
(SELECT COUNT(*) FROM sys.dm_exec_requests WHERE blocking_session_id > 0) AS BlockingCount,
(SELECT MAX(backup_finish_date) FROM msdb.dbo.backupset WHERE database_name = name) AS LastBackup
FROM sys.databases
WHERE name NOT IN (”master”, ”model”, ”msdb”, ”tempdb”)
‘) AT [dev-fgedu-sql01];

— 收集测试环境数据
INSERT INTO #EnvironmentStatus
EXEC (‘
SELECT
”Testing” AS Environment,
@@SERVERNAME AS ServerName,
name AS DatabaseName,
state_desc AS Status,
(SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1) AS ConnectionCount,
(SELECT COUNT(*) FROM sys.dm_exec_requests WHERE blocking_session_id > 0) AS BlockingCount,
(SELECT MAX(backup_finish_date) FROM msdb.dbo.backupset WHERE database_name = name) AS LastBackup
FROM sys.databases
WHERE name NOT IN (”master”, ”model”, ”msdb”, ”tempdb”)
‘) AT [test-fgedu-sql01];

— 收集预生产环境数据
INSERT INTO #EnvironmentStatus
EXEC (‘
SELECT
”Staging” AS Environment,
@@SERVERNAME AS ServerName,
name AS DatabaseName,
state_desc AS Status,
(SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1) AS ConnectionCount,
(SELECT COUNT(*) FROM sys.dm_exec_requests WHERE blocking_session_id > 0) AS BlockingCount,
(SELECT MAX(backup_finish_date) FROM msdb.dbo.backupset WHERE database_name = name) AS LastBackup
FROM sys.databases
WHERE name NOT IN (”master”, ”model”, ”msdb”, ”tempdb”)
‘) AT [stage-fgedu-sql01];

— 收集生产环境数据
INSERT INTO #EnvironmentStatus
EXEC (‘
SELECT
”Production” AS Environment,
@@SERVERNAME AS ServerName,
name AS DatabaseName,
state_desc AS Status,
(SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1) AS ConnectionCount,
(SELECT COUNT(*) FROM sys.dm_exec_requests WHERE blocking_session_id > 0) AS BlockingCount,
(SELECT MAX(backup_finish_date) FROM msdb.dbo.backupset WHERE database_name = name) AS LastBackup
FROM sys.databases
WHERE name NOT IN (”master”, ”model”, ”msdb”, ”tempdb”)
‘) AT [prod-fgedu-sql01];

— 生成报告
SELECT * FROM #EnvironmentStatus ORDER BY Environment, DatabaseName;

— 清理临时表
DROP TABLE #EnvironmentStatus;
END
GO

# 2. 执行监控报告
EXEC dbo.GenerateEnvironmentReport;
GO

Environment ServerName DatabaseName Status ConnectionCount BlockingCount LastBackup
———– —————- ————- ——— ————— ————- ———————–
Development dev-fgedu-sql01 dev_fgedudb ONLINE 10 0 2026-04-08 09:00:00.000
Testing test-fgedu-sql01 test_fgedudb ONLINE 25 0 2026-04-08 08:00:00.000
Staging stage-fgedu-sql01 stage_fgedudb ONLINE 50 0 2026-04-08 07:00:00.000
Production prod-fgedu-sql01 prod_fgedudb ONLINE 200 0 2026-04-08 06:00:00.000