Transcript Database Administration

COIT 342




A database is an organized store of data wherein
the data is accessible by named data elements (for
example, fields, records, and files).
Database: The collection of files and their internal
structures that contain data.
A DBMS (Database Management System)is
software that enables end users or application
programmers to share and manage data, example
DB2, SQL server, Oracle …..etc.
DBMS provides a systematic method of creating,
updating, retrieving, and storing in formation in a
database.
DBMS provides a systematic method of creating,
updating, retrieving, and storing information in
a database.
 A DBMS is also responsible for:1) data integrity,
2) data security,
3) data access control and optimization,
4) automated rollback, restart, and recovery.
Figure 1-2 shows the relationship between a DBMS
and a database.





We will talk about what a DBA is?
why one may or may not be needed?
and how to determine what is appropriate for
an environment.
we will also learn what other skills and roles
may be needed in a DBA.


Database Administration: Short and long-term
management of a database to maximize
performance and minimize downtime
The DBA is responsible for designing and
maintaining an enterprise’s databases



Fireman, Policeman, Surgeon, Shepherd
Fireman because s/he responds to problems
whenever they occur. Tends to be first line of
defense.
Policeman because s/he is responsible for
setting up and maintaining security,
monitoring resource utilization, and
scheduling intensive jobs or outages


Surgeon because s/he needs to provide both
preventative “medicine” as well as emergency
surgery to keep the platform healthy
Shepherd because s/he needs to work with the
development and user community, attempting
to guide them toward the best “pastures” of
platform usage




Frequently first point of contact for database
problems
Expected to provide expert advice and guidance
in technology, especially to developers
Needs to work with management and
engineering to build optimal configurations
Emergency support during a crisis








Knowledgeable, but knows own limitations
Responsive 24x7; answers phone & pager
Really enjoys the work.
Can multitask effectively
Can work with little sleep; some nocturnal
Can shoot from the hip, yet follows plans
Communicates and documents well
Natural problem solver, detail oriented




Database administration is rarely approached as
a management discipline.
The term discipline implies a plan, and
implementation according to that plan.
When database administration is treated as a
management discipline, the treatment of data
within your organization will improve.
It is the difference between being reactive and
proactive DBA.




The reactive DBA functions more like a
firefighter than an administrator; he attempts to
resolve problems only after problems occur.
In contrast, the proactive DBA implements
practices and procedures to avoid problems
before they occur.
A proactive database administrator develops
and implements a strategic blueprint for
deploying databases within the organization.
This plan should address all phases of the
application development life cycle.



A data specialist, usually the DBA, should be
involved during each phase of the cycle, as
shown in Figure 1-3.
During the initiation and requirements
gathering phase, the DBA must be available to
identify the data components of the project.
He can help to determine if the required data
already exists elsewhere in the organization or if
the data is brand new.





During the analysis and design phases, the rudimentary
data requirements must be transformed into a
conceptual and logical data model.
Before development can begin, the logical data
model must be translated to a physical database
design.
that can be implemented using a DBMS such as
Oracle or DB2.
Sample data must be populated into the
physical database to allow application testing.
Furthermore, the DBA must develop and
implement a process to refresh test data to
enable repeatable test runs.




DBA responsibilities
When the application moves from development
to operational status, the DBA ensures that the
DBMS is prepared for the new workload.
This preparation includes implementing
appropriate security measures, measuring and
modifying the storage and memory
requirements for the new application,
and anticipating the impact of the new
workload on existing databases and
applications.
The DBA is also responsible for migrating the
new database from the test environment to the
production environment.




DBA responsibilities
When the application moves from development
to operational status, the DBA ensures that the
DBMS is prepared for the new workload.
This preparation includes implementing
appropriate security measures, measuring and
modifying the storage and memory
requirements for the new application,
and anticipating the impact of the new
workload on existing databases and
applications.
The DBA is also responsible for migrating the
new database from the test environment to the
production environment. ….. Continue



