This section provides a number of recommendations for keeping your Adaptive Server installation working at peak effectiveness. By maintaining these good practices, you can maximize server uptime, correct problems proactively, and be as prepared as possible to handle emergencies.
Keep Up-to-Date Backups
Maintaining current backups of your data is vital for any recovery plan. Keep multiple generations of backups, and keep some offsite as an extra precaution.
Make regular database dumps of:
the master database. To insure that your backup of master is always current, back up master after each maintenance command that affects disks, storage, databases, or segments - for example, after creating or deleting databases, initializing new devices, and creating or modifying segments.
the model database
the sybsystemprocs database
user databases.
Maintain copies of System Tables and DDL
Keep the latest offline copies of the following tables:
sysusages
syslogins
sysloginroles
sysdatabases
sysdevices
syscharsets
sysconfigures
sysservers
sysremotelogins
sysresourcelimits
systimeranges
Use the bcp utility to copy out these tables. In addition, maintain a hardcopy by printing the output of the following queries:
select * from sysusages order by vstart select * from sysusages order by dbid, lstart select * from syslogins select * from sysloginroles select * from sysdatabases select * from sysdevices select * from syscharsets select * from sysconfigures select * from sysservers select * from sysremotelogins select * from sysresourcelimits select * from systimeranges
Also maintain:
copies of your configuration file.
the first two blocks (2 pages) of the master device.
a copy of the config block. You can generate this using Sybase Central or Power Designer. On Unix platforms, you can obtain a copy of the config block with this command:
dd if=master_device of=$SYBASE/config_block.bak bs=1024 count=8
all Data Definition Language (DDL) scripts you use to create user objects, specially stored procedures if you elect to use sp_hidetext.
Implement all changes to schema in the same way that
the installmaster script is implemented.
Verify Database Consistency
Run dbcc checks on a regular basis to monitor the health of your databases. Database-wide checks are available with dbcc checkdb, dbcc checkalloc, and dbcc checkstorage. dbcc checkcatalog is also a useful tool. For a brief overview of dbcc commands, see “Useful dbcc Commands”. Detailed information appears in the System Administration Guide.
Since dbcc checks can be resource intensive, consider adopting a strategy to take advantage of object level dbcc's. On a given day run a certain number of checktable and tablealloc commands for a portion of the database. On subsequent days, run different tables. Over a period of days you can accomplish a complete check of your databases for integrity. For example if your database has 200 tables in addition to the system tables, run dbcc's on the system tables on night one, run dbcc's on each of the first 50 of the user tables on night two, the next 50 the next night and so on, until at the end of five nights you have checked every table in the database. On the sixth night you can begin the cycle again.
Running table-level dbcc's misses
the GAM page checks.
Alternative strategies include:
loading the database to another server, and running the dbcc's on that server;
dbcc checkstorage.
Building dbcc checks into your regular backup/maintenance schedule can ensure that you have consistent, accurate backups available at all times.
Implement Mirroring
Mirroring, either at the Adaptive Server level or at the operating system level, can provide nonstop recovery in the event of media failure.
The factors you need to consider, and instructions on implementing Adaptive Server mirroring, are detailed in the section titled "Mirroring Database Devices" in the System Administration Guide.
Perform Ongoing Maintenance
As part of a routine program of server maintenance, you should:
Monitor the Adaptive Server error log for errors. Note that users may not report errors of severity 17 or 18 if their work is not interrupted.
Set up a routine that browses the error log, searching for errors. See “How to Monitor the Adaptive Server Error Log” for an example. For information on the error log format and severity levels, see the System Administration Guide.
NT users can also monitor server messages by means of
the Windows NT Event Log.
Prune the error log regularly as it grows constantly since Adaptive Server appends informational messages to the log during startup. A full error log with no space to write to may cause the server to freeze. Remember to shut down the server first, and make a copy of the log before pruning.
An example of log pruning on unix follows:
% cp errorlog errorlog.date % cp /dev/null errorlog
where date is the current date.
Monitor the operating system log to keep an eye on the health of the hardware and the server environment. Many Adaptive Server errors can be due to underlying hardware problems, and can therefore indicate hardware problems.
Refer to “Checking the Operating System Error Log” for information on how to locate your log and how to check it.
Monitor space usage with system procedures such as sp_helpsegment, sp_spaceused, and sp_helpdb. By running sp_spaceused regularly, for example, you can determine if a database is running out of space for new objects.
Alternatively, you can set up thresholds to monitor free space on database segments.
See "Getting Information About Database Storage" and “Creating Threshold Procedures” in the System Administration Guide for details.
Monitor and maintain optimizer statistics. As a table grows and changes, these statistics become old, and the server may start to choose the wrong index strategy for queries.
Adaptive Server maintains statistics in the sysstatistics and systabstats tables. For a detailed description of tbese tables and their use, refer to “Managing Statistics to Improve Performance” in the Performance and Tuning Guide
Avoid Risky Practices
Avoid moving tempdb off the master device. When Adaptive Server is installed, tempdb resides on the master device. Although it is possible to move tempdb off the master device later for space considerations, this is not advisable. Once tempdb is moved off the master device, it is difficult and time-consuming to recover if a problem occurs on the master device or the device to which tempdb is moved.
Never put anything other than master, model and tempdb on the master device. Storing user databases on the master device may make it difficult to recover the system databases or user databases if either become damaged.
Recovery Tips, Or What to do When Things Go Wrong
Choose the correct recovery method. Your choice of methods will be dictated by the type of failure you encounter. For example, loss of a device will require restoring from backups.
Network/machine failure usually has little impact on the server but could corrupt data in some situations, and recovery may fail.
If mirroring is enabled at your site, disable the mirror before loading a dump, thus preserving a copy of what you had before in case dumps are bad.
Never run device creation utilities (dataserver for 12.5 and later, buildmaster for 12.0.x and earlier) on the original master device. It may contain information you need later. Instead, do the device build on a different device, and when your environment is completely restored, you can move back to your original master device.
Additional Tips
After an operating system upgrade, check permissions on your sybase devices.
