Get started with Log Shipping on Linux

Article
11/18/2022
4 minutes to read

Applies to: SQL Server - Linux

SQL Server Log shipping is a HA configuration where a database from a primary server is replicated onto one or more secondary servers. In a nutshell, a backup of the source database is restored onto the secondary server. Then the primary server creates transaction log backups periodically, and the secondary servers restore them, updating the secondary copy of the database.

Diagram showing the log shipping workflow.

As described in the this picture, a log shipping session involves the following steps:

Backing up the transaction log file on the primary SQL Server instance
Copying the transaction log backup file across the network to one or more secondary SQL Server instances
Restoring the transaction log backup file on the secondary SQL Server instances

Prerequisites

Install SQL Server Agent on Linux

Note

This tutorial uses CIFS + Samba to setup the network share. If you want to use NFS, leave a comment and we will add it to the doc.

Configure Primary Server

Run the following to install Samba

sudo apt-get install samba #For Ubuntu
sudo yum -y install samba #For RHEL/CentOS

Create a directory to store the logs for Log Shipping and give mssql the required permissions

mkdir /var/opt/mssql/tlogs
chown mssql:mssql /var/opt/mssql/tlogs
chmod 0700 /var/opt/mssql/tlogs

Edit the /etc/samba/smb.conf file (you need root permissions for that) and add the following section:
```
[tlogs]
path=/var/opt/mssql/tlogs
available=yes
read only=yes
browsable=yes
public=yes
writable=no
```
Create a mssql user for Samba
```
sudo smbpasswd -a mssql
```

Restart the Samba services

sudo systemctl restart smbd.service nmbd.service

Configure Secondary Server

Run the following to install the CIFS client

sudo apt-get install cifs-utils #For Ubuntu
sudo yum -y install cifs-utils #For RHEL/CentOS

Create a file to store your credentials. Use the password you recently set for your mssql Samba account

    vim /var/opt/mssql/.tlogcreds
    #Paste the following in .tlogcreds
    username=mssql
    domain=<domain>
    password=<password>

Run the following commands to create an empty directory for mounting and set permission and ownership correctly

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

Add the line to etc/fstab to persist the share

    //<ip_address_of_primary_server>/tlogs /var/opt/mssql/tlogs cifs credentials=/var/opt/mssql/.tlogcreds,ro,uid=mssql,gid=mssql 0 0

Mount the shares
```
sudo mount -a
```

Setup Log Shipping via T-SQL

Run this script from your primary server

BACKUP DATABASE SampleDB
TO DISK = '/var/opt/mssql/tlogs/SampleDB.bak'
GO

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

Run this script from your secondary server

RESTORE DATABASE SampleDB FROM DISK = '/var/opt/mssql/tlogs/SampleDB.bak'
WITH NORECOVERY;

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

Verify Log Shipping works

Verify that Log Shipping works by starting the following job on the primary server
```
USE msdb ;  
GO  

EXECUTE dbo.sp_start_job N'LSBackup_SampleDB' ;  
GO  
```

Verify that Log Shipping works by starting the following job on the secondary server

USE msdb ;  
GO  

EXECUTE dbo.sp_start_job N'LSCopy_SampleDB' ;  
GO  
EXECUTE dbo.sp_start_job N'LSRestore_SampleDB' ;  
GO

Verify that Log Shipping failover works by executing the following command

Warning

This command will bring the secondary database online and break the Log Shipping configuration. You will need to reconfigure Log Shipping after running this command.
```
RESTORE DATABASE SampleDB WITH RECOVERY;
```

Get started with Log Shipping on Linux

Prerequisites

Configure Primary Server

Configure Secondary Server

Setup Log Shipping via T-SQL

Verify Log Shipping works

Feedback

Additional resources

Additional resources

Get started with Log Shipping on Linux

Prerequisites

Setup a network share for Log Shipping using CIFS

Configure Primary Server

Configure Secondary Server

Setup Log Shipping via T-SQL

Verify Log Shipping works

Feedback

Additional resources

Additional resources