Database

Administration
Chapter 2: Creating the Database Environment
 CONTENTS DBMS
DBMS Clustering
Architectures
Installing
DBMS
Choosing DBMS

DBMS Upgrading
Organization’s DBMS
Strategy
Database Standards
and Procedures
 Organization’s DBMS Strategy
• The decision to purchase and install a new DBMS is Sometimes driven by a business need or a new

application.

• Sometimes a new DBMS product is purchased and installed without first examining if the application

could be successfully implemented using an existing DBMS.

• There are other reasons for the existence of multiple DBMS platforms in a single organization:

✔ the company purchased a commercial off-the-shelf application package that does not run on any

of the current DBMS platforms.

 Organization’s DBMS Strategy (cont.)
✔ buy a new DBMS is driven by the desire to support the latest and greatest technology For example:
moving from a hierarchic (IMS) or CODASYL (IDMS) database model to the relational model
deployed DB2.

• when a new DBMS is installed, old applications and databases are usually not migrated to it, The old

DBMS remains and must continue to be supported (removal can be difficult).

• The DBA group should be empowered to make the DBMS decisions for the organization.

• No business unit should be allowed to purchase a DBMS without the permission of the DBA group.

• Business politics often work against the DBA group because it frequently possesses less organizational

power than other business executives.

 Choosing DBMS
• Tier-1 DBMS products:

✔ DB2

✔ Oracle

✔ SQL Server

• Tier-2 DBMS products:

✔ Informix Dynamic Server

✔ Adaptive Server Enterprise

✔ Teradata

✔ MySQL

• Tier 1 represents the most heavily implemented and supported products on the market.
 Choosing a DBMS (cont.)
• Another major player is Microsoft SQL Server, but only for Windows platforms, DB2 and Oracle run

on multiple platforms.

• Tier 2 are quality DBMS products but their installed base is smaller, these products are engineered

and marketed for niche purposes.

• When choosing a DBMS, be sure to consider each of these factors:

1. Operating system support: Does the DBMS support the operating systems in use at your
organization, including the versions that you are currently using and plan on using?

2. Type of organization: Take into consideration the corporate philosophy.

 Choosing a DBMS (cont.)
3. Benchmarks: What performance benchmarks are available from the DBMS vendor and other users of
the DBMS?

❑ The Transaction Processing Performance Council (TPC) publishes official database performance
benchmarks that can be used as a guideline for the basic overall performance of many different
types of database processing.

4. Scalability: Does the DBMS support the number of users and database sizes you intend to implement?

5. Availability of supporting software tools: Are the supporting tools you require available for the
DBMS?

❑ These items may include query and analysis tools, data warehousing support tools, database
administration tools, backup and recovery tools, performance-monitoring tools, capacity-planning
tools, database utilities, and support for various programming languages.
 Choosing a DBMS (cont.)
6. Technicians: Is there a sufficient supply of skilled database professionals for the DBMS?

❑ DBAs, technical support personnel (system programmers and administrators, operations

analysts, etc.) and application programmers.

7. Cost of ownership: What is the total cost of ownership of the DBMS?

❑ DBMS vendors charge wildly varying prices for their technology.

8. Release schedule: How often does the DBMS vendor release a new version?

❑ Depending on your approach, If you want cutting-edge features, a rapid release cycle is good.
However, if your shop is more conservative, a DBMS that changes frequently can be difficult to
support.
 Choosing a DBMS (cont.)
9. Reference customers: Will the DBMS vendor supply
current user references?

❑ How is support? Does the vendor respond well to

problems? Do things generally work as advertised?
What is the quality of new releases?
 DBMS Architectures
Level 01 Level 03
Enterprise DBMS Personal DBMS

Level 02 Level 04
Departmental DBMS Mobile DBMS
 Enterprise DBMS
Enterprise DBMS

❑ is designed for:

✔ Scalability.

✔ High performance.

❑ Must be capable of

✔ Supporting very large databases.

✔ A large number of concurrent users.

✔ Multiple types of applications.

 Departmental DBMS

Departmental DBMS:

❑ Sometimes referred to as a workgroup DBMS.

❑ Supports small to medium sized workgroups within an organization.

❑ Runs on a UNIX, Linux, or Windows server.

❑ Hardware and software upgrades can allow a departmental DBMS to tackle tasks that previously could
be performed only by an enterprise DBMS.

❑ The steadily falling cost of departmental hardware and software components further contributes to
lowering the total cost of operation and enabling a workgroup environment to scale up to serve the
enterprise
 Personal DBMS

Personal DBMS:

❑ is designed for a single user, typically on a low- to medium-powered

PC platform.

❑ is suitable only for very small-scale projects and should never be

deployed for multiuser application

examples of personal database software:

✔ Microsoft Access

✔ SQLite

✔ FileMaker
 mobile DBMS

The mobile DBMS:

