By Iggy Fernandez A Structured Approach to Database Administration

A Structured Approach to Database Administration
using the principles of ITIL and ITSM
By Iggy Fernandez
Speaker Introduction
10+ years of Oracle DBA experience
 Previously, Manager of Database
Administration Services at Corio
 Currently, Lead DBA at Intacct

2/8/2005
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
2
Presentation Structure



2/8/2005
Part I - Problem Statement
Part II - Other approaches
Part III - ITIL and ITSM
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
3
Part I – The Problem Statement
How many DBAs does it take
to change a light bulb?

2/8/2005
“A chief technology officer of a G2000
company recently told me that he had
three database administrators (DBAs)
managing 130 instances of software on a
24/7 basis. I shuddered at the thought.
No matter how qualified and
knowledgeable those individuals might
be, three IT professionals were not
sufficient for the task” – Dr Tim Chou –
President, Oracle Outsourcing
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
5
What are the deliverables
of the DBA function?


2/8/2005
“What is a DBA's responsibility? This
million-dollar question is difficult for most
IS organizations--let alone DBAs
themselves--to answer” – Published
Oracle author
“If you don't know Which to Do, Of all
the things in front of you, Then what
you'll have when you are through, Is just
a mess without a clue” – Winnie the
Pooh Bear
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
6
Wiretap Recording
First DBA
: it seems very quiet this week...
First DBA
: i don't think i'll have 4 - 5 hours of real
work
Second DBA
: why don’t you enter the time for regular db
maintenance on the servers
First DBA
: uhhhhhhhhhhh, what regular maint?
First DBA
: there hasn't been any this week
Second DBA
: but at least you can enter the time for
proactive maintenance
First DBA
: what proactive maint?
First DBA
: haven't touched a box this week.
Second DBA
: check the error log and other db related
logs
First DBA
: hmmmmm
2/8/2005
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
7
HA technology does not
produce HA



2/8/2005
Intel DBA writes quickie script
eBay DBA types in wrong window
Amazon database cannot be restarted
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
8
Wiretap Recording
First DBA
: too many fires
Second DBA : ?
First DBA
: oh the usual
First DBA
: prod is down, archive logs missing
First DBA
: etc etc
First DBA
: and everybody is running around
like a headless chicken
2/8/2005
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
9
Recap – The Problem
Statement
Inadequate staffing?
 No consensus on deliverables
 No rating methodology
 Bad things happen easily
 HA Technology does not produce
HA

2/8/2005
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
10
Part II – Other Approaches
Capability Maturity Model
(Software Development)





2/8/2005
INITIAL – “Individual Heroics”
REPEATABLE – Basic Processes e.g.
Project Management, Quality Assurance
DEFINED – Documentation,
Standardization, Integration,
Communication
MANAGED – Monitoring, Measurement,
Reporting
OPTIMIZING – Continuous Improvement
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
12
Other Quality Management
Systems
ISO 9000 family
 Six Sigma

2/8/2005
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
13
Deficiencies of Quality
Management Systems
No specific guidance for I.T. (SWCMM)
 No specific guidance for any field
(ISO 9000)
 Only applicable to manufacturing
(Six Sigma)
 Top-heavy bureaucracy

2/8/2005
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
14
COBIT
Control Objectives for Information
Technology
 “Controls” and “Control Objectives”
 Key Success Factors
 Key Goal Indicators
 Key Performance Indicators

2/8/2005
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
15
What about SAS 70?
Not a check list of best practices –
no such thing as “SAS 70
compliance”
 Auditing methodology published by
the American Institute of Certified
Public Accountants
 Type I and Type II audits
 Can hide as much as they reveal

2/8/2005
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
16
Part III – ITIL and ITSM
I.T. Service Management




2/8/2005
Created by the U.K. government
Described in the ITIL publications (I.T.
Infrastructure Library)
Adopted by I.T. Service Providers like
IBM Global Services and by Fortune 100
companies like eBay and Genentech
10 interrelated management processes in
two groups (over-simplification)
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
18
Service Support
Incident Management
 Problem Management
 Configuration Management
 Change Management
 Release Management

2/8/2005
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
19
Incident Management

2/8/2005
“Restore normal service
operation as quickly as
possible and minimize
the adverse impact on
business operations,
thus ensuring that the
best possible levels of
service quality and
availability are
maintained” – Best
Practice for Service
Delivery – ITIL Series




Reactive, Break-fix
Database down,
Database slow, Job
failure, Schema
Changes, Add users
Service Desk, Call
Center, Ticketing
System, P1, SEV-1
24x7, Remote Access,
VPN
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
20
Problem Management

2/8/2005
“Minimize the adverse
impact of Incidents and
Problems on the
business that are
caused by errors within
the IT infrastructure,
and to prevent
recurrence of Incidents
related to these errors”
– Best Practice for
Service Delivery – ITIL
Series


Proactive, Root Cause
Analysis, Post-Mortem,
Trend Analysis
Keep separate from
Incident Management
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
21
Configuration Management

