Log shipping across domains with FTP

If you have decided to use SQL Server Log Shipping as a Disaster Recovery (DR) solution, but for whatever reason your offsite data centre or external host isn’t on the same domain as you is Log Shipping still viable?

Yes, but you are going to have to go outside of the usual interface and work those cross company / cross domain issues yourself.

Basic Log Shipping Process

  1. Take Log backups on a schedule every N minutes
  2. Transfer Log backups to N* machines you wish to host a warm DR copy of the database
  3. Restore the Log backup (in either “no recover” or “standby”)
  4. Repeat 1-3
  5. If DR event occurs, restore last Log “With Recovery” and inform users of new DB location

Points 2 and 3, the transfer and restore process, is what you are interested in once outside of the standard process.

Backup

Use the Transaction Log Shipping setup option for your desired master database, but stop short at configuring the secondary server instances.  This will create the master Log backup jobs and schedule them as required.

FTP Transfer

I’ve used FTP as the method to transfer files offsite from the local domain to a hosted DR server in an external data centre. WinSCP is good because it offers a few transmission choices including file synchronisation, is scriptable and free.

Using a scheduled task run WinSCP and load a script.

“C:\Program Files (x86)\WinSCP\WinSCP.exe” /console /script=C:\<yourFilePath>\<BDScript.txt>

Here is the script to sync the directories. You will need to create a connection profile first in WinSCP using the GUI to reference in the script so you don’t need to worry about username / passwords being available in a text file.

# Abort automatically from script on errors
option batch abort
# Disable overwrite confirmations for previous files
option confirm off
#Setup site connection in WinSCP first, ref here
open vm_ls@172.22.21.21
#change local dir
lcd "E:\SQL\LS_Path"
#Change remote path
cd /
#file transfer in binary mode
option transfer binary
#Sync with the remote server, check by size, preserve file time and allow delete
synchronize -delete remote -criteria=size -preservetime -resumesupport=on
#disconnect
close
#Exit WinSCP
exit

SQL Restore

On the secondary server(s) (yes, you can send files to as many servers as required and have licenses for) you need to create the SQL Agent restore job as outside of the usual process SQL Server won’t create the jobs for you on the secondary’s.  You will also need to perform the first time initialisation of the database and leave it in a restoring state to accept further Log backups.

SQL Agent job script

USE [master]
DECLARE
 @LSCopy_Job uniqueidentifier,
 @LSRestore_Job uniqueidentifier,
 @LSSecondary_Id uniqueidentifier

exec sp_add_log_shipping_secondary_primary
 @primary_server = '<machine_name>',
 @primary_database = '<db_name>',
 @backup_source_directory = 'C:\<path>\<here>',
 @backup_destination_directory = 'C:\<path>\<here>',
 @copy_job_name = 'JobNameCopy',
 @restore_job_name = 'JobNameRestore',
 @file_retention_period = 4320, 
 @overwrite = 1,
 @copy_job_id = @LSCopy_Job OUTPUT ,
 @restore_job_id = @LSRestore_Job OUTPUT ,
 @secondary_id = @LSSecondary_Id OUTPUT

exec sp_add_log_shipping_secondary_database
 @secondary_database = '<db_name>',
 @primary_server = '<machine_name>',
 @primary_database = '<db_name>',
 @restore_delay = 0,
 @restore_all = 1,
 @restore_mode = 1,
 @disconnect_users = 1,
 @restore_threshold = 120,
 @threshold_alert_enabled = 1

This script will create two jobs, ‘JobNameCopy’ and ‘JobNameRestore’.  Thanks to the FTP process we don’t need to pull the log backups from the primary server source so the ‘JobNameCopy’ can be deleted or disabled. More info from MSDN here and here.

Now you can configure the timing of the restore job to run at suitable intervals to tie up with your copy process.  Remember that you need to allow time for the FTP copy process to complete before the restore job runs as SQL requires an exclusive lock on the Log backup and part transferred files cannot be included in any running restore process.

Initialise database

Take a full backup of the source database, ship it over (perhaps using your new FTP link?) and restore it, setting the restore option, recovery state to either “Restore with NoRecovery” or “Restore with Standby”

Conclusion

Hopefully you will have a smooth DR process with Log Shipping, even when implemented across the usual company domain boundaries using FTP.  Log Shipping is a highly robust process and works well even over high latency satellite connections with FTP.

This entry was posted in Backups, Microsoft, Networking, SQL Server. Bookmark the permalink.