1 / 22

Data and Database Administration

Data and Database Administration. Transaction Management Concurrency Control Database Security. Learning Objectives. Describe the importance of data and database management Describe database transactions are and how they are managed

bisa
Download Presentation

Data and Database Administration

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data and Database Administration Transaction Management Concurrency Control Database Security

  2. Learning Objectives • Describe the importance of data and database management • Describe database transactions are and how they are managed • Explain concurrency control and locking methods and what role they have in ensuring database integrity • Describe database backup and recovery and the role they play in database integrity • Describe the role and responsibilities of a Database Administrator and tools used to perform required tasks • Apply transaction management principles to solve problems

  3. Traditional Administration Definitions Data Administration: • High-level function • Policy & Procedure Planning • DBMS independent • Strategic Database Administration: • Technical function • Policy &Procedure Enforcement • DBMS dependent • Tactical

  4. What is a database transaction? • Logical unit of work in your database • Succeeds / fails as a whole • Works independently of other “units of work” • Doesn’t know about anything else but itself. • Whether you realize it or not, everything’s a transaction. When you want to change the

  5. Transaction ACID Properties • Atomic • Transaction cannot be subdivided. Logical unit of work. • Consistent • Constraints don’t change from before transaction to after transaction • Isolated • Database changes not revealed to users until after transaction has completed • Durable • Database changes are permanent once committed.

  6. Transaction FFCU Demo Demonstration of the ACID principles.

  7. Transactions on SQL server • SET IMPLICIT_TRANSACTIONS ON • DBA responsible for ending the transaction using COMMIT/ ROLLBACK. • Great way to have an “Undo” feature while working interactively • Make sure you commit all transactions and check @@TRANCOUNT • SET IMPLICIT_TRANSACTIONS OFF • Default behavior; used in stored procedures and database logic • DBA must figure out when to COMMIT / ROLLBACK • @@ROWCOUNT inspection is a common approach

  8. Transaction FFCU Demo Implicit and explicit transactions, and transaction-safe coding.

  9. Concurrency Control • Problem–in a multiuser environment, simultaneous access to data can result in interference and data loss • Solution–Concurrency Control • The process of managing simultaneous operations against a database so that data integrity is maintained and the operations do not interfere with each other in a multi-user environment

  10. Lost update (no concurrency control in effect) Simultaneous access causes updates to cancel each other A similar problem is the inconsistent read problem

  11. Concurrency Control Techniques • Serializability • Finish one transaction before starting another • Locking Mechanisms • The most common way of achieving serialization • Data that is retrieved for the purpose of updating is locked for the updater • No other user can perform update until unlocked

  12. Updates with locking (concurrency control) This prevents the lost update problem

  13. Locking Mechanisms • Locking level: • Database–used during database updates • Table–used for bulk updates • Block or page–very commonly used • Record–only requested row; fairly commonly used • Field–requires significant overhead; impractical • Types of locks: • Shared lock–Read but no update permitted. Used when just reading to prevent another user from placing an exclusive lock on the record • Exclusive lock–No access permitted. Used when preparing to update

  14. Versioning - Example Better performance than locking

  15. Database Security • Database Security: Protection of the data against accidental or intentional loss, destruction, or misuse • Increased difficulty due to Internet access and client/server technologies • More exposure == Greater Risk!

  16. Possible locations of data security threats

  17. Security Authorization matrix These are implemented through the external model (views / sp’s) and controlling accessing to database objects with GRANT/REVOKE

  18. Database Backup – the audit trail From the backup and logs, databases can be restored in case of damage or loss

  19. Basic recovery techniques - Rollback Like and “Undo” button for the Database

  20. Basic recovery techniques - Rollforward Point-in-time restore for the Database

  21. Advanced Database Concepts Questions?

More Related