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.<\/p>\n
I tried to simulate this scenario on my lab environment as shown below:<\/span><\/p>\n The \/var\/opt\/mssql\/tlogs <\/em>folder on Linux09 is part of the Samba share. Then the \/var\/opt\/mssql\/logshipping_tlogs<\/em> folder on Linux08 will store local database backup copies and finally the \/var\/opt\/mssql\/data\/backup_logshipping<\/em> folder will be used as destination folder of the database backup medias before restoring on the standby server.<\/p>\n 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\u2019t use LDAP-based but only Linux-based authentication and I didn\u2019t 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.<\/p>\n 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 \u201climited\u201d exposition scenario. Please feel free to comment if you have a better solution.<\/p>\n My effective Samba configuration is as follows:<\/p>\n 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.<\/span><\/p>\n We may use the \u2013Z parameter of the ls command to get security-relevant information of the tlogs folder.\u00a0<\/span><\/p>\n The folder is not tagged with the correct domain type and we must change it by using the chcon command as follows:<\/span><\/p>\n Let\u2019s 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.<\/p>\n 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:<\/p>\n And the final result …<\/p>\n Here we go!<\/p>\n 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:<\/p>\n But wait \u2026 a CmdExec subsystem and a Windows-like command (c:\\tools\\\\bin\\sqllogship.exe<\/em>). 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.<\/p>\n 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.<\/em><\/strong><\/p><\/blockquote>\n Let\u2019s finish this blog with my wish on this topic:<\/p>\n 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!<\/p>\n By David Barbarin<\/span><\/p>\n","protected":false},"excerpt":{"rendered":" In this blog let\u2019s talk a little bit about the log-shipping feature from the SQL Server CTP 2.0. First of all, let\u2019s 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 […]<\/p>\n","protected":false},"author":26,"featured_media":10145,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[84,73,1113,15,51],"type_dbi":[],"class_list":["post-10144","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring","tag-high-availability","tag-linux","tag-log-shipping","tag-migration","tag-sql-server"],"acf":[],"yoast_head":"\n![\"blog](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-122-00-logship-scenario-e1495648523959.jpg\")
<\/p>\n[root@LINUX09 mssql]# testparm\nLoad smb config files from \/etc\/samba\/smb.conf\nrlimit_max: increasing rlimit_max (1024) to minimum Windows limit (16384)\nProcessing section \"[tlogs]\"\nLoaded services file OK.\nServer role: ROLE_STANDALONE\n\nPress enter to see a dump of your service definitions\n# Global parameters\n[global]\n server string = Samba %v on %L\n workgroup = DBI-SERVICES\n map to guest = Bad User\n security = USER\n idmap config * : backend = tdb\n hosts allow = 192.168.40.24\/24 192.168.5.81\/24\n hosts deny = 0.0.0.0\/0\n\n[tlogs]\n comment = Log Shipping repository\n path = \/var\/opt\/mssql\/tlogs\n browseable = No\n force user = mssql\n guest ok = Yes\n read only = No\n write list = mssql<\/pre>\n
[root@LINUX09 mssql]# getenforce\nEnforcing<\/pre>\n
[root@LINUX09 mssql]# ls -dZ | grep tlogs\ndrwxr-xr-x. mssql 700 unconfined_u:object_r:var_t:s0 tlogs<\/pre>\n
[root@LINUX09 mssql]# chcon -R -t samba_share_t tlogs\/\n[root@LINUX09 mssql]# ls -dZ | grep tlogs\ndrwxr-xr-x. mssql 700 unconfined_u:object_r:samba_share_t:s0 tlogs<\/pre>\n
[root@LINUX09 mssql]# smbstatus --share\n\nSamba version 4.4.4\nPID Username Group Machine Protocol Version Encryption Signing\n----------------------------------------------------------------------------------------------------------------------------------------\n4363 mssql mssql 192.168.40.24 (ipv4:192.168.40.24:53314) NT1 - - \n4265 nobody nobody 192.168.5.81 (ipv4:192.168.5.81:50798) SMB3_11 - - \n\u2026<\/pre>\n
:CONNECT WIN20161SQL16\\SQL16 \nBACKUP DATABASE [AdventureWorks2012]\nTO DISK = '\\\\linux09\\tlogs\\AdventureWorks2012_INIT.BAK'\nWITH INIT, FORMAT, COMPRESSION, STATS = 5;\nGO\n\nBACKUP LOG [AdventureWorks2012]\nTO DISK = '\\\\linux09\\tlogs\\AdventureWorks2012_INIT.TRN'\nWITH INIT, FORMAT, COMPRESSION, STATS = 5;\nGO\n\n:CONNECT LINUX08 -Usa -PXXXXX\nRESTORE DATABASE [AdventureWorks2012]\nFROM DISK = '\/var\/opt\/mssql\/logshipping_tlogs\/AdventureWorks2012_INIT.BAK'\nWITH MOVE 'AdventureWorks2012_Data' TO '\/var\/opt\/mssql\/data\/AdventureWorks2012_Data.mdf',\n MOVE 'AdventureWorks2012_Log' TO '\/var\/opt\/mssql\/data\/AdventureWorks2012_log.ldf',\nNORECOVERY, STATS = 5;\nGO\n\n:CONNECT LINUX08 -Usa -PXXXXX\nRESTORE LOG [AdventureWorks2012]\nFROM DISK = '\/var\/opt\/mssql\/logshipping_tlogs\/AdventureWorks2012_INIT.TRN'\nWITH NORECOVERY, STATS = 5;\nGO\n\n\n:CONNECT WIN20161SQL16\\SQL16 \nDECLARE @LS_BackupJobId AS uniqueidentifier \nDECLARE @LS_PrimaryId AS uniqueidentifier \nDECLARE @SP_Add_RetCode As int \n\nEXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database \n @database = N'AdventureWorks2012' \n ,@backup_directory = N'\\\\linux09\\tlogs' \n ,@backup_share = N'\\\\linux09\\tlogs' \n ,@backup_job_name = N'LSBackup_AdventureWorks2012' \n ,@backup_retention_period = 4320\n ,@backup_compression = 2\n ,@backup_threshold = 60 \n ,@threshold_alert_enabled = 1\n ,@history_retention_period = 5760 \n ,@backup_job_id = @LS_BackupJobId OUTPUT \n ,@primary_id = @LS_PrimaryId OUTPUT \n ,@overwrite = 1 \n\nIF (@@ERROR = 0 AND @SP_Add_RetCode = 0) \nBEGIN \n\nDECLARE @LS_BackUpScheduleUID As uniqueidentifier \nDECLARE @LS_BackUpScheduleID AS int \n\nEXEC msdb.dbo.sp_add_schedule \n @schedule_name =N'LSBackupSchedule' \n ,@enabled = 1 \n ,@freq_type = 4 \n ,@freq_interval = 1 \n ,@freq_subday_type = 4 \n ,@freq_subday_interval = 15 \n ,@freq_recurrence_factor = 0 \n ,@active_start_date = 20170418 \n ,@active_end_date = 99991231 \n ,@active_start_time = 0 \n ,@active_end_time = 235900 \n ,@schedule_uid = @LS_BackUpScheduleUID OUTPUT \n ,@schedule_id = @LS_BackUpScheduleID OUTPUT \n\nEXEC msdb.dbo.sp_attach_schedule \n @job_id = @LS_BackupJobId \n ,@schedule_id = @LS_BackUpScheduleID \n\nEXEC msdb.dbo.sp_update_job \n @job_id = @LS_BackupJobId \n ,@enabled = 1 \n\nEND \n\nEXEC master.dbo.sp_add_log_shipping_alert_job\nGO\n\nEXEC master.dbo.sp_add_log_shipping_primary_secondary \n @primary_database = N'AdventureWorks2012' \n ,@secondary_server = N'LINUX08' \n ,@secondary_database = N'AdventureWorks2012' \n ,@overwrite = 1;\nGO\n\n\n:CONNECT LINUX08 -Usa -PXXXXX\nDECLARE @LS_Secondary__CopyJobId AS uniqueidentifier \nDECLARE @LS_Secondary__RestoreJobId AS uniqueidentifier \nDECLARE @LS_Secondary__SecondaryId AS uniqueidentifier \nDECLARE @LS_Add_RetCode As int \n\nEXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary \n\t@primary_server = N'WIN20161SQL16\\SQL16' \n\t,@primary_database = N'AdventureWorks2012' \n\t,@backup_source_directory = N'\/var\/opt\/mssql\/logshipping_tlogs\/' \n\t,@backup_destination_directory = N'\/var\/opt\/mssql\/data\/backup_logshipping\/' \n\t,@copy_job_name = N'LSCopy_AdventureWorks2012' \n\t,@restore_job_name = N'LSRestore_AdventureWorks2012' \n\t,@file_retention_period = 4320\n\t--,@monitor_server = 'LINUX09'\n\t--,@monitor_server_security_mode = 0 -- SQL authentication\n\t--,@monitor_server_login = 'logship_login'\n\t--,@monitor_server_password = 'XXXXX'\n\t,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT \n\t,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT \n\t,@secondary_id = @LS_Secondary__SecondaryId OUTPUT \n\t,@overwrite = 1 \n\nSELECT @LS_Secondary__CopyJobId;\nSELECT @LS_Secondary__RestoreJobId;\nSELECT @LS_Secondary__SecondaryId\n\nIF (@@ERROR = 0 AND @LS_Add_RetCode = 0) \nBEGIN \n\nDECLARE @LS_SecondaryCopyJobScheduleUID As uniqueidentifier \nDECLARE @LS_SecondaryCopyJobScheduleID AS int \n\nEXEC msdb.dbo.sp_add_schedule \n @schedule_name =N'DefaultCopyJobSchedule' \n ,@enabled = 1 \n ,@freq_type = 4 \n ,@freq_interval = 1 \n ,@freq_subday_type = 4 \n ,@freq_subday_interval = 15 \n ,@freq_recurrence_factor = 0 \n ,@active_start_date = 20170418 \n ,@active_end_date = 99991231 \n ,@active_start_time = 0 \n ,@active_end_time = 235900 \n ,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT \n ,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT \n\nEXEC msdb.dbo.sp_attach_schedule \n @job_id = @LS_Secondary__CopyJobId \n ,@schedule_id = @LS_SecondaryCopyJobScheduleID \n\nDECLARE @LS_SecondaryRestoreJobScheduleUID As uniqueidentifier \nDECLARE @LS_SecondaryRestoreJobScheduleID AS int \n\nEXEC msdb.dbo.sp_add_schedule \n @schedule_name =N'DefaultRestoreJobSchedule' \n ,@enabled = 1 \n ,@freq_type = 4 \n ,@freq_interval = 1 \n ,@freq_subday_type = 4 \n ,@freq_subday_interval = 15 \n ,@freq_recurrence_factor = 0 \n ,@active_start_date = 20170418 \n ,@active_end_date = 99991231 \n ,@active_start_time = 0 \n ,@active_end_time = 235900 \n ,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT \n ,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT \n\nEXEC msdb.dbo.sp_attach_schedule \n @job_id = @LS_Secondary__RestoreJobId \n ,@schedule_id = @LS_SecondaryRestoreJobScheduleID \n\nEND \n\nDECLARE @LS_Add_RetCode2 As int \nIF (@@ERROR = 0 AND @LS_Add_RetCode = 0) \nBEGIN \n\nEXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database \n @secondary_database = N'AdventureWorks2012' \n ,@primary_server = N'WIN20161SQL16\\SQL16' \n ,@primary_database = N'AdventureWorks2012' \n ,@restore_delay = 0 \n ,@restore_mode = 0 \n ,@disconnect_users = 0 \n ,@restore_threshold = 45 \n ,@threshold_alert_enabled = 1 \n ,@history_retention_period = 5760 \n ,@overwrite = 1 \n\nEND \n\nIF (@@error = 0 AND @LS_Add_RetCode = 0) \nBEGIN \n\nEXEC msdb.dbo.sp_update_job \n @job_id = @LS_Secondary__CopyJobId \n ,@enabled = 1 \n\nEXEC msdb.dbo.sp_update_job \n @job_id = @LS_Secondary__RestoreJobId \n ,@enabled = 1 \n\nEND<\/pre>\n
![\"blog](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-122-01-logship-logshipping-session-e1495648986399.jpg\")
<\/p>\n![\"blog](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-122-02-logship-sql-agent-subsystem.jpg\")
<\/p>\n