3.4 ZENworks Database

The ZENworks database is the most important aspect of the ZENworks infrastructure. The database contains all information about bundles, policies, configuration, and how these apply to the devices and users under management.

The main considerations that influence the choice of ZENworks database platform are as follows:

For more information on ZENworks database see the following:

3.4.1 Virtualizing the ZENworks Database Server

Novell does not recommend virtualization of the ZENworks database server. You should use a dedicated server for the ZENworks database to ensure that the available resources for the database are known and are under control. Placing the database on a server with other virtual machines can lead to performance degradation, which in turn can affect other aspects of ZENworks, from the performance of the ZENworks Control Center to the responsiveness of ZENworks on managed devices.

If you want to virtualize your database on a VMware ESXi server, and you are planning to utilize Microsoft SQL Server, please refer to VMware’s documentation regarding Virtualizing Microsoft SQL Server on VMware ESXi. This document needs to be reviewed prior to installing the database and other ZENworks services.

3.4.2 ZENworks Database Scalability

The following table lists the number of devices that different databases can handle in a production environment:

Table 3-2 Database Platform and Supported Number of Devices

Database Platform

Number of devices

Sybase (embedded)

As many as 1,000 (lab environments)

Sybase (remote)

As many as 3,000

Microsoft SQL Server 2008 R2

1,500 to 40,000

Oracle 11g

1,500 to 40,000

Use the vendor database backup tools to regularly back up the database to ensure that if the database is lost, you can restore from a backup.

The following sections highlight the best practices when managing and maintaining your Database Server.

3.4.3 Saving the ZENworks Database Password

When you create a new ZENworks Zone, a database password is generated and subsequently used by each Primary Server. To back up the ZENworks database password, run the zman dgc command and save the retrieved password.

3.4.4 Sybase

Regular backups must be created on the ZENworks database. You cannot create backups of the database if:

  • The dbsrv12 process is crashing periodically.

    • The database service does not start.

    • Database server crashes irrecoverably.

For more information on different tasks performed on the Sybase, see:

Full Backups

Creating a full backup is the best option as it includes a copy of both the database and the transaction log file. In this situation, the restoration and recovery of the database is much easier but 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, administrators have a choice of performing 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.

Incremental Backups

An incremental backup makes a copy of the transaction log file only and can be restored using the dbtran command. They are used frequently. The data is lost if this file gets corrupt.

Online Backups

An online backup is performed without stopping the ZENworks Database server using the zman database-backup <path to backup directory> command. They are used for incremental backups. Though this provides higher availability for the database server, it results in a slower backup process. Use “SET TEMPORARY OPTION BACKGROUND_PRIORITY = 'ON';”

Online backups can use incremental backup (transaction logs only) or full backup.

Offline Backups

An offline database backup requires the database server to be stopped. 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.

Sybase Backup Strategy

Some general tips to consider when defining the database backup strategy are as follows:

  • Back up your database after every server update.

  • Make sure your database backup has the same name as your current database.

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

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

Sybase Database Backups

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. For more information, see Database Commands in the ZENworks 10 Configuration Management Asset Management Command Line Utilities Reference guide. Also, you can see information on Sybase backup techniques at the following location: http://tldp.org/HOWTO/Sybase-ASA-HOWTO/backup.html

BACKUP DATABASE DIRECTORY '<backupDirectory>' TRANSACTION LOG TRUNCATE

Starting in the Sybase that ships with 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

Back up not only your database, but also the data in the content repository, certification information, and other recommended files.

Database Validation

Novell recommends that you validate the data before and after backups. The validation process does no check the ZENworks schema, but focusses 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.

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. Make sure 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 place.

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 as is located in the following locations:

  • Linux: /etc/opt/novell/zenworks

  • Windows: %ZENWORKS_HOME%\conf

Some of the parameters to be changed are 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 useful way of limiting the processors used when using an embedded database to allow for 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.

Enabling Sybase Debug mode

If necessary, the database can 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

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 can be at the Sybase Web site:

http://www.ianywhere.com/developer/product_manuals/sqlanywhere/1001/en/html/dbdaen10/da-dbengines.html

A sample Sybase is seen below:

  • (Blue) Database fragmentation warnings

  • (Green) Loading transaction log

  • (Red) Database server starts

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. Make sure that logs are still included in the future backups.

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:

  • 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.

Index Fragmentation

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

Novell recommends that indexes that you check 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

Table Fragmentation

