Database Administration Operations Flashcards

Question 1

What is the main purpose of a database backup?

Accepted Answer

A database backup is a copy of database data and supporting files that can be used to restore the database after loss or damage. Backups protect against hardware failure, accidental deletion, software errors, and data corruption. Without a recent, tested backup, an organization may be unable to recover its data after an incident.

Question 2

What is the difference between a full backup and an incremental backup?

Accepted Answer

A full backup copies the entire database at a point in time, so it can be restored on its own. An incremental backup copies only the data changed since the most recent backup of any type, which saves time and storage. Restoring from incremental backups requires the last full backup plus each incremental backup.

Question 3

What does the recovery point objective describe in disaster planning?

Accepted Answer

The recovery point objective is the maximum acceptable amount of data loss, measured as the time between the last usable backup and the moment of failure. A shorter objective means backups or replication must run more often. It is set by the business based on how much data it can afford to lose.

Question 4

Why is a restore test important even when backups complete successfully?

Accepted Answer

A backup job that reports success only shows that files were written, not that the database can be recovered from them. Restore tests confirm that the backup is readable, complete, and compatible with the target system. Administrators may discover missing logs or corrupt files only during a test rather than during a real emergency.

Question 5

What is the purpose of transaction logs in backup and recovery?

Accepted Answer

Transaction logs record every committed change made to the database, in the order the changes occurred. They allow administrators to roll forward from a backup, reapplying work completed after that backup was taken. Log backups therefore reduce potential data loss and let recovery stop at a specific point in time.

Question 6

What does point-in-time recovery accomplish when a database is damaged by an error?

Accepted Answer

Point-in-time recovery restores a database to the state it had at a chosen moment, just before an error such as a bad update or accidental table drop. It requires a full backup taken before that moment together with the transaction logs covering the period up to it.

Question 7

Why should backup files be stored away from the primary database server?

Accepted Answer

Storing backups away from the primary server protects them when the server is lost to fire, flood, theft, or a site-wide outage. If backups sit on the same disks or in the same room as the data, a single event can destroy both copies. Many keep at least one copy in a separate location.

Question 8

What is the difference between a logical backup and a physical backup?

Accepted Answer

A logical backup stores database content as exported statements or structures, and can be loaded into a compatible system. A physical backup copies the actual data files, log files, and control files at the storage level. Logical backups are more portable across systems, but physical backups usually restore faster for large databases.

Question 9

What is the principle of least privilege in database security?

Accepted Answer

The principle of least privilege means each user, application, or role receives only the permissions needed to perform its required tasks, and no more. This limits the damage that a compromised account or a mistake can cause. Administrators review granted permissions regularly and remove any that are no longer needed.

Question 10

How does role-based access control simplify permission management for many users?

Accepted Answer

Role-based access control assigns privileges to named roles, then grants roles to users rather than granting each privilege to each person. When a user changes jobs, the administrator moves them to a different role instead of editing many individual grants. This approach is easier to audit and reduces the chance of inconsistent permissions.

Question 11

What is the difference between authentication and authorization?

Accepted Answer

Authentication verifies who a user is, usually through a password, certificate, token, or another credential. Authorization decides what that verified user is allowed to do, such as reading a table or running a stored procedure. A user must pass authentication before authorization rules are applied to their requests.

Question 12

Why should shared database accounts be avoided in production systems?

Accepted Answer

Shared accounts make it impossible to tell which person performed a given action, which weakens accountability and audit trails. They also tend to keep broad privileges long after the original need has passed. Individual accounts let administrators disable one person's access quickly when they leave the organization or change roles.

Question 13

What is a password policy, and which settings does it commonly control?

Accepted Answer

A password policy is a set of rules that defines requirements for passwords, such as minimum length, complexity, expiration intervals, and reuse restrictions. Commonly it also sets account lockout after repeated failed logins. Good policies balance security with usability, since overly frequent forced changes lead users to write passwords down or make trivial variations.

Question 14

What is the purpose of auditing database activity?

Accepted Answer

Auditing records who performed which actions, when they occurred, and whether they succeeded, creating a trail that can be reviewed later. Audit records support investigations after suspicious activity or data loss. They also help organizations demonstrate that access controls are working as intended during internal or external reviews.

