Contact us

Madeira Data Solutions

Your Data, Our Solutions

Can Log Shipping Work Without Shipping the Log?

Written By: Matan Yungman 30/03/2015

When you configure log shipping, there are 4 SQL Server Agent jobs that are set up:

 

Backup: Performs a backup log operation on the primary server, logs history and deletes old backup files and history information

 

Copy: Copies the backup files to the secondary server and logs history

 

Restore: Restores the backups on the secondary server, logs history and deletes old files and history information

 

Alert: Raises an alert for primary and secondary databases when a backup or restore operation does not complete successfully within a specified threshold

 

One of our clients wanted to configure Log Shipping for Disaster Recovery. However, he mentioned that the two SQL Server servers won’t necessarily be connected, and that the log backup copy will be done by an external process due to a bad network infrastructure.

 

The question is: Can we setup log shipping and cut the copy part, or all the parts of the Log Shipping process have to be in place?

 

So we started testing. We:

 

  1. Configured Log Shipping on two connected servers
  2. Disabled the copy job
  3. Disconnected one server from the network
  4. Executed the backup job
  5. Manually copied the generated backup between the servers
  6. Executed the restore job

 

And everything worked well.

In order to verify we didn’t miss anything, we then looked at the documentation of Log Shipping and sqllogship, which is the utility the Log Shipping jobs call and actually performs the work. As you can see in the documentation, the backup, copy and restore jobs log their activity, but do not depend on each other. The alert job (which was not needed by the customer) does not monitor the copy operation, but only whether a backup/restore operation had not completed within a certain threshold.

 

The last step was to generate the needed scripts in order to run them at the production site. Since the two servers won’t be connected, the operation can’t be done using the wizard. For that reason, using the wizard, we configured Log Shipping on the system again and scripted the configuration. We then separated the script into two: One for the primary server and one for the secondary.

 

To save you the hassle, here are two generic scripts you can use to configure Log Shipping in this manner:
zip

 

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *