dcsimg
December 3, 2016
Hot Topics:

Building Database Applications with Remote Support in Mind

  • June 5, 2006
  • By Paul Horan
  • Send Email »
  • More Articles »

Design Unattended Database Backups

The design and implementation of disaster recovery procedures is very specific to the nature of the application being maintained. There's no "one size fits all" scheme for every possible set of requirements. Applications that are highly transaction intensive may want to take more frequent log-only backups, while others may only require a nightly full backup. For an excellent tutorial on developing robust backup and recovery procedures, see chapter 9 of Breck Carter's book SQL Anywhere Studio 9 Developer's Guide, ISBN: 1-55622-506-7.

The simplest example is a nightly event that performs a complete image backup to a folder on the server's file system, and truncates the active transaction log.

CREATE EVENT "nightlyFullBackup" ENABLE AT ALL
HANDLER
begin
   BACKUP DATABASE
   DIRECTORY 'd:\database\backup'
   TRANSACTION LOG TRUNCATE
end
go
   ALTER EVENT "nightlyFullBackup"
   ADD SCHEDULE "nightlyFullBackup_1"
   START TIME '00:00:01' ON
   ('Tuesday','Wednesday','Thursday','Friday','Saturday')
go

This example could be made more dynamic by retrieving the backup location from a database column from a "settings" table, parsing that value into the BACKUP DATABASE statement, and using EXECUTE IMMEDIATE, as follows:

CREATE EVENT "nightlyFullBackup" ENABLE AT ALL
HANDLER
begin
   declare backupFolder varchar(255);
   declare backupCmd varchar(255);
   set backupFolder = (Select folderName
   From dbOptions );
   set backupCmd = ' BACKUP DATABASE ' ||
                   ' DIRECTORY ' || backupFolder ||
                   ' TRANSACTION LOG TRUNCATE';
   EXECUTE IMMEDIATE WITH ESCAPES OFF backupCmd ;
end
go

In a high-transaction or replicating environment, it might be necessary to perform "log only" backups at regular intervals throughout the business day. The Event Scheduler can be directed to execute events at specific recurring intervals, as follows:

CREATE EVENT "hourlyLogBackup" ENABLE AT ALL
HANDLER
begin
   declare backupFolder varchar(255);
   declare backupCmd varchar(255);
   set backupFolder=(select folderName from dbOptions);
   set backupCmd = ' BACKUP DATABASE ' ||
                   ' DIRECTORY ' || backupFolder ||
                   ' TRANSACTION LOG ONLY ' ||
                   ' TRANSACTION LOG RENAME MATCH ';
   EXECUTE IMMEDIATE WITH ESCAPES OFF backupCmd ;
end
go
   ALTER EVENT "hourlyLogBackup"
   ADD SCHEDULE "hourlyLogBackup_1"
   BETWEEN '10:00:00' AND '18:00:00'
   EVERY 1 HOURS ON ('Monday','Tuesday','Wednesday','Thursday',
                     'Friday')
Go

Event handlers also can be written to monitor critical server resources, such as low disk space or excessive database file fragmentation. When the item that is being monitored is something that can be corrected as well, such as table or index fragmentation, the event itself can be written to repair the condition. Here's an event that runs at 4:00am on Sunday morning, and detects for indexes that have grown beyond four levels deep. If any are detected, they are reorganized on-the-fly. This event demonstrates another powerful feature of SQL Anywhere—the ability to write SELECT statements across stored procedures.

HANDLER
BEGIN
   declare tName varchar(60);
   declare iName varchar(60);
   declare iType varchar(60);
   declare stmt varchar(60);
   declare err_notFound exception for sqlstate value '02000';
   declare indexLevels no scroll cursor for
SELECT
   tableName,
   indexName,
   indexType
FROM sa_index_levels()
WHERE
   levels >= 4;
OPEN indexLevels;
   indexLoop:
LOOP
FETCH NEXT indexLevels
INTO tName,
   iName,
   iType;
IF sqlstate = err_notFound then
LEAVE indexLoop
END IF;
SET stmt='REORGANIZE TABLE ' || tName;
CASE upper(iType)
WHEN 'PKEY' THEN
SET stmt=stmt || ' PRIMARY KEY;'
WHEN 'FKEY' THEN
SET stmt=stmt || ' FOREIGN KEY ' || iName || ';'
WHEN 'NUI' THEN
SET stmt=stmt || ' INDEX ' || iName || ';'
END CASE ;
MESSAGE string(now) || ' Event indexReorg: ' || stmt to console;
EXECUTE IMMEDIATE stmt ;
END LOOP indexLoop;
CLOSE indexLevels;
   message string(now(*)) || 'Event indexReorg completed.' to console;
END
go
ALTER EVENT "indexReorg"
ADD SCHEDULE "indexReorg_1"
START TIME '04:00:00' ON ('Sunday')
go

When the condition being monitored requires physical access to the server machine, such as a low disk space condition, a message to the console is inadequate. More immediate attention is required. With that in mind, SQL Anywhere has the ability to send an e-mail, either through the MAPI interface, or through an SMTP server.

Note for Microsoft Exchange users: Microsoft, in an effort to cut down on SPAM, has placed specific restrictions in the MAPI interface that prevent the automatic sending of e-mails from non-Outlook clients. This makes the simple MAPI interface very undesirable for use in an unattended server-based application. In these environments, it may be more beneficial to research the use of SMTP for outbound e-mails.

Here's an example of an Event that detects free disk space on the main database drive. If the database uses multiple dbSpaces, it will examine each of the drive letters referenced by the different dbSpaces. If the free disk space on any drive falls below 10%, this event sends a proactive e-mail to an administrative account stored in the dbOptions table.

CREATE EVENT "lowDiskSpace"
TYPE "DBDiskSpace"
WHERE event_condition('DBFreePercent') <= 10
ENABLE AT ALL
HANDLER
BEGIN
   declare sMailType char(4);
   declare sMailAcct varchar(60);
   declare sMailPwd varchar(60);
   declare sMailServer varchar(60);
   declare iSMTPPort smallint;
   declare sRecip varchar(60);
   declare SiteName varchar(60);
   declare iStatus integer;
SELECT
   sMailType = mailType,
   sMailPwd = mailPwd,
   sMailServer = mailServer,
   iSMTPPort = mailPort,
   sRecip = adminEmail,
   SiteName = site_name
FROM dbOptions ;
CASE sMailType
WHEN 'MAPI' THEN
   iStatus =
CALL xp_startMAIL(
   mail_user = sMailAcct,
   mail_password = sMailPwd );
WHEN 'SMTP' THEN
   iStatus = CALL xp_startSMTP(
      smtp_sender = sMailAcct,
      smtp_server = sMailServer,
      smtp_port = iSMTPPort );
END CASE;
If iStatus <> 0 then
   MESSAGE 'Low Disk Space Warning!! ' ||
           'Cannot start Mail session. ' ||
           'status = ' || string(iStatus)
      to console;
   return
end if;
iStatus = CALL xp_sendMail(
   recipient=adminEmail,
   subject='Free disk space below 10% at: ' || SiteName
) ;
If iStatus <> 0 then
   MESSAGE 'Low Disk Space Warning!! ' ||
           'Cannot send e-mail to: ' || adminEmail ||
           ' via ' || sMailtype || '. status = ' ||
   string(iStatus)
   TO console ;
end if;
CALL xp_stopMail() ;
END




Page 2 of 3



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date
Rocket Fuel