Lonely Secondary Log Shipping

Have you ever had that rare situation where you need log shipping to another server, but have zero access to the primary server? I encountered that issue recently and my solution was two parts.

A Secondary Log Shipping Target without a Primary. Pretty Cool.

I had the need to consume transaction logs into a StandBy(ReadOnly) database, and continue apply logs to keep it in sync. I could then write code to extract data from that database to the Data Warehouse / Operational Data Store. The original Setup was a traditional Log shipping setup, but the decision was made to allow the Vendor to host the data in their own data center instead of on-prem. The vendor agreed to take an initial backup and put transaction log backups into some Azure blob storage for me to pickup and consume.

My solution ended up to use azcopy.exe and PowerShell to grab the files from blob storage featuring a Shared Access Signature to put them in a folder local to the secondary server, and the to configure the “Lonely Secondary Log Shipping”, so that only the secondary was aware of the arrangement. This has a huge advantage, because the log shipping already has the logic to read the headers of the backups, decide which files with the desired lsn needs to be restored, and cleans up older files automatically. There was no need to re-invent the wheel here.

The PowerShell Portion was fairly simple and clean. I put azcopy.exe in a specific folder on my secondary server, and tracked down the specifics I would need:

Created the Shared Access Signature for the storage account with a ten year expiry so I don’t have to look at it again.
copied the url for the storage account.
Identified the specific Blob and sub directory where the files are being placed.

##copied to this location
$azcopy = 'D:\Data\PowerShell\Azure\azcopy.exe'

## provided, unique
$SharedAccessSignature = '?sv=2020-08-04&ss=bfqt&srt=sco&sp=rwdlacupitfx&se=2032-04-27T09:51:47Z&st=2022-04-27T01:51:47Z&spr=https&sig=nzU%2AnRK6iDC1YYehK7HvoZtPxNVTwpnfxhgGboF6lZc%3D'

## Variables and Defaults
$SourceContainer = "https://totallyfakestorageblobname.blob.core.windows.net/allscripts-pm-db-replication/DataSourceDB/*"
$FinalContainer = $SourceContainer + $SharedAccessSignature
$DestinationDirectory = "D:\logshipping\DataSourceDB"
if(![System.IO.Directory]::Exists($DestinationDirectory)){[System.IO.Directory]::CreateDirectory($DestinationDirectory)}

## Copy Command from Blob to Local Disk, only File extensions common to SQL Backups
&$azcopy copy $FinalContainer $DestinationDirectory --include-pattern '*.bak;*.trn' --overwrite=false

For the secondary Log shipping, I adapted code I had already automated via PowerShell to create only the secondary portions, and parameterized it for SQL instead. little improvements over the default log shipping, like customized names, and staggering the restore to be five minutes after the copy are just pieces that make this a bit more robust. the Primary Server name can be totally fake, we never will connect to it, but it should at least imply where it really comes from. I of course had to restore the full backup first, but after that, there are only four @parameters to change to make this work for yourself.

--#################################################################################################
-- leverage Log Shipping Secondary where the primary may be doing full and transaction logs,
-- but no access between the two
--#################################################################################################
--restore the full backup first, in standby read only
USE [master]
RESTORE DATABASE [DataSourceDB] 
FROM  DISK = N'\\mydomain\DataManagement_Backup\SQLBackups\MyProdServer\DataSourceDB\DataSourceDB_backup_2022_04_27_071611.bak' 
WITH  FILE = 1,  
MOVE N'DataSourceDB' 
TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data\DataSourceDB.mdf',  
MOVE N'DataSourceDB_log' 
TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\DataSourceDB_log.ldf',  
--NORECOVERY,
STANDBY = N'\\mydomain\DataManagement_Backup\SQLBackups\MyProdServer\DataSourceDB_RollbackUndo_2022-04-27_07-17-25.bak',  
NOUNLOAD,  
STATS = 5
GO
--On the secondary server, execute sp_add_log_shipping_secondary_primary supplying the details of the primary server and database. This stored procedure returns the secondary ID and the copy and restore job IDs.
--On the secondary server, execute sp_add_jobschedule to set the schedule for the copy and restore jobs.
--On the secondary server, execute sp_add_log_shipping_secondary_database to add a secondary database.
--SKIPPED AS No AccessOn the primary server, execute sp_add_log_shipping_primary_secondary to add the required information about the new secondary database to the primary server.
--On the secondary server, enable the copy and restore jobs. For more information, see Disable or Enable a Job.
DECLARE 
@LSPrimaryServer NVARCHAR(128) = 'OffDomainSourceServer',
@LSPrimaryDatabase NVARCHAR(128) = 'DataSourceDB',
--@LSSecondaryServer NVARCHAR(128) = @@SERVERNAME,
@LSSecondaryDatabase NVARCHAR(128) = 'DataSourceDB',
--this is the folder where we expect the log shipping files
@BasePath VARCHAR(8000) = 'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\logshipping\'

