Wednesday, April 6, 2011

Log Shipping

Log Shipping provides database redundancy by sending transactional log backups periodically to a standby server or servers for the entire database. Transaction logs are automatically backed up, copied, and restored on the standby server(s). If the active server goes down, the standby server can be brought up by restoring any remaining shipped logs and then the database is recovered for use. More info on Log Shipping can be read from this MSDN library article.
  • It is good option when we want to have multiple secondary failover servers for a database.
  • Recovery Time Objective (RTO) - Failover is manual for this option, so this will take as long as it takes you to get the secondary server up and running.
  • Recovery Point Objective (RPO) - If there is no data loss (due to data deletion), there is minimal to no data loss during the failover. Because transaction backups are applied to the secondary server all transactions will be replicated as they were done on the primary server. If you delay applying the transaction logs you could do a point in time recovery right before an accidental deletion of data occurred.
  • Log shipping can be configured to multiple standby servers.
  • Can use compressed backup feature to limit network bandwidth
  • It includes all the database objects along with their schema changes.
  • By specifying Log Shipping in short interval, it can be near real-time database.
  • Can be setup to use secondary server for read only activity
  • No additional cost, except for the need to have another available server for the secondary copy.
  • Can delay when the logs get applied to secondary server to help minimize data loss in the event of accidental data deletion (point in time recovery)
  • If secondary server is used in a passive mode (only for failover), additional SQL Server licensing is not needed.
  • There is no automatic failover.
  • Other items need to be handled outside of Log Shipping such as logins, SQL Agent jobs, etc...
  • Requires additional storage for log backups and shipped copy
  • If standby copy is used for read only, SQL requires exclusive access to the database when a log backup is restored

No comments:

Post a Comment