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=falseFor 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