Log Shipping is a true and tried method of making databases available in case of a Disaster. From books online , SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances.
Advantages of Log Shipping
- The delayed application of transaction log at the secondary , gives time to recover from mistakes made on primary.
- Secondary database in Standby mode can be used for reporting.
- Works with SQL Server Standard Edition. It means it is a cheaper solution.
- Since this is setup per database, failover to secondary of one database does not affect other databases on the primary server.
Disadvantages of Log Shipping
- Requires lot of sqlagent job management. For each database that takes part in log shipping, 3 jobs are created. A backup job on the primary, a restore job and a copy job on the secondary.
- Failover is manaual.
- After failover, log ship need to be recreated.
- During transaction log restores, any users connected to secondary server for running report will be kicked out. They have to log back in again.
Here we discussed advantages and disadvantages of logshippin as disaster recovery solution for SQL Server. I will write about how to create log shipping in my next post.