印刻数据库

外观

SQLServer 同版本迁移 ​

SQL Server 同版本迁移是指将 SQL Server 数据库从一个实例迁移到另一个相同版本的实例，通常用于服务器硬件升级、数据中心迁移、负载均衡、灾备建设等场景。虽然是同版本迁移，但不同 SQL Server 版本的同版本迁移在工具支持和功能细节上仍存在差异。

对于生产环境中的 DBA 来说，同版本迁移是一项常见任务，需要确保数据完整性、业务连续性和系统性能。本文将详细介绍 SQL Server 同版本迁移的方法、步骤、工具和生产环境最佳实践。

版本差异要点 ​

虽然是同版本迁移，但不同 SQL Server 版本在迁移工具和功能支持上存在差异：

SQL Server 版本同版本迁移特点
SQL Server 2012Always On 可用性组功能初步，日志传送配置复杂
SQL Server 2014增强了 Always On 可用性组，支持更多配置选项
SQL Server 2016引入了 Data Migration Assistant (DMA)，简化迁移评估
SQL Server 2017增强了 Linux 支持，支持跨平台同版本迁移
SQL Server 2019引入了增强的 Always On 可用性组，支持分布式可用性组
SQL Server 2022进一步增强了迁移工具，支持更多自动化功能

迁移前准备 ​

生产环境评估 ​

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

  1. 数据库规模和复杂度评估

    • 统计所有数据库的大小，包括数据文件和日志文件
    • 分析数据库对象数量，如表、索引、存储过程、视图等
    • 评估数据库的事务量和增长趋势

  2. 依赖关系分析

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

  3. 性能基线收集

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

环境准备 ​

  • 目标服务器配置：

    • 确保目标服务器满足 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 或其他高效文件传输工具：
      cmd
      robocopy D:\Backups \\target-server\Backups SalesDB*.bak SalesDB*.trn /Z /MT:16
    • 验证备份文件完整性：
      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;

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

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

生产环境配置步骤 ​

  1. 在目标服务器上安装相同版本的 SQL Server
  2. 配置 Windows 故障转移集群（如果尚未配置）
  3. 在源服务器上配置可用性组
  4. 将目标服务器添加为副本服务器
  5. 初始化副本并同步数据
  6. 在维护窗口内执行故障转移
  7. 验证应用程序连接

分离和附加（中小型数据库首选） ​

分离和附加是一种快速的迁移方法，适用于中小型数据库。

生产环境迁移步骤 ​

  1. 在源服务器上分离数据库

    sql
    -- 确保没有用户连接到数据库
ALTER DATABASE SalesDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- 分离数据库
EXEC sp_detach_db 'SalesDB', 'true';

  2. 复制数据文件和日志文件

    • 复制数据库的数据文件（.mdf）和日志文件（.ldf）到目标服务器
    • 使用 Robocopy 确保文件复制的完整性

  3. 在目标服务器上附加数据库

    sql
    -- 附加数据库
CREATE DATABASE SalesDB ON 
(FILENAME = 'E:\Data\SalesDB.mdf'),
(FILENAME = 'F:\Logs\SalesDB_log.ldf')
FOR ATTACH;

迁移工具对比 ​

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

迁移实施 ​

生产环境迁移流程 ​

  1. 预迁移阶段

    • 完成环境评估和准备工作
    • 制定详细的迁移计划，包括时间窗口、回滚策略
    • 在测试环境中进行完整的迁移测试
    • 通知相关业务部门，协调迁移时间

  2. 迁移执行阶段

    • 执行最终的源数据库备份
    • 传输备份文件到目标服务器
    • 恢复数据库到目标服务器
    • 迁移登录名和权限
    • 迁移 SQL Server 代理作业和维护计划

  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, physical_name, size/128.0 AS SizeMB FROM sys.database_files;

  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. 选择合适的迁移方法 ​

  • 对于大型数据库（> 500GB）：优先考虑 Always On 可用性组或日志传送
  • 对于中小型数据库：优先考虑分离和附加或备份和恢复
  • 对于对停机时间敏感的业务：优先考虑 Always On 可用性组

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

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

3. 优化迁移时间窗口 ​

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

4. 确保数据完整性 ​

  • 使用 CHECKSUM 选项进行备份和恢复
  • 验证备份文件的完整性
  • 迁移后执行 DBCC CHECKDB 验证数据库完整性
  • 比较源数据库和目标数据库的数据量

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');

6. 利用自动化工具 ​

  • 使用 PowerShell 脚本自动化备份和恢复过程
  • 使用 SQL Server Agent 作业调度迁移任务
  • 考虑使用第三方工具如 Redgate SQL Compare 和 SQL Data Compare 进行迁移验证

常见问题 ​

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

A：

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

Q: 迁移后数据库性能下降怎么办？ ​

A：

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

-- 重建所有索引
ALTER INDEX ALL ON SalesDB.dbo.Orders REBUILD;
  • 检查目标服务器的硬件资源，确保满足需求
  • 优化查询计划，使用 Query Store 分析性能问题
  • 检查存储子系统性能，确保 I/O 吞吐量足够

Q: 迁移过程中备份文件传输失败怎么办？ ​

A：

  • 使用 Robocopy 的 /Z 选项进行断点续传
  • 检查网络连接，确保源服务器和目标服务器之间的连接稳定
  • 考虑使用压缩工具减小备份文件大小
  • 对于超大型备份文件，可以考虑使用备份拆分功能

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

A：

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

Q: 如何迁移包含 FILESTREAM 数据的数据库？ ​

A：

  • 备份时确保包含 FILESTREAM 数据
  • 恢复时指定 FILESTREAM 文件组的位置
  • 示例：
    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',
     MOVE 'SalesDB_Filestream' TO 'G:\Filestream\SalesDB';

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

A：

  • 备份和还原 ReportServer 和 ReportServerTempDB 数据库
  • 复制报表服务器配置文件
  • 更新报表数据源连接字符串
  • 测试报表在目标环境中的执行情况

Q: 如何迁移 Analysis Services 数据库？ ​

A：

  • 使用 SQL Server Management Studio 备份和还原 Analysis Services 数据库
  • 使用 PowerShell 脚本自动化迁移过程
  • 更新 Analysis Services 数据库的数据源连接字符串

总结 ​

SQL Server 同版本迁移是 DBA 日常工作中的常见任务，需要充分的准备、合理的方法选择和严格的执行。通过选择合适的迁移方法、制定详细的迁移计划、严格执行迁移流程和全面的验证测试，可以确保同版本迁移的成功。

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

迁移后，DBA 应该密切监控系统性能，确保应用程序正常运行，并制定长期的维护计划，确保数据库系统的高效稳定运行。