6.2 Monitoring and Tuning

You should consider the following tasks for monitoring and tuning the Sybase ASA database:

6.2.1 Backing Up the Database

A backup is a full or partial copy of the information in a database, held in a physically separate location. If the database becomes unavailable, you can restore it from the backup. You can use your backups to restore all committed changes to the database up to the time it became unavailable.

Backing up a running database provides a snapshot of the database where the data is in a consistent state, even though other users are modifying the database.

If the operating system or database server fails, or if the database server does not shut down properly, then the database must be recovered. On database startup, the database server checks to see if the database was shut down cleanly at the end of the previous session. If it was not, the database server runs an automatic recovery process to restore all changes up to the most recently committed transaction.

Regular backups must be created on the ZENworks database.

NOTE:A backup cannot be created under the following conditions:

  • The database service does not start.

  • The database server crashes irrecoverably.

  • The dbsrv process crashes periodically.

The rest of this section describes the different types of backups and how to back up the database:

Online Backups

An online backup is performed against a running database. Backing up a running database provides a snapshot of the database where the data is in a consistent state, even though other users are modifying the database. You must have BACKUP authority or REMOTE DBA authority to create online backups of a database.The following table summarizes the types of online backup supported by SQL Anywhere:

Backup Type

Description

More Information

Full

A full backup is a backup of the database files and the transaction log. Typically, full backups are interspersed with several incremental backups.

The restoration and recovery of the database is much easier with the full backup. However it, can be expensive in terms of the required storage. You must perform full backups regularly, but less frequently than other backups. In order to reduce the required disk space, the transaction log can be truncated. In conjunction with this, you can perform the following

  • Archive backup: ZENworks database and transaction log is stored in one file.

  • Image backup: ZENworks database and transaction logs are stored in separate files.

Full Backups

Incremental

A backup of the transaction log only. It can be restored using the dbtran command. This backup should be taken frequently. You must run a full backup before running an incremental backup.

Incremental Backups

Live

A continuous backup is a backup of the database that runs while the database is running.

Live Backups

Archive

A collection of one or more files that together contain all the required information for the backup, including the main database file, the transaction log, and any additional dbspaces.

Archive Backups

Image

A copy of the database file or the transaction log, each as separate files.

Image Backups

Server-side

A backup made on the database server computer.

Server-side Backups

Client-side

A backup made on the client computer.

Client-side Backups

The easiest way to perform an online backup of the ZENworks database is by using the zman database-backup command. This performs a backup without the need to stop the database or disrupt your ZENworks environment.

Offline Backups

An offline database backup requires the database server to be stopped. An offline backup is a copy of the database files. You can make offline backups by copying the database files when the database is not running. You should only perform an offline backup when the database is not running and when the database server has shut down properly.

This method can be used in conjunction with a scheduling mechanism to automate the process, such as Windows Task Scheduler or crontab. Offline backups can use incremental backup (transaction logs only) or full backup and lend itself better to do a full backup quickly. They are used with full backups at less frequent intervals.

Determining a Backup Strategy

Consider these tips when defining your database backup strategy:

  • Back up your database before and after every ZENworks upgrade.

  • Ensure that your database backup has the same name as your current database.

  • Check disk space. This can impact the best strategy for your backup.

  • Events and notifications can be scheduled through the database server itself.

Commands for Backing Up the ZENworks Database

There are two ways to backup the ZENworks database:

ZMAN Command

The simplest method for backing up the Sybase database is to use the zman command. The zman command does a full online backup and truncates the transaction log. Database commands begin with database- in the long form or with the letter d in the short form.

  • database-backup (db) (backup directory) [schedule SQL file] [options]

    Backs up the embedded Sybase SQL Anywhere database and also allows you to schedule the backup operation. The following parameters can be specified:

    • (backup directory) The local directory on the database server or the network location where the database files are backed up. Ensure that the parent directory already exists and has sufficient disk space. Any existing database files in the directory are overwritten.

    • [schedule SQL file] File that contains the schedule for backing up the database. The SQL file can contain CREATE EVENT, ALTER EVENT, or DROP EVENT SQL statements. For sample SQL files, refer to the files located in /opt/novell/zenworks/share/zman/samples/database on a Linux server

      or

      Installation_directory:\Novell\Zenworks\share\zman\samples\database on a Windows server.

      If you do not specify a schedule file, the database is backed up immediately.

    For more information on defining schedules, see the SQL Anywhere documentation at Scheduling Events in Sybase.

    Accepts the following option:

    -d, --dir-name=[SQL function call] - SQL function call that returns a value. The value is appended to the backup directory path. For example, if this command runs on Tuesday with the backup directory specified as c:\ and the value for this option specified as DAYNAME(now()), the files are backed up to c:\Tuesday.

    Example 6-1 Example:

    Copy and save the script below in an SQL file. Change the time and interval according to your requirement.

    CREATE EVENT ZENDBBackup
  SCHEDULE
  START TIME '02:00 AM' EVERY 24 HOURS

    Use the file path above in the zman command to schedule it.

    zman db c:\dbbackup\ BackupSchedule.sql -d "DAYNAME(now())"

  • database-get-credentials (dgc)

    Retrieves the credentials used to connect to the embedded Sybase SQL Anywhere database.

