SQL Server 2017 on Linux and some log shipping scenarios

In this blog let’s talk a little bit about the log-shipping feature from the SQL Server CTP 2.0. First of all, let’s say that as a Windows version, it is possible to create our own solution based on Linux (and cron jobs for example). You may find out different pointers on the internet that deal with this topic.

But getting the built-in log-shipping feature from SQL Server presents some advantages. Indeed, this is basically the log-shipping feature we already used with previous versions, which includes all the backup / copy / restore / retention stuff as well as the monitoring capabilities in-a-box. It requires probably less effort and we benefit from bug fixes from Microsoft.

I did perform my first tests with a full Linux environment that includes two servers – one primary server and one secondary server by following the Microsoft documentation. Good news! I did not run into issues so far.

Well a secondary scenario draws my attention because it might be a real scenario at customer shops, which concerns migration between a SQL Server 2016 environment on Windows and SQL Server 2017 environment on Linux. Basically a customer who runs on a full Linux environment and wants to take the opportunity to move on Linux their SQL Server environments.

I tried to simulate this scenario on my lab environment as shown below:

The /var/opt/mssql/tlogs folder on Linux09 is part of the Samba share. Then the /var/opt/mssql/logshipping_tlogs folder on Linux08 will store local database backup copies and finally the /var/opt/mssql/data/backup_logshipping folder will be used as destination folder of the database backup medias before restoring on the standby server.

I had to change some configuration settings from the initial Microsoft documentation in regard to my new context. Firstly, connecting from Windows to a Samba share on Linux without requesting the credential was a little bit challenging and introduced some security concerns. Indeed, my samba server doesn’t use LDAP-based but only Linux-based authentication and I didn’t want to introduce additional complexity only for a temporary scenario where the Windows Server will be decommissioned after migrating and upgrading the concerned database server.

I set up my Samba Server by allowing anonymous connections but at the same time I limited the number of servers authorized to connect to the Samba share (tlog in my case) as well as the discovery of the folder from Windows. Basically, only the servers involved in the log shipping session are allowed to connect and interact as the mssql user. Obviously, this is definitely not a perfect solution in terms of security but it produces at least a “limited” exposition scenario. Please feel free to comment if you have a better solution.

My effective Samba configuration is as follows:

[root@LINUX09 mssql]# testparm
Load smb config files from /etc/samba/smb.conf
rlimit_max: increasing rlimit_max (1024) to minimum Windows limit (16384)
Processing section "[tlogs]"
Loaded services file OK.
Server role: ROLE_STANDALONE

Press enter to see a dump of your service definitions
# Global parameters
[global]
        server string = Samba %v on %L
        workgroup = DBI-SERVICES
        map to guest = Bad User
        security = USER
        idmap config * : backend = tdb
        hosts allow = 192.168.40.24/24 192.168.5.81/24
        hosts deny = 0.0.0.0/0

[tlogs]
        comment = Log Shipping repository
        path = /var/opt/mssql/tlogs
        browseable = No
        force user = mssql
        guest ok = Yes
        read only = No
        write list = mssql

One additional topic I had to manage concerned the SELinux context and labeling files for my samba share. Indeed, in my case without configuring the correct label (that is part of the security-relevant information) I experienced denied access issues when I tried to create either a simple text file or to perform a database backup from SQL Server to my Samba share. This is because with SELinux enabled, the server samba (smbd) runs confined by default meaning the corresponding service runs in its own domain.

[root@LINUX09 mssql]# getenforce
Enforcing

We may use the –Z parameter of the ls command to get security-relevant information of the tlogs folder. 

 [root@LINUX09 mssql]# ls -dZ | grep tlogs
drwxr-xr-x. mssql   700 unconfined_u:object_r:var_t:s0   tlogs

The folder is not tagged with the correct domain type and we must change it by using the chcon command as follows:

[root@LINUX09 mssql]# chcon -R -t samba_share_t tlogs/
[root@LINUX09 mssql]# ls -dZ | grep tlogs
drwxr-xr-x. mssql   700 unconfined_u:object_r:samba_share_t:s0 tlogs

Let’s now have a look at the Samba share status. We may notice interesting things. Firstly, there are different protocols and credentials regarding the incoming operation system and the connection method. The primary server (192.168.5.81) which runs on Windows operation system uses the natively SMB3 protocol and anonymous credential (nobody). In my context anonymous connections are mapped to special guest account. Furthermore, the standby server (192.168.40.24) which runs on Linux uses a completely different protocol that is NT1 because we use CIFS as authentication protocol in this case.

[root@LINUX09 mssql]# smbstatus --share

Samba version 4.4.4
PID     Username     Group        Machine                                   Protocol Version  Encryption           Signing
----------------------------------------------------------------------------------------------------------------------------------------
4363    mssql        mssql        192.168.40.24 (ipv4:192.168.40.24:53314)  NT1               -                    -      
4265    nobody       nobody       192.168.5.81 (ipv4:192.168.5.81:50798)    SMB3_11           -                    -      
…

So the ultimate step concerned the configuration of my log-shipping session. Nothing new here and I referred to the Microsoft documentation for that. I just changed the parameter values regarding my context. Here the T-SQL code I used:

:CONNECT WIN20161SQL16\SQL16 
BACKUP DATABASE [AdventureWorks2012]
TO DISK = '\\linux09\tlogs\AdventureWorks2012_INIT.BAK'
WITH INIT, FORMAT, COMPRESSION, STATS = 5;
GO

BACKUP LOG [AdventureWorks2012]
TO DISK = '\\linux09\tlogs\AdventureWorks2012_INIT.TRN'
WITH INIT, FORMAT, COMPRESSION, STATS = 5;
GO

