SQL Server 2008 Log Shipping

  • Article

Here are some notes on “SQL Server 2008 Log Shipping” I took while attending an advanced class on SQL Server taught by Greg Low (from https://sqlblog.com/blogs/greg_low/ and https://www.sqldownunder.com/).

Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without some pre-requisite knowledge and the right context. Reading the post from top to bottom will help.

Log shipping

  • Automated process to ship transaction log backups to another server
  • Three jobs to perform the required actions: backup, copy and recover
  • Involves three servers/instances: primary, secondary and (optional) monitoring server
  • Requires full or bulk logged mode
  • Can have multiple secondary copies
  • Information about log shipping kept in MSDB
  • See https://msdn.microsoft.com/en-us/library/ms187103.aspx

Log Shipping Options

  • Interval – Default is 15 minutes. Consider the impact before using a smaller number
  • Delayed log restore – option that allows you to have an older copy, for an “oops” situation
  • If secondary is far behind. Consider backup/copy to media/restore, consider partitioning the data.
  • Careful – Backup typically goes to a file server share. Agent account needs access to share.
  • Careful – Weekend maintenance jobs (like indexing) can make you run out of disk space
  • No option to synchronize logins – Consider sp_resolve_logins to import.
  • Monitoring – MSDB tables, agent history
  • See https://msdn.microsoft.com/en-us/library/ms190224.aspx

Log Shipping Failover

  • No automated failover. If automatic failover is required, consider Mirroring instead.
  • To failover:
  • - Stop the jobs
  • - Take a tail of the log backup (BACKUP LOG … WITH NO_RECOVERY)
  • - Apply tail of the log backup to secondary with a RESTORE LOG ... WITH RECOVERY
  • - If required, configure log shipping in reverse direction
  • See https://msdn.microsoft.com/en-us/library/ms191233.aspx
  • Allows for rolling patches an upgrades
  • - Upgrade secondary
  • - Failover
  • - Upgrade the original primary (now secondary)
  • See https://msdn.microsoft.com/en-us/library/ms178117.aspx 
  • Consider building a document with detailed instructions.

Comparing to Mirroring

Related blog posts: