Linux 上的日志传送入门

适用于：SQL Server - Linux

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

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

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

必备条件

• 在 Linux 上安装 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;
   

相关内容

• 关于日志传送 (SQL Server)
• 配置日志传送 (SQL Server)