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