In addition to this, you might need to use the REORGANIZE TABLE <table name> command if the database has become severely fragmented. The following tables should be watched 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.

Rebuilding the Sybase Database

If fragmentation exists on numerous tables and the database is large, a quicker 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 re-build 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 on 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 4096 <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, performance increases can be made by increasing the page size. The options for Sybase Page Sizing are the following:

  • 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 page size of either the 16384 or 8192 for customers using Sybase. Setting the page option can be performed using Sybase Central or using the dbinit –p option as show above.

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 that you are trying to connect to.

  • If the service is running.

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 enabled and specified in the zenworks_database.conf file.

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

  • The transaction log and approximate time of the issue

  • The date of your last backup

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

3.4.5 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 11 SP2.

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 on, see Database Mirroring on the Sybase Web site:

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

Useful Reference Sites for Sybase

3.4.6 Microsoft SQL Server

The most important aspects of managing and maintaining a Database Server that is hosted on a Microsoft SQL Server are the following:

  • The customer must have the skills in-house, or readily available (contractor, consultant, or partner) to manage and maintain the Microsoft SQL Server based on the best practices that Microsoft outlines for regular database management.

  • There must be regular database backup routines in place. This should also be documented in the design document.

  • There must be regular database maintenance routines in place. This should also be documented in the design document.

  • Considerations for clustering (high availability) of the Database Server should be made and documented.

  • In general Novell does not recommend virtualizing the ZENworks database server. However, it is possible to run Microsoft SQL Server on a virtual host. Please see the following documentation for more information on VMware communities.

Microsoft SQL Server 2008 R2 has a Maintenance Plan Wizard in the SQL Server Management Studio. This tool should be readily available to any customer that is utilizing Microsoft SQL Server in their data centers.

Maintenance tasks that can be scheduled include the following:

  • Check database integrity

  • Shrink database

  • Reorganize index

  • Rebuild index

  • Update statistics

  • Back up database

These tasks should be thoroughly understood before performing them on a live system that is hosting the ZENworks Configuration Management database.

Microsoft recommends the following best practices for managing Microsoft SQL Server 2008 R2:

  • Backups should be performed daily.

  • A Database Integrity Check should be performed every 14 days.

  • Reorganizing or rebuilding indexes should be done when fragmentation is excessive. If fragmentation is greater than 30 percent, an index should be rebuilt. To determine the fragmentation of the indexes in your database, use the dynamic memory view sys.dm_db_index_physical_stats. Novell recommends that rebuilding indexes should be done at least once per week because the clustered indexes will be fragmented over 75 percent within a few days of insert/update activity. This is a contributing factor to lag, escalating locks, and eventual deadlocks.

A good overview of the Maintenance Plan Wizard is found on the MSSQLTips Web site.

Microsoft also offers the SQL Server 2008 R2 Best Practices Analyzer Tool. This tool addresses a wide variety of best practices as outlined by Microsoft. This tool can be found on the Microsoft TechNet Web site.

For example, the following recommendations were listed for a ZENworks Configuration Management installation on SQL Server 2008 R2:

  • Place data and log files on separate drives for database [zenworks_database] on server [server_name]

  • Check database integrity at least every 14 days for database [zenworks_database] on server [server_name]

The Best Practices Analyzer tool also indicates that log files and data files should be placed on separate hard drives to improve I/O, thus improving overall performance of the Database Server. However, this can be a problem if the database files are hosted on a SAN or inside virtual machines, which are both trends in the industry. Any exceptions to recommend best practices should also be well documented in the design document created during the design phase.

The Microsoft SQL Server Tuning Wizard makes suggestions about indexes you might want to add to the database. However, it uses the term “missing indexes,” which is misleading to anyone who might interpret this as a mandate. Each of these suggestions must be analyzed, balancing the performance trade-offs between inserting or updating data in a given table versus the variety of queries that might be made against a table. Indexes slow inserts and updates, while benefiting specific queries. There are a number of ways this analysis can be performed; the tuning wizard is just a first step. You should use SQL tracing tools and analyze the SQL demands that ZENworks Configuration Management is making on the database while it performs various functions, such as registration, bundle creation and deployment, policy enforcement, inventory, and so forth. After you have accurate information on what kind of load ZENworks Configuration Management is placing on the database, you can then add your indexes.

Tips for using Microsoft SQL as the ZENworks Database platform

Don't use Dynamic Ports

