DATABASE ADMINISTRATORDATABASE ADMINISTRATORPertemuan ke-1

TUJUANTUJUAN

•• Memilih DBMSMemilih DBMS yang tepat untuk sebuah organisasi•• MenjagaMenjaga ketersediaan dan keamanan database•• ManagementManagement database performance• Implementasi backupbackup dandan recoveryrecovery•• Disaster planningDisaster planning Database• Database ConnectivityConnectivity

•• Memilih DBMSMemilih DBMS yang tepat untuk sebuah organisasi•• MenjagaMenjaga ketersediaan dan keamanan database•• ManagementManagement database performance• Implementasi backupbackup dandan recoveryrecovery•• Disaster planningDisaster planning Database• Database ConnectivityConnectivity

MATERIMATERI

DatabaseEnvironment

DatabaseChange

ManagementDatabase

Connectivity

DBADatabaseAvailability

PerformanceManagement

DatabaseSecurity

BackupRecovery

DisasterPlanning

Data &Storage

Management

DAFTAR PUSTAKADAFTAR PUSTAKA

• Mullin, C.S. 2002. Database Administration: The CompleteDatabase Administration: The CompleteGuide to Practices and ProceduresGuide to Practices and Procedures, Addison Wesley.

• Berzukov, Pablo, 2010, Understanding DatabaseUnderstanding DatabaseAdministrationAdministration, CreateSpace

• Mullin, C.S. 2002. Database Administration: The CompleteDatabase Administration: The CompleteGuide to Practices and ProceduresGuide to Practices and Procedures, Addison Wesley.

• Berzukov, Pablo, 2010, Understanding DatabaseUnderstanding DatabaseAdministrationAdministration, CreateSpace

Database-DBMS-Database Administrator

INTRODUCTION

DDATABASE

Sekumpulandata yang saling

berhubungan

Sekumpulandata yang saling

berhubungan

RDBMS

RDRDBMSBMS

perangkat lunakperangkat lunak yang mengaturmengatur danmengorganisasimengorganisasi penyimpanan database

kedalam komputer

RDBMS

EMPLOYEE_ID LAST_NAME FIRST_NAME SALARY COMMISION_PCT DEPARTMENT_ID

SSTRUKTUR PPENYIMPANAN

BARIS

KOLOMPK FK

FIELD

NULL

Emp_Id Last_Name First_Name Dept_Id

HHOW THE DDATA IS OORGANIZED

Dept_Id Dept_Name

Employees Departments

Primary Key

Foreign Key

RDRDBMS MBMS MARKET ssHARE

44%19%

4% 3% 9%OracleIBM

21%

19% IBMMicrosoftiSybaseTeradataOthers

Larry Ellison

THE MMAN BBEHIND THE GGUN

Larry Ellison - CEO Oracle

Steve Ballmer – Bill GatesCEO Microsoft

John S. Chen - CEO Sybase Michael Koehler - CEO Teradata

Samuel J. PalmisanoCEO IBM

CCOMPUTER & CCAREER OOPPORTUNITY

InformationWorker

ITProfessional

SoftwareDeveloper

DDATABASE AADMINISTRATOR

Orang yang memastikan fungsi operasionalaplikasi dan database organisasi dapatberjalan secara efisien

Orang yang memastikan fungsi operasionalaplikasi dan database organisasi dapatberjalan secara efisien

MMANAGEMENT DDISCIPLINE OF DBADBA

MenghadapiMenghadapi masalah

REACTIVEREACTIVEREACTIVEREACTIVE

MenghindariMenghindari masalah

PROACTIVEPROACTIVEPROACTIVEPROACTIVE

Fokus terhadap masalahbesar yang dihadapi

REACTIVEREACTIVE

Mengembangkan danmenerapkan BlueprintBlueprint’untuk mengaplikasikandatabase

PROACTIVEPROACTIVE

A good DBA is integral to the entire application development life cycle.A good DBA is integral to the entire application development life cycle.

DDATA, DDATABASE, AND SSYSTEM AADMINISTRATOR

DATADATA

• responsible forunderstanding thebusiness lexicon andtranslating it into alogical data model

• involved more in therequirementsgathering, analysis, anddesign phase

DATABASEDATABASE

• responsible for usingthe DBMS

• Involved in thedesign, development, testing, and operationalphases.

• will transform the logicaldata model into anefficient physicaldatabase design

• the conduit forcommunication betweenthe DA team and thetechnicians andapplicationprogramming staff.

