Supporting distributed databases Database administration in a distributed environment is manageable with this handy guide

September  1995

"Controlling and supporting a central, mission-critical database is hard enough. How do you support distributed, mission-critical databases?" The answer is simple: Policies. A distributed environment does not have to mean chaos. This month, we outline our database administration roles, responsibilities, and physical policies. (1,600 words)

Mail this article to a friend

A question we hear often from administrators of mainframe-based databases is "Controlling and supporting a central, mission-critical database is hard enough. How do you support distributed, mission-critical databases?"

Our answer: Policies.

The inevitable follow-up question is, "Can you send me your policy manual?"

While we can't share the manual itself, this month we outline roles, responsibilities, and considerations of physical database administration in distributed environments. Consider this our database policy manual in brief. Since we've presented the responsibilities portion in a handy outline form, we've split this section off as a responsibilities sidebar.

Before getting into the meat of this month's column, we need to remind you we've discussed data center organization before. (See "Getting organized," our July 1994 column in Advanced Systems magazine.) The Database Administration (DBA) Group reports to the top Information Technology (IT) management within the Data Center, along with Systems Programming, Production Control, and Computer Operations. The DBA Group is responsible for supporting the database functions of applications and takes ownership of database servers and software.

We'd like to thank Linda Flores for her contributions to this column. Flores served as DBA's manager during Sun's recent rightsizing effort.

`Management' means...
Database Administration has typically carried two meanings:

1. The design, definition, and support of the logical database.

2. The design, definition, and support of the physical database.

In the age of monolithic mainframe computing, these roles were filled by a single person or staff. Database designs were hierarchical. Relationships between objects were defined in a parent-child structure. Together, this allowed for a centralized computing environment with centralized staff.

As the technology moves from holy glass houses to scattered secular server rooms, the purpose of database administration also changes. Relational databases reign today. This requires design and definition issues to be an integral part of application development. (We expect this to continue with object-oriented technology.) As such, the logical database administration tasks fall within a development group, and the physical database administration tasks belong to an operations/support organization.

The physical
Databases should be distributed to allow critical information to be located as closely to users as possible. This allows the production environment to be more reliable due to the removal of multiple points of failure.

For example, if users work in Boston with their server in Colorado, their data traverses myriad network connections. A network failure in Chicago means transactions will either be queued or re-routed through a less expedient path.

Long-distance client/server database design can also fall prey to a more insidious malady -- network saturation. An overburdened WAN dampens the response time to the user. This, of course, exposes the IT department to accusations of undermining that location's success.

So how do you support distributed databases inexpensively? You got it -- policies. Servers need standardized configuration to allow high DBA-to-server ratios. In the event of database outages or errors, it is key that a DBA can access the machines quickly, navigate through the file system structure and obtain information necessary to take corrective action lickety-split.

If the directory structure for the production machines is not established according to guidelines, problem diagnosis and resolution will take longer than normal. We recommend the following guidelines for database servers:

• The HOME environment should be located under /home file system. The standard is that if the RDBMS is Sybase, the file system would be /home/sybase and an Oracle RDBMS would live in /home/oracle, etc.

• All RDBMS partitions are owned by RDBMS user login and not readable by group or world.

• RDBMS software resides in its own subdirectory. Examples: Sybase version 4.9.1 lives in /home/sybase/4.9.1, while Oracle 7.0.15 would be found in /home/oracle/7.0.15.

• Database dump files (required for recovery) are kept in the dbdump directory, which is in /home.

• No database partitions should be found on the system disk. This allows technical support personnel (or OS administrators) to recover from system disk failure without bothering the database administrators.

• Administration utilities are distributed from a centralized development group and can be found in a subdirectory within the RDBMS home environment. Example: Utilities for administering a Sybase system would be distributed to /home/sybase/dist

• Localized utilities and parameters are defined and located in a subdirectory within the RDBMS home environment. Example: Log files reflecting utility processing for Sybase live in /home/sybase/local/log.

• cron programs run routine Database Administration tasks. We keep program names and schedules in the crontab file for the RDBMS user login (i.e., sybase, oracle, etc).

A DBA's role
A good database administrator goes to sleep thinking about tuning and wakes up with thoughts of performance. Although most performance gains are found in clever and logical application and database design, a crafty DBA can optimize performance by balancing a database across physical resources.

For example, allocating data space and index space across separate drives and controllers can remove processing bottlenecks. But to accomplish this tuning, a DBA needs the tools to collect performance statistics.

There are programs available today that track logical and physical writes and reads on an object by object basis for our Oracle databases. We are evaluating similar products for Sybase. We'll discuss our findings in an upcoming column.

Resources

• "Getting organized"
  /sunworldonline/asm-07-1994/asm-07-unix.html
• A list of other Unix Enterprise
  /sunworldonline/common/swol-backissues-columns.html#unix

If you have technical problems with this magazine, contact webmaster@sunworld.com

URL: http://www.sunworld.com/swol-09-1995/swol-09-unix.html
Last modified:

DBMS software support

• Install and upgrade Database Management System software as new releases and maintenance releases become available. All changes to production servers are governed by Change Control procedures. Upgrades to DBMS software will be coordinated with the Applications Support staff, when needed. Release level consistency will be maintained across all affected/mod environments; support, staging, and production.

• Install DBMS patches supplied by the vendor as needed and in coordination with Systems Programming and the Application Support staff.

• Provide consultation support to Systems Programming regarding Operating System release implementation.

• Give recommendations for software products and utilities used to enhance DBMS usage and throughput, such as DBE. Implementation of such products will be coordinated with Systems Programming and the Applications Support staff.

• Provide 24-hour, seven day a week, 365 days a year, on-call support for production DBMS problem resolution.

Database support

• Responsible for physical integrity of the production databases, includes regularly scheduled verification procedures and correction of errors.

• Responsible for verification and correction of errors on non-production status databases on an as-requested basis.

• Responsible for designing procedures for business continuation planning (i.e., backup and recovery) as required by system availability agreements for each application.

• Provide database support for disaster recovery in conjunction with Systems Programming

• Perform database modification function against production applications; such as alter table. Support for non-production status applications will be as requested by the support staff.

• Maintain system runtime configuration information to define DBMS's execution.

• Assist Application Support staff in providing device configuration data (hardware and software) for Systems Programming/UPA agreement; such as disk usage and memory.

• Monitor informational/error logs for messages specific to system operation. Responsibility for monitoring will be shared with Application Support and Security Administration.

• Responsible for maintaining and providing dba-level access as required for all production status applications on production and support/staging environments. Dba-level access in a development environment will be shared with the development staff.

• Responsible for database performance tuning.

• Responsible for monitoring physical capacity.

• Responsible for monitoring database user access.

• Provide problem resolution support for DBMS-related errors.

Vendor interface

• Act as interface with DBMS software vendors and other internal support groups (ie. Sybase contact).

Application support

• Provide consultation support for logical database design for database applications in production and development environments.

• Provide consultation and development support for the physical database design for database applications in production and development environments.

• Enforce conformance to database and Data Center standards in application systems prior to production implementation.

• Provide consultation for selection of the DBMS.

• Provide consultation for system configuration of new applications; such as use of raw partitions vs. file system, client/server architecture.

• Support impact analysis activity related to application design and modification.

• Provide problem resolution support for DBMS application related errors.

Security/access support

• Will consult with Application Developers in architecting and designing security methodologies to establish end user access to the database. The use of standard access methods for application-controlled access will be encouraged as appropriate.

Request response

• Each request made to the DBA staff will be reviewed and planned on an as-requested basis. Efforts will be made to meet the application/project time requirements