BA371 Business Systems Analysis: Database Administration
- Database processing environment: storage and retrieval of all persistent (nontransient) data; e.g.,
- Transaction processing.
- Data warehousing (ETL).
- Reporting and ad hoc querying.
- Simulation / forecasting.
- Business Intelligence (BI).
- Multiple, simultaneous users (mostly automated systems; some (but very few!) people)
(Notice that Access does not really allow/support this).
- Example: USBR Hydrologic DataBase (HDB).
- Example: www.Teachengineering.org
- > 3M unique visitors / year.
- > 500K pageviews / month.
- 1500+ documents and growing.
- 180K educational standards and growing.
- QA/QC backend processes.
- Periodic aggregation / data warehousing.
- Track everything.
- Databases (mostly) 'live' on the file system, are distributed over hardware and networks and come in different types; e.g.,
database tables can have different binary structures.
- The specific layout and structure of a database and its tables on the file system can dramatically(!!) impact its performance.
- Databases and database processing must be recoverable in cases of hardware/network failure.
- Note: commit/rollback do not work here.
- Databases must be administered for access and privileges: users, grants, locks, etc.
- —> Need for database (system) administration.
- Kroenke: "A database is a self-describing collection of
- Meta data or data dictionary:
- Tables, their ownership, and access privileges.
- Integrity constraints.
- Users and user privileges (so-called grants and roles).
- Processes accessing the data.
- Stored procedures
(discussed and practiced in BA372).
stored procedures that execute on signal — can be 'before' or after' (discussed and practiced in BA372).
- Atomic transaction control:
- Commit and rollback: undo all transactions up to this point
e.g., because failure of the current transaction to complete.
- These so-called rollback segments must be stored somewhere on the file system —> they need storage space.
- Problem: Would this require fast or no-so-fast disk?
- Careful: rollback
segments have a finite size —> you can run out of space!!
- Make them too large and you waste space. Make them too small and you run out of space.
- Note: Ms-Access does not (natively) support transaction control. Some of its workspace database engines, however, do.
- Concurrency control: Certain transactions can (logically) occur simultaneously; others cannot:
- Database reads can generally occur simultaneously.
- Database writes may or may not occur simultaneously; e.g., separate updates on the same
record must be queued.
- Database writes can interfere with database reads:
reading a record that has not yet been committed —> leads to a
different value if the write gets rolled back.
or nonrepeatable read: reread that finds a record missing or changed.
reread that finds new records inserted.
- General mechanism to enforce order: locking:
- Implicit locks: placed by DBMS.
- Transaction isolation levels:
- RDBMS have default settings for all of these, but DB administrators can customize these.
- Explicit locks: placed by command:
- Advantages: full control of synchronized transactions.
- Disadvantage: threat of paralyzing transaction wait queues when a
lock does not get released (deadlock).
- Read and/or write locks.
- Database security:
- Protection from the environment:
- Connectivity management (Who or what can and cannot connect? From where? Using which protocols?).
- Separation of users/programs from the database: users/programs interact with (your) (server) applications and applications interact with DBMS:
- Application —> database login —> database transactions.
- Application <—> intermediate program under admin control —> database login —> database transactions.
- HTTP client (e.g., Web browser) <—> HTTP server (e.g., Web server) <—> application <—>
database login <—> database:
- Permissions/privilege management (grant and revoke): e.g., select, insert, update, create/drop/alter table, lock, etc.
- Grant permissions to DB logins, or
- Define generic groups of (theoretical) users as roles.
- Then grant permissions to roles.
- Then allocate DB logins to one or more roles.
- Protection from internal threats: e.g.,
- Disabling of deprecated or obsolete triggers/stored procedures.
- Removal of antiquated permissions/grants.
- Protection from system failures (backup and recovery):
- (Real time) database mirroring or slaving. Note: many modern NoSQL architectures; e.g.,
Hadoop replicate their data several times.
- Crash recovery:
- Change/transaction log (Oracle: redo logs) —> rollforward (redo).
- !!! Store redo logs on a 'separate' storage system !!!
- Careful: redo logs have finite size (Oracle: revolve).
- Regular system backups are a good thing:
- Documented crash recovery procedure.
- Crash recovery testing (have to give up your occasional weekend, Thanksgiving and/or Xmas eve).
- Other DBA tasks:
- Database performance assessment and optimization (this is where you can wow people!).
- Application error diagnosis, testing, bug fixing.
- Periodic (database) software updates.
- Continuous performance testing and tuning.
- Some current developments in DB-land:
- In-memory databases; e.g., SAP HANA.
- Data which do not fit the relational model well:
- Up and coming: the so-called 'NoSQL' databases; e.g.,
- Big data: availability over consistency?
- Consistency: guarantee that each read reflects the latest write (aka 'shared space'). <— RDBMS!
- Availability: guarantees service but only eventual consistency. <— RDBMS & NoSQL!
- DBA demand
- Oracle DB Administrators certification.
- SQL-Server DB Administrator certification.