|
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
|