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:
- Configured Log Shipping on two connected servers
- Disabled the copy job
- Disconnected one server from the network
- Executed the backup job
- Manually copied the generated backup between the servers
- 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:
The post Can Log Shipping Work Without Shipping the Log? appeared first on .