DECLARE @copyjobname NVARCHAR(128)    = N'LSCopy_' + @LSPrimaryServer + '_for_' + @LSSecondaryDatabase,
        @restorejobname NVARCHAR(128) = N'LSRestore ' + '_for_' + @LSPrimaryDatabase + '_' + @LSSecondaryDatabase
  DECLARE @ErrorSeverity INT,
                @ErrorNumber INT,
                @ErrorMessage NVARCHAR(4000),
                @ErrorState INT,
                @ErrorLine INT;
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 = @LSPrimaryServer 
        ,@primary_database = @LSPrimaryDatabase 
        ,@backup_source_directory = @BasePath 
        ,@backup_destination_directory = @BasePath 
        ,@copy_job_name = @copyjobname
        ,@restore_job_name = @restorejobname
        ,@file_retention_period = 1440 
        ,@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 = 60 --every 60 minutes
        ,@freq_recurrence_factor = 0 
        ,@active_start_date = 20090505 
        ,@active_end_date = 99991231 
        ,@active_start_time = 000500 --note copy @ 05 min, restore @10 min
        ,@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 = 60 --every 60 minutes
        ,@freq_recurrence_factor = 0 
        ,@active_start_date = 20090505 
        ,@active_end_date = 99991231 
        ,@active_start_time = 001000 --note copy @ 05 min, restore @10 min
        ,@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 
ELSE
BEGIN
        SELECT @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorNumber   = ERROR_NUMBER(),
               @ErrorMessage  = ERROR_MESSAGE(),
               @ErrorState    = ERROR_STATE(),
               @ErrorLine     = ERROR_LINE()
RAISERROR('ErrorNumber %d, Level %d, State %d, Line %d Err: %s',@ErrorSeverity,@ErrorState,@ErrorNumber,@ErrorSeverity,@ErrorState,@ErrorLine,@ErrorMessage);
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 = @LSSecondaryDatabase 
        ,@primary_server = @LSPrimaryServer 
        ,@primary_database = @LSPrimaryDatabase 
        ,@restore_delay = 0 
        ,@restore_mode = 1  --standby read only
        ,@disconnect_users = 1 --hell yes disconnect those guys
        ,@restore_threshold = 180   
        ,@threshold_alert_enabled = 1 
        ,@history_retention_period = 2880
        ,@overwrite = 1

END 
ELSE
BEGIN
        SELECT @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorNumber   = ERROR_NUMBER(),
               @ErrorMessage  = ERROR_MESSAGE(),
               @ErrorState    = ERROR_STATE(),
               @ErrorLine     = ERROR_LINE()
RAISERROR('ErrorNumber %d, Level %d, State %d, Line %d Err: %s',@ErrorSeverity,@ErrorState,@ErrorNumber,@ErrorSeverity,@ErrorState,@ErrorLine,@ErrorMessage);
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 
ELSE
BEGIN
        SELECT @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorNumber   = ERROR_NUMBER(),
               @ErrorMessage  = ERROR_MESSAGE(),
               @ErrorState    = ERROR_STATE(),
               @ErrorLine     = ERROR_LINE()
RAISERROR('ErrorNumber %d, Level %d, State %d, Line %d Err: %s',@ErrorSeverity,@ErrorState,@ErrorNumber,@ErrorSeverity,@ErrorState,@ErrorLine,@ErrorMessage);
END

Stormrage

Senior SQL DBA and Code Monkey Script Dump

Lonely Secondary Log Shipping

Leave a Reply Cancel reply