SYSTEMSYSTEM

• responsible for DBMSinstallation, modification, and support

• ensures that the ITinfrastructure isoperational for databasedevelopment by settingup the DBMSappropriately, applyingongoing maintenancefrom the DBMSvendor, andcoordinating migrationto new DBMS releasesand versions

• responsible forunderstanding thebusiness lexicon andtranslating it into alogical data model

• involved more in therequirementsgathering, analysis, anddesign phase

• responsible for usingthe DBMS

• Involved in thedesign, development, testing, and operationalphases.

• will transform the logicaldata model into anefficient physicaldatabase design

• the conduit forcommunication betweenthe DA team and thetechnicians andapplicationprogramming staff.

• responsible for DBMSinstallation, modification, and support

• ensures that the ITinfrastructure isoperational for databasedevelopment by settingup the DBMSappropriately, applyingongoing maintenancefrom the DBMSvendor, andcoordinating migrationto new DBMS releasesand versions

DDATA, DDATABASE, AND SSYSTEM AADMINISTRATOR

IT InfrastructureIT Infrastructure SystemAdministrator

DBA(if no SA)

Data and Metadata PolicyData and Metadata Policy Data Administrator DatabaseAdministrator(if no DA)AnalysisAnalysis

DatabaseAdministrator(if no DA)AnalysisAnalysis

DesignDesign DatabaseAdministrator

DevelopmentDevelopment

TestingTesting

Implementation (database, application)Implementation (database, application)

Maintenance & TuningMaintenance & Tuning

TTASKS OF DBADBA

Database Design• transform a logical data model into aphysical database implementation

•database design and implementationwill enable a useful database for theapplications and clients

Data Integrity•A database must be designed tostore the correct data in the correctway without that data becomingdamaged or corrupted

DBMS Release Migration•keeping the DBMS running and up-to- date is an ongoing effort that willconsume many DBA cycles

Performance Monitoring &Tuning•optimization of resource usage toincrease throughput and minimizecontention.

DB Availability•keeping the DBMS up and running•data is available wheneverapplications and clients require it DB Secirity & Authorization

Backup and Recovery•The DBA must be prepared torecover data to a usable point, nomatter what the cause, and to do soas quickly as possible

TTASKS OF DBA: DDATABASE DDESIGN

• the DBA must understand– relational design practices.– relational theory and the specific implementation of the relational database

management system (RDBMS)– conceptual and logical data modeling techniques. The ability to create and interpret

entity-relationship diagrams is essential to designing a relational database.

• The DBA must be able to transform a logical data model into a physicaldatabase implementation

• The DBA must ensure that the database design and implementationwill enable a useful database for the applications and clients that willuse it.

• the DBA must understand– relational design practices.– relational theory and the specific implementation of the relational database

management system (RDBMS)– conceptual and logical data modeling techniques. The ability to create and interpret

entity-relationship diagrams is essential to designing a relational database.

• The DBA must be able to transform a logical data model into a physicaldatabase implementation

• The DBA must ensure that the database design and implementationwill enable a useful database for the applications and clients that willuse it.

Database design is a relatively small portion of the DBA's jobDatabase design is a relatively small portion of the DBA's job

TTASKS OF DBA: PPERFORMANCE MMONITORING

Request

Respondt

Respondt

Performance = AVG(t)Performance = AVG(t)

5 F5 FACTOR PPERFORMANCE MMONITORING

1. Workload:• requested of the DBMS defines the demand.• Combination of online transactions, batch jobs, ad hoc queries, data warehousing,

analytical queries, and commands directed through the system at any given time• The overall workload has a major impact on database performance

2. Throughput• capability of the computer hardware and software to process data• composite of I/O speed, CPU speed, parallel capabilities of the machine, and the efficiency

of the operating system and system software3. Resources

• The hardware and software tools at the disposal of the system4. Optimization

• analysis of database requests with query cost formulas to generate efficient access pathsto data

5. Contention• condition in which two or more components of the workload are attempting to use a single

resource in a conflicting way

1. Workload:• requested of the DBMS defines the demand.• Combination of online transactions, batch jobs, ad hoc queries, data warehousing,

analytical queries, and commands directed through the system at any given time• The overall workload has a major impact on database performance

2. Throughput• capability of the computer hardware and software to process data• composite of I/O speed, CPU speed, parallel capabilities of the machine, and the efficiency

of the operating system and system software3. Resources

• The hardware and software tools at the disposal of the system4. Optimization