DBA responsibilities….continue
While the application is operational, the DBA
performs a host of duties including assuring
availability, performance monitoring, tuning,
backup and recovery, and authorization
management.
When maintenance is requested, the DBA
becomes engaged in the entire process once
again, from requirements gathering to
implementation
Finally, when the application reaches the end of
its useful life, the DBA must help to determine
the final status of the data used by the
application.

DBA responsibilities….continue
A good DBA is integral to the entire application
development life cycle.
Database, Data, and System Administration





There are a business aspects and the technical
aspects of data.
The business aspects of data are aligned with data
administration,
technical aspects are handled by database
administration.
many organizations combine data administration
into the database administration role.
Data administration separates the business aspects
of data resource management from the technology
used to manage data;




Data Administrator
The data administrator (DA) is responsible for
understanding the business lexicon and
translating it into a logical data model
Referring back to the ADLC( Application
Development Life Cycle), the DA would be
involved more in the requirements gathering,
analysis, and design phase,
the DBA also would be involved in the design,
development, testing, and operational phases.
The data administrator can be thought of as the
Chief Data Officer of the corporation.
Data Administrator






A large responsibility of the DA staff is to ensure
that data elements are documented properly,
usually in a data dictionary or repository.
This is another key differentiation between a DA
and a DBA.
The DA focuses on the repository, whereas the
DBA focuses on the physical databases and DBMS.
Furthermore, the DA deals with metadata, as
opposed to the DBA, who deals with data.
Metadata is often described as data about data;
more accurately, metadata is the description of the
data and data interfaces required by the business.

One of the biggest contributions of data
administration to the corporate data asset is the
creation of data models. A conceptual data
model outlines data requirements at a very
high level. A logical data model provides indepth details of data types, lengths,
relationships, and cardinality. The DA uses
normalization techniques to deliver sound data
models that accurately depict the data
requirements of an organization.
•
•
•
•
Database Administrator
Database administration is the focus of this entire
course ,
The first duty of the DBA is to understand the
data models built by the DA and to communicate
the model to the application developers and other
appropriate technicians.
The logical data model is the map the DBA will
use to create physical databases.
The DBA should not rely on the DA for the final
physical model any more than a DA should rely
on a DBA for the conceptual and logical data
models. Figure 1-4 depicts this relationship.

Figure 1-4. DBA vs. DA
The DBA is the conduit for communication between the DA team and the
technicians and application programming staff.





Some organizations, usually the larger ones, also
have a system administrator (SA) that impacts
DBMS implementation and operations.
The SA is responsible for the installation and
setup of the DBMS.
The SA typically has no responsibility for
database design and support.
Instead, the DBA is responsible for the databases
and
the SA is responsible for DBMS installation,
modification, and support.
•
•
•
•
System Administrator
The system administrator responsibility to ensure
that
1- the IT infrastructure is operational for database
development by setting up the DBMS
appropriately,
2- applying ongoing maintenance from the DBMS
vendor.
3- coordinating migration to new DBMS releases
and versions.
Database Administrator
DBA Tasks
• Database design
• Performance monitoring and tuning
• Database availability
• Security
• Backup and recovery
• Data integrity
• Release migration
Database design
• DBA must understand the theory and implementation of the
relational database management system (RDBMS) he’s using to
create the database.
• Database design - needs understanding of conceptual and logical
data modeling techniques.
- create and interpret entity-relationship diagrams is essential for
designing a relational database.
• The DBA must ensure that the database design and
implementation will enable a useful database for the applications and
clients that will use it.
•A poor relational design can result in poor performance.

Users demand information from the database,
and the DBMS supplies this demand for
information. The rate at which the DBMS
supplies the information can be termed
database performance
Performance Monitoring and Tuning
Five factors influence database performance:

Workload

Throughput

Resources

Optimization

Contention



Workload
The workload that is requested of the DBMS
defines the demand. It is a combination of online
transactions, batch jobs, ad hoc queries, data
warehousing, analytical queries, and commands
directed through the system at any given time.
Sometimes workload can be predicted, but at other
times it is unpredictable. The overall workload has
a major impact on database performance.


Throughput
Throughput defines the overall capability of
the computer hardware and software to
process data. It is a composite of I/O speed,
CPU speed, parallel capabilities of the machine,
and the efficiency of the operating system and
system software




Optimization
Optimization refers to the analysis of database
requests with query cost formulas to generate
efficient access paths to data.
Resources
hardware and software tools at the disposal of the
system are known as the resources of the system.
Examples include the database kernel, disk space,
cache controllers, and microcode.


Contention
When the demand (workload) for a particular
resource is high, contention can result.
Contention is the condition in which two or
more components of the workload are
attempting to use a single resource in a
conflicting way (for example, dual updates to
the same piece of data). As contention
increases, throughput decreases.

Database performance can be defined as the
optimization of resource usage to increase
throughput and minimize contention
•
Whenever performance problems are encountered
by an application that uses a database, the DBA is
usually the first one called to resolve the problem.
•
An effective performance monitoring and tuning
strategy requires not just DBMS expertise but
knowledge outside the scope of database
administration
Availability
• Ensure that database information is always available to all
users in a form that suits their needs.
• The faster the DBA can perform administrative tasks, the
more available the data becomes.
• The DBA must understand all of these aspects of availability
and ensure that each application is receiving the correct level
of availability for its needs.
Security
• Once the database is designed and implemented, programmers
and users will need to access and modify the data.
• However, to prevent security breaches and improper data
modification, only authorized programmers and users should
have access.
• It is the responsibility of the DBA to ensure that data is
available only to authorized users.
Backup and Recovery
• The DBA must be prepared to recover data in the event of a
problem.
• The majority of recoveries today occur as a result of application
software error and human error.
• The DBA must be prepared to recover data to a usable point, no
matter what the cause, and to do so as quickly as possible.
• To be prepared for any type of recovery, the DBA needs to develop a
backup strategy to ensure that data is not lost in the event of an error
in software, hardware, or a manual process.


The first type of data recovery that usually comes to
mind is a recover to current, usually in the face of a
major shutdown. The end result of the recovery is that
the database is brought back to its current state at the
time of the failure. Applications are completely
unavailable until the recovery is complete.
Another type of traditional recovery is a point-in-time
recovery. Point-in-time recovery usually deals with an
application-level problem. Conventional techniques to
perform a point-in-time recovery remove the effects of
all transactions since a specified point in time. This can
cause problems if valid transactions occurred during
that timeframe that still need to be applied.


Transaction recovery is a third type of recovery; it
addresses the shortcomings of the traditional types
of recovery: downtime and loss of good data.
Thus, transaction recovery is an application
recovery whereby the effects of specific
transactions during a specified timeframe are
removed from the database. Therefore, transaction
recovery is sometimes referred to as application
recovery.
To be prepared for any type of recovery, the DBA
needs to develop a backup strategy to ensure that
data is not lost in the event of an error in software,
hardware, or a manual process.
Data Integrity
•A database must be designed to store the correct data in the
correct way without that data becoming damaged or corrupted.
To ensure this process, the DBA implements integrity rules
using features of the DBMS.
• Three aspects of integrity :
 Physical
 Semantic
 Internal.
Physical integrity




Physical issues can be handled using DBMS features
such as domains and data types.
Referential constraints are used to specify the
columns that define any relationships between
tables. Referential constraints are used to
implement referential integrity
Unique constraints ensure that the values for a
column or a set of columns occur only once in a
table.
Check constraints are used to place more complex
integrity rules on a column or set of columns in a
table
Semantic Integrity


An example of semantic integrity is the quality of
the data in the database.
Redundancy is another semantic issue.
Internal Integrity

The final aspect of integrity comprises internal
DBMS issues. The DBMS relies on internal
structures and code to maintain links, pointers,
and identifiers. In most cases, the DBMS will
do a good job of maintaining these structures,
but the DBA needs to be aware of their
existence and how to cope when the DBMS
fails



