|
Return to Newsletter Contents...
SQL Server Log Shipping: A Great Database Alternative
by: Troy Gottfried, Sr. Consultant, CRCP, BECP
Many organizations have a
great need for reporting on data stored in their transactional systems, but
cannot risk harming their production database environment by building reports
directly against it. While an
Operational Data Store is doubtless the better alternative, many opt to create a
backup/restore schedule of their transactional system for their reporting needs.
In this example, the production database is backed up on a predetermined
schedule, and that database is then restored to a separate server where reports
will run against it.
In SQL Server 2005 (or any
other platform, for that matter) this solution has several drawbacks.
First, if the backup/restore job does not first gain exclusive access to
the destination, the restore may very well fail.
For example, if the restore is scheduled for 11:30pm, but someone is
already running reports against yesterday’s version of that database, the new
backup cannot restore over top of it.
It would first have to gain exclusive access by killing all existing
connections.
The second major drawback
is one of ‘stale data.’ In those
scenarios where someone needs a report run against today’s data, someone will
either need to manually backup and restore the database again, or may elect to
point the report at the production system for a quick ‘one-off’ against live
data.
SQL Server 2005 offers a
much more versatile alternative: log
shipping. Log shipping didn’t first
appear in SQL Server 2005, but it is now offered in more editions than it was
with SQL Server 2000. At a high
level, log shipping requires a source and a destination, just like a
backup/restore scenario. However, in
the case of log shipping, a time interval is determined by which the source
system will copy and ‘ship’ its transaction logs to the destination database.
The job is then configured for a lag:
That is, if the destination system should wait a number of minutes before
applying the transaction log. Once
the file appears in the specified directory, and the lag-time is over, the
destination database will apply the transaction logs from the source database.
The destination database is required to be in a read-only state for the
logs to be applied.
The log-shipping
methodology offers several advantages.
First, the data is much more current as transaction logs can be applied
at any specified interval, rather than once or twice per day.
Secondly, the issue of exclusive access can be accommodated in one of two
ways: (1) Configure the job to kill
existing connections before applying logs, or (2) Allow transaction logs to
collect until exclusive access can be gained, in which case it will apply all
transaction logs from the last one it missed to the latest.
This keeps the database functional for reporting throughout the day,
while still allowing for transaction logs to be shipped and applied.
These transaction logs can be stored for an indefinite period of time, or
they can be systematically deleted once they reach a certain age.
The final major advantage
of log shipping is that of having a ‘warm-standby’ version of the production
database. In the event of a failure
on the production database, a couple of SQL statements can convert the read-only
secondary database into the primary database, and
vice versa.
In other words, you can quickly reverse the log shipping and point your
application to the standby database to keep working while the production system
is fixed.
Go to Top |
Return to Newsletter Contents
|
