SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Can Log Shipping Work Without Shipping the Log?

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

The post Can Log Shipping Work Without Shipping the Log? appeared first on .

Matan Yungman

Matan Yungman is a Technical Evangelist and senior consultant at Madeira SQL Server Services. His job is to get the word out about SQL Server. He speaks, lectures, writes, teaches and consults about SQL Server, focusing mainly on performance tuning, database development, high availability and database design. He’s passionate about SQL Server, technology and the SQL Server community.

Comments

Leave a comment on the original post [www.madeiradata.com, opens in a new window]

Loading comments...