印刻数据库

外观

SQLServer 日志传送 ​

日志传送概述 ​

SQL Server 日志传送（Log Shipping）是一种高可用性和灾难恢复解决方案，它通过定期备份主数据库的事务日志，将日志文件复制到一个或多个辅助服务器，并在辅助服务器上还原这些日志，从而保持主数据库和辅助数据库的同步。

核心组件 ​

  • 主服务器：包含主数据库，负责生成事务日志备份
  • 辅助服务器：包含辅助数据库，负责还原从主服务器复制过来的事务日志
  • 监视服务器：可选组件，用于监视日志传送状态并生成警报
  • 事务日志备份作业：在主服务器上运行，定期备份事务日志
  • 复制作业：在辅助服务器上运行，将事务日志备份文件从主服务器复制到辅助服务器
  • 还原作业：在辅助服务器上运行，将复制过来的事务日志还原到辅助数据库

主要特性 ​

  • 提供数据库级别的高可用性和灾难恢复保护
  • 支持一个主数据库和多个辅助数据库（最多32个）
  • 仅支持手动故障切换
  • 辅助数据库可以处于只读（STANDBY）或恢复（RESTORING）模式
  • 辅助数据库可用于报告和查询目的，实现读写分离
  • 实现简单，配置成本较低
  • 支持跨版本复制（辅助服务器版本不低于主服务器）

架构设计 ​

基本架构 ​

日志传送由以下核心组件组成：

  • 主服务器：运行主数据库和事务日志备份作业
  • 主数据库：必须处于 FULL 或 BULK_LOGGED 恢复模式
  • 共享目录：用于存储主服务器生成的事务日志备份文件
  • 辅助服务器：运行辅助数据库、复制作业和还原作业
  • 辅助数据库：通过还原主数据库的完整备份和事务日志备份创建
  • 监视服务器：可选组件，用于集中监视日志传送状态

辅助数据库模式 ​

1. 恢复模式（RESTORING） ​

  • 辅助数据库处于 RESTORING 状态
  • 不可用于查询或报告
  • 还原速度较快，资源消耗较低
  • 适用于纯灾难恢复场景

2. 备用模式（STANDBY） ​

  • 辅助数据库处于 STANDBY 状态
  • 可用于只读查询和报告
  • 还原时需要断开所有用户连接
  • 支持在还原间隙进行查询
  • 适用于需要读写分离的场景

工作流程 ​

日志传送的工作流程分为三个主要阶段：

  1. 备份阶段：

    • 主服务器上的事务日志备份作业定期运行
    • 生成事务日志备份文件，存储到共享目录
    • 记录备份信息到主服务器的 msdb 数据库

  2. 复制阶段：

    • 辅助服务器上的复制作业定期运行
    • 将共享目录中的事务日志备份文件复制到辅助服务器的本地目录
    • 记录复制信息到辅助服务器的 msdb 数据库（如果配置了监视服务器，也会记录到监视服务器）

  3. 还原阶段：

    • 辅助服务器上的还原作业定期运行
    • 将本地目录中的事务日志备份文件还原到辅助数据库
    • 记录还原信息到辅助服务器的 msdb 数据库（如果配置了监视服务器，也会记录到监视服务器）

配置步骤 ​

前提条件 ​

在配置日志传送之前，需要确保满足以下条件：

  1. 主数据库必须处于 FULL 或 BULK_LOGGED 恢复模式
  2. 主服务器和辅助服务器必须能够访问共享目录
  3. 确保所有服务器的 SQL Server Agent 服务都已启动
  4. 主服务器和辅助服务器的 SQL Server 版本必须兼容（辅助服务器版本不低于主服务器）
  5. 已经创建主数据库的完整备份

详细配置步骤 ​

1. 准备工作 ​

sql
-- 在主服务器上执行：设置恢复模式
ALTER DATABASE [PrimaryDB] SET RECOVERY FULL;

-- 在主服务器上执行：创建完整备份
BACKUP DATABASE [PrimaryDB] TO DISK = '\\share\backup\PrimaryDB_FullBackup.bak' WITH NOFORMAT, NOINIT, NAME = 'PrimaryDB-Full-Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

-- 在辅助服务器上执行：还原完整备份（使用 NORECOVERY 或 STANDBY 选项）
-- 使用 NORECOVERY 选项（恢复模式）
RESTORE DATABASE [SecondaryDB] FROM DISK = '\\share\backup\PrimaryDB_FullBackup.bak' WITH NORECOVERY, STATS = 10;

