Module 1 Introduction To Database Administration PDF

MODULE 1 INTRODUCTION TO DATABASE ADMINISTRATION AGENDA Introduction Course Material Database Administration/Administrator Data Security, Data Availability, Data Quality 2 INTRODUCTION Overview This module will explore on some preliminary concepts and tools needed for the whole cour...

MODULE 1 INTRODUCTION TO DATABASE ADMINISTRATION AGENDA Introduction Course Material Database Administration/Administrator Data Security, Data Availability, Data Quality 2 INTRODUCTION Overview This module will explore on some preliminary concepts and tools needed for the whole course. It will start by I dentifying the tools that will be needed in the course. This is being separated in a lesson so that the user can initially explore the tolls. After the identification of tools, next lesson will give you the overview of database administration and the core topics of database administration by idenfying the responsibility of a Database Administrator. 3 INTRODUCTION Course Objective After successful completion of this module, you should be able to: 1. Install the tools needed in the course 2. Understand the basic knowledge that must be gained for the entire subject 3. Enumerate the responsibility of a database administrator 4. Identify the skills needed and the certification that they can consider to be a DBA. 4 LESSON 1: TOOLS TO USED IN THIS COURSE 1. VERSION CONTROL GIT 2. Latest MySQL Version 3. MySQL WorkBench 5 LESSON 2: DATABASE ADMINISTRATION/ADMINISTRATOR Database Administration consists of everything required to manage a database it includes ensuring the database to be available everytime and anytime it is needed by the user or software application. The database administrator (DBA) is the person who manages, secures and ensures the availability of the data produced produced and used by the organization. 6 LESSON 2: DATABASE ADMINISTRATION/ADMINISTRATOR A database administrator's responsibilities may include: Installing and upgrading the database server and/or application tools. Planning for and allocating the database system’s physical requirements, such as memory,disk space, network requirements, etc. Modifying the database structure using information provided by application developers. Creating users profiles, and ensuring system security by careful allocation of userpermissions. 7 LESSON 2: DATABASE ADMINISTRATION/ADMINISTRATOR Ensuring compliance with database vendor license agreement, including number of installations, and taking care of licensing renewals. Creating a backup and recovery strategy for the database, and regularly testing the backups to ensure usability. Monitoring technical support for both database systems and related applications. Creating reports by querying from database (as per need). These reports can be in the form of pre-formatted reports using the application frontend, or custom-made ad hoc reports by the 8 database administrator. LESSON 2: DATABASE ADMINISTRATION/ADMINISTRATOR Monitoring and optimizing the database’s performance using either manual or automated tools. This may be a DBA’s most important tasks. Migrating database instances to new hardware and new versions of software from on premise to cloud based databases and vice versa. 9 LESSON 2: DATABASE ADMINISTRATION/ADMINISTRATOR There are several DBA Specialisation 1. There are also database administrators who create and design databases in the first place. They usually work as part of a team, often in a more specialized software development company. And that database is likely to be part of a larger application for some specific business need. 10 LESSON 2: DATABASE ADMINISTRATION/ADMINISTRATOR 2. the Performance Database Administrator or Tuning Database Administrator. This administrator specializes in optimizing and improving the performance of various databases. Data access can then be optimized by use of indexes, which read like a book’s table of contents. DBAs help to ensure that the database is optimized to take advantage of the physical or virtual hardware on which it is hosted. 11 LESSON 2: DATABASE ADMINISTRATION/ADMINISTRATOR 3. the Application DBA. This role focuses specifically on integrating databases into the applications that use them 12 LESSON 2: DATABASE ADMINISTRATION/ADMINISTRATOR There are two main paths to become a database administrator: Focusing on being a DBA from the beginning of an IT career, or (more commonly) starting in a general ICT field, such as helpdesk support or development, and gradually becoming more proficient in database administration. Just like most of the ICT careers you will be needing some set of skill and character to be a DBA: Excellent troubleshooting skills, Logical thinking, common sense and above-average memory, 13 Curiosity and a strong desire to learn. LESSON 2: DATABASE ADMINISTRATION/ADMINISTRATOR Having one of this certification will boost your chances in getting your dream DBA job. Oracle: Oracle DB Certified Associate, Oracle DB Certified Professional Microsoft SQL Server: MCSE (Data platform and Business Intelligence) and the older MCDBA IBM: IBM Analytics Certification Oracle: MySQL Database Developer, MySQL Database Administrator MongoDB: Certified DBA, Certified Developer Cassandra: Certified Cassandra Administrator, Certified Cassandra 14 Architect, Certified Cassandra Developer LESSON 2: DATABASE ADMINISTRATION/ADMINISTRATOR When you are applying for your DBA job, make sure to be prepare to showcase your DBA expertise in relation to the following: Database installation and configuration Data security User creation and maintenance Database backups and recovery Database performance tuning and optimization Data transformation and loading Reporting and querying 15 LESSON 3: DATA SECURITY, DATA AVAILABILITY, DATA QUALITY Data Security is protecting databases from unauthorized access, breaches, and theft. Access Control: Restricting database access to authorized users. Encryption: Protecting data at rest and in transit with encryption methods. Backups: Ensuring that secure copies of data are always available. Audit Logs: Tracking who accessed or changed data. 16 LESSON 3: DATA SECURITY, DATA AVAILABILITY, DATA QUALITY Common Threats: SQL injection, insider threats, ransomware, malware attacks. 17 LESSON 3: DATA SECURITY, DATA AVAILABILITY, DATA QUALITY Data Security Best Practices: Implement multi-factor authentication (MFA). Regularly update and patch database software. Use role-based access control (RBAC). Monitor and audit access logs frequently. Encrypt sensitive data both at rest and in transit 18 LESSON 3: DATA SECURITY, DATA AVAILABILITY, DATA QUALITY Data Availability is Ensuring that data is accessible to users when needed. Redundancy: Duplicate data and systems to prevent downtime. Failover Systems: Backup systems that automatically take over in case of failure. Backup and Recovery Plans: Regular backups to ensure fast recovery in case of failure. Disaster Recovery: Planning for catastrophic events like natural disasters or cyberattacks. 19 LESSON 3: DATA SECURITY, DATA AVAILABILITY, DATA QUALITY Examples of Downtime Causes: Hardware failures, network issues, human errors, and cyberattacks. 20 LESSON 3: DATA SECURITY, DATA AVAILABILITY, DATA QUALITY Data Availability Best Practices: Implement High Availability (HA) architecture: Use clustered databases, load balancers. Regularly test disaster recovery plans. Automate backups and ensure offsite storage for disaster recovery. Use cloud-based solutions to enhance redundancy and scalability. 21 LESSON 3: DATA SECURITY, DATA AVAILABILITY, DATA QUALITY Data Quality is Ensuring that data is accurate, complete, and reliable. Accuracy: Data reflects the real-world object or event correctly. Consistency: Data should be uniform across all platforms. Completeness: No missing or incomplete records. Timeliness: Data should be up-to-date and available in real time. Relevance: Data should be applicable to the 22 business context. LESSON 3: DATA SECURITY, DATA AVAILABILITY, DATA QUALITY Consequences of Poor Data Quality: Poor decision-making, inefficiencies, loss of trust, compliance issues. 23 LESSON 3: DATA SECURITY, DATA AVAILABILITY, DATA QUALITY Data Quality Best Practices Use automated validation checks during data entry. Implement data cleansing processes to remove or correct inaccurate records. Establish a governance policy with data stewards. Ensure regular data audits for quality assessment. Standardize data collection processes. 24 LESSON 3: DATA SECURITY, DATA AVAILABILITY, DATA QUALITY RECAP: 1. Data Security protects against threats. 2. Data Availability ensures systems remain accessible. 3. Data Quality guarantees the usefulness of data for decision-making. Importance of balancing these three pillars to maintain a reliable and efficient database system. 25 THANK YOU