Introduzione a Log Shipping in LinuxGet started with Log Shipping on Linux

QUESTO ARGOMENTO SI APPLICA A: noSQL Server su WindowssìSQL Server su LinuxnoDatabase SQL di AzurenoAzure SQL Data WarehousenoParallel Data Warehouse THIS TOPIC APPLIES TO: noSQL Server on WindowsyesSQL Server on LinuxnoAzure SQL DatabasenoAzure SQL Data WarehousenoParallel Data Warehouse

La distribuzione dei Log di SQL Server è una configurazione a disponibilità elevata in un database da un server primario verrà replicato in uno o più server secondari.SQL Server Log shipping is a HA configuration where a database from a primary server is replicated onto one or more secondary servers. In breve, un backup del database di origine viene ripristinato nel server secondario.In a nutshell, a backup of the source database is restored onto the secondary server. Il server primario crea quindi i backup del log delle transazioni periodicamente e i server secondari ripristino, aggiornare la copia del database secondaria.Then the primary server creates transaction log backups periodically, and the secondary servers restore them, updating the secondary copy of the database.

Log Shipping

Come illustrato nell'immagine precedente, una sessione di log shipping prevede i passaggi seguenti:As described in the picture above, a log shipping session involves the following steps:

  • Backup del file di log delle transazioni nell'istanza primaria di SQL ServerBacking up the transaction log file on the primary SQL Server instance
  • Copia il file di backup del log delle transazioni attraverso la rete a uno o più istanze di SQL Server secondarieCopying the transaction log backup file across the network to one or more secondary SQL Server instances
  • Ripristino di file di backup del log delle transazioni in istanze di SQL Server secondarieRestoring the transaction log backup file on the secondary SQL Server instances

PrerequisitiPrerequisites

Programma di installazione di una condivisione di rete per il Log Shipping tramite CIFSSetup a network share for Log Shipping using CIFS

Nota

Questa esercitazione Usa CIFS + Samba per configurare la condivisione di rete.This tutorial uses CIFS + Samba to setup the network share. Se si desidera utilizzare NFS, lasciare un commento e verranno aggiunte per il documento.If you want to use NFS, leave a comment and we will add it to the doc.

Configurare il Server primarioConfigure Primary Server

  • Eseguire il comando seguente per installare SambaRun the following to install Samba

    sudo apt-get install samba #For Ubuntu
    sudo yum -y install samba #For RHEL/CentOS
    
  • Creare una directory per archiviare i log per il Log Shipping e assegnare mssql delle autorizzazioni necessarieCreate 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
    
  • Modificare il file /etc/samba/smb.conf (è necessario disporre delle autorizzazioni radice per tale) e aggiungere la sezione seguente: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
    
  • Creare un utente mssql per SambaCreate a mssql user for Samba

    sudo smbpasswd -a mssql
    
  • Riavviare i servizi SambaRestart the Samba services

    sudo systemctl restart smbd.service nmbd.service
    

Configurare il Server secondarioConfigure Secondary Server

  • Eseguire il comando seguente per installare il client CIFSRun the following to install the CIFS client

    sudo apt-get install cifs-utils #For Ubuntu
    sudo yum -y install cifs-utils #For RHEL/CentOS
    
  • Creare un file per archiviare le credenziali.Create a file to store your credentials. Utilizzare la password impostata per l'account Samba mssql recenteUse 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>
    
  • Eseguire i comandi seguenti per creare una directory vuota per il montaggio e impostare autorizzazioni e proprietà correttamenteRun 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
    
  • Aggiungere la riga e così via/fstab per mantenere la condivisioneAdd 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
    
  • Le condivisioni di montaggioMount the shares

    sudo mount -a
    

Programma di installazione per il Log Shipping tramite Transact-SQLSetup Log Shipping via T-SQL

  • Eseguire lo script dal server primarioRun 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 
    EXEC @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 
    
    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 
    
    EXEC 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 
    
  • Eseguire lo script dal server secondarioRun 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 
    
    EXEC @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 
    
    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'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 
    
    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 
    

Verificare il funzionamento di Log ShippingVerify Log Shipping works

  • Verificare che il Log Shipping funzioni avviando il processo seguente nel server primarioVerify that Log Shipping works by starting the following job on the primary server

    USE msdb ;  
    GO  
    
    EXEC dbo.sp_start_job N'LSBackup_SampleDB' ;  
    GO  
    
  • Verificare che il Log Shipping funzioni avviando il processo seguente nel server secondarioVerify that Log Shipping works by starting the following job on the secondary server

    USE msdb ;  
    GO  
    
    EXEC dbo.sp_start_job N'LSCopy_SampleDB' ;  
    GO  
    EXEC dbo.sp_start_job N'LSRestore_SampleDB' ;  
    GO  
    RESTORE DATABASE SampleDB WITH RECOVERY;