外观
SQLServer 升级后验证
SQL Server 升级完成后,必须进行全面的验证,以确保系统能够正常运行,满足业务需求。升级后验证是确保升级成功的关键步骤,直接关系到业务连续性和系统稳定性。
对于生产环境中的 DBA 来说,升级后验证需要系统化、标准化,确保覆盖所有关键领域。不同 SQL Server 版本在升级后验证上存在差异,需要根据目标版本的特性调整验证重点。
升级后验证目标与范围
验证目标
- 确保系统可用性:验证 SQL Server 实例和数据库能够正常启动和运行
- 确保功能完整性:验证所有核心功能和组件能够正常工作
- 确保性能满足需求:验证升级后的性能符合预期,不低于升级前的水平
- 确保数据完整性:验证数据库数据的完整性和一致性
- 确保安全性合规:验证系统的安全性配置符合要求
验证范围
- SQL Server 实例:服务状态、版本信息、配置选项
- 数据库:状态、完整性、兼容性级别
- 核心组件:SQL Server 代理、Integration Services、Analysis Services 等
- 应用程序:与 SQL Server 的兼容性和核心功能
- 性能指标:CPU、内存、磁盘 I/O、查询性能等
- 安全性配置:登录账号、权限、加密配置等
版本差异要点
| SQL Server 版本 | 升级后验证重点差异 |
|---|---|
| SQL Server 2016 | 重点验证 Query Store 功能、JSON 支持 |
| SQL Server 2017 | 验证 Linux 兼容性、图形数据库功能 |
| SQL Server 2019 | 验证智能查询处理、UTF-8 支持、增强的 Always On |
| SQL Server 2022 | 验证增强的查询存储、JSON 路径索引、新的安全功能 |
系统验证
SQL Server 服务状态
检查 SQL Server 服务状态
powershell# 使用 PowerShell 检查核心服务 Get-Service -Name MSSQLSERVER, SQLSERVERAGENT, SQLBrowser, MSSQLFDLauncher, SQLWriter | Format-Table -AutoSize验证 SQL Server 实例连接
sql-- 使用 sqlcmd 或 PowerShell 验证连接 -- sqlcmd -S <ServerName> -U <UserName> -P <Password> -Q "SELECT @@VERSION" -- 使用 T-SQL 验证基本功能 SELECT 'SQL Server 连接成功' AS Result, @@SERVERNAME AS ServerName, GETDATE() AS CurrentTime;
SQL Server 版本信息
sql
-- 详细验证版本信息
SELECT
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel,
SERVERPROPERTY('ProductUpdateReference') AS ProductUpdateReference,
@@VERSION AS FullVersionInfo;数据库状态
sql
-- 验证所有数据库的基本状态
SELECT
name AS DatabaseName,
state_desc AS State,
recovery_model_desc AS RecoveryModel,
compatibility_level AS CompatibilityLevel,
create_date AS CreateDate
FROM sys.databases
ORDER BY name;
-- 验证数据库文件状态
SELECT
DB_NAME(database_id) AS DatabaseName,
name AS FileName,
type_desc AS FileType,
state_desc AS FileState,
size * 8 / 1024 AS SizeMB,
physical_name AS PhysicalPath
FROM sys.master_files
ORDER BY DatabaseName, FileType;系统配置验证
sql
-- 验证关键配置选项
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure N'max server memory (MB)';
EXEC sp_configure N'min server memory (MB)';
EXEC sp_configure N'max degree of parallelism';
EXEC sp_configure N'cost threshold for parallelism';
EXEC sp_configure N'optimize for ad hoc workloads';
-- 验证内存使用情况
SELECT
physical_memory_kb / 1024 AS PhysicalMemoryMB,
locked_page_allocations_kb / 1024 AS LockedPagesMB,
virtual_address_space_committed_kb / 1024 AS VirtualMemoryMB
FROM sys.dm_os_process_memory;数据库完整性验证
数据库一致性检查
sql
-- 对所有用户数据库执行一致性检查
EXEC sp_MSforeachdb 'IF DB_ID(''?'') > 4 BEGIN
PRINT ''正在检查数据库: ?'';
DBCC CHECKDB(''?'') WITH NO_INFOMSGS, ALL_ERRORMSGS, PHYSICAL_ONLY;
END';
-- 对系统数据库执行一致性检查
DBCC CHECKDB('master') WITH NO_INFOMSGS, ALL_ERRORMSGS, PHYSICAL_ONLY;
DBCC CHECKDB('model') WITH NO_INFOMSGS, ALL_ERRORMSGS, PHYSICAL_ONLY;
DBCC CHECKDB('msdb') WITH NO_INFOMSGS, ALL_ERRORMSGS, PHYSICAL_ONLY;
DBCC CHECKDB('tempdb') WITH NO_INFOMSGS, ALL_ERRORMSGS, PHYSICAL_ONLY;数据完整性验证
验证关键表数据
sql-- 示例:验证订单表的数据完整性 SELECT COUNT(*) AS TotalOrders, MIN(OrderDate) AS MinOrderDate, MAX(OrderDate) AS MaxOrderDate, COUNT(DISTINCT CustomerID) AS UniqueCustomers FROM Sales.Orders;验证索引完整性
sql-- 验证所有用户表的索引状态 SELECT DB_NAME(database_id) AS DatabaseName, OBJECT_NAME(object_id) AS TableName, name AS IndexName, index_id AS IndexID, state_desc AS IndexState, type_desc AS IndexType, avg_fragmentation_in_percent AS FragmentationPercent FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'LIMITED') ips JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id WHERE ips.database_id > 4 -- 排除系统数据库 AND i.type_desc != 'HEAP' -- 排除堆表 ORDER BY FragmentationPercent DESC;验证约束完整性
sql-- 验证所有外键约束 SELECT DB_NAME() AS DatabaseName, CONSTRAINT_NAME, TABLE_NAME AS ChildTable, COLUMN_NAME AS ChildColumn, REFERENCED_TABLE_NAME AS ParentTable, REFERENCED_COLUMN_NAME AS ParentColumn FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME;
功能验证
核心功能验证
SQL Server 代理作业
sql-- 验证代理作业状态 SELECT name AS JobName, enabled AS IsEnabled, CASE WHEN last_run_outcome = 0 THEN '失败' WHEN last_run_outcome = 1 THEN '成功' WHEN last_run_outcome = 2 THEN '重试' WHEN last_run_outcome = 3 THEN '取消' WHEN last_run_outcome = 4 THEN '进行中' ELSE '未知' END AS LastRunStatus, CASE WHEN enabled = 1 THEN '手动运行作业测试' ELSE '作业已禁用,跳过测试' END AS TestAction FROM msdb.dbo.sysjobs ORDER BY name;备份和恢复功能
sql-- 测试备份功能(使用临时数据库) CREATE DATABASE TestBackupRestore; GO BACKUP DATABASE TestBackupRestore TO DISK = 'C:\Temp\TestBackupRestore.bak' WITH COMPRESSION, CHECKSUM; GO -- 测试恢复功能 RESTORE DATABASE TestBackupRestore_Restored FROM DISK = 'C:\Temp\TestBackupRestore.bak' WITH NORECOVERY, MOVE 'TestBackupRestore' TO 'C:\Temp\TestBackupRestore_Restored.mdf', MOVE 'TestBackupRestore_log' TO 'C:\Temp\TestBackupRestore_Restored.ldf'; RESTORE DATABASE TestBackupRestore_Restored WITH RECOVERY; GO -- 验证恢复后的数据库 SELECT name, state_desc FROM sys.databases WHERE name LIKE 'TestBackupRestore%'; GO -- 清理测试数据库 DROP DATABASE IF EXISTS TestBackupRestore; DROP DATABASE IF EXISTS TestBackupRestore_Restored; GO高可用性功能
sql-- 验证 Always On 可用性组状态(如适用) IF EXISTS (SELECT * FROM sys.availability_groups) BEGIN SELECT ag.name AS AGName, ar.replica_server_name AS ReplicaServer, ar.role_desc AS Role, ar.synchronization_health_desc AS SyncHealth, ar.connected_state_desc AS ConnectionState FROM sys.dm_hadr_availability_replica_states ar JOIN sys.availability_groups ag ON ar.group_id = ag.group_id; END ELSE BEGIN PRINT 'Always On 可用性组未配置'; END
应用程序兼容性验证
应用程序连接测试
- 更新应用程序连接字符串,指向升级后的 SQL Server 实例
- 测试应用程序的核心功能,如用户登录、数据查询、事务处理
- 监控应用程序日志,查找兼容性错误
自定义代码验证
- 执行关键存储过程、函数、触发器测试
- 验证视图和同义词是否正常工作
- 检查错误日志,查找自定义代码执行错误
第三方应用程序验证
- 验证第三方应用程序与新 SQL Server 版本的兼容性
- 测试第三方应用程序的核心功能
- 联系第三方供应商,获取兼容性确认
版本特定功能验证
根据目标 SQL Server 版本,验证新增或增强的功能:
Query Store 验证(SQL Server 2016+)
sql-- 启用并验证 Query Store ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON; SELECT name AS DatabaseName, actual_state_desc AS QueryStoreState, is_query_store_on AS IsQueryStoreEnabled FROM sys.database_query_store_options;智能查询处理验证(SQL Server 2019+)
sql-- 验证智能查询处理相关配置 SELECT name AS DatabaseName, compatibility_level AS CompatibilityLevel, is_parameter_sniffing_enabled AS ParameterSniffingEnabled, is_query_store_on AS QueryStoreEnabled FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb');JSON 路径索引验证(SQL Server 2022+)
sql-- 如适用,测试 JSON 路径索引功能 IF SERVERPROPERTY('ProductMajorVersion') >= 16 BEGIN -- 创建测试表和 JSON 路径索引 CREATE TABLE TestJSON (Id INT PRIMARY KEY, JsonData NVARCHAR(MAX) CHECK (ISJSON(JsonData) = 1)); CREATE INDEX IX_TestJSON_JsonData_Path ON TestJSON(JSON_VALUE(JsonData, '$.CustomerId')); PRINT 'JSON 路径索引创建成功'; DROP TABLE TestJSON; END
性能验证
性能基线比较
建立性能基线
- 在升级前使用 Performance Monitor 或 SQL Server Profiler 建立性能基线
- 采集关键指标:CPU 使用率、内存使用率、磁盘 I/O、批处理请求数、等待统计信息
- 保存基线数据,用于升级后比较
关键性能指标验证
sql-- 查看 CPU 使用率 SELECT record_id, DATEADD(ms, (timestamp - sqlserver_start_time_ms), GETDATE()) AS SampleTime, cntr_value AS CPUUsagePercent FROM sys.dm_os_ring_buffers CROSS JOIN sys.dm_os_sys_info WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR' AND record_id > (SELECT MAX(record_id) - 20 FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'); -- 查看磁盘 I/O 统计信息 SELECT TOP 10 DB_NAME(vfs.database_id) AS DatabaseName, mf.physical_name AS PhysicalFileName, vfs.io_stall_read_ms + vfs.io_stall_write_ms AS TotalIOStall_ms, vfs.num_of_reads + vfs.num_of_writes AS TotalIOCount, (vfs.io_stall_read_ms + vfs.io_stall_write_ms) / NULLIF(vfs.num_of_reads + vfs.num_of_writes, 0) AS AvgIOStall_ms FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs INNER JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id ORDER BY TotalIOStall_ms DESC;等待统计信息分析
sql-- 查看主要等待类型 SELECT TOP 10 wait_type AS WaitType, wait_time_ms / 1000 AS WaitTimeSec, waiting_tasks_count AS WaitCount, wait_time_ms / NULLIF(waiting_tasks_count, 0) AS AvgWaitTime_ms FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN') ORDER BY WaitTimeSec DESC;
查询性能验证
使用 Query Store 分析查询性能
sql-- 查看 Query Store 中的慢查询(如已启用) SELECT TOP 10 qt.query_sql_text AS QueryText, qps.plan_id AS PlanId, qps.execution_type_desc AS ExecutionType, qps.count_executions AS ExecutionCount, qps.avg_duration AS AvgDuration_ms, qps.avg_cpu_time AS AvgCPUTime_ms, qps.avg_logical_io_reads AS AvgLogicalReads FROM sys.query_store_plan AS qp INNER JOIN sys.query_store_query AS qq ON qp.query_id = qq.query_id INNER JOIN sys.query_store_query_text AS qt ON qq.query_text_id = qt.query_text_id INNER JOIN sys.query_store_runtime_stats AS qps ON qp.plan_id = qps.plan_id WHERE qps.count_executions > 5 ORDER BY qps.avg_duration DESC;测试关键业务查询
- 执行 5-10 个核心业务查询,测量其执行时间
- 与升级前的执行时间进行比较
- 分析执行计划,查找性能退化的查询
- 示例:sql
-- 测试订单查询性能 SET STATISTICS TIME ON; SELECT * FROM Sales.Orders WHERE OrderDate BETWEEN '2024-01-01' AND '2024-01-31'; SET STATISTICS TIME OFF;
安全性验证
登录账号和权限验证
sql
-- 验证登录账号状态
SELECT
name AS LoginName,
type_desc AS LoginType,
is_disabled AS IsDisabled,
is_locked AS IsLocked,
create_date AS CreateDate,
modify_date AS ModifyDate
FROM sys.server_principals
WHERE type IN ('S', 'U', 'G') -- SQL 登录、Windows 用户、Windows 组
ORDER BY name;
-- 验证服务器角色成员
SELECT
sp.name AS LoginName,
sr.name AS ServerRole
FROM sys.server_role_members srm
INNER JOIN sys.server_principals sp ON srm.member_principal_id = sp.principal_id
INNER JOIN sys.server_principals sr ON srm.role_principal_id = sr.principal_id
ORDER BY ServerRole, LoginName;
-- 验证数据库用户和权限(示例:针对 Sales 数据库)
USE Sales;
SELECT
dp.name AS UserName,
dr.name AS DatabaseRole
FROM sys.database_role_members drm
INNER JOIN sys.database_principals dp ON drm.member_principal_id = dp.principal_id
INNER JOIN sys.database_principals dr ON drm.role_principal_id = dr.principal_id
ORDER BY DatabaseRole, UserName;安全配置验证
身份验证模式
sql-- 验证身份验证模式 SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 0 THEN '混合模式(Windows + SQL Server 身份验证)' WHEN 1 THEN '仅 Windows 身份验证' ELSE '未知' END AS AuthenticationMode;加密配置
sql-- 验证 SSL/TLS 配置 SELECT name AS ProtocolName, value_data AS Value, registry_key AS RegistryKey FROM sys.dm_server_registry WHERE registry_key LIKE '%SuperSocketNetLib%' AND value_name IN ('Certificate', 'Enabled', 'ForceEncryption');审核配置
sql-- 验证审核配置 SELECT name AS AuditName, status_desc AS Status, audit_file_path AS AuditFilePath, audit_file_size_mb AS AuditFileSizeMB FROM sys.dm_server_audit_status;
安全漏洞扫描
- 使用 SQL Server 评估工具或第三方安全扫描工具执行漏洞扫描
- 验证系统是否符合安全合规要求(如 GDPR、PCI DSS 等)
- 检查是否需要安装最新的安全补丁
升级后配置优化
更新数据库兼容级别
sql
-- 根据目标版本更新数据库兼容级别
-- 注意:逐步更新,先在非生产环境测试
ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 160; -- SQL Server 2022
-- ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 150; -- SQL Server 2019
-- ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 140; -- SQL Server 2017
-- ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 130; -- SQL Server 2016更新统计信息和重建索引
sql
-- 更新所有数据库的统计信息
EXEC sp_MSforeachdb 'USE [?];
IF DB_ID(''?'') > 4
BEGIN
PRINT ''正在更新数据库 ? 的统计信息...'';
EXEC sp_updatestats;
END';
-- 重建所有用户表的索引(生产环境建议分批执行)
EXEC sp_MSforeachdb 'USE [?];
IF DB_ID(''?'') > 4
BEGIN
DECLARE @TableName NVARCHAR(255);
DECLARE @Sql NVARCHAR(MAX);
DECLARE TableCursor CURSOR FOR
SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + ''.'' + QUOTENAME(name) AS TableName
FROM sys.tables;
OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql = ''ALTER INDEX ALL ON '' + @TableName + '' REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, MAXDOP = 2);'';
PRINT ''正在重建索引: '' + @TableName;
EXEC sp_executesql @Sql;
FETCH NEXT FROM TableCursor INTO @TableName;
END;
CLOSE TableCursor;
DEALLOCATE TableCursor;
END';配置新功能
根据目标版本的特性,配置相应的新功能:
- Query Store:启用并配置适当的保留策略
- 自动优化:启用查询计划反馈和索引建议
- 智能查询处理:确保兼容级别设置正确,自动启用
升级后验证报告
生成验证报告
验证完成后,生成详细的验证报告,包括:
- 验证概述:验证的目标、范围和方法
- 系统验证结果:服务状态、版本信息、数据库状态等
- 数据库完整性验证结果:一致性检查、数据完整性、索引状态等
- 功能验证结果:核心功能、应用程序兼容性、版本特定功能等
- 性能验证结果:性能基线比较、关键指标、查询性能等
- 安全性验证结果:登录账号、权限、安全配置等
- 升级后配置优化:执行的配置优化操作
- 问题和建议:发现的问题、解决方案和改进建议
报告分发
将验证报告分发给相关人员:
- DBA 团队:用于后续维护和优化
- 系统管理员:了解系统状态
- 业务负责人:确认系统能够满足业务需求
- IT 管理层:了解升级结果和系统状态
常见问题与解决方案
Q: 升级后应用程序无法连接到数据库怎么办?
A:
- 检查目标数据库的状态,确保其处于 ONLINE 状态
- 验证应用程序连接字符串是否正确
- 检查目标服务器的防火墙规则,确保允许访问 SQL Server 端口
- 验证登录账号是否存在且密码正确
- 检查 SQL Server 配置管理器中的网络协议设置
Q: 升级后查询性能下降怎么办?
A:
- 更新统计信息和重建索引
- 分析查询执行计划,查找性能瓶颈
- 检查是否有新的等待类型出现
- 考虑暂时降低数据库兼容级别,逐步优化查询
- 启用 Query Store,分析查询计划变化
Q: 升级后数据库处于恢复或可疑状态怎么办?
A:
- 检查 SQL Server 错误日志,了解具体原因
- 尝试恢复数据库:
ALTER DATABASE <DatabaseName> SET ONLINE; - 执行数据库一致性检查:
DBCC CHECKDB(<DatabaseName>) WITH NO_INFOMSGS, ALL_ERRORMSGS; - 如果恢复失败,从最新备份恢复数据库
Q: 升级后 SQL Server 代理作业失败怎么办?
A:
- 检查作业历史记录,查看具体错误信息
- 验证作业使用的登录账号权限是否正确
- 检查作业步骤中使用的路径和文件是否存在
- 验证作业依赖的资源是否可用
- 重新测试作业,逐步排查问题
Q: 升级后某些功能不可用怎么办?
A:
- 检查目标版本是否支持该功能
- 验证功能是否需要额外配置
- 检查是否需要安装相关组件或扩展
- 参考 Microsoft 文档,了解功能在新版本中的变化
Q: 升级后安全审核日志异常怎么办?
A:
- 检查审核配置是否正确
- 验证审核文件路径是否存在且有写入权限
- 检查审核日志大小限制是否合理
- 验证登录账号是否有审核查看权限
生产环境最佳实践
- 制定详细的验证计划:在升级前制定详细的验证计划,明确验证的目标、范围、方法和步骤
- 建立性能基线:在升级前建立性能基线,用于升级后的性能比较
- 使用自动化脚本:编写验证脚本,提高验证效率和准确性
- 优先验证核心业务:优先验证核心业务功能,确保核心业务能够正常运行
- 分阶段验证:先验证系统基本功能,再验证高级功能和性能
- 持续监控:升级后持续监控系统状态和性能,及时发现和解决问题
- 记录验证结果:详细记录验证结果,包括成功的验证和发现的问题
- 定期回顾:定期回顾验证结果,总结经验教训,改进未来的升级和验证过程
- 准备回滚方案:确保在验证过程中发现严重问题时能够快速回滚
- 保持沟通:与业务团队保持密切沟通,及时通报验证进度和结果
通过全面、系统的升级后验证,可以确保 SQL Server 升级的成功,保障系统的可用性、性能和安全性。验证过程中发现的问题应及时解决,确保系统能够满足业务需求。