-- 或使用 STANDBY 选项（备用模式）
RESTORE DATABASE [SecondaryDB] FROM DISK = '\\share\backup\PrimaryDB_FullBackup.bak' WITH STANDBY = 'D:\backup\UndoFile.ldf', STATS = 10;

2. 使用 SSMS 配置日志传送 ​

  1. 配置主服务器：

    • 打开 SQL Server Management Studio (SSMS)
    • 右键点击主数据库，选择 "属性"
    • 选择 "事务日志传送"
    • 勾选 "启用此数据库作为日志传送配置中的主数据库"
    • 点击 "备份设置"，配置以下参数：
      • 备份目录：指定共享目录路径
      • 备份作业计划：设置备份频率（如每15分钟）
      • 备份压缩：建议启用，减少网络传输和存储开销
      • 备份保留期：设置合适的保留时间

  2. 添加辅助服务器：

    • 点击 "添加" 按钮，打开 "辅助数据库设置" 对话框
    • 选择或连接到辅助服务器实例
    • 选择或创建辅助数据库

  3. 配置复制设置：

    • 点击 "复制文件" 选项卡
    • 指定辅助服务器上的复制目标目录
    • 设置复制作业计划（建议与备份作业频率一致）

  4. 配置还原设置：

    • 点击 "还原事务日志" 选项卡
    • 选择还原模式：NORECOVERY（恢复模式）或 STANDBY（备用模式）
    • 设置还原作业计划（建议与备份作业频率一致）
    • 配置延迟还原选项（可选）

  5. 配置监视服务器（可选）：

    • 勾选 "使用监视服务器实例"
    • 选择或连接到监视服务器实例
    • 配置警报设置，在日志传送出现问题时发送通知

  6. 完成配置：

    • 点击 "确定" 保存配置
    • 系统会自动创建所需的 SQL Server Agent 作业

3. 验证配置 ​

sql
-- 检查日志传送配置
SELECT * FROM msdb.dbo.log_shipping_primary_databases;
SELECT * FROM msdb.dbo.log_shipping_secondary_databases;

-- 查看日志传送状态
EXEC master.dbo.sp_help_log_shipping_monitor;

-- 查看作业状态
SELECT job_id, name, enabled, last_run_date, last_run_time, last_run_outcome 
FROM msdb.dbo.sysjobs 
WHERE name LIKE '%log shipping%';

版本差异 ​

SQL Server 2008/2012 ​

  • 完全支持日志传送功能
  • 支持最多32个辅助服务器
  • 提供基本的监控和管理功能
  • 支持通过 SSMS 图形界面配置

SQL Server 2014 ​

  • 增强了日志传送的性能
  • 改进了监控和报告功能
  • 支持与 Always On Availability Groups 结合使用
  • 引入了新的动态管理视图和存储过程

SQL Server 2016 及以上 ​

  • 保留了日志传送功能，但微软开始推荐使用 Always On Availability Groups
  • 增强了与 Azure 存储的集成
  • 改进了错误处理和报告机制
  • 支持更多的备份压缩选项
  • 增强了安全功能，如透明数据加密 (TDE) 支持

故障切换操作 ​

手动故障切换 ​

日志传送不支持自动故障切换，需要手动执行故障切换操作。以下是详细步骤：

  1. 准备工作：

    • 确认主服务器已经不可用
    • 确保所有事务日志备份都已复制到辅助服务器

  2. 在主服务器上执行最后的日志备份（如果可能）：

    sql
    BACKUP LOG [PrimaryDB] TO DISK = '\\share\backup\PrimaryDB_FinalLogBackup.trn' WITH NOFORMAT, NOINIT, NAME = 'PrimaryDB-Final-Log-Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

  3. 在辅助服务器上还原最后的日志备份：

    sql
    -- 确保最后一次日志备份已复制到辅助服务器
-- 使用 RECOVERY 选项将辅助数据库恢复为可用状态
RESTORE LOG [SecondaryDB] FROM DISK = 'D:\backup\PrimaryDB_FinalLogBackup.trn' WITH RECOVERY;

  4. 更新辅助数据库名称（如果需要）：

    sql
    ALTER DATABASE [SecondaryDB] MODIFY NAME = [PrimaryDB];

  5. 更新客户端连接：

    • 更新应用程序连接字符串，指向新的主服务器
    • 更新 DNS 记录或负载均衡配置（如果适用）

  6. 重新配置日志传送：

    • 将原辅助服务器配置为新的主服务器
    • 配置原主服务器（如果已恢复）或其他服务器作为新的辅助服务器

故障恢复 ​

