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 http://sqlblog.com/blogs/greg_low/ and http://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.
- 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 http://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 http://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 http://msdn.microsoft.com/en-us/library/ms191233.aspx
- Allows for rolling patches an upgrades
- - Upgrade secondary
- - Failover
- - Upgrade the original primary (now secondary)
- See http://msdn.microsoft.com/en-us/library/ms178117.aspx
- Consider building a document with detailed instructions.
Comparing to Mirroring
- Mirroring provides synchronous mode, with no data loss
- Mirroring provides automated failover
- Log shipping allows for multiple copies
- Log shipping allows for delay in applying logs
- See http://msdn.microsoft.com/en-us/library/ms187016.aspx
Related blog posts: