印刻数据库

外观

SQLServer 跨版本迁移 ​

SQL Server 跨版本迁移是指将数据库从一个 SQL Server 版本迁移到另一个不同版本的实例，通常用于版本升级或降级。跨版本迁移比同版本迁移复杂，需要考虑版本兼容性、功能差异和性能影响等因素。

对于生产环境中的 DBA 来说，跨版本迁移是一项关键任务，直接关系到业务连续性和系统稳定性。不同版本的 SQL Server 在功能支持、性能表现和管理工具上存在差异，需要根据实际环境选择合适的迁移策略。

版本兼容性与差异 ​

支持的升级路径 ​

源版本目标版本支持升级注意事项
SQL Server 2012SQL Server 2014是直接升级
SQL Server 2012SQL Server 2016是直接升级
SQL Server 2012SQL Server 2017否需要先升级到 2014 或 2016
SQL Server 2014SQL Server 2016是直接升级
SQL Server 2014SQL Server 2017是直接升级
SQL Server 2014SQL Server 2019是直接升级
SQL Server 2016SQL Server 2017是直接升级
SQL Server 2016SQL Server 2019是直接升级
SQL Server 2016SQL Server 2022是直接升级
SQL Server 2017SQL Server 2019是直接升级
SQL Server 2017SQL Server 2022是直接升级
SQL Server 2019SQL Server 2022是直接升级

兼容性级别 ​

兼容性级别决定了数据库的行为，包括 T-SQL 语法支持、查询优化器行为和新功能可用性：

SQL Server 版本默认兼容性级别支持的兼容性级别范围关键新功能
SQL Server 2012110100-110Columnstore 索引（初步支持）
SQL Server 2014120100-120In-Memory OLTP
SQL Server 2016130100-130Query Store、JSON 支持、PolyBase
SQL Server 2017140100-140Graph 数据库、智能查询处理
SQL Server 2019150100-150增强的智能查询处理、UTF-8 支持
SQL Server 2022160100-160增强的查询存储、JSON 路径索引

版本差异要点 ​

  • SQL Server 2016：引入了 Query Store，这是一个重要的性能监控和调优工具
  • SQL Server 2017：增强了 Linux 支持，引入了图形数据库功能
  • SQL Server 2019：大幅改进了智能查询处理，支持 UTF-8 字符集
  • SQL Server 2022：进一步增强了查询存储，引入了 JSON 路径索引等新功能

迁移前准备 ​

生产环境评估 ​

在迁移前，必须对源数据库进行全面评估，这是确保迁移成功的关键步骤：

  1. 功能兼容性评估

    • 使用 Data Migration Assistant (DMA) 扫描源数据库，识别不支持的功能
    • 重点检查：已弃用功能、CLR 集成、SSIS 包、SQL Server 代理作业等
    • 示例：SQL Server 2022 中移除了 SQL Server Profiler，需要使用 Extended Events 替代

  2. 性能基线收集

    • 收集源服务器的性能数据，包括 CPU、内存、I/O 和网络使用率
    • 记录关键查询的执行时间和计划
    • 使用以下脚本收集性能计数器：
      sql
      -- 收集当前性能计数器
SELECT * FROM sys.dm_os_performance_counters 
WHERE counter_name IN ('Processor Time %', 'Page Life Expectancy', 'Batch Requests/sec');

  3. 数据库依赖关系分析

    • 识别数据库之间的依赖关系，如链接服务器、跨数据库查询
    • 分析应用程序与数据库的交互方式
    • 确定迁移顺序，确保依赖关系正确

环境准备 ​

  • 目标服务器配置：

    • 确保目标服务器满足目标 SQL Server 版本的硬件要求
    • 安装必要的操作系统补丁和组件
    • 配置网络设置，确保源服务器和目标服务器之间的连接稳定

  • 权限准备：

    • 源服务器：需要 sysadmin 或 db_owner 权限
    • 目标服务器：需要 sysadmin 权限
    • 文件系统权限：确保 SQL Server 服务账号有足够的权限访问备份文件和数据目录

迁移方法 ​

备份和恢复（生产环境首选） ​

备份和恢复是最可靠、最常用的跨版本迁移方法，适用于从低版本迁移到高版本。

生产环境迁移步骤 ​

  1. 源服务器备份

    sql
    -- 生产环境建议使用压缩备份，减少存储和传输时间
BACKUP DATABASE SalesDB TO DISK = 'D:\Backups\SalesDB_Full.bak' 
WITH COMPRESSION, CHECKSUM, INIT;

-- 事务日志备份，用于最小化数据丢失
BACKUP LOG SalesDB TO DISK = 'D:\Backups\SalesDB_Log.trn' 
WITH COMPRESSION, CHECKSUM, INIT;

  2. 备份文件传输

    • 使用网络共享、Robocopy 或其他高效文件传输工具
    • 验证备份文件完整性：
      sql
      -- 验证备份文件