当原主服务器恢复正常后，可以选择将其重新集成到日志传送配置中：

  1. 准备原主服务器：

    • 从新主服务器获取最新的完整备份
    • 在原主服务器上还原完整备份和所有事务日志备份（使用 NORECOVERY 选项）

  2. 配置原主服务器为辅助服务器：

    • 使用 SSMS 或 T-SQL 将原主服务器添加为新主服务器的辅助服务器
    • 配置复制和还原作业

  3. 验证配置：

    • 检查所有作业是否正常运行
    • 验证数据同步状态

监控与管理 ​

监控工具 ​

1. SQL Server Management Studio (SSMS) ​

  • 日志传送状态报告：在对象资源管理器中，右键点击数据库，选择 "报表" → "事务日志传送状态"
  • 作业监视器：查看 SQL Server Agent 作业的运行状态和历史记录
  • 日志传送配置：查看和修改日志传送配置

2. 系统视图和存储过程 ​

sql
-- 查看主服务器配置
SELECT * FROM msdb.dbo.log_shipping_primary_databases;
SELECT * FROM msdb.dbo.log_shipping_primary_secondaries;

-- 查看辅助服务器配置
SELECT * FROM msdb.dbo.log_shipping_secondary_databases;
SELECT * FROM msdb.dbo.log_shipping_secondary_log;

-- 查看监视服务器信息
SELECT * FROM msdb.dbo.log_shipping_monitor_primary;
SELECT * FROM msdb.dbo.log_shipping_monitor_secondary;

-- 获取日志传送状态报告
EXEC master.dbo.sp_help_log_shipping_monitor;
EXEC master.dbo.sp_help_log_shipping_monitor_primary;
EXEC master.dbo.sp_help_log_shipping_monitor_secondary;

3. 监视服务器报告 ​

如果配置了监视服务器，可以使用内置的报告查看日志传送状态：

  • 在监视服务器上，打开 SSMS
  • 连接到监视服务器实例
  • 展开 "管理" → "维护计划" → "事务日志传送状态报告"

4. 自定义监控脚本 ​

可以创建自定义脚本定期检查日志传送状态，并在出现问题时发送警报：

sql
-- 示例：检查日志传送延迟
DECLARE @threshold_minutes INT = 60; -- 延迟阈值（分钟）

SELECT 
    primary_server,
    primary_database,
    secondary_server,
    secondary_database,
    DATEDIFF(MINUTE, last_backup_date, GETDATE()) AS backup_delay_minutes,
    DATEDIFF(MINUTE, last_copy_date, GETDATE()) AS copy_delay_minutes,
    DATEDIFF(MINUTE, last_restore_date, GETDATE()) AS restore_delay_minutes
FROM msdb.dbo.log_shipping_monitor_secondary
WHERE 
    DATEDIFF(MINUTE, last_backup_date, GETDATE()) > @threshold_minutes OR
    DATEDIFF(MINUTE, last_copy_date, GETDATE()) > @threshold_minutes OR
    DATEDIFF(MINUTE, last_restore_date, GETDATE()) > @threshold_minutes;

常见管理任务 ​

1. 修改作业计划 ​

  • 打开 SQL Server Agent
  • 找到对应的日志传送作业
  • 右键点击作业，选择 "属性"
  • 点击 "调度" 选项卡，修改作业执行频率

2. 切换辅助数据库模式 ​

sql
-- 从 RESTORING 切换到 STANDBY 模式
RESTORE DATABASE [SecondaryDB] WITH STANDBY = 'D:\backup\UndoFile.ldf';

-- 从 STANDBY 切换到 RESTORING 模式
RESTORE DATABASE [SecondaryDB] WITH NORECOVERY;

3. 添加新的辅助服务器 ​

  • 按照初始配置步骤，在主服务器的日志传送配置中添加新的辅助服务器
  • 确保新辅助服务器已还原主数据库的完整备份

4. 移除辅助服务器 ​

  • 在主服务器的日志传送配置中移除辅助服务器
  • 删除辅助服务器上对应的 SQL Server Agent 作业
  • 可选：删除辅助数据库

生产场景应用 ​

场景1：企业核心数据库灾难恢复 ​

场景描述：某企业核心业务数据库需要高可用性保护，要求在主服务器发生故障时能够快速恢复业务，同时实现异地灾难恢复。

解决方案：

  • 配置主服务器和本地辅助服务器，实现本地高可用性
  • 配置异地辅助服务器，实现灾难恢复
  • 本地辅助服务器使用 STANDBY 模式，用于报告查询
  • 异地辅助服务器使用 RESTORING 模式，纯用于灾难恢复
  • 配置监视服务器，实时监控日志传送状态
  • 定期测试故障切换流程，确保灾难恢复能力

