db administration.ppt
TRANSCRIPT
-
8/14/2019 DB Administration.ppt
1/26
Database Administration
-
8/14/2019 DB Administration.ppt
2/26
Database Administration
Rarely approach as a management disciplineImplies a plan and an implementation according tothe plan
Includes the following:development and design of database strategies,monitoring and improving database performanceand capacity
planning for future expansion requirementsimplement security measures to safeguard thedatabase
-
8/14/2019 DB Administration.ppt
3/26
Reactive vs Proactive DB Administration
ReactiveMore like a fire fighters
Attempts to solve problems only after they occur
Focused on resolving the problem at hand
ProactiveImplements practices and procedures to avoidproblems before they occurImplements a strategic blueprint for deployingDBs
-
8/14/2019 DB Administration.ppt
4/26
Data Administration
Separates the business aspect of dataresource management from the technologyused to manage the dataUnderstanding business lexicon andtranslating it to a logical model
-
8/14/2019 DB Administration.ppt
5/26
Data Administrator
Identifying and cataloging the data required bybusiness usersProducing accurate conceptual data model thatdepicts relationship among data elementsCreating a data enterprise modelSetting data policies of the organizationIdentifying data owners and stewardsSetting standards for control and usage of data
-
8/14/2019 DB Administration.ppt
6/26
DA vs DBA
-
8/14/2019 DB Administration.ppt
7/26
DBA
Ensures that an organizations data areuseful, available and reliable/correctPerformance and Tuning
-
8/14/2019 DB Administration.ppt
8/26
Performance and Tuning
workload, throughput, resources, optimizationand contention
Workload online transactions, batch jobs, ad hoc
queries, analytical queriesThroughput capability of the computer HW & SWto process dataOptimization analysis of database requests with
query cost formulas to generate efficient accesspathsContention the condition at which two or morecomponents of the workload are attempting to usea single resource in a conflicting way
-
8/14/2019 DB Administration.ppt
9/26
Performance and Tuning
Database PerformanceOptimization of resource usage to increasethroughput and minimize contention enabling the
largest possible workload to be processed An effective performance monitoring andtuning requires not just DBMS expertise butknowledge outside the scope of DB adminHandling performance is an enterpriseendeavor
-
8/14/2019 DB Administration.ppt
10/26
-
8/14/2019 DB Administration.ppt
11/26
Availability
Utilities are provided while to analyze thedatabase while application read and writefrom itClustering technologies provide failovertechniques that reduces downtime
-
8/14/2019 DB Administration.ppt
12/26
Database Security & Authorization
Only authorized programmers and usersshould have access
It is the responsibility of the DBA that only
authorized users have access to itInternal security features of the DBMS GRANT & REVOKE
Group authorization features
-
8/14/2019 DB Administration.ppt
13/26
Database Security
Actions that needed to be secured:Creation of DB objects tables, views etc.
Altering structure of DB objects Accessing the system catalogReading & modifying data in tablesCreating & accessing user-defined functions
Running stored proceduresStarting and stopping databases
-
8/14/2019 DB Administration.ppt
14/26
Back-up and Recovery
Majority of recoveries today resulted fromsoftware and human errorHardware failure is not as prevalent as theyused to be
80% of errors are due to software and humanfactors
The DBA must be prepared to recover data atany usable point, no matter what the cause,and to do it as quickly as possible
-
8/14/2019 DB Administration.ppt
15/26
Back-up and Recovery
Recover to currentDB is brought back to its current state before thefailure occurred
Point-in-time recovery Application level problemRemoves all transactions since a specified pointin time
Transaction RecoveryEffects of specific transactions during a specifiedtimeframe are removed from the DB
Application recovery
-
8/14/2019 DB Administration.ppt
16/26
Back-up and Recovery
What should the DBA do?Develop a back-up strategy to ensure that data isnot lost in the event of an error
Applicable to DB processingProcedures shroud be in place to keep datasynchronized & accurate
-
8/14/2019 DB Administration.ppt
17/26
Staffs involved in DB Administration
System DBADatabase ArchitectDatabase Modeler
Application DBAData Warehouse Administrator
-
8/14/2019 DB Administration.ppt
18/26
-
8/14/2019 DB Administration.ppt
19/26
Database Architect
For design and implementation of newdatabases
Not involved in the maintenance; in new design
onlyRationale
Skills required for designing new databases aredifferent from those that are required to keep anexisting database up and running
-
8/14/2019 DB Administration.ppt
20/26
Database Architect
Creating a logical data modelTranslating logical models to physicaldatabase designImplementing efficient databases
Analyzing data access to ensure efficientSQL and optimal database designCreating back-up and recovery strategies fornew databases
-
8/14/2019 DB Administration.ppt
21/26
Database Analyst
Junior DBASometimes his role is similar to the databasearchitectSometimes referred to also as the dataadministrator
-
8/14/2019 DB Administration.ppt
22/26
Database Modeler
Collecting data requirements for developmentprojects
Analyzing data requirementsDesigning project-based and conceptual datamodelsCreating and updating corporate data modelEnsuring that DBA has sound understandingof the data models
-
8/14/2019 DB Administration.ppt
23/26
Application DBA
Focuses on database design and the supportand administration of databases for a specificapplication
Expert in writing and debugging SQLstatements
understands the best way to incorporate DB
requests into the application program
-
8/14/2019 DB Administration.ppt
24/26
Application DBA
-
8/14/2019 DB Administration.ppt
25/26
Data Warehouse Administrator
Monitor and support data warehouseenvironment
Business intelligence, query & reporting tools
Database design for read-only accessData warehousing design issues such as starschemaData transformation and conversionData quality issues
-
8/14/2019 DB Administration.ppt
26/26
End of Lecture