“Provide accurate
information on
configurations and their
documentation to
support all the other
Service Management
processes” – Best
Practice for Service
Delivery – ITIL Series





2/8/2005
How does Server A
differ from Server B?
Who has access to
Server A?
Which patches have
been applied to this
Peoplesoft
environment?
When does the Support
Contract expire?
Who is the Business
Owner?
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
22
Change Management

“Ensure that standardized
methods and procedures
are used for efficient and
prompt handling of all
Changes, in order to
minimize the impact of
Change related incidents
upon service quality, and
consequently to improve
the day-to-day operations
of the organization” –
Best Practice for Service
Delivery – ITIL Series












2/8/2005
Risk Analysis, ROI
Analysis
Pre-Test Plan
Pre-Communication Plan
Pre-Signoffs
Backup Plan
Execution Plan
Backout Plan
Post-Test Plan
Post-Communication Plan
Post-Signoffs
Documentation Updates
Contingency Plan Updates
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
23
Release Management

2/8/2005
“Design and implement
efficient procedures for
the distribution and
installation of Changes
to I.T. Systems” – Best
Practice for Service
Delivery – ITIL Series



Installs
Upgrades
Patches
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
24
Service Delivery
Service Level Management
 Financial Management
 Capacity Management
 Continuity Management
 Availability Management

2/8/2005
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
25
Service Level Management

2/8/2005
“Maintain and improve
I.T. Service quality,
through a constant
cycle of agreeing,
monitoring and
reporting upon I.T.
Service achievements
and instigation of
actions to eradicate
poor service – in line
with business or cost
justification” – Best
Practice for Service
Delivery – ITIL Series



Service Level
Agreements
Operational Level
Agreements
Satisfaction Surveys
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
26
Financial Management

2/8/2005
“Provide cost-effective
stewardship of the I.T.
assets and resources
used in providing I.T.
Services” – Best
Practice for Service
Delivery – ITIL Series



Hardware, Software,
Personnel, Facilities,
Service Contracts
TCO, ROI, Budgeting,
Accounting, Charging
Server Consolidation,
Standard Edition,
Colocation, Linux, Open
Source
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
27
Capacity Management

2/8/2005
“Ensure that costjustifiable I.T. capacity
always exists and that
it is matched to the
current and future
needs of the business”
– Best Practice for
Service Delivery – ITIL
Series




Monitoring
Tuning!
Capacity Planning
Demand Management
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
28
Continuity Management

2/8/2005
“Support the overall
Business Continuity
Management process by
ensuring that the required
I.T. technical and service
facilities (including
computer systems,
networks, applications,
technical support and
Service Desk) can be
recovered within required,
and agreed, business
timescales” – Best
Practice for Service
Delivery – ITIL Series


Disaster Recovery,
Contingency Planning
Fire, earthquake, flood,
power failure
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
29
Availability Management

2/8/2005
“Understand the
Availability requirements
of the business and plan,
measure, monitor and
continuously improve the
Availability of the I.T.
Infrastructure, services
and supporting
organization to ensure
that these requirements
are met consistently” –
Best Practice for Service
Delivery – ITIL Series





Availability is Job #1!
High Availability, Data
Guard
Redundancy, RAC
SAN, NAS, Active-Passive
Configuration
Backups! Backups!
Backups! Test! Test! Test!
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
30
What are the deliverables
of the DBA function?





2/8/2005
A stable, secure and resilient
infrastructure
A log or database or all operational
events, alerts and alarms
A set of operational scripts
A resilience and fail-over testing
schedule
A set of operational work schedules
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
31
What are the deliverables
of the DBA function?





2/8/2005
A set of operational management tools
Management reports and information
Exception reviews and reports
Review and audit reports
A secure Operational Document
Library
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
32
Do we have the time?
Quadrant I – Activities that are
Important and Urgent e.g. Incident
Management
 Quadrant II – Activities that are
Important but not Urgent e.g.
Configuration Management
 Quadrant III – Activities that are
not Important but Urgent
 Quadrant IV – Activities that are not
Important and not urgent

2/8/2005
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
33
Recap (How many DBAs does
it take to fix a light bulb?)
1.
2.
3.
4.
5.
2/8/2005
Rate each ITSM focus area
Rate the quality of each deliverable
Decide what level you want to reach
Determine how much work is
involved
Determine how many DBAs you need
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
34
Further Reading






2/8/2005
CMM
Six Sigma
ISO 9000
COBIT
SAS 70
ITSM
sei.cmu.edu/cmm/
ge.com/sixsigma/
iso.org
isaca.org
systemexperts.com/tutors/sas70.pdf
itsmf.com
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
35
ITIL Series





2/8/2005
Best Practice for Service Delivery
Best Practice for Service Support
Best Practice for ICT Infrastructure
Management
Best Practice for Application
Management
Best Practice for Security Management
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
36
Q&A

2/8/2005
Send e-mail to
[email protected]
A STRUCTURED APPROACH TO
DATABASE ADMINISTRATION
37