❑ is a specialized version of a departmental or enterprise DBMS.

❑ is designed for remote users who are not usually connected to the network.

❑ enables local database access and modification on a laptop or handheld

device.

❑ provides a mechanism for synchronizing remote database changes to a

centralized enterprise or departmental database server.

 DBMS Clustering

❑ clustering is the use of multiple “independent” computing

systems working together as a single, highly available
system.

❑ A modern DBMS offers clustering support to enhance

availability and scalability.

❑ The two architectures for clustering are shared-disk and

shared-nothing
 Shared-nothing clustering
❑ each system has its own private resources (memory, disks,
etc.).

❑ The clustered processors communicate by passing

messages through a network that interconnects the
computers. In addition, requests from clients are
automatically routed to the system that owns the
resource.

❑ Only one of the clustered systems can “own” and access a

particular resource at a time.

❑ In the event a failure occurs, resource ownership can be

dynamically transferred to another system in the cluster.

❑ main advantage is scalability.

 Shared-disk
❑ share resources (memory, disks, etc.).

❑ The clustered processors can directly address all

the disks.

❑ does not scale, is better suited to

large-enterprise processing in a mainframe
environment.

❑ requiring for applications or workloads that are

very difficult to partition and Applications with
heavy data update
Comparison of Shared-Disk and Shared-Nothing Architectures

Shared -disk Shared -nothing

Quick adaptability to Can exploit simpler, cheaper

changing workloads hardware

High availability Almost unlimited scalability

Performs best in a heavy Works well in a high-volume,

read environment read-write environment

Data need not be Data is partitioned across the

partitioned cluster
 Hardware issues

❑ When establishing a database environment for application development, selecting the

DBMS is only part of the equation. The hardware and operating system on which the
DBMS will run will greatly impact the reliability, availability, and scalability (RAS) of the
database environment.
 Cloud Database Systems
❑ A cloud database system delivers DBMS services over the Internet.

❑ a cloud provider to store and manage your data in return for minimizing database administration and
maintenance cost and effort.

❑ Utilizing cloud database systems can enable organizations, especially smaller ones without the
resources to invest in an enterprise computing infrastructure, to focus on their business instead of
their computing environment

❑ the downside is that your data is now stored and controlled by an external agent—the cloud provider.
 Installing the DBMS
❑ Once the DBMS has been chosen, you will need to install it.

❑ You will need to understand the DBMS requirements and prepare

the environment for the new DBMS.

❑ The very first thing to do when you install a DBMS for the first
time is to understand the prerequisites.

❑ Every DBMS comes with an installation manual or guide containing a

list of the operating requirements that must be met for the DBMS
to function properly.
 Installing the DBMS (cont.)

❑ Examples of prerequisites include:

✔ Ensuring that an appropriate version of the operating system is being used

✔ Verifying that there is sufficient memory to support the DBMS

✔ Ensuring that any related software to be used with the DBMS is the

proper version and maintenance level

 Hardware Requirements

Every DBMS :

❑ has a basic CPU requirement, meaning a CPU version and minimum processor speed

required for the DBMS to operate.

❑ Additionally, some DBMSs specify hardware models that are required or unsupported.
 Storage Requirements
A DBMS will use disk storage for the indexes to be defined on the databases as well as for the following
items:

✔ The system catalog or data dictionary used by the DBMS to manage and track databases and
related information. The more database objects you plan to create, the larger the amount of storage
required by the system catalog.

✔ Log files that record all changes made to every database. These include active logs, archive logs,
rollback segments, and any other type of change log required by the DBMS.

✔ Start-up or control files that must be accessed by the DBMS when it is started or initialized.

✔ Work files used by the DBMS to sort data or for other processing needs.
 Storage Requirements (cont.)

✔ Tapes are also required for tasks such

as database backups and log offloading.

When the active log file fills up, the log

records must be off-loaded to an archive
log either on disk or on tape
 Memory Requirements

❑ A DBMS requires memory for basic functionality and will use it for most internal processes such as :

✔ maintaining the system global area.

✔ performing many DBMS tasks.

❑ A DBMS requires a significant amount of memory to cache data in memory structures in order to avoid

I/O. Reading data from a disk storage device is always more expensive and slower than moving the data
around in memory.
 Memory Requirements (cont.)

❑ DBMS uses a memory structure called a

buffer pool or data cache to reduce
physical I/O requests.

❑ By caching data that is read into a

buffer pool, the DBMS can avoid I/O
for subsequent requests for the same
data, as long as it remains in the buffer
pool.
 Configuring the DBMS

❑ Each DBMS also provides a method to change the system parameters once

the DBMS is operational.

❑ Sometimes you can use DBMS commands to set the system’s parameters.

❑ Sometimes you must edit a file that contains the current system

parameter settings.
 Connecting the DBMS to Supporting Infrastructure
Software

