Skip to content

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 服务状态

  1. 检查 SQL Server 服务状态

    powershell
    # 使用 PowerShell 检查核心服务
    Get-Service -Name MSSQLSERVER, SQLSERVERAGENT, SQLBrowser, MSSQLFDLauncher, SQLWriter | Format-Table -AutoSize
  2. 验证 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;

数据完整性验证

  1. 验证关键表数据

    sql
    -- 示例:验证订单表的数据完整性
    SELECT 
        COUNT(*) AS TotalOrders,
        MIN(OrderDate) AS MinOrderDate,
        MAX(OrderDate) AS MaxOrderDate,
        COUNT(DISTINCT CustomerID) AS UniqueCustomers
    FROM Sales.Orders;
  2. 验证索引完整性

    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;
  3. 验证约束完整性

    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;

功能验证

核心功能验证

  1. 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;
  2. 备份和恢复功能

    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
  3. 高可用性功能

    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

应用程序兼容性验证

  1. 应用程序连接测试

    • 更新应用程序连接字符串,指向升级后的 SQL Server 实例
    • 测试应用程序的核心功能,如用户登录、数据查询、事务处理
    • 监控应用程序日志,查找兼容性错误
  2. 自定义代码验证

    • 执行关键存储过程、函数、触发器测试
    • 验证视图和同义词是否正常工作
    • 检查错误日志,查找自定义代码执行错误
  3. 第三方应用程序验证

    • 验证第三方应用程序与新 SQL Server 版本的兼容性
    • 测试第三方应用程序的核心功能
    • 联系第三方供应商,获取兼容性确认

版本特定功能验证

根据目标 SQL Server 版本,验证新增或增强的功能:

  1. 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;
  2. 智能查询处理验证(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');
  3. 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

性能验证

性能基线比较

  1. 建立性能基线

    • 在升级前使用 Performance Monitor 或 SQL Server Profiler 建立性能基线
    • 采集关键指标:CPU 使用率、内存使用率、磁盘 I/O、批处理请求数、等待统计信息
    • 保存基线数据,用于升级后比较
  2. 关键性能指标验证

    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;
  3. 等待统计信息分析

    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;

查询性能验证

  1. 使用 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;
  2. 测试关键业务查询

    • 执行 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;

安全配置验证

  1. 身份验证模式

    sql
    -- 验证身份验证模式
    SELECT 
        CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
            WHEN 0 THEN '混合模式(Windows + SQL Server 身份验证)'
            WHEN 1 THEN '仅 Windows 身份验证'
            ELSE '未知'
        END AS AuthenticationMode;
  2. 加密配置

    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');
  3. 审核配置

    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

  • 检查审核配置是否正确
  • 验证审核文件路径是否存在且有写入权限
  • 检查审核日志大小限制是否合理
  • 验证登录账号是否有审核查看权限

生产环境最佳实践

  1. 制定详细的验证计划:在升级前制定详细的验证计划,明确验证的目标、范围、方法和步骤
  2. 建立性能基线:在升级前建立性能基线,用于升级后的性能比较
  3. 使用自动化脚本:编写验证脚本,提高验证效率和准确性
  4. 优先验证核心业务:优先验证核心业务功能,确保核心业务能够正常运行
  5. 分阶段验证:先验证系统基本功能,再验证高级功能和性能
  6. 持续监控:升级后持续监控系统状态和性能,及时发现和解决问题
  7. 记录验证结果:详细记录验证结果,包括成功的验证和发现的问题
  8. 定期回顾:定期回顾验证结果,总结经验教训,改进未来的升级和验证过程
  9. 准备回滚方案:确保在验证过程中发现严重问题时能够快速回滚
  10. 保持沟通:与业务团队保持密切沟通,及时通报验证进度和结果

通过全面、系统的升级后验证,可以确保 SQL Server 升级的成功,保障系统的可用性、性能和安全性。验证过程中发现的问题应及时解决,确保系统能够满足业务需求。