Chapter 19: SQL Server 2005 Log Shipping


Log shipping is a technique that became available several releases ago. In log shipping, the database transaction log from one SQL Server is backed up and restored onto a secondary SQL Server, where it is often deployed for high-availability, reporting, and disaster-recovery scenarios. SQL Server 2005 log shipping has been enhanced to continue to deliver business continuity and to be one of the high-availability solutions to maintain a warm standby server for failover. For many years, organizations have depended on log shipping for their business continuity, as it is a low-cost, efficient, and simple solution to deploy. It takes advantage of the transaction-log backup and restore functionalities of SQL Server. The two log-shipping SQL Servers can be located next to each other for high availability or across a distance for disaster recovery. The only distance requirement for the two SQL Servers is that they share connectivity to enable the standby SQL Server to copy the transaction log and restore it. In this chapter, you learn about log-shipping architecture and deployment scenarios. We discuss how to configure log shipping and the various scenarios for switching roles between the primary and standby servers. We also tell you how to troubleshoot your log-shipping setup and how to integrate log shipping with other high-availability solutions.

Log Shipping Deployment Scenarios

In this section, we discuss different scenarios where log shipping is deployed: as a warm standby server to maintain a backup database copy in the same physical location to protect from a primary server failure; as a disaster recovery solution where the two servers are distantly located in case the local area where the primary server resides suffers from a disaster; or as a reporting solution where the standby server is used to satisfy the reporting needs.

Log Shipping as a Warm Standby Server

A common log-shipping scenario is as a warm standby server where the log-shipping server is located in proximity to the primary server. If the primary server goes down for planned or unplanned downtime, the standby server will take over and maintain business continuity. Then, the DBA may choose to failback when the primary server becomes available. Oftentimes, log shipping is used instead of Windows failover clustering, as it is a less expensive solution; for example, clustering requires a shared disk system that an organization may not own. Additionally, clustering requires Windows failover-cluster compatible hardware that appears in the Hardware Compatibility List. Log shipping does not have such hardware requirements, so an organization may already own hardware that is not failover-cluster compatible that it can use for log shipping.

Moreover, in log shipping, the primary and secondary server's databases are physically separated in certain cases where one database may become corrupted and the other may not. Windows failover clustering uses is one shared disk system with one database, which could become corrupted. It is simple to configure a warm standby server with log shipping, because it uses the dependable transaction log backup, operating system copy file, and transaction log restore. In most warm standby scenarios, the OPERATOR should configure the log-shipping jobs to execute at a shorter interval to maintain the standby server closely in sync with the primary server, to reduce the amount of time to switch roles, and to reduce data loss. Additionally, to further limit data loss, if the active portion of the primary server's transaction log is available, the standby server would be restored to the point in time of the failed primary server. However, in some situations, the active portion of the transaction log may not be available, or some transaction log files that were in transit may not have made it to the secondary, causing some data loss. In a typical role-switch scenario, the OPERATOR would recover all in-transit transaction logs and the active portion of the transaction log before recovering the standby server. Users would also need to be redirected, because log shipping, unlike Windows failover clustering, has no automatic user redirect.

Log Shipping as a Disaster Recovery Solution

An organization may have a local high-availability solution, perhaps around Windows failover clustering, but then deploy log shipping to a standby server at a remote location to protect from a power grid failure or local disaster. The challenges with this scenario are that the network bandwidth must have the capacity to support log shipping large log files to the remote location. Moreover, there is a potential that in a disaster some of the files may be in transit and may not make it to the standby server. Even if the bandwidth supports log shipping comfortably, there is a possibility that during a disaster the bandwidth may be constrained by other activity that will slow down the file transfers. Therefore, there is a possibility of data loss. For mission-critical applications where you want to minimize any data loss, you may need to choose another solution.

Even in the best scenario, because of the transaction-log granularity instead of at each individual committed transaction, there is a greater possibility that the active partition of the log from the primary server will not be accessible. You may need to accept a greater amount of data loss, or the backup folder may no longer be accessible and any transaction logs there will be lost. If the transaction log files in the backup folder or the active transaction log are not accessible, as in a disaster where the primary server can be restarted because of a power grid failure, for example, you may be able to stitch the primary server's active transaction log and transaction log files together by using a third-party transaction log analyzer to identify transactions that did not make it across and manually apply them. The transaction log files backed up by the backup job should be archived to provide point-in-time recovery of the primary (if, for example, a user error modifies some data that needs to be recovered). Moreover, archiving the transaction logs along with a full database backup will offer another disaster recovery option, when needed. To control when the transaction log files are deleted so that the OS backup program can back up these files on its own schedule, set the "Delete files older than" to a time period great than that of the OS backup program schedule. This option is found in the Transaction Log Backup Settings. For example, if the OS backup is scheduled to run every night, set the "Delete files older than" to at least keep the files there until the OS backup completes.

Log Shipping as a Report Database Solution

In certain scenarios, it may be feasible to use the standby server's database for production reporting provided that the database recovery mode is in standby. However, there are several inherent disadvantages to using this server for reporting. The restore process needs exclusive access to the database while restoring; if users are running reports, the restore process will fail to restore and the job will wait for the next restore interval to try again. Log Shipping Alerts may trigger, sending an alert that the standby server has fallen behind. Moreover, at the time of role-switching, there may be transaction logs that have not been applied because reporting prevented it, which would increase the role-switching time as these transaction logs are applied. However, log shipping can be configured to disconnect users that are in the database to restore the transaction logs, but longer-running reports may be kept from completing in that case. To improve the chances that the report will run to completion, the restore job interval would have to be longer, which makes the standby server fall further behind. Additionally, the data for the reports will not be current. Moreover, the secondary server's database schema cannot be optimized for reporting, because it is read-only. For example, if particular indices are beneficial to the report database, the indices need to be created in the primary server's database, which may suffer from having the additional indices. Therefore, using log shipping for reporting has several challenges and does not make a good reporting solution for some environments. For occasional reporting, provided the organization can live with these challenges, you can certainly use log shipping for reporting. A better report solution may be transaction replication, which provides concurrency, granularity, and near real-time synchronization with the added flexibility to modify the database schema.



Professional SQL Server 2005 Administration
Professional SQL Server 2005 Administration (Wrox Professional Guides)
ISBN: 0470055200
EAN: 2147483647
Year: 2004
Pages: 193

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net