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:
[[email protected] 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.
[[email protected] mssql]# getenforce Enforcing
We may use the –Z parameter of the ls command to get security-relevant information of the tlogs folder.
[[email protected] 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:
[[email protected] mssql]# chcon -R -t samba_share_t tlogs/ [[email protected] 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.
[[email protected] 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