Question 15

How should administrators handle an account when an employee leaves the organization?

Accepted Answer

The account should be disabled promptly, and any open sessions should be terminated so access ends immediately. Ownership of objects the account created should be reviewed and reassigned if needed, so applications do not break. Keeping the record rather than deleting it preserves the audit history of past actions.

Question 16

What is a privileged account, and why does it need extra controls?

Accepted Answer

A privileged account holds elevated rights, such as the ability to change schema, grant permissions, or read all data. Because misuse of these accounts can cause severe damage, they need stricter controls like separate credentials, multifactor authentication, and tighter logging. Administrators should use them only when a task requires it.

Question 17

What is the purpose of monitoring a database server?

Accepted Answer

Monitoring collects metrics about availability, resource use, and workload so administrators can detect problems early and plan capacity. Typical metrics include CPU use, memory pressure, disk input and output, active connections, and query response times. Good monitoring turns raw numbers into alerts when values move outside normal ranges.

Question 18

What is a baseline, and why is it useful when diagnosing slow performance?

Accepted Answer

A baseline is a record of normal performance measurements collected over typical workloads, such as average response times and resource use. When performance degrades, administrators compare current metrics against the baseline to see what changed. Without a baseline, it is hard to tell whether a reading is unusual or simply normal for the system.

Question 19

What is an execution plan, and how does it help with query tuning?

Accepted Answer

An execution plan is the sequence of operations the database engine chooses to retrieve the requested rows, such as index scans, joins, and sorts. Reading the plan shows where most of the cost occurs, for example a full table scan. Tuning may involve rewriting the query, adding a suitable index, or updating statistics.

Question 20

Why does an index speed up some queries but slow down data changes?

Accepted Answer

An index is a separate structure that lets the engine find matching rows without scanning the whole table. Each insert, update, or delete must also maintain every index on the table, adding work and storage cost. Administrators balance faster lookups against extra write cost by indexing only columns that queries filter or join on.

Question 21

What is a deadlock, and how does the database usually resolve it?

Accepted Answer

A deadlock occurs when two or more transactions each hold a lock that the other needs, so none can proceed. Most database engines detect deadlocks and roll back one of the transactions as the victim, allowing the others to finish. Accessing tables in a consistent order and keeping transactions short reduces deadlocks.

Question 22

What do wait statistics reveal about database performance?

Accepted Answer

Wait statistics show where sessions spend time waiting instead of working, such as for locks, disk reads, memory grants, or network responses. The largest waits point to the most likely bottleneck, which guides where to investigate first. Comparing waits over time helps confirm whether a tuning change actually reduced delays.

Question 23

Why is it important to change one performance setting at a time?

Accepted Answer

Changing one setting at a time lets administrators attribute any improvement or regression to a specific change. If several settings change together, the cause of a new problem becomes difficult to identify and reverse. Each change should be recorded along with the reason for it and the measured effect.

Question 24

What is the difference between vertical and horizontal scaling for a database?

Accepted Answer

Vertical scaling adds more capacity to a single server, such as more memory, faster storage, or additional processors. Horizontal scaling spreads data or load across several servers, which adds capacity by adding machines. Vertical scaling is simpler to operate, but horizontal scaling can grow beyond the limits of any single machine.

Question 25

What is routine database maintenance, and what tasks does it usually include?

Accepted Answer

Routine maintenance is the scheduled work that keeps a database healthy over time, such as checking integrity, updating statistics, rebuilding or reorganizing fragmented indexes, and clearing old log or temporary data. Performing these tasks on a regular schedule prevents gradual slowdowns and reduces the risk of failures.

Question 26

Why must database software be patched, and what risks does delay create?

Accepted Answer

Patches fix security vulnerabilities, software defects, and sometimes data integrity bugs found after a release. Delaying them leaves known weaknesses open to attackers and can leave known defects in place. Patching should follow a tested process, since an unplanned update can also introduce new problems.

Question 27

What should administrators do before applying a patch to a production database?

Accepted Answer

They should read the vendor's release notes, test the patch in a non-production environment that closely matches production, and confirm that a verified backup exists. They should also prepare a rollback plan in case the patch causes problems. Finally, they should notify stakeholders and schedule the change within an approved window.

