Linux 上的日志传送入门

适用于:SQL Server - Linux

日志传送是一种 SQL Server 高可用性 (HA) 配置,支持将数据库从主服务器复制到一个或多个辅助服务器上。 日志传送可使源数据库中的备份文件还原到辅助服务器。 主服务器会定期创建事务日志备份,辅助服务器会还原备份,同时更新数据库的次要副本。

显示日志传送工作流的关系图。

如前面的关系图所示,日志传送过程包含以下步骤:

  • 在 SQL Server 主实例上备份事务日志文件
  • 将事务日志备份文件通过网络复制到一个或多个辅助 SQL Server 实例
  • 还原辅助 SQL Server 实例上的事务日志备份文件

必备条件

使用 CIFS 为日志传送设置网络共享

注意

本教程使用 CIFS 和 Samba 设置网络共享。

配置主服务器

  1. 使用以下命令安装 Samba:

    • 对于 Red Hat Enterprise Linux (RHEL):

      sudo yum -y install samba
      
    • 对于 Ubuntu:

      sudo apt-get install samba
      
  2. 创建用于存储日志传送的日志的目录,并为 mssql 用户提供所需权限:

    mkdir /var/opt/mssql/tlogs
    chown mssql:mssql /var/opt/mssql/tlogs
    chmod 0700 /var/opt/mssql/tlogs
    
  3. 编辑 /etc/samba/smb.conf 文件(需要根权限)并添加以下部分:

    [tlogs]
    path=/var/opt/mssql/tlogs
    available=yes
    read only=yes
    browsable=yes
    public=yes
    writable=no
    
  4. 为 Samba 创建 mssql 用户:

    sudo smbpasswd -a mssql
    
  5. 重启 Samba 服务:

    sudo systemctl restart smbd.service nmbd.service
    

配置辅助服务器

  1. 可以使用以下命令安装 CIFS 类:

    • 对于 RHEL:

      sudo yum -y install cifs-utils
      
    • 对于 Ubuntu:

      sudo apt-get install cifs-utils
      
  2. 创建文件以存储凭据。 在本例中,我们使用 /var/opt/mssql/.tlogcreds。 使用最近为 mssql Samba 帐户设置的密码并替换 <domain>

    username=mssql
    domain=<domain>
    password=<password>
    
  3. 运行以下命令创建空目录,用于装载并正确设置权限和所有权

    mkdir /var/opt/mssql/tlogs
    sudo chown root:root /var/opt/mssql/tlogs
    sudo chmod 0550 /var/opt/mssql/tlogs
    sudo chown root:root /var/opt/mssql/.tlogcreds
    sudo chmod 0660 /var/opt/mssql/.tlogcreds
    
  4. 将此行添加到 etc/fstab 以保留共享。 将 <ip_address_of_primary_server> 替换为适当的值:

    //<ip_address_of_primary_server>/tlogs /var/opt/mssql/tlogs cifs credentials=/var/opt/mssql/.tlogcreds,ro,uid=mssql,gid=mssql 0 0
    
  5. 装载共享:

    sudo mount -a
    

