Heavy Duty LiteSpeed Log Shipping Part 1

Considerations and Planning

By Bill Wunder

 

The simplest way to get a copy of all the data and DDL in a database to a second location is to backup the database and restore it. As changes are made to the original - or source - database the second or - destination - database grows stale and begins to drift from the source database in terms of data and structure. If we continually back up the transaction logs from the source database and restore those logs to the destination database in sequential order we can keep the two databases synchronized within a time difference of as little as a minute or two. The destination database can then take over for the source database should the source become unavailable for what ever reason or the destination can be used as a read only query server to move some load off of the primary server. This has come to be known as log shipping.

 

In addition to those common uses for log shipping, I have also had good luck using log shipping to migrate a databases to new hardware that may even be at a more recent service pack or patch level with only a minute of down time required and am having good success with automating failover to the degree that in our shop we fail over a heavily used SQL Server regularly to support reboots and system maintenance when the application cannot afford any down time.

 

Log shipping is the oldest, easiest, possibly most reliable and easily the lowest cost method to achieve high availability. Many shops have been running a warm spare since long before the days of replication and clustering. Several years ago I published my SQL Server 6.5 log shipping scripts. Then Microsoft made log shipping available for SQL Server 7 as an add-in from the Back Office Resource Kit. With SQL Server 2000 Microsoft’s log shipping became an integrated part of the Enterprise Edition. It still takes a little smoke and mirrors, but the Maintenance Wizard in Enterprise Manager can even be used to install native SQL Server log shipping provided the necessary shares are first created in the file system of the destination server. As you can see, log shipping has come a long way. Now I’d like to share my experiences with using SQL LiteSpeed high compression high performance backup software in a robust log shipping process. We’ll look at the configuration requirements, operational processing, and even consider some failover methods. Why would we want to use SQL LiteSpeed for log shipping you may wonder, and why doesn’t DBassociatesIT include it with the product if it’s a worthwhile endeavor?

 

Well, SQL LiteSpeed does have a log shipping script posted on their web site. It is simplistic and proved to be of no use in my efforts to ship logs using their product. You have to log in to find it so I won’t post a link. I will take a three sentence side track to wonder why DBassociatesIT makes you log in to access this script and their meager Knowledge Base. Seems like they’d want to make the canned on line help as readily available as possible, but what do I know? I’m just a hard to please DBA trying to do my job quickly and efficiently.

 

The advantages that a SQL LiteSpeed based log shipping process offers over native SQL Server log shipping include a much smaller footprint, faster processing, does not require SQL Server 2000 Enterprise Edition, backup files can be stored at any network location, and there’s good reason to expect it to work with Yukon. Most of the size and speed advantages are due to the fact that SQL LiteSpeed produces much smaller backup files than the same backup operation using the native SQL Server backup facility. Smaller files means less network traffic to get the backup file to the destination server and les disk space needed to store the backup. And, as we’ll see, I’ve been able to make a few additional improvements in the space used and speed along the way. The cost of SQL LiteSpeed will chew up some of the cost advantage of not needing SQL Server Enterprise Edition, but there will still be plenty left over for those that might be considering Enterprise Edition just to gain access to Microsoft’s Log Shipping. Even with Enterprise Edition, the speed and space advantages make the SQL LiteSpeed based log shipping option a better choice in my view. For that matter, unless you’re locked into direct backup to tape, SQL LiteSpeed is an excellent choice for all your SQL Server backup needs. It’s the only thing we use and is a welcome change to our previous experiences with a Veritas Agent blowing a SQL Servers mind from time to time.       

 

Microsoft’s log shipping, in particular if you configure with the wizard, wants to backup the log to a local storage location on the source Server, copy the backup file to the destination server, then restore the copy to the destination server. The SQL LiteSpeed based log shipping we’ll be looking at will require only one location, anywhere on the network, where the backup will be save to and restored from. In practice I find that I use a location on the destination server to stage the backup files for my two production uses of log shipping, it could as easily be a third server and each file could even be moved to tape during the processing should that prove valuable.

 

One of the failure scenarios I’ve faced more than once with native SQL Server log shipping is high disk space utilization on the log device and on the backup file share along with long latency periods between log backups and restores whenever index maintenance or a delete/archive operations would run on the source server. With SQL LiteSpeed based log shipping processing times are greatly improved and the risk of running out of disk space because of a log jam (pun intended) are greatly reduced.

 

It is still necessary to plan your database, and log and file storage systems need to do their part to help avoid that log jam. For example a good indexing strategy will consider the amount of transaction log activity that is needed to adequately maintain the chosen indices. And a delete/archive or index maintenance operation can be trained to minimize or control the amount of log growth that can happen in any given log shipping cycle. Note that actual log size between log backups is the same with native SQL Server log backups or with the SQL LiteSpeed based log backup so you’ll need the same amount of log space regardless of the backup software used. When you initialize a SQL LiteSpeed based log shipping setup you’ll need to indicate how far into the past you want to keep backup files around on disk. Before we get to the point of providing that setting you’ll want to give thought to maximum space that you expect the backup files to use in that interval and plan for a safe amount of disk space. The more space you can allocate for potential log file storage, the longer you will be able to keep running without a full backup and restore should an unexpected problem arise. It’s a given that the backup file staging location will need to be able to hold a full backup and several log backups when log shipping is first started between tow locations. Much of that space will free up after log shipping has been running a while and older backup files begin aging out of the “active set”.

 

This article was intended to peak your interest in SQL LiteSpeed as a robust alternative to native SQL Server log shipping. In the next article we’ll begin looking at the configuration and deployment of Heavy Duty LiteSpeed Log Shipping. Then we’ll dig into the code necessary to process logs. Finally, we’ll look at some of the cool tools you can use to help log shipping and the other aspects of a system play nice together. Hope you stay with me because I think you’ll find a tool your going to want to use in these next few articles.

 

Bill