Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

SQL etc

Posted Aug 11, 2010

IT Management's Top 10 List of DBMS Requirements

By Lockwood Lyon

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


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?


IBM Autonomics
DRII -- The Institute for Continuity Management
Information Systems Audit and Control Association
Information Security -- Wikipedia

» See All Articles by Columnist Lockwood Lyon

SQL etc Archives

Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM