Building Database Applications with Remote Support in Mind
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
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