关键配置：

  • 备份频率：每15分钟
  • 复制和还原频率：每15分钟
  • 备份压缩：启用
  • 本地辅助服务器延迟：0分钟
  • 异地辅助服务器延迟：30分钟（防止误删除数据导致同步到所有服务器）

场景2：报表系统读写分离 ​

场景描述：某电商平台的报表系统需要访问实时数据，但直接查询主数据库会影响交易性能，需要实现读写分离。

解决方案：

  • 配置主服务器处理所有写操作
  • 配置辅助服务器，使用 STANDBY 模式
  • 报表系统连接到辅助服务器进行查询
  • 日志传送备份频率设置为每5分钟，确保数据延迟最小
  • 配置警报，在日志传送延迟超过10分钟时通知管理员

关键配置：

  • 辅助数据库模式：STANDBY
  • 备份、复制、还原频率：每5分钟
  • 备份压缩：启用
  • 配置报表系统连接字符串指向辅助服务器

场景3：数据库迁移（零停机） ​

场景描述：某企业需要将数据库从旧服务器迁移到新服务器，要求零停机或最小停机时间。

解决方案：

  • 在新服务器上配置为辅助服务器
  • 日志传送同步完成后，执行手动故障切换
  • 将旧服务器的流量切换到新服务器

关键步骤：

  1. 配置日志传送，将新服务器作为辅助服务器
  2. 等待数据同步完成
  3. 在业务低峰期执行故障切换
  4. 验证新服务器正常运行
  5. 将旧服务器从日志传送配置中移除

优势：

  • 迁移过程中业务持续可用
  • 停机时间仅为故障切换和连接更新的时间（通常几分钟）
  • 可回滚（如果新服务器出现问题）

常见问题（FAQ） ​

日志传送与 Always On Availability Groups 有什么区别？ ​

答案：

  • 故障切换：日志传送只支持手动故障切换，Always On AG 支持自动故障切换
  • 复杂度：日志传送实现简单，配置成本低；Always On AG 实现复杂，需要 Windows 故障转移群集
  • 数据库数量：日志传送支持一个主数据库和多个辅助数据库（最多32个）；Always On AG 支持多个数据库组成一个组，最多9个辅助副本
  • 只读访问：两者都支持只读辅助数据库，但 Always On AG 的只读副本性能更好
  • 适用场景：日志传送适用于对自动故障切换要求不高的场景；Always On AG 适用于对可用性要求极高的关键业务系统

日志传送需要多少个服务器？ ​

答案：

  • 最少需要2个服务器：1个主服务器和1个辅助服务器
  • 最多可以有1个主服务器和多个辅助服务器（最多32个）
  • 监视服务器是可选的，用于集中监控日志传送状态

如何初始化辅助数据库？ ​

答案： 有三种主要方式：

  1. 从完整备份初始化：在主服务器上创建完整备份，在辅助服务器上使用 NORECOVERY 或 STANDBY 选项还原
  2. 从完整备份和差异备份初始化：如果主数据库很大，可以先还原完整备份，再还原最近的差异备份，减少事务日志还原数量
  3. 使用数据库复制工具：如 SQL Server Integration Services (SSIS) 或第三方工具，直接复制数据库文件

日志传送会影响主服务器性能吗？ ​

答案： 日志传送对主服务器性能有一定影响，主要体现在：

  • 事务日志备份会消耗 CPU 和 I/O 资源
  • 生成的日志备份文件会占用存储空间
  • 网络传输日志文件会占用带宽

可以通过以下方式减少影响：

  • 合理设置备份频率，平衡数据一致性和性能
  • 使用高性能存储设备存储日志备份
  • 启用备份压缩，减少网络传输和存储开销
  • 安排在业务低峰期进行完整备份

如何处理日志传送延迟？ ​

答案： 日志传送延迟可能由以下原因导致：

  1. 备份作业问题：检查主服务器备份作业是否正常运行
  2. 网络问题：检查主服务器和辅助服务器之间的网络带宽和延迟
  3. 存储问题：检查备份存储和辅助服务器存储的 I/O 性能
  4. 还原作业问题：检查辅助服务器还原作业是否正常运行
  5. 事务量过大：主服务器事务量超过日志传送处理能力

解决方法：

  • 检查并修复故障的 SQL Server Agent 作业
  • 优化网络连接，增加带宽
  • 升级存储设备，提高 I/O 性能
  • 增加辅助服务器资源（CPU、内存）
  • 调整备份、复制、还原作业的频率
  • 考虑使用 Always On AG 替代日志传送（如果事务量持续过大）

日志传送支持跨版本复制吗？ ​

