IT Management’s Top 10 List of DBMS Requirements


In the spirit of David Letterman’s Top
Ten lists, here is a look at what IT management requires from database
management systems (DBMSs).

10. Interoperability

With budgets shrinking, IT must sometimes
look to multiple vendors for outside software, hardware, and staffing
solutions. All these people and processes must work together. This includes
fast, transparent data transfer between DBMSs; standard connectivity across
hardware platforms; acceptance of non-DBMS-specific SQL across database
management systems; data availability across several differing application
types such as report writers and query tools; and easy migration of data, SQL,
and processes such as stored procedures from one DBMS to another.

9. Encryption support

Identity theft is the latest of
several security-related concerns that IT must address. One of the easiest
methods of dealing with data theft or misplacement of media or laptops is data
encryption. All data stored should be encrypted, with decryption only allowed
after a security check.

Most modern hardware has options for
hardware-based data encryption, making the process faster than relying on a
software solution.

8. Ease of Development and Integration of e-business applications

Most e-business applications and
application development kits are configured to create a distributed solution
that accesses a centralized database server. This usually takes the form of
applications written in java, C++ or some other modern programming language
that execute on a distributed platform (PC or server running Linux, Unix,
Windows, etc.). These applications must function well regardless of the
physical site of the data or the specific DBMS implementation.

IT must support these applications, which
are sometimes business-critical. Some tools they may employ include: Reduction
of SQL limitations; use of SQL syntax and functionality enhancements;
implementation of Unicode enhancements such as allowing multiple CCSIDs in
single SQL statements; utilizing XML support, perhaps even storing native XML
data in the DBMS; and realizing connectivity improvements such as dedicated
communication servers and network paths.

7. DBMS Autonomics and Process Automation

Budgets are tight, and the IT staff is
stretched thin. To reduce the overabundance of work you must have automation
processes in-place. These begin with automated database utility processing,
such as backups, reorgs, and statistics gathering. Along with alleviating the
workload of the DBAs, the results of these processes can be entered into a
spreadsheet to monitor various key indicators such as data growth and data
volatility.

On a more advanced level, some DBMSs
feature self-detecting, self-healing, and self-correcting processes called
autonomics. These allow the DBMS to be configured to automatically take one or
more actions based on certain criteria. For example, when a table becomes
disorganized, the DBMS can automatically reorganize it.

6. Scalability: Users, Transactions, Data

As the enterprise grows, so should
IT’s ability to support the enterprise; hence, it is in IT’s interest to
implement hardware and software architectures that can expand or scale as
increases in users, transactions, or data volume are encountered.

Such enhancements include SQL access
path "star join" support and performance; utilization of 64-bit
storage and hardware instructions; data sharing or clustering of DBMS servers;
and the offloading of main central processor work to specialized processors.

5. Application Performance

Most application performance can be
considered in terms of resource consumption and contention. Application code
and database accesses consume CPU, disk I/O, memory, and other resources. Even
application throughput in units of elapsed time can be considered a resource.
IT’s job is to balance and weigh resource usage, reduce contention, and
coordinate such usage across multiple applications and even multiple systems.

Various tools are available for
reducing resource usage, including: adding appropriate data indexes,
implementing data compression, gathering data distribution statistics,
configuring dynamic SQL query optimization, using a cost-based SQL access path
optimizer, and implementing high-performance DBMS options such as hash indexes,
I/O parallelism, or data prefetch.

4. Multi-Level Data Security

Here, IT must provide management with
the tools and reports necessary to satisfy application lines of business and
external and internal auditors that enterprise data access is limited to those
authorized to do so. This should be expanded to include column-level security;
that is, certain classes or groups of users will have access to only select
fields or columns in records or tables.

Data security systems should also
include automation tools (such as security group maintenance, detection of
improper access, and audit reporting) to assist the security administrator. Such
an administrator must be separate and distinct from the database administration
staff, and should report up through a different line of management in order to put
into practice the separation of duties required by most internal IT auditors.

3. Application Data Availability

IT is responsible for detection,
correction, and prevention of issues that affect data availability. These
typically take the form of data contention by competing processes, locking,
deadlock, and timeout issues, and concurrent access by utility processes such
as Load and Reorg with user data access.

These issues are separate and distinct
from simple performance, which deals with reduction in transaction elapsed
times, reduced CPU usage, and general reductions in resource usage.

2. Application-Level Data Recovery

Typical IT applications have ratings
associated with their recovery time objectives (RTOs), usually ranging from A+
(recover within 1-2 hours of a failure) to A, B, C, and other designations. IT
needs a coordinated, consistent, documented plan for recovering application
data, and it must be relevant for all sizes and types of recovery; from
recovering corrupt data in a single table to recovering a set of application
tables to a consistent point-in-time … to recovering all of IT.

Application data recovery has
implications for disaster recovery, internal and external audit, any external
bodies that govern IT, such as the way banks are subject to the Federal Reserve
and the Office of the Comptroller of Currency.

1. System-Level Data Recovery

DBMS software and related data must be
recoverable within a pre-defined recovery time objective. This is an absolutely
essential feature of the DBMS. It has implications for disaster recovery, as
well as for internal and external auditors, governing bodies, and other
high-level stakeholders.

Typical methods for accomplishing this
include data replication, a geographically-dispersed DBMS cluster, and perhaps
even hardware in a "hot standby" mode. Should a major power or
facility outage occur, the DBMS is usually one of the first applications to be
recovered (after hardware, operating system, data storage and network).

Summary

Some may argue with the above item
ordering. Perhaps your favorite requirement was left out, or relegated to a
lower importance?

One argument I often hear about #1 and
#2 (recovery) goes like this: "Our most important requirement is
Performance! If our applications run slowly, we will lose customers!".

Perhaps; but, consider: what would you
prefer? A system where 5 percent of transactions run extremely slowly? Or one
where 5 percent of the data was invalid, inconsistent, or unrecoverable?

References

IBM Autonomics

DRII — The Institute for Continuity Management

Information Systems Audit and Control Association

Information Security — Wikipedia

»


See All Articles by Columnist

Lockwood Lyon

Lockwood Lyon
Lockwood Lyon
Lockwood Lyon is a systems and database performance specialist. He has more than 20 years of experience in IT as a database administrator, systems analyst, manager, and consultant. Most recently, he has spent time on DB2 subsystem installation and performance tuning. He is also the author of The MIS Manager's Guide to Performance Appraisal (McGraw-Hill, 1993).

Latest Articles