Database Administrator Interview Question - Advanced Level

Level 5: The Caretaker
You understand the system inside and out and protect it as such. If anything happens, you’re the first one to pick up on how bad it can get and the last one to go home after its been fixed. Sometimes pictured as a miracle worker, it’s your job to make sure the project keeps going, building a team that can find new ways to improve upon the designs, and keep the wheels spinning.

• What is ODBC?

Open Database Connectivity is a way to make different kinds of frontends talk to different data sources (DSNs) such as Databases. The specifics available depend on the type of application being used, the driver being used and the backend to which it is being applied.

• For Oracle systems, what is OFA?

Optimal Flexible Architecture (OFA) is the recommended layout for installing and configuring an Oracle database.

• For Oracle systems, what is error “ORA-01034”?

The full error is “ORA-01034: ORACLE not available”. While there are many potential causes, the most common is that the service is just not running. The resolution is to start the service, then see if the error comes back.

• What is Normalization?

When most people first start working with databases, the first instinct is to create massive tables for storing data — one place, one query — keeps things simple. However, as they grow to unmanageable levels, it is a good idea to look into Database Normalization. This idea allows for data to be split off into smaller more efficient tables that (hopefully) reduce the amount of duplicate data. In this way, smaller queries can be run on individual tables instead of having everybody always talking to one big one — thus improving performance.

• For Microsoft SQL Server, what is a DMV?

Dynamic Management Views are functions built into Microsoft SQL Server that allow for troubleshooting, diagnostics and server health monitoring.

• What are the default ports for MySQL, SQL Server and Oracle, and can/should this be changed?

The default port for MySQL is 3306, and can be changed in Windows as noted in this article or in *nix as noted in this article. The default port for Microsoft SQL Server is 1433, and can be changed as noted in this article. The default port for Oracle is 1521, and can be changed as noted in this article. Depending on your security stance, changing the port that your database server uses can be a good way to lower your profile and reduce the amount of unauthorized access attempts against the server.

• For Microsoft SQL Server, What is Log Shipping?

A form of backup on Microsoft SQL Server, Log Shipping is similar to replication and allows for rapid failover if the main server goes down. One thing to bear in mind, however, is that a log shipping based failover must be activated manually; it will not switch over automatically.

• For Microsoft SQL Server, what is DBCC?

Database Console Commands (DBCC) are a series of utilities for SQL Server designed for maintenance and reporting. A full list of the commands can be found here.

• What is Cloud Computing?

Cloud Computing is usually a catch all term for data being stored “over there”. Placing high-requirement applications onto dedicated hosting services can be beneficial depending on the application, however it can also cause catastrophic security problems and availability issues. It is therefore highly recommended to keep important data in-house, and only outsource in situations that it cannot be avoided. Cloud Computing, Big Data and Data Mining are many times talked

about in the same sentence since processing power required for one usually means the others become viable either as a requirement or a side effect.

• What is Hadoop?

Hadoop is a Data Mining application designed to handle very, very large amounts of data across a wide variety of environments — from one to thousands of systems. Used in situations that don’t necessarily fit into standard database structures, its main strength is being able to take one giant project and split it off to each of its member servers, have them each process their own job, then have their findings recombined into one viewable result.