答案： 支持，但有以下限制：

  • 辅助服务器的 SQL Server 版本不能低于主服务器版本
  • 从低版本到高版本的复制是完全支持的
  • 从高版本到低版本的复制是不支持的
  • 建议主服务器和辅助服务器使用相同的补丁级别

如何监控日志传送状态？ ​

答案： 可以通过以下方式监控：

  1. SSMS 日志传送状态报告：图形化展示日志传送状态
  2. 监视服务器报告：集中监控多个日志传送配置
  3. 动态管理视图和存储过程：通过 T-SQL 查询日志传送状态
  4. 自定义监控脚本：创建脚本定期检查状态并发送警报
  5. 第三方监控工具：如 SentryOne、Redgate SQL Monitor 等

如何更改日志传送的备份目录？ ​

答案：

  1. 暂停日志传送作业：禁用备份、复制和还原作业
  2. 修改备份目录：
    • 更新主服务器上的备份作业，修改备份目标路径
    • 更新辅助服务器上的复制作业，修改源目录路径
  3. 重启作业：启用所有日志传送作业
  4. 验证配置：检查作业是否正常运行，验证数据同步

日志传送的备份文件如何管理？ ​

答案：

  1. 设置合适的备份保留期：根据存储容量和业务需求设置
  2. 启用备份压缩：减少存储占用
  3. 使用集中存储：便于管理和备份
  4. 定期清理过期备份：可以通过备份作业的 "删除过期文件" 选项自动清理
  5. 备份备份文件：将日志传送备份文件纳入企业备份策略，防止主备份丢失

如何测试日志传送的故障切换？ ​

答案：

  1. 选择测试时间：在业务低峰期进行
  2. 准备测试环境：确保有完整的测试计划和回滚方案
  3. 执行故障切换：按照故障切换步骤执行
  4. 验证业务功能：测试关键业务功能是否正常
  5. 回滚或保持新配置：根据测试结果决定是否回滚
  6. 更新文档：记录测试结果和改进措施

最佳实践 ​

架构设计 ​

  1. 物理分离：主服务器和辅助服务器应位于不同的物理位置，实现灾难恢复
  2. 独立存储：使用独立的存储设备存储事务日志备份，避免单点故障
  3. 监视服务器：建议配置监视服务器，集中监控日志传送状态
  4. 多辅助服务器：根据业务需求配置多个辅助服务器，实现本地高可用和异地灾难恢复
  5. 版本一致性：确保所有服务器的 SQL Server 版本和补丁级别一致

性能优化 ​

  1. 合理设置作业频率：根据业务需求和系统性能，设置合适的备份、复制和还原频率
  2. 启用备份压缩：减少网络传输和存储开销，提高备份和还原速度
  3. 优化存储：使用高性能存储设备存储事务日志备份，提高 I/O 性能
  4. 优化网络：确保主服务器和辅助服务器之间有足够的带宽和低延迟
  5. 合理设置备份保留期：避免备份文件占用过多存储空间

监控与维护 ​

  1. 定期监控：建立日常监控机制，及时发现和解决问题
  2. 配置警报：在日志传送出现延迟或故障时发送警报，确保快速响应
  3. 定期测试：每季度至少测试一次故障切换流程，确保灾难恢复能力
  4. 文档更新：及时更新日志传送配置文档，包括服务器信息、作业配置和故障切换步骤
  5. 培训：确保DBA团队熟悉日志传送配置、监控和故障处理流程

安全性 ​

  1. 保护共享目录：限制共享目录的访问权限，仅允许主服务器和辅助服务器访问
  2. 最小权限原则：为 SQL Server Agent 服务账户配置最小必要的权限
  3. 加密备份：考虑使用透明数据加密 (TDE) 或备份加密，保护敏感数据
  4. 定期审计：审计日志传送配置和作业，确保安全性
  5. 网络安全：使用专用网络或 VPN 连接主服务器和辅助服务器，保护数据传输安全

总结 ​

SQL Server 日志传送是一种成熟、可靠的高可用性和灾难恢复解决方案，具有配置简单、成本低、支持多辅助服务器等优点。它通过定期备份、复制和还原事务日志，保持主数据库和辅助数据库的同步，可用于本地高可用性、异地灾难恢复和读写分离场景。

虽然日志传送只支持手动故障切换，但其可靠性和易用性使其成为许多组织的首选解决方案。对于需要自动故障切换和更高级功能的环境，可以考虑使用 Always On Availability Groups。

作为 DBA，深入理解日志传送的架构、配置和管理是至关重要的。通过合理设计日志传送架构、优化配置参数、建立完善的监控机制和定期测试故障切换流程，可以确保数据库系统的高可用性和灾难恢复能力，保障业务的连续性。