In "SQL Server High Availability for Developers, Part 1," we touched on core high-availability concepts such as the need to avoid mistaking high-availability solutions as a complete replacement for disaster recovery requirements. I also provided an overview of how to effectively measure and account for potential downtime and data loss through the use of recovery point objectives (RPO) and recovery time objectives (RTO). I then took a look at clustering, along with some of the pros and cons that clustering offers to developers whose applications need to be made highly available. In this article, we'll take a look at log shipping as it provides several great redundancy benefits to DBAs with very few negative implications for developers.
Log Shipping
With log shipping, SQL Server takes advantage of the fact that all changes made to SQL Server databases are written to the transaction log before being executed as a means of ensuring transactional consistency in case of a disaster. Accordingly, because a SQL Server transaction log provides a change-by-change record of all modifications made to a given database, it stands to reason that if you were somehow able to replay those operations against another remote copy of your database, you'd be able to keep it roughly in-sync with your primary production database. That's exactly how SQL Server log shipping works: by regularly shipping copies of transaction log backups to secondary servers or locations in which each and every change made against the primary database can be replayed as a means of keeping warm standby servers in operation.
Log shipping provides a variety of benefits, including the following:
- Simplicity. From an IT perspective, log shipping is quite easy to set up. Wizards that ship with SQL Server make it easy for DBAs to configure regular log file backups and schedule additional jobs that copy backups to remote servers.
- Buffering. When implementing log shipping, DBAs can also specify a length of time to wait before applying transactional records to secondary servers. In this way, DBAs can easily backup transaction logs at the primary database at about every 15 minutes and push those files regularly to the secondary servers to minimize the potential for data loss. But to prevent against cases in which applications or end users might mangle data through mistakes, DBAs can have log shipping agents wait for about two hours before applying transactions at a given secondary server. This in turn provides DBAs with the ability to buffer against potential disasters.
- Licensing. Thanks to Microsoft's very generous licensing terms, log shipping is highly accessible to any organization that can spare redundant hardware and a corresponding license of Windows. As long as log shipping secondary servers are used solely for redundancy and failover purposes, organizations don't need dedicated SQL Server licenses. Instead, if and when you failover to a secondary server, you're allowed 30 days of operation before you need to produce a license.
- Range. Log shipping is primarily a question of pushing log file backups from one server to another. As such, it's much easier for DBAs to deploy log shipping across sites or data centers rather than deploying solutions such as database mirroring (which is primarily designed for LANs) or clustering. This, in turn, makes log shipping a great remote availability or redundancy solution that can be used in smoke and rubble contingency scenarios.
- Redundant redundancy. Although log shipping isn't as effective as clustering or database mirroring from an RPO or RTO perspective, many organizations use log shipping as an additional redundancy option to ensure relatively up-to-date copies of its data off site. This lets organizations rely on clustering or database mirroring locally to ensure easier failovers and availability on a daily basis.
- Reporting. With log shipped databases, it's possible to create read-only copies of databases that can be kept relatively up-to-date for reporting purposes. In this sense, log shipping provides excellent improved availability by means of making data more readily available for reporting purposes. Be aware that if you use log shipping for this kind of reporting, you'll need a valid SQL Server license on your reporting server. Likewise, be mindful of the fact that if you try to add reporting options into a database that was set up for disaster recovery purposes (or vice versa), you'll end up complicating and compromising the benefits of both your solutions.
Log shipping does have a few negative implications, though, including the following:
- Failover. Failover with log shipped databases can be tough to manage for DBAs. In addition to needing to shunt operations from one server to another, DBAs need to ensure that connectivity and permissions are properly configured at secondary servers as part of the failover process. Handling these kinds of environmental issues is commonly the responsibility of DBAs, but changing connection strings for applications might or might not be something that DBAs are comfortable doing. Without some sort of regularly rehearsed testing for failover (or to ensure that sample apps can be easily switched over to use a failed-over location), a failover process can result in significant downtime and confusion.
- Higher potential for data loss and downtime. Because log shipping is a disconnected solution that uses periodic shipments of operations as a means of synchronization, it's commonly going to be susceptible to larger windows of potential data loss than other high-availability solutions. With that said, log shipping can be realistically configured to lose five or 10 minutes worth of transactions in most environments. However, ensuring that this objective can be met (instead of merely stating that it can be) will require regular testing and detailed documentation.
- Failback. Most log shipping failovers occur as emergencies. This means that a common requirement for every failover is the need to fail back to the primary database. In most cases, this involves a degree of non-trivial coordination to correctly orchestrate and might result in data loss if performed incorrectly.
Up Next
In the next installment in this series, I'll take a look at database mirroring and its new and very powerful replacement in SQL Server 2012—AlwaysOn Availability Groups. In a following article, I'll look at replication, which provides a wide variety of options and benefits, including the ability to have multiple master databases or locations in which changes can be made simultaneously (something that none of the options we've looked at so far provide).
