BA 371 Business Systems Design - DB Administration

• Database processing environment:

• Storage & retrieval of all persistent (nontransient) data:

• Transaction processing.
• Data warehousing (ETL).
• Reporting & ad hoc querying.

• Forecasting.

• Multiple, simultaneous users (mostly automated systems; some people) (Notice that Access does not really allow/support this).
  

• Example: USBR Hydrologic DataBase (HDB) .

• ==> Need for database (system) administration (OS-like).
• Kroenke: "A database is a self-describing collection of related records."
• Meta data or data dictionary:
• Tables, their ownership, and access privileges.
• Processes accessing the data.
• Users.
• Integrity constraints.
• Stored procedures (discussed and practiced in BA372).
• Triggers: stored procedures that execute on signal (discussed and practiced in BA372).

• Atomic transaction control:
• Commit and rollback: undo the former transactions because failure of the current transaction (may practice in BA372).
• Careful: (Oracle) rollback segments have a finite size.

• Concurrency control:
• Certain transactions can (logically) occur simultaneously; others cannot:
• Database reads can generally occur simultaneously.
• Database writes may or may not; e.g., separate updates on the same record must be queued.
• Database writes can interfere with database reads:
• Dirty read: read of a record that has not yet been committed --> leads to a different value if the write gets rolled back.
• Inconsistent or nonrepeatable read: reread that finds a record missing or changed.
• Phantom read: reread that finds new records inserted.
• General mechanism to enforce order: locking:
• Implicit locks: placed by DBMS.
• Transaction isolation levels:

  SQL Isolation Level	Dirty Read	Nonrepeatable Read	Phantom Read
  READ UNCOMMITTED	Permitted	Permitted	Permitted
  READ COMMITTED	--	Permitted	Permitted
  REPEATABLE READ	--	--	Permitted
  SERIALIZABLE	--	--	-

• Isolation levels can be set and changed; work on session level.
• 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).
  
• DIY: makes you very powerful; you better know what you're doing.

• Read and/or write locks.

• Database Security:
• Protection from the environment:
• Firewall.
• Disable unneeded protocols.
  
• Separate out the DBMS server.

• Disable unused stored procedures.
• Separate users/programs from the database: users/programs talk to (your) apps and apps talk to DBMS:

• Compare:

• 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.
  
• SQL Grant permissions:

• Define generic groups of theoretical users as roles.
• Grant permissions to roles.

• Allocate real users to one or more roles.
  

• Protection from system failures (backup and recovery)
• Database mirroring or 'slaving.'

• Crash recovery:

• Change/transaction log (Oracle: redo logs) --> rollforward (redo)
  

• Careful: redo logs have finite size (Oracle: revolve).
• Always store redo logs on a 'separate' storage system.

• Regular system backup is a good thing:

• Hot vs. cold backups.

• Documented crash recovery procedure.
• Crash recovery testing (have to give up your weekend, Thanksgiving and/or Xmas eve).

• Other DBA tasks:
• Application error diagnosis, testing, bug fixing.
• Periodic software updates.
• Continuous performance testing.

• DBA certifications:
• Oracle DB Administrators certification.
• SQL Server DB Administrator certification.

• Etc.