Index consistency. An index is really nothing but
an ordered list of pointers to data in database
tables.
Pointer consistency. Sometimes large multimedia
objects are not stored in the same physical files as
other data. Therefore, the DBMS requires pointer
structures to keep the multimedia data
synchronized to the base table data. Once again,
these pointers may get out of sync if proper
administration procedures are not followed.
Backup consistency. Some DBMS products
occasionally take improper backup copies that
effectively cannot be used for recovery. It is
essential to identify these scenarios and take
corrective actions.
Types of DBAs
•There are a different type of DBAs
1-DBAs who focus on logical design
2- DBAs who focus on physical design;
3- DBAs who specialize in building systems
4- DBAs who specialize in maintaining and
tuning systems;
5-specialty DBAs and general-purpose DBAs.
•Truly, the job of DBA encompasses many roles.
Some organizations choose to split DBA
responsibilities into separate jobs.
•
System DBA
•
Database architect
•
Database analyst
•
Data modeler
•
Application DBA
•Data
warehouse administrator
System DBA

Focuses on technical rather than business issues, primarily
system administration area.


Installing new DBMS versions and applying it
Interfacing with any other technologies required by database
applications .

Ensuring appropriate storage for the DBMS .







Installing new DBMS versions and applying
maintenance fixes supplied by the DBMS vendor
Setting and tuning system parameters
Tuning the operating system, network and
transction processors to work with the DBMS
Ensuring appropriate storage for the DBMS
Enabling the DBMS to work with storage devices
and storage management software
Interfacing with any other technologies required
by database applications
Installing DBA tools and utilities
Database Architect

Involved only in new design and development work not in
maintenance, administration, or tuning.

Designing new databases skills are different from
implementation and running existing database.

Translating logical data models into physical database
designs
Typical tasks performed by the database architect include:
 Creation of a logical data model (if no DA or data
modeler position exists)
 Translation of logical data models into physical
database designs
 Implementing efficient databases including physical
characteristics, index design, and mapping database
objects to physical storage devices
 Analysis of data access and modification requirements
to ensure efficient SQL and to ensure that the database
design is optimal
 Creation of backup and recovery strategies for new
databases
Database Analyst

Really no set definition for this position.

Sometimes junior DBAs are referred to as database analysts.
A role similar to that of the database architect.

A database analyst is just another term used by some
companies instead of database administrator.


There is really no standard definition for the
Database Analyst job
Could be:
Junior DBA
 Database Architect
 Data Administrator or Data Analyst


Or could just be another term used for DBA
Data Modeler

Data models describe structured data for storage in data
management systems such as relational databases.

The main aim is to support the development of information
systems by providing the definition and format of data.
When the DA role is not defined or staffed there may be a
data modeler role defined. A data modeler is usually
responsible for a subset of the DA’s responsibilities.
Data modeling tasks include:
 the collection of data requirements for development
projects
 analysis of the data requirements
 design of project-based conceptual and logical data
models
 creation of a corporate data model and keeping the
corporate data model up-to-date
 working with the DBAs to ensure they have a sound
understanding of the data models
Application DBA

Expert in writing and debugging complex SQL.

Knows the best ways to convert database requests into
application programs.

Focus on an individual application, result in better service to
the developers of that application.

Have a better understanding of how the application impacts
the overall business.
Data Warehouse Administrator

Data warehouses are implemented for performing in-depth
data analysis.

To monitor and support the data warehouse environment
DBA’s are required.

Data warehouse administration requires experience with BI
and query tools.

Specialized database design for data warehousing.

So, knowledge on data warehousing technologies such as
OLAP, ETL skills are required.
Common data warehouse administration tasks and
requirements include:
 Experience with business intelligence, data analytics,
query, and reporting tools
 Database design for read only access
 Data warehousing design issues such as star schema
 Data warehousing technologies such as OLAP
(including ROLAP, MOLAP, and HOLAP)
 Data transformation and conversion skills
 An understanding of data quality issues
 Experience with data formats for loading and
unloading of data
 Middleware implementation and administration
Organizations that implement data warehouses for
performing in-depth data analysis often staff
DBAs specifically to monitor and support the data
warehouse environment. Data warehouse
administrators must be capable DBAs, but with a
thorough understanding of the differences
between a database that supports OLTP and a data
warehouse. Common data warehouse
administration tasks and requirements are shown
in the bullets above.
At least two separate environments must be created and
supported for a quality database implementation:
production and test (or development). New development
and maintenance work is performed in the test
environment; the operational functioning applications are
run in the production environment. It is necessary to
completely separate the test environment from the
production environment to ensure the integrity and
performance of operational work