• analysis of database requests with query cost formulas to generate efficient access pathsto data

5. Contention• condition in which two or more components of the workload are attempting to use a single

resource in a conflicting way

TTASKS OF DBA: PPERFORMANCE MMONITORING

Database performance can be defined as the optimizationoptimizationof resource usage to increaseincrease throughput and minimizeminimizecontention, enablingenabling the largest possible workload to beprocessed

Database performance can be defined as the optimizationoptimizationof resource usage to increaseincrease throughput and minimizeminimizecontention, enablingenabling the largest possible workload to beprocessed

An effective performance monitoring and tuning strategy requires not justDBMS expertise but knowledge outside the scope of database administrationAn effective performance monitoring and tuning strategy requires not justDBMS expertise but knowledge outside the scope of database administration

TTASKS OF DBA: AAVAILABILITY

• Keeping the DBMS up and running so that data is available wheneverapplications and clients require it

• Vigilant monitoring and automated alerts can be used to warn ofDBMS outages and the need for corrective action

• needs to design the data- base so that it can be maintained withminimal disruptions,

• helps developers design applications to minimize conflicts whenconcurrent access is required

• minimizing the amount of downtime required to performadministrative tasks

• Keeping the DBMS up and running so that data is available wheneverapplications and clients require it

• Vigilant monitoring and automated alerts can be used to warn ofDBMS outages and the need for corrective action

• needs to design the data- base so that it can be maintained withminimal disruptions,

• helps developers design applications to minimize conflicts whenconcurrent access is required

• minimizing the amount of downtime required to performadministrative tasks

The DBA must understand all of these aspects of availability and ensure thateach application is receiving the correct level of availability for its needsThe DBA must understand all of these aspects of availability and ensure thateach application is receiving the correct level of availability for its needs

TTASKS OF DBA: DDATABASE SSECURITY

• ensure that data is available only to authorized users• works with the internal security features of the DBMS in the form of

SQL GRANT and REVOKE statements• the DBA interfaces frequently with external security methods when

they impact database security

• ensure that data is available only to authorized users• works with the internal security features of the DBMS in the form of

SQL GRANT and REVOKE statements• the DBA interfaces frequently with external security methods when

they impact database security

the DBA must understand and be capable of implementing any aspect ofsecurity that impacts access to databasesthe DBA must understand and be capable of implementing any aspect ofsecurity that impacts access to databases

TTASKS OF DBA: BBACKUP & RRECOVERY

• must be prepared to recover data, in theevent of a problem, to a usable point, nomatter what the cause, and to do so asquickly as possible.

• "Problem" can mean anything from asystem glitch or program error to anatural disaster that shuts down anorganization

• The majority of recoveries today occuras a result of application software errorand human error.

• must be prepared to recover data, in theevent of a problem, to a usable point, nomatter what the cause, and to do so asquickly as possible.

• "Problem" can mean anything from asystem glitch or program error to anatural disaster that shuts down anorganization

• The majority of recoveries today occuras a result of application software errorand human error.

TTASKS OF DBA: TTYPES OF RRECOVERY

1. 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

2. point-in-time recovery• usually deals with an application-level problem.• 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

3. Transaction recovery• application recovery whereby the effects of specific transactions during a specified

timeframe are removed from the database

1. 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

2. point-in-time recovery• usually deals with an application-level problem.• 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

3. Transaction recovery• application recovery whereby the effects of specific transactions during a specified

timeframe are removed from the database

the DBA needs to develop a backup strategy to ensure that data is not lost inthe event of an error in software, hardware, or a manual process

TTASKS OF DBA: DDATA IINTEGRITY

• A database must be designed to store the correct data in the correct way withoutthat data becoming damaged or corrupted

• Implements integrity rules using features of the DBMS• Three aspects of integrity

• Physical handled using DBMS features such as domains and data types. The DBA chooses the appropriate data type for each column of each table DBAs can also utilize constraints to further delineate the type of data that can be stored in

database columns• Semantic is more difficult to control and less easily defined

the quality of the data in the database Redundancy

• Internal DBMS Index consistency Pointer consistency Backup consistency

• A database must be designed to store the correct data in the correct way withoutthat data becoming damaged or corrupted

• Implements integrity rules using features of the DBMS• Three aspects of integrity

• Physical handled using DBMS features such as domains and data types. The DBA chooses the appropriate data type for each column of each table DBAs can also utilize constraints to further delineate the type of data that can be stored in

database columns• Semantic is more difficult to control and less easily defined

