Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Custom Log Shipping

By Chris Kempster,

The following article discusses the custom log shipping routines used for the "MYDB" production database. The destination server is used for corporate reporting functions whilst in standby mode.

Architecture

 

The architecture is simple enough.  We take full and transaction log backups via a custom written stored procedure, the routine will dump the files to a disk array on the source server and optionally gzip (compress) them.  The stored procedure will then copy the file to the remote server over a network share with appropriate service user privileges for the instance.  Any failures are reported to the DBA via email.  Also note that the copy to the remote server can be easily changed to an FTP and servers must be time-synchronised otherwise you will get the error  "There is a time difference between the client and the server".

At the destination server, we manually run the Initialise stored procedure.  This routine will search the dump directory for specific pre and post fixed files specified by the procedures incoming parameters.  Using xp_cmdshell and the dir command, we build up a list of files in the directory, locate the last FULL (unzip) then restore.  We then search for a differential backup, apply this, and carry on the subsequent transaction log files.  All restore commands used for the database are logged into a user defined master database table.

Finally, we call a simple monitoring stored procedure that looks for errors in the log table every N minutes (as defined by the DBA); emailing errors via CDO-SYS and your local SMTP server.

Advantages

  • No requirement for linked servers
  • Simply scripts with no complex lookups over the MSDB database, very easy the change and enhance
  • Easy to setup and configure
  • Will not, by default, force the overwriting of existing database files used by other databases
  • Will search for full, differentials and logs and apply in correct order, so long as files copy OK

Disadvantages/Issues

  • Requires a network share to copy files (can be a security issue)
  • Cant pre-detect missed or missing files (as you could if you utilised the MSDB)
  • Cant pre-detect invalid file sizes
  • Does not do a quick header and file check and compare DBA's passed in parameters with
  • Relies on the DBA to supply move commands for the restore as a parameter (see later), does not dynamically pick up database files/filegroups from the backup files themselves.
  • User sessions must be killed on the log shipped database before attempting the restore command
    • The only way I can see around this is via a physical log reader/parser program and the DBA runs SQL scripts rather than applying the log itself.

Configuration

All scripts are custom written and are typically stored in the master database of the instance.

Installation

Source Server (server-1)

The source server utilises the following database objects:

  • DBBackup_sp - master database - dumps full, log or differential backups to specified directory on source server, optionally zips files, emails on error, copies (duplexes) backups to destination server via UNC path, delete files older the N days.
  • SendMail_sp - master database - utilises simplecdo.dll (custom written VB COM that uses CDOSYS to send emails, can use JMail instead) to email the administrator on backup errors.
  • dtdelete.exe - c:\scripts\ - command line executable that will remove files from a directory (and recursively if so desired) that are N days old from the backup destination directory
  • gzip.exe - c:\scripts\ - command line file compression utility for backup files

Destination Server (server-2)

The destination server utilises the following database objects:

  • usp_LogShipping_Init - master database - run manually (1st time only or on serious error), searches the incoming backup directory, applies most recent FULL backup, then last differential (if any) and applies subsequent transaction log files, leaves database in norecovery or standby mode. Logs all recoveries to its audit table.
  • usp_LogShipping_Continue - master database - as above, but searches for differentials and transaction logs only.  If a full is found then Init is recalled to reapply the full backup again. Logs all recoveries to its audit table.
  • usp_LogShipping_Finish - master database - manually called by the DBA, will finalise the recovery of a database and make it available for read/write.  IMPORTANT - DBA must turn off log shipping jobs on the destination server before attempting this command.
  • usp_LogShipping_Monitor - master database - reads the audit table below and emails the DBA errors found
  • LogShipping_Audit - master database - table that logs all recovery attempts via the log shipping routines
  • SendMail_sp - master database - utilises simplecdo.dll (custom written VB COM that uses CDOSYS to send emails, can use JMail instead) to email the administrator on backup errors.
  • gzip.exe - c:\scripts\ - command line file de-compression utility for backup files
  • usp_KillUsers - master database - kills all users connected to a database for the instance

