|
Heavy Duty LiteSpeed Log Shipping
Part 3
Processing logs
By Bill
Wunder
If you’ve been following the last
couple of articles, you’ll have a good understanding of some of
the advantages SQL LiteSpeed based log shipping can offer when
compared to native SQL Server log shipping and you may even have
already gone through the steps of deploying the log shipping
utility in a test environment to begin moving changes from a
source database to a destination database using transaction log
backups generated by SQL LiteSpeed. Now we’ll take a closer look
at the processing of Heavy Duty LiteSpeed Log Shipping from
start to finish. First we’ll look at initialization of an active
backup set for a database, then we’ll consider the normal
processing of log backups, and finally we’ll look at what needs
to happen to failover to the destination database.
If you haven’t already, you can
download the Heavy Duty LiteSpeed
Log Shipping stored procedures
and save them all to a location that you have permission to
access from the network using the file names indicated in the
download script for each procedure’s script. Then follow the
steps outlined in
Heavy Duty LiteSpeed Log Shipping
Part 2 for deploying the
log shipping job on a test server. You can easily try things out
by log shipping the pubs database from one instance on a server
to another instance for example. I would encourage you to set up
a test set and try things out as we go over each stored
procedure used in the processing. The only note of caution would
be to be careful of any SQLAgent jobs you might have running on
the source server. At failover, the log shipping job will also
try to fail over the scheduled jobs. Read on and you’ll
understand how it works. I’ll be sure to warn you again about
the jobs at failover.
I’ll assume you’ve already deployed
the log shipping utility and that you’ve created or purposed a
SQL login to act as the log shipping owner. Log into the source
SQL Server using Query Analyzer as that SQL login and prepare an
execution statement that will call the InitSLSLogShipping stored
procedure that should now exist in the admin database.
InitSLSLogShipping takes a few parameters. As with all the
procedures, you can see and example execution string in the
header comments of the stored procedure. The procedure must be
executed once for each database that will be the source for a
log shipping operation on a server. (And yes that means you can
ship multiple database on a server to multiple destination
servers or all to the same destination server.
Some explanation for each parameter
of InitSLSLogShipping is useful:
@DatabaseName - Name of the database
for the logs you will ship
@StandbyServer - Standby
server to receive/apply the logs
@MinuteInterval -How
often to ship the logs in minutes
@BackupLocationPath -
UNC backup path to location of the backup files. Specify null to
use the path you provided in DBAConfigSLSLogShipping, or specify
a new path here. Useful if you are shipping multiple databases.
Each database can stage backups to a different network location
or the same location as you see fit. Note that if you specify a
new path here you’ll also need to make sure that the SQLAgent
service account has change permission to that share.
@NotifyOnFail –Email
address(s) of who to notify if the job is in a failed state. The
job is not considered failed until the most recent fully
successful backup and restore has exceeded the
@MinutesAcceptableLatency period. This recipient would be the
24/7 operations staff or a general on call pager to make sure
that action is taken to prevent out of space conditions and
inability to fail over should the need arise.
@HoursToKeep -How long
to leave backup files on disk after restore in hours. The
“active set” is determined by this setting. Fully successful
back-ups and restores that are older than the @HoursToKeep
interval from the current processing time are removed from the
backup file share and from the admin.dbo.SLSLogShippingLog
table.
@MinutesAcceptableLatency - Don't fail until process is behind
by more than the number of minutes specified here. This provides
a window of comfort for doing index maintenance and daily full
backups that are not a part of log shipping.
@NotifyOnLatent –Email
address(s) of who to notify if the job is latent but not yet in
a failed state. This recipient would likely be you or a small
group of staff that would know not to respond to the mail as a
catastrophic event but would want to be aware that log shipping
is falling behind even if due to a planned event.
@BackupThreads - Number
of Threads to use for SQL LiteSpeed Backup, Use 1 if unsure. See
the SQL LiteSpeed xp_backup_database documentation for a full
discussion of this flag. Default is 1
@BackupPriority - Base
Priority of SQL LiteSpeed Backup, Use 0 if unsure. See the SQL
LiteSpeed xp_backup_database documentation for a full discussion
of this flag. Default is 0
@debug bit = 0 – Verbose
mode to help track down problems. Default is 0
When you execute this procedure, the
first thing it does is create a linked server with a 4 hour
query timeout on the source server pointing to the destination
server. If you are working with an extremely large database you
may need to increase that timeout, depending on how long it
takes to complete a full restore of the database. For the pubs
DB 4 hours represents gross overkill. Next the procedure will
remove all existing backup files (always named with the
extension .sls) from the backup file staging folder at the share
you specified as the @BackupLocationPath. Note that if you
specify null now for the @BackupLocationPath, the
@LogShippingShare you specified when you executed
DBAConfigSLSLogShipping will be used. After this cleanup is
successfully done the procedure will set the database you
specified to the full recovery model, initialize the
SLSLogShipping table with the parameters you have specified and
clear the SLSLogShippingLog table. At this point, unless you get
a message indicating a problem, the procedure will execute a
full backup of the source database followed buy a restore of
that backup to the destination SQL Server leaving the
destination in the NORECOVERY state. Next it will create the log
shipping job using a standardized name for the job:
'SQL LiteSpeed Log Shipping - ' +
@Database Name
The schedule of the job will be
based on the stored procedure’s @MinuteInterval parameter. The
last thing the procedure does is to run the job for the first
time. Once this execution of the job completes the database on
the destination server will remain in Read Only (more precisely
STANDBY) mode and available for queries between log shipping
intervals until it is recovered.
The job will continue to run at the
interval you have specified with the call to SLSLogShippingLog.
I think you’ll find that either server can be shut down at any
time, and the log shipping will be able to recover. Still, I
recommend that if you have to shut down either server while the
log shipping job is in use that you let any current log shipping
job execution complete and disable the job before stopping the
SQLAgent on the source server.
The job calls the GetSLSLogPrimary
at each execution. It would get to confusing to try to state
each thing that happens in this procedure. Try to test all the
failure scenarios you can come up with to gain confidence with
the utility and to become familiar with all the things that are
going on. In a nutshell, this job backs-up the transaction log
on the source server before it does anything else. This will
assure that, even if there is a problem in the process, the
transaction log on the source server will be the last place that
can run out of storage space. After the Source log is backed up
the process will begin restoring staring with the oldest
un-restored backup file in the active set. Once a log is
restore, it’s corresponding row in SLSLogShippingLog is marked
with a ShipCompleteDt and it become eligible to be aged out of
the active set.
A valid backup file cannot leave the
active set until it has been restored to the destination server.
Once it’s restore and the interval between it’s ShipCompleteDt
and the current system time exceeds the @HoursToKeep value you
specified when executing InitSLSLogShipping the backup file will
be deleted from the file system and the corresponding
SLSLogShippingLog row will be deleted from the table.
The two email recipient parameters
you specify when executing InitSLSLogShipping provide two levels
of notification. The first level recipient, @NotifyOnLatent is
notified if the any backup and/or restore operation encounters a
problem. The second level recipient is notified only if backup
and/or restore operations continue to fail beyond the interval
between @MinutesAcceptableLatency and the current time on the
source server. The reason for two levels is because there are a
number of things that can interrupt log shipping but won’t break
it. For instance a daily fill backup might run longer than the
interval between log shipping operations. No need for a company
wide fire drill in such a case. You can simply send yourself an
email so you can be aware of the backlog situation. When it’s
been longer than say twice the time it usually takes to do a
full backup then you may have a bigger problem and that’s the
time to send a notification to the 24/7 on site staff so they
can roust someone out of bed before that source server log runs
out of space.
Ultimately, I think you’ll find that
the log shipping job plays nice and is quite resilient to what
ever strange things that can happen on either the source or
destination server. You may find with a little run time
experience that you need to adjust the
@MinutesAcceptableLatency. This is accomplished by updating the
value in SLSLogShipping at any time.. In fact everything except
@MinuteInterval can be changed merely by updating the
SLSLogShipping Table. The @MinuteInterval interval can only be
changed by adjusting the schedule of the SQLAgent “SQL LiteSpeed
Log Shipping - <database>’ Job. If you do change the schedule
you may want to also update the table just so it has the correct
information.
If you need to fail over to the
destination server and you want to get one last log shipped from
the source to the destination server, log into the source SQL
Server using Query Analyzer as the log shipping SQL login and
execute the GetSLSLogPrimary stored procedure specifying the
parameters:
Exec [admin].[dbo].[GetSLSLogPrimary]
@DatabaseName = ‘<Name
of the database>’
@RecoveryFlag bit = 1
Remember that in this case, for each
SQLAgent job that exists on both the source and destination
server with exactly the same name and where the job is enabled
on the source server and disabled on the target server,
GetSLSLogPrimary will disable all such jobs on the source server
and enable them on the destination server. Nothing will happen
to any job that does not exist on both servers with exactly the
same name or with the source job not enabled or the destination
job not disabled.
If you need to fail over to the
destination server and the source server is unavailable, log
into the destination SQL Server using Query Analyzer as the log
shipping SQL login and execute the ApplySLSLogSecondary stored
procedure specifying the parameters
Exec
[admin].[dbo].[ApplySLSLogSecondary]
@DatabaseName = ‘<Name
of the database>’
@RecoveryFlag bit = 1
In the latter case you will probably
have lost some data, but in most cases that’s better than
nothing at all. You’ll also have to manually enable any jobs on
the destination server that need to be running
As I mentioned, try this out on a
test environment with pubs or some other small database. I think
you’ll find it takes only a short time to get comfortable with
Heavy Duty LiteSpeed Log Shipping and then you’ll be able to use
it in many situations: For a warm spare, a query server,
hardware migrations, and more.
Check out my next article for a look
at a really nifty tactic you can use to manage how much log
space a maintenance operation like an index reorganization or a
delete/archive task can use between log shipping operations.
It’s a cool technique that you may even have some other uses
for. See you then
Bill
|