Click on our Sponsors to help Support SunWorld
Supporting distributed databases
Database administration in a distributed environment
is manageable with this handy guide
"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)
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
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
Database Administration has typically carried two meanings:
- The design, definition, and support of the logical database.
- 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
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
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
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
- 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
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
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
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.
Click on our Sponsors to help Support SunWorld
If you have technical problems with this magazine, contact
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.
- Responsible for physical integrity of the production databases,
includes regularly scheduled verification procedures and correction
- 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
- 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
- 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.
- Act as interface with DBMS software vendors and other
internal support groups (ie. Sybase contact).
- Provide consultation support for logical database design
for database applications in production and development
- 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
- 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.
- 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