RESTORE VERIFYONLY FROM DISK = 'D:\Backups\SalesDB_Full.bak';

  3. 目标服务器恢复

    sql
    -- 恢复完整备份
RESTORE DATABASE SalesDB FROM DISK = 'E:\Backups\SalesDB_Full.bak' 
WITH NORECOVERY,
     MOVE 'SalesDB' TO 'E:\Data\SalesDB.mdf',
     MOVE 'SalesDB_log' TO 'F:\Logs\SalesDB_log.ldf';

-- 恢复事务日志，确保数据一致性
RESTORE LOG SalesDB FROM DISK = 'E:\Backups\SalesDB_Log.trn' 
WITH RECOVERY;

-- 更新数据库兼容级别
ALTER DATABASE SalesDB SET COMPATIBILITY_LEVEL = 160;

Always On 可用性组（最小停机时间方案） ​

对于需要最小化停机时间的生产环境，可以使用 Always On 可用性组进行跨版本迁移。

生产环境配置步骤 ​

  1. 在目标服务器上安装 SQL Server 高版本
  2. 配置 Windows 故障转移集群（如果尚未配置）
  3. 将目标服务器添加到可用性组
  4. 初始化副本并同步数据
  5. 在维护窗口内执行故障转移
  6. 更新数据库兼容级别

数据迁移助手 (DMA) ​

DMA 是 Microsoft 提供的官方迁移工具，适合中大型数据库的跨版本迁移。

生产环境使用要点 ​

  1. 评估阶段：

    • 运行 DMA 评估，重点关注 "兼容性问题" 和 "功能建议" 部分
    • 生成详细的评估报告，用于修复不兼容问题

  2. 迁移阶段：

    • 选择 "迁移" 项目类型，配置源和目标服务器
    • 选择要迁移的数据库对象，包括表、存储过程、视图等
    • 监控迁移进度，处理可能出现的错误

迁移工具对比 ​

迁移工具适用场景优点缺点
备份和恢复各种规模数据库，要求数据一致性简单可靠，支持时间点恢复停机时间长
Always On 可用性组大型数据库，要求最小停机时间近实时同步，停机时间短配置复杂，需要集群环境
Data Migration Assistant中大型数据库，需要评估功能兼容性自动化评估和迁移，生成详细报告迁移速度可能较慢
Log Shipping大型数据库，要求最小停机时间配置相对简单，支持只读副本手动切换，需要额外存储空间
BCP大型表的批量迁移迁移速度快，支持并行只迁移数据，不迁移架构

迁移实施 ​

生产环境迁移流程 ​

  1. 预迁移阶段

    • 完成环境评估和准备工作
    • 修复 DMA 评估中发现的问题
    • 制定详细的迁移计划，包括时间窗口、回滚策略
    • 在测试环境中进行完整的迁移测试

  2. 迁移执行阶段

    • 执行最终的源数据库备份
    • 传输备份文件到目标服务器
    • 恢复数据库到目标服务器
    • 更新数据库兼容级别
    • 更新统计信息和重建索引

  3. 迁移后配置

    • 登录名迁移：
      sql
      -- 使用 sp_help_revlogin 脚本迁移登录名和密码哈希
-- 在源服务器上执行 sp_help_revlogin，生成迁移脚本
-- 在目标服务器上执行生成的脚本
    • SQL Server 代理作业迁移：
      • 导出源服务器的作业脚本
      • 在目标服务器上执行脚本创建作业
      • 验证作业配置和调度
    • 维护计划配置：
      • 重新配置备份计划
      • 调整索引维护计划
      • 配置统计信息更新作业

迁移后验证 ​

生产环境验证清单 ​

  1. 系统验证

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

