managing database change with data modeling l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Managing Database Change with Data Modeling PowerPoint Presentation
Download Presentation
Managing Database Change with Data Modeling

Loading in 2 Seconds...

play fullscreen
1 / 36

Managing Database Change with Data Modeling - PowerPoint PPT Presentation


  • 164 Views
  • Uploaded on

Managing Database Change with Data Modeling. Bert Scalzo, PhD Bert.Scalzo@Quest.com. Bert Scalzo …. Database Expert & Product Architect for Quest Software Oracle Background: Worked with Oracle databases for over two decades (starting with version 4)

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Managing Database Change with Data Modeling' - alayna


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
managing database change with data modeling
Managing Database Change with Data Modeling
  • Bert Scalzo, PhD
  • Bert.Scalzo@Quest.com
bert scalzo
Bert Scalzo …

Database Expert & Product Architect for Quest Software

Oracle Background:

Worked with Oracle databases for over two decades (starting with version 4)

Work history includes time at both “Oracle Education” and “Oracle Consulting”

Academic Background:

Several Oracle Masters certifications

BS, MS and PhD in Computer Science

MBA (general business)

Several insurance industry designations

Key Interests:

Data Modeling

Database Benchmarking

Database Tuning & Optimization

"Star Schema" Data Warehouses

Oracle on Linux – and specifically: RAC on Linux

Articles for:

  • Oracle’s Technology Network (OTN)
  • Oracle Magazine,
  • Oracle Informant
  • PC Week (eWeek)

Articles for:

  • Dell Power Solutions Magazine
  • The Linux Journal
  • www.linux.com
  • www.orafaq.com

2

books by bert
Books by Bert …

Coming in 2009 …

Out Now …

2nd Edition

Coming Soon

3

agenda
Agenda
  • Define Database Change Management
  • Examine Common DBA Options for Managing Database Structural Changes
  • Examine the World of Modeling
  • Why Data Modeling Often Makes Sense as the Change Management Methodology
  • Examples using Toad Data Modeler
presentation objectives
Presentation Objectives

To demonstrate:

  • Why a change management system is essential
  • That database structural changes can be accomplished in many ways
  • How data modeling tools can be an essential part of database change management
  • The advantages of data modeling in change management
what is database change management
What is Database Change Management?

…Ensuring that the source and target databases are in sync after changes to the environment:

  • Development database
  • Test database(s)
  • Production database
  • Distributed databases
  • Replicated databases

Task is harder than you may think!

change management can be tedious
Change Management Can Be Tedious…
  • Some database sync tasks require lots extra, indirect work (depending on the Oracle server version)
    • Drop table columns
    • Resize (i.e. shorten) table columns
    • And a few others …
  • Therefore, the DBA must
    • Be aware of all these scenarios
    • Write extended alteration scripts
    • E.G. copy table, copy data, rename, grants
common change management options
Common Change Management Options
  • Direct to Database
  • One Way DDL Scripts
  • One Way DDL Scripts + Version Control
  • Two Way DDL Scripts
  • Two Way DDL Scripts + Version Control
  • DB Schema Managers
1 direct to database
#1: Direct to Database
  • Pros:
    • Method of least resistance (i.e. easy)
    • Manually alter the database directly
    • Often use highly visual tools like TOAD DBA and OEM
    • Requires no extra tools (no extra $ or learn curve)
  • Cons:
    • The database is the Master
    • Totally ad-hoc database mods can be problematic
    • Extended alters require lots manual DBA time
    • History of structural changes is lost
2 one way ddl scripts
#2: One Way DDL Scripts
  • Pros:
    • An Old Timer’s favorite
    • Manually edit a Master DDL script
    • Requires no extra tools (no extra $ or learn curve)
  • Cons:
    • The DDL Script is the Master
    • Can forget & thus loose ad-hoc database mods
    • Extended alters require lots manual DBA time
    • History of structural changes is lost
3 one way ddl scripts version control
#3: One Way DDL Scripts + Version Control
  • Pros:
    • Manually edit a version of Master DDL script
    • Requires source control tool (free on UNIX)
    • Addresses history shortcoming of prior method
  • Cons:
    • The DDL Script is the Master
    • Can forget & thus loose ad-hoc database mods
    • Extended alters require lots manual DBA time