The test environment need not be exactly the
same as the production environment.
While the production environment contains all
of the data required to support the operational
applications, the test environment needs only a
subset of data required for acceptable
application testing
Furthermore, the test DBMS implementation
usually will not be set up with the same
amount of resources as the production
environment

The test and production environments should
structured similarly though.
Some organizations implement more than two
environments, as shown here. If special care is needed for
complex application development projects additional levels
of isolated testing may need to occur. For example, a unit
test environment may exist for individual program
development, then an integration testing environment to
ensure that new programs works together, or that new
programs work correctly with existing programs. A quality
assurance environment may need to be established to
perform rigorous testing against new and modified
programs before they are migrated to the production
environment.
Environments
• System Design
• Database Design
• Application
Development
• Unit Testing
• Integration
Testing
Test
• Application
Shakeout
• Testing With
Related Systems
• Volume Testing
Quality
Assurance
• Operational
Status
Production





DBAs need to keep abreast of new technologies
three specific newer technologies that rely on
database administration to be effectively
implemented:
database-coupled application logic,
Triggers, stored procedures, user defined functions
Procedural DBA
Internet-enabled e-business development Internet:
eDBA (e-commerce)
handheld computing _Cloud Computing
Traditionally, the domain of a database management system
was, appropriately enough, to store, manage, and access
data. Although these core capabilities are still required of
modern DBMS products, additional procedural functionality
is slowly becoming not just a nice feature to have, but a
necessity. Features such as triggers, user-defined fucntions,
and stored procedures provide the ability to define business
rules to the DBMS instead of in separate, application
programs. These features tightly couple application logic to
the database server.
The procedural DBA should be responsible for those
database management activities that require procedural
logic support. This should include primary responsibility
for ensuring that stored procedures, triggers, and userdefined functions are effectively planned, implemented,
shared, and reused. The procedural DBA also should take
primary responsibility for coding and testing all triggers.
Stored procedures and user-defined functions, however,
will most likely be coded by application programmers,
and reviewed for accuracy and performance by
procedural DBAs.


Stored procedures can be thought of as programs that
live in a database. The procedural logic of a stored
procedure is maintained, administered, and executed
through the database commands. The primary reason
for using stored procedures is to move application
code from a client workstation to the database server.
Stored procedures typically consume less overhead in a
client/server environment because one client can
invoke a stored procedure that causes multiple SQL
statements to be run. The alternative, the client
executing multiple SQL statements directly, increases
network traffic and can degrade overall application
performance.
A stored procedure is a freestanding database object;

Triggers are event-driven specialized procedures
that are attached to database tables. The trigger
code is automatically executed by the RDBMS as
data changes in the database. Each trigger is
attached to a single, specified table. Triggers can be
thought of as an advanced form of rule or
constraint that uses procedural logic. A trigger
cannot be directly called or executed; it is
automatically executed (or "fired") by the RDBMS
as the result of a SQL INSERT, UPDATE, or
DELETE statement issued on its associated table.
Once a trigger is created, it is always executed
when its firing event occurs.

A user-defined function (UDF) provides a
result based on a set of input values. UDFs are
programs that can be executed in place of
standard, built-in SQL scalar or column
functions. A scalar function transforms data for
each row of a result set; a column function
evaluates each value for a particular column in
each row of the results set and returns a single
value. Once written, and defined to the
RDBMS, a UDF becomes available just like any
other built-in database function.


A new type of DBA is required to accommodate
the administration of database procedural logic.
This new role can be defined as a procedural DBA.
The procedural DBA is responsible for those
database management activities that require
procedural logic support. He ensures that stored
procedures, triggers, and user-defined functions
are effectively planned, implemented, shared, and
reused. The procedural DBA also takes primary
responsibility for coding and testing all triggers.
Stored procedures and user-defined functions,
although likely to be coded by application
programmers, should be reviewed for accuracy
and performance by procedural DBAs