IMPORTANT - Consider using the command alter database xxx set resticted_user rollback immediate  rather than utilising the usp_KillUsers stored procedure.  The routines themselves use alter database, switch as required.

Log Shipping Example 1 - Setup and Running

Server 1 (source)

Here we assume the server has been configured, instances and databases all running nicely and the DBA is now ready to sort out the backup recovery path for the database MYDB.  This database has four file groups (1 data file per group) and a single log file.  The database itself is approx 3.6Gb in size, full recovery mode and requires point in time recovery in 10min cycles.

The DBA creates the following directory on the server to hold backups:   d:\dbbackup\mydb\
There is ample space for 4 days worth of backups. Litespeed or other 3rd party backup products are not being used.
The DBA wants the full backup files zipped, and files older than 4 days automatically removed.

On the remote server, the DBA creates the duplex directory:  e:\dbbackup\mydb\
A share is created on the dbbackup directory called standbydest for one of a better word and NT security configured accordingly.

The DBA configures the following stored procedure to run 2 x daily for FULL backups via a DTS job:

exec DBBackup_sp 'full', 'mydb', 'c:\scripts', 4, 'c:\scripts', 1, 'd:\dbbackup\mydb\','\\server2\standbydest\mydb\', 'support@chriskempster.com', 'Y'

We are running full backups at 6am and 8pm to cover cover ourselves nicely in terms of recovery (not shown here, in DTS).  We chose not to run differentials and are happy with recovery times in general.  The script above and its parameters tells the backup where our gzip and dtdelete.exe files are (c:\scripts), the backup destination, the duplex destination on server-2.  We are retaining files no more than 4 days old and the value one (1) tells the routine to zip the file created.

Next we schedule the transaction log file backups:

exec DBBackup_sp 'log', 'mydb', 'c:\scripts', 4, 'c:\scripts', 0, 'd:\dbbackup\mydb\','\\server2\standbydest\mydb\', 'support@chriskempster.com', 'N'

The script above and its parameters tells the backup where our gzip and dtdelete.exe files are (c:\scripts), the backup destination, the duplex destination on server-2.  We are retaining files no more than 4 days old and the value one (1) tells the routine to zip the file created.  The value zero (0) represents the email, when zero the DBA is only notified on backup failure, not success.

The DBA should actively monitor the backups for a good two or three days, ensuring full and log backups are copied successfully, can be manually restored on server-2, and the deletion of files older than N days is working fine.

Server 2 (destination)

As mentioned in server 1 (source) setup, the DBA has already created the duplex directory e:\dbbackup\mydb\ and configured a share on the \dbbackup directory called standbydest using NT security.   For server-2, we schedule three jobs that execute stored procedure routines to initialise, continue and monitor log-shipping.

Initialise

The main stored procedure is log shipping initialise, we supply the routine a number of parameters, they being the name of the database to be restored, the location of the backups (remember - files were copied from server-1), the standby redo file, the pre and post-fix file extensions so the routine can build a list of files from disk to restore from, and finally, the MOVE command for each database filegroup.

Here is an example:

exec usp_LogShipping_Init
    'mydb'
    ,'e:\dbbackup\mydb\'
    ,'e:\dbbackup\mydb_standby.rdo'
    ,'mydb_'
    ,'.bak*'
    ,'_full.bak'
    ,'_dif.bak'
    ,'_trn.bak'
    ,'
    MOVE ''MYDB_SYSTEM'' TO ''c:\dbdata\mydb\mydbstandby_system01.mdf'',
    MOVE ''MYDB_DATA'' TO ''c:\dbdata\mydb\mydbstandby_data01.mdf'',
    MOVE ''MYDB_INDEX'' TO ''c:\dbdata\mydb\mydbstandby_index01.mdf'',
    MOVE ''MYDB_AUDIT'' TO ''c:\dbdata\mydb\mydbstandby_audit01.mdf'',
    MOVE ''MYDB_LOG'' TO ''c:\dbdata\mydb\mydbstandby_log01.mdf''
    '
    ,'c:\scripts'

The DBA may consider further customisation of this script, namely the building of the MOVE statement by reading the backup file header.  YOu can, but I work on the KISS principal and in this scenario we cant go wrong.