Dynamic Ports can automatically reassign connections to different ports. This can potentially cause problems with ZENworks install or post-installation tasks which in some cases rely on static ports such as 1433 to be active. To disable the dynamic ports, go into the MS SQL Configuration Manager and edit the ports used on the database for TCP/IP. The Dynamic Ports field should be left blank and a static port should be specified.

See the following links for more information:

Managing Large Transaction Logs

Large and growing transactions are an indication that a backup plan could be optimized. For example, it is not desirable to have transactions logs larger than the database. Large transaction logs are typically caused by three factors:

  1. Old transactions not being disregarded as part of the backup. When configuring the backup, it is important that old transactions from the backup point are not included as they have already been committed into the database.

  2. Infrequent backups

  3. Initial transaction log size initially set too low and the auto grow size percentage is not set large enough

Please see the following links and excerpts on this topic:

In rare cases, you might need to shrink the transaction log, b/c of a performance issue or otherwise, run:

--Shrink the transaction log a lot!!USE <database_name>;GODBCC SHRINKFILE(<database_name>_log, 1)BACKUP LOG <database_name> WITH TRUNCATE_ONLYDBCC SHRINKFILE(<database_name>_log, 1)GO

Where <database_name> is the name of the database on which you wish to shrink the transaction log.

For more information on this topic see:

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

Read Committed Snapshot

Read Committed Snapshot allows transactions to share locks to databases so you can allow two distinct processes to update the same table at the same time. If this setting is not enabled, large amounts of blocking can occur that decreases ZENworks performance.

To enable Read Committed Snapshot (no quotes on db name), run the following command from an SQL editor run:

USE '<database name>';GOALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;GOALTER DATABASE <database name> SET READ_COMMITTED_SNAPSHOT ON;GOALTER DATABASE <database name> SET MULTI_USER;GOTo verify that read_committed_snapshot has been successfully enabled, run:SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= '<database name>'

Index Fragmentation

Novell recommends that you defragment indexes every two weeks. You can do this by creating scripts or by using third-party utilities to easily defragment the entire database. You can use the following scripts to rebuild indexes and detect fragmentation in MS SQL server:

DBCC SHOWCONTIG DBCC DBREINDEX

If a table or index is more that 50% fragmented, you should re-index it to increase performance.

For more information, see:

Troubleshooting Microsoft SQL

Enabling Deadlock Tracing

If the database is experiencing high utilization caused by blocking or deadlocks, enable deadlock logging from an SQL editor, such as Microsoft SQL Management Studio, as follows:

dbcc traceon(1204,1222,-1)

See http://msdn.microsoft.com/en-us/library/ms188396.aspx, for more information.

NOTE:Do not perform maintenance during production or when servers are running. If this is not possible, you should use single user mode for long, resource-intensive operations.

ERRORLOG Location

The Microsoft SQL error log is found by default at: Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG

For more information see, http://msdn.microsoft.com/en-us/library/ms187885.aspx

Backing up Microsoft SQL Databases

Novell recommends that the Microsoft SQL Server Management Studio is used to manage backups, alternatively customers can create automatic scripts to do regular backups.

Taking a Backup

The following screen shots show the process of starting the backup process of a Microsoft SQL database using Microsoft SQL Server Management Studio.

For more information on backup best practices for Microsoft SQL server, see http://msdn.microsoft.com/en-us/library/ms175477.aspx

During the backup configuration, check the Ownership of tables and procedures to ensure that ZENworks tables are owned by dbo and not DB Owner. Ownership can change if you authenticate as a user other than the ZENworks user, then make changes.

Performance Tuning and Profiling a Microsoft Database

Microsoft SQL Server Management Studio provides the SQL Server Profiler application as shown below. This program will allow the administrator to see what SQL statements are being made in real-time and can be a useful tool in debugging database issues.

NOTE:Novell recommends that you consult with Novell Support before using the Database Engine Tuning Advisor for additional indexing of the ZENworks database.

3.4.7 Oracle

For performance and maintenance suggestions in an environment where Oracle is the Database Server hosting the ZENworks Configuration Management database, you must rely on the knowledge and expertise of the Oracle database administrator at the customer site. The individuals responsible for the day-to-day management of the Oracle infrastructure must be involved in the ZENworks Configuration Management deployment project from the beginning.

However, you should familiarize yourself with some of the administrative concepts about Oracle database management. The Oracle Database Documentation Web site contains information about Oracle 11g and should be used as a reference.