-- 验证数据库完整性
DBCC CHECKDB('SalesDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- 检查兼容级别
SELECT name, compatibility_level FROM sys.databases WHERE name = 'SalesDB';

  2. 数据验证

    • 比较源数据库和目标数据库的表行数
    sql
    -- 创建行数比较脚本
SELECT t.name AS TableName, SUM(p.rows) AS RowCount
FROM sys.tables t
JOIN sys.partitions p ON t.object_id = p.object_id
WHERE t.type = 'U'
GROUP BY t.name
ORDER BY RowCount DESC;
    • 验证关键业务数据的准确性，如最近一周的交易数据

  3. 功能验证

    • 测试 SQL Server 代理作业是否正常执行
    • 验证链接服务器和分布式查询
    • 测试应用程序连接和核心功能
    • 验证备份和恢复功能是否正常

  4. 性能验证

    • 运行关键业务查询，比较执行时间
    • 监控系统资源使用率（CPU、内存、I/O）
    • 检查等待统计信息，识别性能瓶颈
    sql
    -- 查看前 10 个等待类型
SELECT TOP 10 wait_type, wait_time_ms/1000.0 AS WaitTimeSec
FROM sys.dm_os_wait_stats
ORDER BY WaitTimeSec DESC;

生产环境最佳实践 ​

1. 选择合适的迁移时间 ​

  • 选择业务低峰期，如周末凌晨 2-4 点
  • 提前通知业务部门，做好业务暂停准备
  • 预留足够的验证时间，建议至少 2-4 小时

2. 制定详细的回滚计划 ​

  • 保留源数据库的完整备份，确保可以随时恢复
  • 准备好回滚脚本，包括应用程序连接字符串切换
  • 明确回滚触发条件，如迁移失败超过预定时间、关键功能验证失败

3. 优化目标服务器配置 ​

  • 根据目标 SQL Server 版本的最佳实践调整配置
  • 配置合适的最大内存使用量：
    sql
    -- 设置最大内存使用量（预留 2-4GB 给操作系统）
EXEC sp_configure 'max server memory (MB)', 32768;
RECONFIGURE;
  • 优化 TempDB 配置，包括多个数据文件和适当的初始大小

4. 利用目标版本的新功能 ​

  • 启用 Query Store：
    sql
    ALTER DATABASE SalesDB SET QUERY_STORE = ON;
ALTER DATABASE SalesDB SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60
);
  • 启用智能查询处理功能
  • 考虑使用新的存储格式，如列式存储索引

5. 监控迁移过程 ​

  • 使用 SSMS 或 Azure Data Studio 监控备份和恢复进度
  • 对于大型数据库，使用以下查询监控恢复进度：
    sql
    SELECT session_id, command, percent_complete,
       estimated_completion_time/1000 AS EstimatedSec,
       start_time, text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE command IN ('RESTORE DATABASE', 'BACKUP DATABASE');

常见问题 ​

Q: 迁移后查询性能下降怎么办？ ​

A：

  • 更新统计信息和重建索引：
    sql
    -- 更新所有统计信息
EXEC sp_updatestats;

-- 重建所有索引
ALTER INDEX ALL ON SalesDB.dbo.Orders REBUILD;
  • 检查查询计划是否发生变化，使用 Query Store 进行分析
  • 考虑暂时降低兼容级别，逐步迁移到新的查询优化器

Q: 迁移后某些 SQL Server 代理作业失败怎么办？ ​

A：

  • 检查作业步骤类型，确保目标版本支持该类型
  • 验证作业使用的路径和账户是否正确
  • 检查作业日志，查看具体错误信息
  • 对于 CmdExec 作业，确保命令在目标服务器上可执行

Q: 如何迁移 SSIS 包？ ​

A：

  • 使用 SSIS 目录迁移向导
  • 对于文件系统中的包，直接复制到目标服务器并更新配置
  • 测试 SSIS 包在目标环境中的执行情况

Q: 迁移后无法连接到数据库怎么办？ ​

A：

  • 检查目标数据库的状态，确保其处于 ONLINE 状态
  • 验证登录名和密码是否正确迁移
  • 检查防火墙规则，确保允许访问 SQL Server 端口
  • 验证应用程序连接字符串是否正确

Q: 如何处理跨版本迁移中的字符集问题？ ​

A：

  • 确保源数据库和目标数据库使用兼容的排序规则
  • 对于 SQL Server 2019+，考虑使用 UTF-8 字符集以提高兼容性
  • 在迁移前测试字符集转换，确保数据完整性

Q: 迁移后 CLR 程序集无法加载怎么办？ ​

A：

  • 检查 CLR 程序集是否兼容目标 .NET Framework 版本
  • 重新编译 CLR 程序集，确保兼容目标环境
  • 在目标服务器上启用 CLR 集成：
    sql
    EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

Q: 如何迁移 Reporting Services 报表？ ​

A：

  • 使用 Reporting Services 迁移工具
  • 备份和还原 ReportServer 数据库
  • 更新报表数据源连接字符串
  • 测试报表在目标环境中的执行情况

总结 ​

SQL Server 跨版本迁移是一个复杂的过程，需要 DBA 具备全面的技术知识和丰富的生产环境经验。通过充分的评估准备、选择合适的迁移方法、严格执行迁移流程和全面的验证测试，可以确保跨版本迁移的成功。

在实际生产环境中，建议优先考虑备份和恢复方法，因为它简单可靠，支持完整的数据一致性。对于需要最小化停机时间的场景，可以考虑 Always On 可用性组或 Log Shipping。

迁移后，DBA 应该密切监控系统性能，利用目标版本的新功能优化数据库，并制定长期的维护计划，确保数据库系统的高效稳定运行。