Professional Microsoft® SQL Server® 2008 Administration

19.13. Summary

Log shipping is a simple, inexpensive, dependable SQL Server high-availability solution with a long track record. As a disaster recovery solution, it has been deployed to maintain a secondary server over a distance for business continuity to protect from a local disaster, power-grid failure, and network outages. Log shipping can ship the log anywhere in the world. Its only limitation is the network bandwidth capacity to transfer the transaction log file in a timely manner. It can be combined with a reporting solution when an organization requires point-in-time reporting, rather than real time. Moreover, it has been deployed as an alternative to Windows failover clustering to provide a local, high-availability solution, where it is less expensive to implement because it does not require a shared disk system.

The main challenge in deploying log shipping is that it does not provide any automatic client redirect; therefore, an operator needs to have an approach for it. In addition, the role switch requires some user intervention to ensure that all of the transaction logs have been restored to the secondary server. Switching the roles back also requires some manual intervention. Another challenge is that log shipping is at the user database level and does not copy new logins, database users, SQL jobs, Integration Services, or DTS packages from the primary server. However, using Integration Services' Tasks and SQL Agent jobs, it can be accomplished.

Many organizations ...