Sybase Backup Command

You can use the Sybase backup command to do a full online backup and truncate the transaction log.

BACKUP DATABASE DIRECTORY '<backupDirectory>' TRANSACTION LOG TRUNCATE

Beginning with the Sybase that ships with ZENworks 10.3 (also available as an FTF), you can use Sybase Central.

  • %ZENWORKS_HOME%\share\ASA\Sybase Central 5.0.0\win32\scjview.exe

  • /opt/novell/zenworks/share/sybase/bin32s/sybcentral500/scjview

For more information, see Sybase backup techniques at the following locations:

Recommendations:

  • Backup not only database files, but also the data in the content repository, certification information, and other recommended files.

  • Although online incremental backups provide higher availability for the ZENworks database server, it results in a slower backup process. To set high priority for the backup and background process, run the statement below in the ZENworks database.

    "SET TEMPORARY OPTION BACKGROUND_PRIORITY = 'ON';"

6.2.2 Database Validation

Novell recommends that you validate the data before and after backups. The validation process does not check the ZENworks schema, but focuses on maintaining the database integrity. The validation process can be run using Sybase Central or from the command line as follows:

dbvalid -c "UID=zenadmin;PWD=<password>; ENG=<database service name>"

If the ZENworks database validation fails, try unloading the database and restoring the last known good backup.

6.2.3 Database Recovery

To recover the ZENworks database in Sybase, you first need to understand the problem. The best recovery strategy depends on the type of problem that has occurred. When recovering, Novell recommends that you perform recovery in offline mode. Ensure that the database is stopped and all services on all Primary Servers have also been stopped to ensure that the Primary Servers do not attempt to connect to the database. To recover, use one of two methods:

  • Apply the good transaction logs to the last full backup copy of your database. (Sybase has a utility called dbtran for this purpose.)

  • Restore the last good backup of your database by copying it in the same location.

6.2.4 Sybase Tuning and Maintenance

Tuning the database is an important step in improving the performance of ZENworks. If possible, place the transaction log on a separate physical drive from the database and the operating system to improve disk I/O completion. For more information, see “Improving Performance in SQL Anywhere”.

Also consider changing the startup options of Sybase by editing the zenworks_database.conf file. The zenworks_database.conf file contains all of the database server startup options and is located in the following locations:

  • Linux: /etc/opt/novell/zenworks

  • Windows: %ZENWORKS_HOME%\conf

You can change these parameters as follows:

  • Increase the cache size ( -c ).

  • Set the minimum cache size to a value larger than the default ( -cl ).

  • Change the checkpoint interval (-gc ).

  • The number of physical processors to use ( -gt ). This can be a useful way of limiting the processors used when using an embedded database to allow the processing power to be assigned to the ZENworks Primary Server.

By default, the cache size is set to 8 MB. Change this to a value more aligned with your environment.

To calculate the cache size, use the following formula:

max(2 MB, min(dbsize, 0.25*TotalPhysicalMemory));

For more information, see the following links:

http://dcx.sap.com/sqla170/en/html/3bc735bd6c5f1014a916919140c74362.html

http://dcx.sap.com/sqla170/en/html/818595ec6ce210148491e211f5e933eb.html

6.2.5 Enabling Sybase Debug Mode

If necessary, the database can be placed in debug mode by updating the zenworks_database.conf file as follows:

  • -os 50M (size of log file for messages)

  • -o <location>

  • For custom logging, include the -zr option and set options specific to the types of statements you are debugging.

6.2.6 Enabling Sybase Debug Mode (Verbose)

To place the Sybase database in verbose debug mode, set the following parameters:

  • -zs 50M (size of request logging)

  • -os 50M (size of log file for messages)

  • -zr all (log sql operations)

  • -z (connection diagnostics)

  • -zt (timing information)

  • -o <location>

When logging is enabled, verify the log sizes as well as the disk space that is being consumed.

More information on Sybase startup parameters refer to the SAP Sybase website.