The initialise routine is NOT SCHEDULED and is only run manually if we need to force the re-initialisation of log shipping from the last full backup.

The master..LogShipping_Audit is updated accordingly with the files applied by the routine or any failure/error information.

NOTE - This routine will locate the last full backup and apply it, then the last differential (if any) and all subsequent transaction logs.

Continue

This is the crux of the log shipping routines and is scheduled to run every two hours from 7am to 10pm. The routine has identical parameters to that of the initialise procedure.  When run, the routine will determine if Initialise must be called (missing standby database), or a new full backup file has been found and we need to start from scratch with the full and differentials.  This routine is basically the driver for log shipping, in both its initialisation and continuing to apply transaction logs as they arrive in the backup directory from server-1.

exec usp_LogShipping_Continue
    'mydb'
    ,'e:\dbbackup\mydb\'
    ,'e:\dbbackup\mydb_standby.rdo'
    ,'mydb_'
    ,'.bak*'
    ,'_full.bak'
    ,'_dif.bak'
    ,'_trn.bak'
    ,'
    MOVE ''MYDB_SYSTEM'' TO ''c:\dbdata\mydb\mydbstandby_system01.mdf'',
    MOVE ''MYDB_DATA'' TO ''c:\dbdata\mydb\mydbstandby_data01.mdf'',
    MOVE ''MYDB_INDEX'' TO ''c:\dbdata\mydb\mydbstandby_index01.mdf'',
    MOVE ''MYDB_AUDIT'' TO ''c:\dbdata\mydb\mydbstandby_audit01.mdf'',
    MOVE ''MYDB_LOG'' TO ''c:\dbdata\mydb\mydbstandby_log01.mdf''
    '
    ,'c:\scripts'

Monitor

Using a simple-cdo custom DLL, this scheduled job running at the same schedule as continue log shipping calls this:

    exec usp_LogShipping_Monitor 'support@chriskempster.com', 'LOGSHIP', 15

The DBA is emailed error rows from the master..LogShipping_Audit table.  Here is an example of its contents:

Log Shipping Example 2 - Finalising Recovery / Failover

The DBA will attempt the following in order, I say "attempt" as the first steps may fail and must be carefully monitored.

1)  Re-evaluate the need to cutover and double check that the situation is such that cut over is required

2)  Attempt to run a backup on server-1 using your scheduled DBBackup_sp command or via Query Analyser

3)  Verify backup and file copy, manually copy if required

4)  Run  usp_LogShipping_Continue  (or its scheduled job) on server-2

5)  Disable above job

6)  Manually run   exec..usp_LogShipping_Finish 'db-name-here'

Conclusion

The method presented is simple, easy to implement and does not rely on linked servers or numerous system table lookups.  One of the big disadvantages with log shipping is more to do with the recovery process, that being "user sessions must be killed before attempting to recover the database".  If users need to be kicked from the standby database to apply further logs, then its tough setting a recover time if the database is also used for corporate reporting.  Hopefully we will see changes in SQL Server Yukon.

In closing - as a DBA do not feel that you need to lock yourself into maintenance plans or the EM wizards, take time to explore the alternatives with T-SQL scripts and external utilities. Drill through on each command and leverage the options to your advantage to really expand on the functionality and richness of SQL Server 2000.

See http://www.chriskempster.com/scripts/custom_logshipping_v1.zip  for all scripts and external routines.

Total article views: 24312 | Views in the last 30 days: 44
 
Related Articles
ARTICLE

Powershell Database Backup Script

Learn how to write a script for regular database backups using Powershell and SMO.

SCRIPT

Script to Get the Backup History

Script to get the database backup history on SQL Server 2000/2005/2008

BLOG

Script: Backup All your Databases

We all know the importance of taking regular backups of our SQL Server databases. This is a useful l...

FORUM

Server Database Backup

Server Database Backup

SCRIPT

Backup & Restoration Script

This script provides very useful information about database backup and restoration.

Tags
administration    
availability    
miscellaneous    
programming    
sql server 7    
strategies    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones