SQL Server跨版本升级的全流程，涵盖备份迁移、兼容性调整、性能优化等关键环节

-- SQL Server 2014 vs 2022功能差异演示
-- 旧版不支持STRING_AGG函数
SELECT 
    DepartmentID,
    STUFF((SELECT ', ' + EmployeeName 
           FROM Employees e2 
           WHERE e1.DepartmentID = e2.DepartmentID 
           FOR XML PATH('')), 1, 2, '') AS Employees
FROM Employees e1
GROUP BY DepartmentID;

-- 2022新版写法
SELECT 
    DepartmentID,
    STRING_AGG(EmployeeName, ', ') AS Employees
FROM Employees
GROUP BY DepartmentID;

Invoke-SqlCmd -Query "DBCC CHECKDB ('AdventureWorks') WITH NO_INFOMSGS, ALL_ERRORMSGS" `
-ServerInstance "OLD_SERVER"

# 检查兼容性级别
$compatibility = Invoke-SqlCmd -Query "SELECT compatibility_level 
                                      FROM sys.databases WHERE name = 'AdventureWorks'"
if ($compatibility -lt 130) {
    Write-Warning "需要升级兼容性级别！"
}

-- 迁移旧版证书到新版TDE
USE master;
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'old_password';
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'New@Strong!Pass2023';

-- 重新加密数据库
ALTER DATABASE AdventureWorks
SET ENCRYPTION ON;

-- 利用2022查询存储增强功能
ALTER DATABASE CURRENT SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
ALTER DATABASE CURRENT SET QUERY_STORE CLEAR;

-- 对比升级前后执行计划
SELECT 
    qsp.plan_id,
    qsqt.query_sql_text,
    qspy.avg_duration / 1000 AS avg_ms
FROM sys.query_store_plan qsp
JOIN sys.query_store_query qsq ON qsp.query_id = qsq.query_id
JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_runtime_stats qspy ON qsp.plan_id = qspy.plan_id
WHERE qsqt.query_sql_text LIKE '%Orders%';

-- 禁用已弃用功能
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 0;
EXEC sp_configure 'Database Mail XPs', 0;
RECONFIGURE;

-- 处理列存储索引碎片
ALTER INDEX ALL ON Sales.OrderDetail REORGANIZE 
WITH (LOB_COMPACTION = ON);

-- 统计信息更新
UPDATE STATISTICS Sales.OrderDetail 
WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

# DNS别名切换脚本
Add-DnsServerResourceRecordCName -Name "sqlprod" -HostNameAlias "sql2022-01" -ZoneName "contoso.com"
Start-Sleep -Seconds 300
Remove-DnsServerResourceRecord -Name "sqlprod" -RRType "CNAME" -ZoneName "contoso.com" -Force

-- 快速回滚脚本模板
EXEC sp_detach_db 'AdventureWorks';
RESTORE DATABASE AdventureWorks 
FROM DISK = 'F:\Backup\pre_upgrade.bak' 
WITH REPLACE, RECOVERY;

-- 使用新扩展事件跟踪死锁
CREATE EVENT SESSION [Deadlock_Monitor] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'Deadlock_Monitor.xel')
WITH (STARTUP_STATE=ON);