A sample Sybase is seen below:

  • (Blue) Database fragmentation warnings

  • (Green) Loading transaction log

  • (Red) Database server starts

6.2.7 Moving Database Files

If you need to move the database files, such as to a new separate physical disk, this operation is as simple as a file copy. When the file has been moved, adjust the zenworks_database.conf file to point to the new location.

If the transaction log needs to be separated from the database, this can be achieved by using the dblog command.

dblog -t <location of new transaction log> <path to database>

When this is done, the transaction log can be moved and once again placed on a separate physical disk. Ensure that logs are still included in future backups.

6.2.8 Sybase Performance Monitoring

Sybase SQL Anywhere comes with built-in performance monitoring capabilities in the Sybase Central tool.

After starting the Application Profile Wizard, the tool tracks the transactions, such as Stored Procedures being run and queries that are performed on the database during that period of time. Do not leave this process running, because it can have detrimental effects on database performance.

Performance monitoring can report on items such as the following:

  • Deadlock information

  • CPU usage

  • Trigger and Stored Procedure efficiency

  • Queries being executed

  • Query run times

Longer profiling times provide greater accuracy but can cause the database to become less responsive, so it is important to only use this tool for short periods of time.

6.2.9 Index Fragmentation

Fragmented indexes are a top reason for performance degradation of the ZENworks database.

Novell recommends that you check the indexes and rebuild in the following scenarios:

  • System Update

  • After enabling ZENworks Patch Management in the zone

  • After adding a large number of devices

  • After adding a large number of bundles or bundles with lots of content defined

6.2.10 Table Fragmentation

You might need to use the REORGANIZE TABLE <table name> command if the database has become severely fragmented. The following tables should be monitored closely for fragmentation:

  • zMessage

  • zObjectInfo

  • zStatusEvent

The following example shows an SQL report detailing the table fragmentation that exists within the database.

The following example shows how to reorganize the PATCHFLAGS and zContent by using an SQL query.

6.2.11 Rebuilding the Sybase Database

If fragmentation exists on numerous tables and the database is large, a faster method for fixing fragmentation is to rebuild the entire database by using the Sybase Central tool.

When you unload the database, select all objects to rebuild the entire database.

When you unload and reload the database, you are given an option to encrypt the database. Novell recommends that you do not use this option.

The database can also be rebuilt in the command line by using the dbunload command:

  • Unload into a new database:

    • dbunload -c "UID=zenadmin;PWD=<database password>;ENG=<database service name>" -an "<path to new db>"

  • Unload database definitions into .dat files and reload:

    • dbunload -c "UID=zenadmin;PWD=<database password>;ENG=<database service name>" "<path to unload directory>"

    • dbinit -p 16384 <path to new db>

    • dbisql -c "UID=DBA;PWD=sql;DBF='<path to new db>'" read reload.sql

When you rebuild the database, if the database is large, the performance can be increased by increasing the page size. The options for Sybase Page Sizing are as follows:

  • 2048

  • 4096

  • 8192

  • 16384

Since ZENworks 10 SP3, new Sybase databases are created with a page size of 16384. However, each time the database is unloaded, the page size can be adjusted. Novell recommends a page size of either 16384 or 8192 for customers using Sybase. The page option can be set by using Sybase Central or using the dbinit –p option as shown above.

6.2.12 Tips for Rebuilding a Sybase Database

Until you copy the newly built database in place of your existing database (remember to stop the service), your servers will use the old database. If you have a problem connecting to the database service, use the dblocate command to verify the following:

  • The service name to which you are trying to connect.

  • The status of the service. Whether it is running.

6.2.13 Engaging with Novell Support

If you encounter problems with your database, collect the following information before engaging with Novell Support:

  • The output from the log file that is enabled and specified in the zenworks_database.conf file.

  • The version of the database that you are running. The Sybase tool dblocate will provide that information when run from a command prompt.

  • The transaction log and the approximate time of the issue.

  • The date of your last backup.

  • Optional: Deadlock information, connection information, and profiling statistics from the Sybase Central tool.

6.2.14 Using Sybase Mirroring to Enable High Availability of the Sybase Database

Sybase SQL Anywhere supports database mirroring as a method to implement high availability and fault tolerance. This technique requires multiple database servers and is therefore not set up by default with ZENworks.

NOTE:Mirroring should not be implemented as a means to distribute the database work to remote locations. Mirrored database servers should be connected over a high-speed link.

For more information, see Database Mirroring on the Sybase website:

http://www.ianywhere.com/developer/product_manuals/sqlanywhere/1000/en/html/dbdaen10/da-da-highavailability-s-4980336.html

Useful Reference Sites for Sybase