The primary considerations about database scalability include:
How many devices are you managing? If you need to scale more than 1,500 devices, you might want to consider putting the database in either Oracle or Microsoft SQL Server.
Can you leverage clustering technologies to achieve fault tolerance for the database? This ensures that the database is always available.
The following table lists the number of devices 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 |
|
Sybase (remote) |
As many as 1,500 |
|
Microsoft SQL Server 2005 |
1,500 to 40,000 |
|
Oracle 10g |
1,500 to 40,000 |
Ensure that you use the vendor database backup tools to regularly back up the database. This ensures that if the database is lost, you can restore from a backup and get back to operations quickly.
The following sections highlight recommended best practices when managing and maintaining your Database Server:
One of the most important aspects of Sybase database maintenance is regular backups of the database files. You can use zman commands to back up the Sybase database. For more information, see Database Commands
in the ZENworks 10 Configuration Management Command Line Utilities Reference.
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.
Microsoft SQL Server 2005 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 when managing Microsoft SQL Server 2005:
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 scenario is a contributing factor to lag, escalating locks, and eventual deadlocks.
A good overview of the Maintenance Plan Wizard can be found on the MSSQLTips Web site.
Microsoft also offers the SQL Server 2005 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.
Here are two examples we have seen reported on a ZENworks Configuration Management installation on SQL Server 2005:
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. This 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.
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 10g 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 simply need to know what additional information they need to back up as a result of the implementation of ZENworks Configuration Management.
As a general rule of thumb, Novell has seen that the database size increases at a rate of approximately 1 GB per one thousand (1,000) devices in the Management Zone.
However, this is 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 smaller disks are always faster than fewer larger 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.