:CONNECT LINUX08 -Usa -PXXXXX
RESTORE DATABASE [AdventureWorks2012]
FROM DISK = '/var/opt/mssql/logshipping_tlogs/AdventureWorks2012_INIT.BAK'
WITH MOVE 'AdventureWorks2012_Data' TO '/var/opt/mssql/data/AdventureWorks2012_Data.mdf',
     MOVE 'AdventureWorks2012_Log' TO '/var/opt/mssql/data/AdventureWorks2012_log.ldf',
NORECOVERY, STATS = 5;
GO

:CONNECT LINUX08 -Usa -PXXXXX
RESTORE LOG [AdventureWorks2012]
FROM DISK = '/var/opt/mssql/logshipping_tlogs/AdventureWorks2012_INIT.TRN'
WITH NORECOVERY,  STATS = 5;
GO


:CONNECT WIN20161SQL16\SQL16 
DECLARE @LS_BackupJobId AS uniqueidentifier 
DECLARE @LS_PrimaryId   AS uniqueidentifier 
DECLARE @SP_Add_RetCode As int 

EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database 
         @database = N'AdventureWorks2012' 
        ,@backup_directory = N'\\linux09\tlogs' 
        ,@backup_share = N'\\linux09\tlogs' 
        ,@backup_job_name = N'LSBackup_AdventureWorks2012' 
        ,@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 

EXEC 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 

EXEC msdb.dbo.sp_attach_schedule 
        @job_id = @LS_BackupJobId 
        ,@schedule_id = @LS_BackUpScheduleID  

EXEC msdb.dbo.sp_update_job 
        @job_id = @LS_BackupJobId 
        ,@enabled = 1 

END 

EXEC master.dbo.sp_add_log_shipping_alert_job
GO

EXEC master.dbo.sp_add_log_shipping_primary_secondary 
        @primary_database = N'AdventureWorks2012' 
        ,@secondary_server = N'LINUX08' 
        ,@secondary_database = N'AdventureWorks2012' 
        ,@overwrite = 1;
GO


:CONNECT LINUX08 -Usa -PXXXXX
DECLARE @LS_Secondary__CopyJobId    AS uniqueidentifier 
DECLARE @LS_Secondary__RestoreJobId AS uniqueidentifier 
DECLARE @LS_Secondary__SecondaryId  AS uniqueidentifier 
DECLARE @LS_Add_RetCode As int 

EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary 
	@primary_server = N'WIN20161SQL16\SQL16' 
	,@primary_database = N'AdventureWorks2012' 
	,@backup_source_directory = N'/var/opt/mssql/logshipping_tlogs/' 
	,@backup_destination_directory = N'/var/opt/mssql/data/backup_logshipping/' 
	,@copy_job_name = N'LSCopy_AdventureWorks2012' 
	,@restore_job_name = N'LSRestore_AdventureWorks2012' 
	,@file_retention_period = 4320
	--,@monitor_server = 'LINUX09'
	--,@monitor_server_security_mode = 0 -- SQL authentication
	--,@monitor_server_login = 'logship_login'
	--,@monitor_server_password = 'XXXXX'
	,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT 
	,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT 
	,@secondary_id = @LS_Secondary__SecondaryId OUTPUT 
	,@overwrite = 1 

SELECT @LS_Secondary__CopyJobId;
SELECT @LS_Secondary__RestoreJobId;
SELECT @LS_Secondary__SecondaryId

IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) 
BEGIN 

DECLARE @LS_SecondaryCopyJobScheduleUID As uniqueidentifier 
DECLARE @LS_SecondaryCopyJobScheduleID  AS int 

EXEC 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 

EXEC msdb.dbo.sp_attach_schedule 
        @job_id = @LS_Secondary__CopyJobId 
        ,@schedule_id = @LS_SecondaryCopyJobScheduleID  

DECLARE @LS_SecondaryRestoreJobScheduleUID  As uniqueidentifier 
DECLARE @LS_SecondaryRestoreJobScheduleID   AS int 

EXEC 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 

EXEC 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 

EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database 
        @secondary_database = N'AdventureWorks2012' 
        ,@primary_server = N'WIN20161SQL16\SQL16' 
        ,@primary_database = N'AdventureWorks2012' 
        ,@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 

EXEC msdb.dbo.sp_update_job 
        @job_id = @LS_Secondary__CopyJobId 
        ,@enabled = 1 

EXEC msdb.dbo.sp_update_job 
        @job_id = @LS_Secondary__RestoreJobId 
        ,@enabled = 1 

END

And the final result …

Here we go!

One last thing that drew my attention during my tests concerns the related SQL Server agent jobs on the Linux side. If we take a look at the different steps we may notice an interesting point as shown below:

But wait … a CmdExec subsystem and a Windows-like command (c:\tools\\bin\sqllogship.exe). My first reaction was it is impossible to run a Windows executable file on Linux. I also double checked on my Linux environment if such path exists and obviously not. Then I remembered the new SQL Server PAL architecture which is basically a bubble where a NT process can be executed. Why not using the sqllogship.exe in the same way? I got an answer from Microsoft (Thanks Mihaela and Val) that definitely confirmed my assumption.

Yes, sqllogship.exe runs within the sqlserver container without changes. Since the container is not generic, CmdExec subsystem runs only internal program(s), it cannot be used for anything else.

Let’s finish this blog with my wish on this topic:

Log-shipping seems to work well on Linux but I would like to get central monitor capabilities for my log-shipping sessions. It concerns either full linux-based or mixed-based scenarios (Windows and Linux). But at the moment of the CTP 2.1, linked servers are not supported on Linux and configuring a central monitor server requires a piece of linked servers on both primary and secondary servers. But we have good chance to get support on this topic as mentioned by Travis Wright from Microsoft in the comment section. Keep waiting!

By David Barbarin