the procedural DBA requires communication
skills as much as he requires technological
acumen. In addition to managing and
optimizing database procedural objects, the
procedural DBA must inform the development
community of new triggers, stored procedures,
and UDFs. Furthermore, the DBA must
promote reuse. If the programmers do not
know that these objects exist, they will never be
used



Other procedural administrative functions can be allocated to the
procedural DBA. Depending on the number of DBAs and the
amount of application development needed, the procedural DBA
can be assigned to additional functions such as the following:
• Participating in application code design reviews
• Reviewing and analyzing SQL access paths (from "EXPLAIN" or
"SHOW PLAN")

• Debugging SQL

• Writing and analyzing complex SQL statements

• Rewriting queries for optimal execution
The procedural DBA should participate in and lead the
review and administration of all procedural database
objects: that is, triggers, stored procedures, and UDFs.
Although the procedural DBA is unlikely to be as skilled
at programming as an application programmer or systems
analyst, he must be able to write and review program
code reasonably well. The skill level required depends on
what languages are supported by the DBMS for creating
procedural objects, the rate and level of adoption within
the organization, and whether or not an internal
organization exists for creating common, reusable
programs. Additionally, the procedural DBA should be
on call for any problems that occur to database procedural
objects in production.

Cloud computing offers a new model for the delivery of IT
resources to users. The primary defining characteristic of
Cloud Computing is to give the illusion of on-demand
access to an infinite amount of computing resources.


A good example of a Cloud Computing service is offered by
Salesforce.com, which delivers access to a CRM application over
the web.
Another aspect prevalent with Cloud computing offerings is
that users can rent computing power with no commitment.
Instead of buying a server, you can rent the use of one and
pay just for what you use.
This used to be referred to as utility computing because it mimics
how people pay for utilities, such as water or electricity.
 It is a “pay as you go” service.


Pervasive devices such as smart phones often are used to
interact with data in the cloud.


DBAs may be called upon to administer the
databases used by cloud computing
Similar duties, availability becomes more of an
issue

DBAs will not be needed to manage and work on the database on each
PDA, but the job of the DBA will be impacted by this development. A
database the size of those stored on PDAs should not require the in-depth
tuning and administration that is required of enterprise database
implementations. However, DBAs will be called upon to help design
appropriately implemented databases for small form factor devices like
PDAs. But this is not the biggest impact.


A big impact on DBA is in the planning for and management of the data
synchronization from hundreds or thousands of PDAs. When should
synchronization be scheduled? How will it impact applications that use
large production databases that are involved in the synchronization?
How can you ensure that a mobile user will synchronize his data reliably
and on schedule?
Alternately, for Cloud implementations, DBAs will likely be responsible for
assuring reliable data availability. Designing and tuning a database
implementation for Cloud computing can require significant resources to
manage large amounts of data and assure around-the-clock availability.

Professional certification is offered by the
leading DBMS vendors



IBM, Oracle, Microsoft, others
General concept: a certified DBAs should be
capable of performing DBA tasks and duties
But passing a test is not always a viable
indicator of being able to perform a complex
job like DBA.
p://www.craigsmullins.com/dbta_012.htm





Database security:
- System security
- Data security
System privileges: Gaining access to the database
Object privileges: Manipulating the content of the database
objects
Schemas: Collections of objects, such as tables, views, and
sequences
The database administrator has high-level system privileges for
tasks such as:
 Creating new users
 Removing users
 Removing tables
 Backing up tables
Some of the frequent queries executed by DBA:

CREATE USER statement to create and configure an database user.

ALTER DATABASE statement to open/mount a database.

BACKUP statement to take backup of control files.

RECOVER statement to recover the saved control files.
Sample query:

create directory my_dir as '/home/oracle/andyb‘;

SQL Server Management Studio is a software application first
launched with the Microsoft SQL Server 2005 that is used for
configuring, managing, and administering all components within
Microsoft SQL Server.

Adminer is a tool for managing content in MySQL databases.
"Light-weight" - released in a form of a single file, approx160 KB
in size. User-friendly interface .