Heavy Duty LiteSpeed Log Shipping Part 2

Configuration and Deployment

By Bill Wunder


In this article we will turn our attention to the configuration, initialization, and processing of a SQL LiteSpeed based log shipping.


In the previous article I left you pondering the sizing requirements for your backup file space and other planning considerations. Exactly how much disk space you’ll need for file system staging of backup files is obviously a tough call and will vary considerably from application to application, database to database, shop to shop. The good news is, what ever you needed for native log shipping you can probably get by with one forth, one eighth, or even less - if your careful - of that space when using SQL LiteSpeed and will undoubtedly still be able to store as many – or more  backup files. Perhaps the best strategy is to try to share the potential space needed with other disk space consumers that also have a small actual disk space requirement when compared with potential disk usage. The thinking in that suggestion is that it is less likely that two or three processes will all dip into their potential usage allocation at the same time. This is made easier because both the backups and restores done by this log shipping utility are intended to work from a network share.  


With your disk allocation planned the next step is to download the Heavy Duty LiteSpeed Log Shipping stored procedures and save them all to a location that you and your SQL Server Service account have permission to access from the network. Be sure to save each procedure script to a file named as indicated in the download script. If the names aren’t just as indicated the configuration process automation won’t work.


As with native SQL Server log shipping, it is necessary to create a share and give it the correct permissions. The user that needs access to the share will be the SQL Agent service account. That means, first of all, you need the permissions necessary to create the share and properly set permissions to it. It also means that the SQL Agent service account must be a domain user. Make sure that account has change access to the network share. If both you and the SQL Agent login account are local administrators on both SQL Servers and you have sufficient room to stage backup files on the standby server getting permissions working should be relatively easy. If you want to stage files on a file server on your network, you may want to work with your network admins to make sure the share and permissions meet their standards. Permissions can get complicated depending upon the security model in your environment.


It should go without saying – but I’ll say it anyway – that you need to make sure SQL LiteSpeed backup software is installed and properly working on both servers before you can use it to ship logs.


And of course, as with any database administration tool I create, the scripts expect to find a database named admin on each SQL Server involved with log shipping. It shouldn’t take more than a MB of allocated database storage to hold the three tables and 7 stored procedures used to ship logs.


The final thing that needs to be done before you can use the stored procedures I use to configure, initialize and process log shipping is to define a SQL login that is a member of the sysadmins role on both servers to be the owner of the scheduled job that will ship your log backups. You can use “sa” or you can define a new SQL login. Either way the user must be a member of sysadmins in order to perform the SQL LiteSpeed operations used in log shipping processing. I use a SQL user because a trusted user would require delegation through Active Directory in order to get this job done and I don’t have Active Directory. There are several operations that will require sysadmin level SQL Server permissions. I suppose you could always make [NT AUTHORITY\ANONYMOUS LOGON] a member of sysadmins on the destination server and use a trusted connection, but I certainly wouldn’t recommend it! One final recommendation is to give the SQL login the same name and password on both servers. This will allow you to use the admin.dbo.AddLinkedServer stored procedure as provided in the script Library when configuring the linked servers. If you use different passwords be sure to provide a password mapping in your linked server configuration.


OK, now that you have a share, SQL LiteSpeed is installed, the admin database exists on both servers and you’ve created a SQL login to be the owner of your log shipping job your ready to compile and execute the DBAConfigSLSLogShipping stored procedure. You’ll need to log in to the server you’ll be shipping logs FROM – the Source server - through Query Analyzer using a trusted connection that is a member of sysadmins on both the source and destination servers. We’re going to use a trusted connection for this procedure only because in this procedure we will make sure the SQL login that will be executing all other procedures is member of sysadmins on both servers. Delegation won’t come in to play in the DBAConfigSLSLogShipping procedure because we will make used of osql via xp_cmdshell to get our cross server work done. Open the script dbo.DBAConfigSLSLogShipping.PRC, compile it, then execute it using the information you’ve already determined for the parameters. Here’s a brief description of each parameter:


@StandByServer  – The destination SQL Server


@ConfigFilesPath – UNC path to the stored procedures that you have down loaded and save using the exact naming convention indicated in the download script


@logShippingUser – The SQL login we just discussed that will own the log shipping job


@logShippingShare – UNC share (and path if needed) that you created to stage all backup files.


@DropExistingTables - If you ever need to completely re-init the log shipping installation      you can specify the fifth parameter as true to wipe out any existing log shipping control tables. Otherwise leave it at the default value of False. Normally, you won’t need to specify this parameter. It’s purpose is primarily for the case where the structure of a table needs to change.


Upon execution, the procedure will verify that your configuration efforts are acceptable, create the three tables used by the log shipping process and compile the other six log shipping procedures on the correct server.  Be sure to review the output generated by procedure as any problems in your configuration should be pinpointed for you. Make corrections as necessary and re-execute until the procedure runs without complaint.


On the source server in the admin database you’ll end up with three tables:


admin.dbo.SLSLogShippingConfig – contains the parameters provided by the DBAConfigSLSLogShipping procedure. You can configure log shipping to multiple serves. Each destination server would get a new row in this table


admin.dbo.SLSLogShipping – contains the parameters provided for the procedure admin.dbo.InitSLSLogShipping. You can init log shipping for multiple databases. Each database would get a new row in this table


admin.dbo.SLSLogShippingLog – contains information about the log backups in the “active set”. Each backup file will get a new row in this table.


And the stored procedures:

InitSLSLogShipping  - establishes the basic settings for log shipping of a database. These are discussed below. This procedure also sets the database to the full recovery model, does the full backup and restore necessary to attain initial sync between the log shipping pair, creates a scheduled job that will move log backups between servers, and creates a linked server to the destination server.


GetSLSLogPrimary – executed by the scheduled job at the interval you have specified during the initialization to do the log backups and call the restore procedure via a linked server call. This procedure is also used to fail over to the destination server. The failover will also include any jobs with identical names that you have established as enabled on the source server and disabled on the destination server prior to failover.    


AddLinkedServer – called by InitSLSLogShipping to create the linked server used by GetSLSLogPrimary to call the restore procedure on the destination.


NotifySLSLogShippingFailure – executed as a second step of the log shipping job to notify of any errors that occurred. Please see my previous articles “from xp_sendmail to safe_sendmail” and  “From SQLMail to SMTP in the SQLAgent” for a full explanation of the SMTP based email protocol used by this procedure. Note that if you’re not using the mail subsystem described in that article you’ll need to modify this procedure by removing any references to “sysmon.dbo.safe_sendmail” else the procedure may not compile for you if the sysmon database doesn’t exist on your server.


On the destination server you’ll have the procedures:


ApplySLSLogSecondary – Restores backups to the destination to StandBy Mode to allow read only access and when directed to do so recovers the destination database. See SQL Server Books On Line RESTORE command topic for a full description of Standby Mode. The procedure will also attempt to remove any users from the destination database through a call to ExpungeUsers before each restore and it will recover the database for read/write activity when told to do so.


ExpungeUsers – removes all connections from the destination database before each restore


Once you have successfully executed admin.dbo.DBAConfigSLSLogShipping you have a fully configured log shipping installation ready to ship logs from any database on the source server to a database with the same name on the destination server.


In the next article we’ll get into the day to day processing of Heavy Duty SQL LiteSpeed Log Shipping.