4 two way ddl scripts
#4: Two Way DDL Scripts
  • Pros:
    • Manually create DDL script based upon compare
    • Requires DDL extract tool (can be SQL script)
  • Cons:
    • The DDL Script is the Master
    • Can miss & over-ride ad-hoc database mods
    • Extended alters require lots manual DBA time
    • History of structural changes is lost
5 two way ddl scripts version control
#5: Two Way DDL Scripts + Version Control
  • Pros:
    • Manually create DDL script based upon compare
    • Requires DDL extract tool (can be scripts)
    • Requires diff & source control tools (free on UNIX)
    • Addresses history shortcoming of prior method
  • Cons:
    • The DDL Script is the Master
    • Can miss & override ad-hoc database mods
    • Extended alters require lots manual DBA time
6 db schema managers
#6: DB Schema Managers
  • Pros:
    • Automatically create DDL script or directly apply selected changes (i.e. sync) based upon compare
    • Requires DB comp & sync tool, but then no other tools
    • Addresses history shortcoming of prior methods
    • Extended alters as easy as any other modification
  • Cons:
    • Disconnect between data models and database
data modeling as a change management methodology
Data Modeling as a Change Management Methodology
  • Works off the meta-data documenting the business requirements (i.e. the true source code so to speak)
  • Requires using just one tool – the data modeling tool
  • Eliminates having multiple steps and branch points
  • Sometimes picture of what’s changed is more readily apparent and understandable than the alternatives
  • By very nature, keeps database & its requirements in sync!
the world of modeling
The World of Modeling …
  • Improve process efficiency
  • Define/document Bus. Processes - create correct and complete application requirements

Business Process Modeling

(BPM)

  • End-user
  • IT Partner/Liaison
  • Business Analyst
  • Support for all UML diagrams - Analyze requirements - Design application
  • Reverse/forward engineer code

Object-Oriented Modeling

(OOM - UML)

  • System Architect
  • System Analyst
  • App Developer
  • Identify all data & relationships - E/R (Entity/Rel’ship) diagrams - DB independent view
  • Business Rules?

Conceptual Data Modeling

(CDM – E/R)

  • Bus. Analyst
  • Data Architect
  • Data Analyst
  • DB-specific model
  • Reverse engineer existing DB
  • Create/Update DB from model
  • Data Warehouse Modeling

Physical Data Modeling

(PDM)

  • DBA
  • DB Developer
  • DB Architect
6 data modeling tools
#6: Data Modeling Tools

Toad Data Modeler

Toad Data Modeler

  • Pros:
  • Automatically create DDL script or directly apply selected changes (i.e. sync) based upon compare
  • Requires DB compare & sync tool, but then no other tools
  • Addresses history shortcoming of prior methods
  • Extended alters as easy as any other modification
  • No disconnect between data models and database
compare sync combinations
Compare & Sync Combinations
  • Can compare & sync
    • Logical to Logical
    • Logical to Physical
    • Physical to Logical
    • Physical to Physical
    • Script to Physical
    • Database to Physical
    • Database to Database
    • Version to Version
here we go
Here we go …
  • “Best laid plans of mice and men” 
  • Real life is not as rigid or structured as we plan
  • Thus even when adopting a data modeling change management approach, two way compare/sync will still be critical (need to catch exceptions to method)
  • In following demo:
    • Will change some live database objects using Toad (e.g. alter table, create table, drop table)
    • Will change some source code or data modeling items (e.g. alter entity, create entity, drop entity)
    • See how modeling tool can find and fix these!
parting thoughts
Parting Thoughts
  • Data modeling can be an invaluable method for managing and addressing change in your DB environments
  • There are many other issues data modeling can address:
    • Modeling accurate business requirements
    • Transforming logical into physical models
    • Breaking data models into sub-models
    • Round-trip Engineering:
      • Conceptual -> Physical Model compare and sync
      • Physical Model -> Database compare and sync
    • Repository-based collaborative modeling
    • Horizontal and Vertical Partitioning
    • Data Warehousing (Star Schema design)
    • Object-Relational Mapping
slide36

Questions and Answers …

Thank You

Presenter:

Bert Scalzo: Bert.Scalzo@Quest.com