In addition, you should also familiarize yourself with some of the performance tuning concepts for an Oracle Database Server, especially the reference documentation on the SQL Performance Analyzer. This information is available in the Oracle Database Performance Tuning Guide.

Best practice information suggested by Oracle regarding backup and recovery can be found on the Oracle Web site. The onsite Oracle database administrators should be familiar with these concepts and procedures. They administrators simply need to know what additional information they need to back up as a result of the implementation of ZENworks Configuration Management.

The following section describes some key points to consider when configuring an Oracle database for use with ZENworks:

Shared vs Dedicated Server Modes

Most customers use Dedicated Server Mode as the default, and in some cases Shared connection mode can be used. At any given time a ZENworks Primary Server can have up to approximately 150 connections to the ZENworks database. Each connection with Oracle utilizes a certain amount of RAM, if the number of Primary Servers multiplied by the number of connections causes the required RAM to exceed what can be addressed by the operating system, such as with a 32bit OS which is limited 4GB, Shared Server Mode can be used to combat this. Shared Server Mode allows connections to be pooled and shared across a single memory allocation.

If the customer notices a large number of dedicated connections being rejected, use the following command to determine the status of connection rejection for a particular listener:

lsnrctl status

For more information, see http://www.dba-oracle.com/t_mts_multithreaded_servers_shared.htm

Important Log Locations

The alert.log is located at the path specified by the sql command:

show parameter BACKGROUND_DUMP_DEST

The listener.log is be found by checking the Listener Log File path after running lsnrctl status from a command prompt.

See the following locations for more information:

Character Encoding

Oracle supports some hybrid versions of UTF-8, but ZENworks requires true UTF-8 or UTF-16. The following commands report on the character encoding supported by the database:

  • select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

  • select value from nls_database_parameters where parameter = 'NLS_NCHAR_CHARACTERSET';

SQL Developer vs. SQL Plus

SQL Developer and SQL Plus are two tools traditionally used by the administrators to execute SQL commands to manage the Oracle server. SQL Developer allows you to maintain history and is easier to run queries from. You can find more information on SQL Developer at:

SQL Developer

Sqlplus can output to a file. For example, to output the information contained in the zZENServer table into a file at /tm/sqldata.dat, simply run the following command:

SQL>SPOOL /tmp/sqldata.datSQL>select * from zZENServer;SQL>SPOOL OFF

3.4.8 Database Sizing and Performance Considerations

Although it is not possible to accurately predict the size of ZENworks database, it is possible to identify the factors that influence database size and provide some basic guidelines. The factors that affect the size of the ZENworks database are as follows:

  • Number of users under management

  • Number of devices under management

  • Number of bundles

  • Number of ZENworks policies

  • Products that are enabled in the ZENworks Zone, such as Asset Management, Configuration Management, Patch Management, Endpoint Security Management, and ZENworks Reporting Server.

The following chart gives an indication of the types of database size to expect based on the numbers of users and devices in a ZONE with 100 bundles.

The following chart gives an indication of the types of database sizes to expect based on the number of bundles, devices, and users in a ZENworks Zone.

Disk space requirements are not the only consideration to make when designing the Database Server. Best practices for fault tolerance, maintenance, and performance need to be considered along with the general calculations for overall database size.

Most larger customers have Service Level Agreements that commit to minimal downtime and require robust storage capabilities. For sites with more than 10,000 devices, RAID (mirror with stripe) is recommended for the database, the transaction log, the TempDB, and the TempDB log. In fact, these four items need to be located on four separate LUNs (four separate disks or four separate logical arrays of disks). This addresses potential reliability issues.

Database Servers are very sensitive to disk performance. More small disks are always faster than a few large disks. This must be discussed while planning the database because a single 10 GB drive for a site with 10,000 devices might not perform adequately, although it might meet the database sizing formula. Ten smaller drives should perform much better.

Testing and monitoring are an essential part of database configuration. You must measure the throughput (MB/sec) that the application is demanding of the database, and size the disk array accordingly. In addition, the operating system and executables do not have high I/O requirements and can reside on a mirrored array (a single mirrored pair) to provide reliability with no added performance.

ZENworks Configuration Management requires a dedicated Database Server that is not shared with other database applications. This needs to be discussed during the design phase so that everyone involved in the project (especially the database administrator) is fully aware of the requirements. This might not be the case in very small implementations of ZENworks Configuration Management.