the quality of the data in the database Redundancy

• Internal DBMS Index consistency Pointer consistency Backup consistency

TTASKS OF DBA: DDBMS RRELEASE MMIGRATION

• Managing the migration from release to release of the DBMS• Approach is taken must conform to the needs of the organization,

while reducing outages and minimizing the need to changeapplications

• Managing the migration from release to release of the DBMS• Approach is taken must conform to the needs of the organization,

while reducing outages and minimizing the need to changeapplications

The task of keeping the DBMS running and up-to- date is an ongoingeffort that will consume many DBA cycles.

TTYPES OF DDBA

• focuses on technical rather than business issues• They might get involved in application tuning when operating system

parameters or complex DBMS parameters need to be altered.System DBASystem DBA

• The database architect is involved in new design and developmentwork only

DatabaseArchitectDatabaseArchitect

• The database architect is involved in new design and developmentwork only

DatabaseArchitectDatabaseArchitect

• no set definition for this position• Sometimes a database analyst performs a role similar to that of the

database architect

DatabaseAnalyst

DatabaseAnalyst

• usually responsible for a subset of the DA's responsibilitiesData ModelerData Modeler

• focuses on database design and the ongoing support andadministration of databases for a specific application or applicationsApplication DBAApplication DBA

TTASK-ORIENTED OF DDBA

•focuses solely on the performance of database applications•must understand the details and nuances of SQL coding for performance andbe able to design databases for performance

•have very detailed technical knowledge of the DBMS so that he can makeappropriate changes to DBMS and system parameters when required

•must be able to speak to application developers in their language in order tohelp them facilitate appropriate program changes for performance

•The performance analyst is usually the most skilled, senior member of theDBA staff.

PerformanceAnalysis

PerformanceAnalysis

•focuses solely on the performance of database applications•must understand the details and nuances of SQL coding for performance andbe able to design databases for performance

•have very detailed technical knowledge of the DBMS so that he can makeappropriate changes to DBMS and system parameters when required

•must be able to speak to application developers in their language in order tohelp them facilitate appropriate program changes for performance

•The performance analyst is usually the most skilled, senior member of theDBA staff.

PerformanceAnalysis

PerformanceAnalysis

•devotes his entire day to ensuring the recoverability of the organization'sdatabases.

Backup andRecovery

Backup andRecovery

SSTAFFING CCONSIDERATION

o How many DBA’s ?• Number of databases, Size of the databases, Number of users, Number

of applications• Service-level agreements (SLAs), Availability requirements,• Impact of downtime, Performance requirements• Type of Applications, Volatility• DBA staff experience, Programming staff experience, End user

experience• Variety of DBMSs, DBA tools.

o Industry analysts at the META Group• system complexity, application immaturity,• end-user sophistication, staff sophistication• software functionality, system availability

o How many DBA’s ?• Number of databases, Size of the databases, Number of users, Number

of applications• Service-level agreements (SLAs), Availability requirements,• Impact of downtime, Performance requirements• Type of Applications, Volatility• DBA staff experience, Programming staff experience, End user

experience• Variety of DBMSs, DBA tools.

o Industry analysts at the META Group• system complexity, application immaturity,• end-user sophistication, staff sophistication• software functionality, system availability

DDBA RREPORTING SSTRUCTURE

CIO

ApplicationDevelopment

ApplicationTeam #1

DBA

ApplicationTeam #2 …

TechnicalSupport

DataResource

Management

DatabaseAdministrator

DataAdministrator

Operation

NNEW TTECHNOLOGY AND THE DDBA

1. Procedural DBAs: Managing Database Logic

NNEW TTECHNOLOGY AND THE DDBA

2. The Internet: From DBA to e-DBAAn e-DBA is a DBA who is capable of managing Web-based applicationsbecause he understands the special issues that arise because of theInternet. Many factors impact database administration:• 24/7 data availability• New technologies such as Java and XML• Web connectivity• Integration of legacy data with Web-based applications• Database and application architecture• Web-based administration• Performance engineering for the Internet• Unpredictable workload

3. The PDA DBA

2. The Internet: From DBA to e-DBAAn e-DBA is a DBA who is capable of managing Web-based applicationsbecause he understands the special issues that arise because of theInternet. Many factors impact database administration:• 24/7 data availability• New technologies such as Java and XML• Web connectivity• Integration of legacy data with Web-based applications• Database and application architecture• Web-based administration• Performance engineering for the Internet• Unpredictable workload

3. The PDA DBA