使用 Transact-SQL 设置日志传送

  1. 在主服务器上备份数据库:

    BACKUP DATABASE SampleDB TO DISK = '/var/opt/mssql/tlogs/SampleDB.bak'
    GO
    
  2. 在主服务器上配置日志传送:

    DECLARE @LS_BackupJobId AS UNIQUEIDENTIFIER;
    DECLARE @LS_PrimaryId AS UNIQUEIDENTIFIER;
    DECLARE @SP_Add_RetCode AS INT;
    
    EXECUTE @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
        @database = N'SampleDB',
        @backup_directory = N'/var/opt/mssql/tlogs',
        @backup_share = N'/var/opt/mssql/tlogs',
        @backup_job_name = N'LSBackup_SampleDB',
        @backup_retention_period = 4320,
        @backup_compression = 2,
        @backup_threshold = 60,
        @threshold_alert_enabled = 1,
        @history_retention_period = 5760,
        @backup_job_id = @LS_BackupJobId OUTPUT,
        @primary_id = @LS_PrimaryId OUTPUT,
        @overwrite = 1;
    
    IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)
    BEGIN
        DECLARE @LS_BackUpScheduleUID AS UNIQUEIDENTIFIER;
        DECLARE @LS_BackUpScheduleID AS INT;
    
        EXECUTE msdb.dbo.sp_add_schedule
            @schedule_name = N'LSBackupSchedule',
            @enabled = 1,
            @freq_type = 4,
            @freq_interval = 1,
            @freq_subday_type = 4,
            @freq_subday_interval = 15,
            @freq_recurrence_factor = 0,
            @active_start_date = 20170418,
            @active_end_date = 99991231,
            @active_start_time = 0,
            @active_end_time = 235900,
            @schedule_uid = @LS_BackUpScheduleUID OUTPUT,
            @schedule_id = @LS_BackUpScheduleID OUTPUT;
    
        EXECUTE msdb.dbo.sp_attach_schedule
            @job_id = @LS_BackupJobId,
            @schedule_id = @LS_BackUpScheduleID;
    
        EXECUTE msdb.dbo.sp_update_job @job_id = @LS_BackupJobId, @enabled = 1;
    END
    
    EXECUTE master.dbo.sp_add_log_shipping_alert_job;
    
    EXECUTE master.dbo.sp_add_log_shipping_primary_secondary
        @primary_database = N'SampleDB',
        @secondary_server = N'<ip_address_of_secondary_server>',
        @secondary_database = N'SampleDB',
        @overwrite = 1;
    
  3. 在辅助服务器上还原数据库:

    RESTORE DATABASE SampleDB
    FROM DISK = '/var/opt/mssql/tlogs/SampleDB.bak'
    WITH NORECOVERY;
    
  4. 在辅助服务器上配置日志传送:

    DECLARE @LS_Secondary__CopyJobId AS UNIQUEIDENTIFIER;
    DECLARE @LS_Secondary__RestoreJobId AS UNIQUEIDENTIFIER;
    DECLARE @LS_Secondary__SecondaryId AS UNIQUEIDENTIFIER;
    DECLARE @LS_Add_RetCode AS INT;
    
    EXECUTE @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary
        @primary_server = N'<ip_address_of_primary_server>',
        @primary_database = N'SampleDB',
        @backup_source_directory = N'/var/opt/mssql/tlogs/',
        @backup_destination_directory = N'/var/opt/mssql/tlogs/',
        @copy_job_name = N'LSCopy_SampleDB',
        @restore_job_name = N'LSRestore_SampleDB',
        @file_retention_period = 4320,
        @overwrite = 1,
        @copy_job_id = @LS_Secondary__CopyJobId OUTPUT,
        @restore_job_id = @LS_Secondary__RestoreJobId OUTPUT,
        @secondary_id = @LS_Secondary__SecondaryId OUTPUT
    
    IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
    BEGIN
        DECLARE @LS_SecondaryCopyJobScheduleUID AS UNIQUEIDENTIFIER;
        DECLARE @LS_SecondaryCopyJobScheduleID AS INT;
    
        EXECUTE msdb.dbo.sp_add_schedule
            @schedule_name = N'DefaultCopyJobSchedule',
            @enabled = 1,
            @freq_type = 4,
            @freq_interval = 1,
            @freq_subday_type = 4,
            @freq_subday_interval = 15,
            @freq_recurrence_factor = 0,
            @active_start_date = 20170418,
            @active_end_date = 99991231,
            @active_start_time = 0,
            @active_end_time = 235900,
            @schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT,
            @schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT;
    
        EXECUTE msdb.dbo.sp_attach_schedule
            @job_id = @LS_Secondary__CopyJobId,
            @schedule_id = @LS_SecondaryCopyJobScheduleID;
    
        DECLARE @LS_SecondaryRestoreJobScheduleUID AS UNIQUEIDENTIFIER;
        DECLARE @LS_SecondaryRestoreJobScheduleID AS INT;
    
        EXECUTE msdb.dbo.sp_add_schedule
            @schedule_name = N'DefaultRestoreJobSchedule',
            @enabled = 1,
            @freq_type = 4,
            @freq_interval = 1,
            @freq_subday_type = 4,
            @freq_subday_interval = 15,
            @freq_recurrence_factor = 0,
            @active_start_date = 20170418,
            @active_end_date = 99991231,
            @active_start_time = 0,
            @active_end_time = 235900,
            @schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT,
            @schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT;
    
        EXECUTE msdb.dbo.sp_attach_schedule
            @job_id = @LS_Secondary__RestoreJobId,
            @schedule_id = @LS_SecondaryRestoreJobScheduleID;
    END
    
    DECLARE @LS_Add_RetCode2 AS INT;
    
    IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
    BEGIN
        EXECUTE @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database
            @secondary_database = N'SampleDB',
            @primary_server = N'<ip_address_of_primary_server>',
            @primary_database = N'SampleDB',
            @restore_delay = 0,
            @restore_mode = 0,
            @disconnect_users = 0,
            @restore_threshold = 45,
            @threshold_alert_enabled = 1,
            @history_retention_period = 5760,
            @overwrite = 1;
    END
    
    IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
    BEGIN
        EXECUTE msdb.dbo.sp_update_job
            @job_id = @LS_Secondary__CopyJobId,
            @enabled = 1;
    
        EXECUTE msdb.dbo.sp_update_job
            @job_id = @LS_Secondary__RestoreJobId,
            @enabled = 1;
    END
    

验证日志传送是否正常运行

  1. 通过在主服务器上启动以下作业来验证日志传送是否正常运行:

    USE msdb;
    GO
    
    EXECUTE dbo.sp_start_job N'LSBackup_SampleDB';
    GO
    
  2. 通过在辅助服务器上启动以下作业来验证日志传送是否正常运行:

    USE msdb;
    GO
    
    EXECUTE dbo.sp_start_job N'LSCopy_SampleDB';
    GO
    
    EXECUTE dbo.sp_start_job N'LSRestore_SampleDB';
    GO
    
  3. 通过执行以下命令验证日志传送故障转移是否正常运行:

    警告

    此命令将使辅助数据库处于联机状态并中断日志传送配置。 运行此命令后,将需要重新配置日志传送。

    RESTORE DATABASE SampleDB WITH RECOVERY;