SQL Server 2008 Log Shipping
Here are some notes on “SQL Server 2008 Log Shipping.
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 http://msdn.microsoft.com/en-us/library/ms187103.aspx
- 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
- 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.
- 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
No comments:
Post a Comment