Transcript Creating Enterprise DBA 709 KB, Powerpoint Slides Uploaded on

Creating the Enterprise Database
Administrator Role:
Moving from Vision to Effective Practices
Deanna McMurray, Enterprise Database Administrator
Marina Arseniev, Director of Enterprise Architecture, Security, and Data
Management Services
Copyright © 2008 The Regents of the University of California
All Rights Reserved.
Permission is granted for this material to be shared for noncommercial, educational purposes, provided that this copyright
statement appears on the reproduced materials.
A Common Problem: Silo Specific DBA Groups
•
Most IT organizations have silo specific database
administration groups based on database platforms
–
–
–
–
Oracle
Sybase
DB2
Microsoft SQL Server
Database administration is rarely considered part of the overall
Enterprise Architecture strategy for cross-platform database
development and operations.
The Missing Role: An Enterprise DBA
• The absence of Enterprise-level coordination of product/
platform or application silo specific database administration
groups = redundant and inconsistent database management
• This missing role of an Enterprise-level cross-DBA control
point that is part of an Enterprise Architecture team results in
a number of problems in the IT organization
Common Problems from this Missing Role:
•
•
•
•
•
•
•
Inconsistent procedures across project team silos
Security oversights
Insufficient disaster recovery preparedness
Redundant data
Inadequate documentation
Lack of education in data management best practices
Highly trained DBAs not being optimally utilized across
the department since they report into silos
• Experts are dedicated to single projects—problem!
Solution: Create an Enterprise DBA Role!
Benefits of an Enterprise-level DBA role:
• Shared common resource of competency
• Provides guidance to ensure consistent procedures are
implemented cross database platforms and projects
• Reduces risks and costs
• Implements data management best practices in the IT
organization
All Databases have a Common Set of
Functions—Be Consistent!
Common Functions:
•
•
•
•
•
Data Quality Management
Data Security Management
Database Management
Data Governance
Data Architecture, Analysis and Design
It is the job of the Enterprise DBA role to set up an
Enterprise-wide consistent blueprint for database
development and management
Implementing an
Enterprise DBA Role:
Step 1—Become Educated in Data
Management Best Practices
• I.T. Architects in Academia www.itana.org is currently
focusing on Data Management — developing metrics to
measure maturity of Data Management in an organization
– ITANA is a Peer Group for I.T. Architects in Higher
Education sponsored by Educause and Internet2
– Meeting later today at Educause
• Data Governance Institute www.datagovernance.com
• Attend DAMA Data Management and Metadata Conference
Who is DAMA?
• DAMA -- Data Management Association International
• Not-for-profit, vendor-independent dedicated to
advancing the concepts and practices of data resource
management (DRM)
• Purpose -- to promote the understanding, development
and practice of managing information and data as a key
enterprise asset
DAMA DMBOK GUIDE Version3
Data Management Body of Knowledge
• Providing a definitive introduction to data management
• Presenting a standard industry view of data
management functions, terminology and best practices
Estimated date of publication—March 2009
• Goals (from www.dama.org)
– Build consensus for a generally applicable view of data
management functions
– Provide standard definitions for commonly used data
management functions, deliverables, roles and terminology
– Overview commonly accepted good practices, widely
adopted methods and techniques, and significant alternative
approaches, without reference to specific technology vendors
– Define scope for Data Management
DAMA DMBOK Framework V3
10 Functions 100+ Activities
Data
Quality
Management
Data
Architecture
Management
Data
Development
Meta Data
Management
Data
Governance
Database
Operations
Management
Data
Security
Management
Document &
Content
Management
Data
Warehousing
& Business
Intelligence
Management
Reference &
Master Data
Management
Used with permission
www.dama.org
DAMA DMBOK Framework
Environmental Elements
Used with permission
www.dama.org
Zachman Framework2 for Enterprise
Architecture Announced at DAMA
• Product neutral - no product or consulting as with Meta
or Gartner
• Used by Ford, Volkswagen, Firestone, GM, DOD, US
Treasury
• Comprehensive, working model that aligns IT with
business by looking at pieces that fit into the whole of
an enterprise puzzle
• Simple, logical model--not technical
• A language that helps people think about complex
concepts and communicate in non-technical
terminology
• A planning and problem solving tool that enables
abstraction and simplification without neglecting the
complexity of the Enterprise as a whole
Research Data Governance Best Practices
• Data Governance Institute, www.datagovernance.com,
offers in depth vendor-neutral information
• Techniques, models, and best practices for data
governance
• Data Governance and Stewardship “Community of
Practice” to share information – nominal fee for
membership, some information available at no cost
Step 2: Incorporate Data Management
Best Practices into your Strategic Plan
• Educate your DBAs and Managers about the DAMA
Framework Functions
– Address all functions during strategic plan
development
• Use ITANA Data Management Metrics — measure
Data Management maturity
– Think about: What areas of Data Management does
your organization need to improve in?
• Use the Zachman Framework2 as one of your guides
for Enterprise Architecture
Step 3: Set up your Matrix Managed DBA
Team
• One useful way -- implement your Enterprise DBA
function using a matrix management style
• Important to oversee the consistency of procedures:
– Security (protection of Personal Identity Data, PCI
and HIPAA data)
– Disaster Recovery
– Documentation
– Service Level Agreements
Justify Creating an Enterprise DBA Role in
your Organization
• Show the many organizational benefits!
• Keep metrics to demonstrate improvements
• Write “executive reports” showing the impact of your new
Enterprise DBA role to high-level managers in your
organization
Benefit #1: Consistent Procedures Across
Platforms and Projects =
Reduced Problems and Risks
Before
Transaction logs locations
handled on an ad-hoc basis
After
Standardized locations for
transaction log across
systems
No Administrator password
management procedures
Consistent Administrator
password management
procedures implemented
No standard account
management across project
teams – each team had its
own way of implementing
SQL Server accounts
Consistent account
management in the SQL
Server or Active Directory
environment as appropriate.
Benefit #2: Consistent Development of
Service Level Agreements for Data
Recovery with Customers
Before
No Service Level
Agreements for data
recovery for our customers
After
Implemented Service Level
Agreement for data recovery
of customer applications
hosted by Adcom Services
• Sybase and MS-SQL
Server
• Example: 15+ applications
for Human Resources
• Recovery time based on
High, Medium, Low Priority
Benefit #3: Improved Disaster Recovery
Preparedness
Before
No database point-in-time
recovery in place
After
Implemented procedures for
point-in-time recovery
Minimal testing of disaster
recovery procedures
Regular (every 6 months)
disaster recovery testing
Our offsite backup rotation
left us with a potential 3 day
window of data loss:
Friday 8am through Monday
8am
Enterprise level review
showed this was an
unacceptable level of risk.
Added an additional pickup
on Saturday morning to
correct this problem.
Benefit #4: Significantly Improved
Database Security
Before
After
No one was running security
tools to help detect and
close security vulnerabilities
on our database servers
Automated regular database
vulnerability scanning using
SCUBA (vendor: Imperva)
and MBSA (Microsoft
Baseline Security Analyzer)
and remediated
vulnerabilities identified
Over 300 unaudited
accounts on Sybase and
Microsoft SQL Server
Audited and removed
obsolete accounts
Benefit #5: Mapping the Flow of
Secured Data
California law protects key data elements
Before
After
Incomplete knowledge of
Conduct regular data security
sensitive data location and
and data flow reviews across
flow
UC Irvine departments
• Documented outcomes in
diagrams and text
• Data security reviews reveal
complexity of data management
issues
How end users handle sensitive
data produces points of risk:
email, fax, unsecured file
transfers, printing, cut and
paste
Benefit #6: Consistent Data Models and a
Common Data Dictionary
Before
Each project had
independent data modeling
on an ad-hoc basis with no
coordination or central
management
After
Added a formal data design
review as part of our SDLC
•Eliminated silo-based data
modeling and application
development
•Centrally coordinates data
model development across
project teams
Benefit #7: Cross Training and Staff
Rotations Become Easier
Before
No forum for
communication or
coordination for DBAs
After
Matrix managed DBA teams
communicate & collaborate
more frequently than silobased DBA teams
No cross-training in place
Cross-platform DBA control
point oversees rotation of staff
for proper coverage and crosstraining
Narrow focus, reactive
Common issues can be
addressed in a broader way
than silo-based teams
Lessons Learned from Implementing the
Enterprise DBA Role at UC Irvine
• Communication and collaboration are essential
• Recognize staff resource limitations
• Record metrics showing return on investment of creating
this role
• Research best practices outside of your organization
Future Plans:
Focus on Data Quality in 2009
• Example of challenges: Student Billing System contains
data that originates from many different sources
• Problem: How do you improve the quality of the data
when you are not the originator of the data?
• Plan: Identify the Master of Record if you have
conflicting values for your data elements
Five Year Picture: A Perfect World
• Reduce platforms/complexity –- 2 database platforms
• Uniformity of all servers
• Highly trained staff—shared
• Databases are secured
• Increased dialog with business owner and better
alignment of resources to reduce risks
• Know location of your data
– Which data is regulated?
– Know what we have!
For More Information…
• DAMA www.dama.org
• ITANA www.itana.org
• Zachman Framework2 for Enterprise Architecture
www.zachmaninternational.com
• Data Governance Institute
www.datagovernance.com