❑ Part of the DBMS installation process is the connection of the DBMS to

other system software components that must interact with the DBMS.

❑ Typical infrastructure software that may need to be configured to work

with the DBMS includes networks, transaction processing monitors,

message queues, other types of middleware, programming languages,
systems management software, operations and job control software, Web
servers, and application servers.
 DBMS Upgrading

• Major releases cycle for DBMS software is 18 to 24 months

• Constant bug fixes and maintenance updates are delivered in between

• Keeping DBMS software up-to-date can be a full-time job

• DBA must develop an upgrading approach to minimize business disruption

 Version Vs Release

A major concern, with many changes

Version and new features

Typically minor, with fewer changes

Release and not as many new features

Usually DBMS vendors increase prices for versions, but not necessarily for
releases
 Pros and cons of upgrading
Advantages Disadvantages

New features and functionality Business operations disruption

Enhanced performance and availability Having to convert database structures

Purchased application may require New releases may cause features and syntax
specific DBMS version to enable being used to be deprecated
specific functionality
DBMS vendors provide better support Supporting SW products may lack immediate
and respond faster for problems in new support for a new release
releases

Cost savings, as DBMS vendors may High cost of an upgrade

charge extra if running multiple
versions
Providing a consistent environment for A new DBMS release may generate SQL access
development and implementation paths that perform worse than before
 Factors to determine when to upgrade

DBMS problems are Developers and users

fixed in the new know how new
release features work

Cost and effort of Infrastructure is

app development is prepared to support
minimized by the new the new DBMS
release functionality functionality
 Complexity of the DBMS environment
The more complex the database environment, the more difficult it will be to
upgrade to a new DBMS release

types of
Size of the apps being
environment supported

Location of the
database
servers
 Technology Style

When it comes to developing new products and technology, every business has characteristics that
show its preferred method.

Organizations have been divided into three main categories:

∙ Type A Enterprise: It is more likely to take risks with new and unproven technologies to gain

a competitive advantage.

∙ Type B Enterprise: Is less willing to take risks but will implement new technology once they

have been tested properly by others.

∙ Type C Enterprise: very conscious of cost and averse to risk, will lag behind the majority

when it comes to trying new technology.

 DBA Staff Skill Set

• Upgrading the DBMS is easier if your DBA staff is highly skilled and/or experienced.

The risk of an upgrade increases as the skills of the DBA staff decreases.

• If your DBAs are not highly trained or have never upgraded a DBMS to a new version,

consider adding consultants to your DBA staff for the update; therefore, you will be
certain that your upgrade goes as smoothly as possible, and the DBA staff will be
better prepared to handle the future upgrades alone.

• If consultants are required, be sure to include their contracting cost in the DBMS

release upgrade budget. The budget should allow you to retain the consultants until
all production database environments are stable
 Platform Support

When a DBMS vendor produces a new release of its product, not all
platforms and operating systems are immediately supported. The DBMS
vendor usually first supports the platforms and operating systems for
which it has the most licensed customers.
 Fallback Planning
Every new DBMS version or release should include a handbook that lists the new features and
explains how to go back to an earlier version of the DBMS. If the upgrade has a bug, performance
issues result, or other issues show up during or right away after release, you might need to go back
to an earlier DBMS release. Remember that not every new DBMS release offers fallback as an
option.
 Data Administration Standards

If a DA group exists within your organization, they should develop a basic data administration standards
guide to outline the scope of their job role

The data administration standards should include the following items:

•Guidelines for establishing data ownership and stewardship

•Rules for data creation, data ownership, and data stewardship

•Metadata management policy

•The organization’s goals with regard to creating an enterprise data model

 Database Administration Standards

∙ A basic set of database administration standards should be established to ensure the ongoing success

of the DBA function

For example

✔ standards can be developed that outline how requests are made to create a new database or make
changes to existing databases.

✔ Standards can establish backup and recovery proceduresm (including disaster recovery plans).

∙ Although the DBA standards will be most useful for the DBA staff, the application development staff

will need them to learn how best to work with the DBA staff

∙ The more the application programmers understand the nuances of the DBMS and the role of the DBA,

the better the working relationship between DBA and development will be—resulting in a more efficient
database environment
 System Administration Standards

Once again, standards for system administration or systems programming are required only if your
organization separates the SA function from the DBA function. System administration standards are needed
for many of the same reasons that DBA standards are required. Standards for SA may include

• Bug fix and maintenance practices.

•DBMS installation and testing procedures.

•DBMS storage, usage, and monitoring procedures.

 Database Security Standards

The DBA group often applies and administers DBMS security. However, at some shops the
corporate data security unit handles DBMS security.

A resource outlining the necessary standards and procedures for administering database security
should contain the following information:

oDetails on what authority to grant for specific types of situations, for example, if a program is

being migrated to production status, what DBMS authorization must be granted before the
program will operate successfully in production
THANKS!