Question 28

What is the purpose of a change management process for database changes?

Accepted Answer

A change management process records proposed changes, assesses their risk, obtains approval, and documents the outcome. It creates accountability and gives teams a chance to catch conflicts before work begins. Following the process prevents surprise modifications that could disrupt applications or other administrators' work.

Question 29

How do administrators verify database integrity during maintenance?

Accepted Answer

They run consistency checks that examine the physical and logical structure of data pages, indexes, and allocation records for errors. They also review error logs for corruption messages and compare row counts or checksums against expected values. Any findings should be investigated immediately, because corruption tends to spread if left alone.

Question 30

Why does index fragmentation matter, and how is it usually addressed?

Accepted Answer

Fragmentation occurs when index pages become out of order or partly empty after many inserts, updates, and deletes, which can increase reads and waste space. Administrators address it by reorganizing or rebuilding affected indexes. The right choice depends on the amount of fragmentation and the downtime available.

Question 31

What is a statistics update, and why does the optimizer depend on it?

Accepted Answer

A statistics update refreshes information about data distribution, such as row counts and value frequencies in each column. The query optimizer uses these estimates to choose execution plans, so stale statistics can lead to poor choices. Updating statistics after large data changes helps the optimizer estimate how many rows each step will return.

Question 32

What should be documented after completing a maintenance task?

Accepted Answer

Administrators should record what was done, when it was done, who performed it, and what results were observed. They should also note any errors, warnings, or deviations from the plan. Clear records make future troubleshooting faster and show auditors that maintenance follows a consistent, repeatable process.

Question 33

What does high availability mean for a database system?

Accepted Answer

High availability means designing the system so that the database remains accessible despite the failure of individual components. It is usually measured as the percentage of time the service is operational over a period. Achieving it typically involves redundant hardware, replication, and automatic failover.

Question 34

What is the difference between synchronous and asynchronous replication?

Accepted Answer

Synchronous replication waits until the standby copy confirms each change before the primary reports the commit as complete. This protects committed data but adds latency to every write. Asynchronous replication sends changes without waiting, which is faster, but recent commits may be lost if the primary fails before they arrive.

Question 35

What is failover, and what must happen before clients can use the standby?

Accepted Answer

Failover is the process of moving database service from a failed primary to a standby server. Before clients can use the standby, it must be promoted to accept writes, and connections must be redirected to it. Applications should retry connections gracefully so that short interruptions do not cause lasting errors.

Question 36

Why is a split-brain condition dangerous in a clustered database?

Accepted Answer

Split-brain occurs when two nodes both believe they are the active primary, so each accepts writes that the other does not see. The resulting data divergence can be very difficult to reconcile. Clusters prevent it with quorum voting, fencing of failed nodes, or witness systems that decide which side may continue.

Question 37

What is a quorum, and why do clustered systems rely on one?

Accepted Answer

A quorum is the minimum number of nodes that must agree before the cluster makes a decision, such as promoting a new primary. Requiring a majority ensures that only one partition of a divided cluster can continue operating. This protects data consistency, but it halts writes when too few nodes are healthy.

Question 38

What does a hot standby server provide that a cold standby does not?

Accepted Answer

A hot standby runs continuously, stays synchronized with the primary, and can take over quickly. A cold standby is powered off or idle, so bringing it into service requires restoring data and starting software, which takes longer. The choice balances recovery speed against the cost of keeping extra capacity running.

Question 39

What is capacity planning, and which measurements guide it?

Accepted Answer

Capacity planning estimates the storage, memory, processing, and connection resources a database will need to meet expected demand. Planners use historical growth trends from monitoring data, along with projected business activity, to forecast future needs. Good plans leave headroom so that unexpected spikes do not cause immediate slowdowns.

Question 40

How should a disaster recovery test be planned and reviewed?

Accepted Answer

Plan the test with a defined scenario, clear success criteria, and named roles for each participant. Run it in a controlled environment, measure actual recovery time and data loss, and record any steps that failed or took longer than expected. Review the results and update the runbook and objectives based on what was learned.

Database Administration Operations Flashcards

1Backup and recovery fundamentals

2User and access management

3Monitoring and performance tuning

4Maintenance and patching

5High availability and capacity planning

Learning Resources