- 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
- Interval – Default is 15 minutes. Consider the impact before using a smaller interval
- Delayed log restore – option that allows you to have an older copy
- 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 shared files
- Careful – Weekend maintenance jobs can make you run out of disk space
- Monitoring – MSDB tables, agent history
Log shipping is a compromise but it is not the ideal solution, it is a practical solution given real-world budget constraints. Some of the problems with log shipping include:
- Log shipping failover is not automatic. The DBA must be present when the failover occurs.
- The users will experience some downtime. How long depends on how well you implemented log shipping, the nature of the production server failure, your network, the standby server, and the application or applications to be failed over.
- Some data can be lost, although not always. How much data is lost depends on how often you schedule log shipping and whether or not the transaction log on the failed production server is recoverable.
- The database or databases that are being failed over to the standby server cannot be used for anything else. But databases on the standby server not being used for failover can still be used normally.
- When it comes time for the actual failover, you must do one of two things to make your applications work: either rename the standby server the same name as the failed production server (and the IP address), or re-point your user's applications to the new standby server. In some cases, neither of these options is practical.