SAs and DBAs - Interactions across Traditional Lines of Responsibility

John Berninger and Dana Lee

Database administration is a new field relative to systems administration. In the infancy of database systems, the systems administrator (SA) also acted as the database administrator (DBA), since the database was actually no more than a series of either flat ASCII files or KSAM (Keyed Sequential Access Method) files referenced with tools akin to sed, awk, and grep. As the sizes of databases grew, and the number of related and cross-referenced pieces of information grew, these flat files became unwieldy and far too slow, even with the improvements in searching algorithms, to keep up with the expected database lookup rates. In response to these pressures, the database industry created a new way of looking for information within a data file and a new database structure - the relational database or RDBMS.

With the development of the relational database system and the specialized knowledge required to fully exploit all of its benefits, there was a split between core systems administrators and database-oriented systems administrators that further widened with time and specialization into today's difference between system administrators (SAs) and database administrators (DBAs). These two groups often report to different management chains and have different goals for system performance and reliability, but must work closely together to achieve a reliable and available relational database instance installation. In this artitcle, we examine different ways the SA and the DBA interact during the various phases of a database project's life cycle and attempt to point out ways in which that interaction can be used for cross-training and process improvement by both the DBA and the SA.

Capacity Planning

There are actually two areas of planning in which the SA and DBA must work together: growth planning, which involves planning for continued growth and use of an established system; and capacity planning, which involves estimating requirements for a new instance or a soon-to-be-acquired system. Capacity planning involves estimating hardware requirements, such as numbers of processors, speed of processors, amount of RAM, amount of disk space, number of concurrent users, and the usage load of the database instance. Some of this information must be supplied by the application development team or application vendor, but the SA and DBA must evaluate these numbers for rationality and then use these load estimates to configure the machine appropriately. If the application is such that there will be several hundred possible concurrent users accessing a multi-gigabyte database instance, that environment will require significantly more horsepower than one used by at most ten users holding only a few megabytes of information.

This is where the SA and DBA must be able to communicate with each other most effectively; the DBA cannot be expected to be well-versed in the intricacies of multi-threaded kernels, with threads bound to specific CPUs, or the complicated interrelationships of certain kernel tunable parameters. Likewise, the SA cannot be expected to fully appreciate the internal hash tables used to search and sort database records into tables and fields within the RDBMS system. They must both be able to communicate these low-level requirements by using higher-level phraseology and must also be able to explain these requirements in terms both can relate to. Capacity planning often involves a lot of educated guessing, so the DBA and SA must also have confidence in each others skill set and knowledge base. Without this confidence, any attempt at capacity planning can quickly lead to wildly divergent system specifications and, perhaps more detrimentally, bad feelings between the DBA and SA.

Installation and Creation

Before installing an RDBMS system and creating database instances, the database system must be planned in accordance with the underlying operating system configuration, and the OS must be installed according to several database system requirements. These requirements will differ between database packages. The vendor of the package that you (the SA and DBA) are going to install and use must be consulted for specific system and OS requirements. Often, an RDBMS system will not require extensive reconfiguration and rebuilding of the operating system, but will instead have recommended settings for areas such as kernel parameters, shared memory availability, and thread scheduling for optimal database performance. These recommendations should usually be followed fairly closely, as performance of an RDBMS usually either does not degrade with additional load or degrades extremely rapidly.

The next part of the installation plan is completely at the joint discretion of the DBA and SA; it involves planning the actual file locations for the database system binaries and data files. These directory paths should be plotted in concert with any local system standards of the company, any corporate standards, any OS requirements, and any RDBMS system requirements of the specific package. Once the DBA and SA have agreed on file locations and install destinations, these should be reviewed for coherency and standards conformance by at least two other technical representatives unassociated with the system or project under consideration. Again, this is to ensure that the system meets accepted standards, and also that it will be easily understood and maintained for the life of the instance or system despite possible employee turnover.

During the installation of a relational database package, the SA and the DBA must work most closely together so that each knows the assumptions under which the other is working. They must determine and publish the directory paths for the database executables, the binary data files, the log files, and any other supporting path areas required during the installation. Generally, the SA is required to generate a user account for the actual database programs; in the case of an Oracle installation, this user is most often named 'oracle'. The actual installation of the RDBMS binaries is then performed by the DBA using this account, which automatically ensures that binary files have the proper user and group ownerships and permissions. After the binaries are installed, some post-install configuration may have to take place under the authority of the 'root' ID, and again the DBA and SA must work closely together.

After the installation has been verified, the DBA and SA should audit the system to ensure that the RDBMS package was installed according to the path decisions previously made, and that sufficient space remains in all filesystems for normal use. A general rule of thumb is that, at this point, filesystems which will not house data files for RDBMS instances should have no less than 5-10 percent available space, and filesystems that will house data files for RDBMS instances should either be completely empty or have only empty directory structures in place. It is generally considered less than optimal to place RDBMS data files on the same filesystem used to store regular user files or other database instances. For example, if three instances of an RDBMS reside on a given system, the optimal solution for storing data would be on four different partitions - one for each database instance, and one for conventional user files.

The next task is to actually build the database instance. Again, the directory locations for the data files and any instance support files should be pre-determined and agreed upon by the DBA and the SA. Examples of database instance support files would be rollback and rollforward logs for an Oracle instance, or AI (after image) and BI (before image) extents for a Progress instance. Once the proper destination directories are known and agreed upon, the SA can do little toward actually building the instance. At this point, the DBA takes the lead, and constant communication is no longer as important. The SA and DBA should still keep one another informed of any significant milestones or developments that occur.

Before building a database, some decisions must be made. Some of the questions the DBA should consider before building the actual instance are: How big will the database be?; Where do I want to store the database?; Do I want to mirror/RAID the data across multiple disks?; and What do I want to name the database?. Determining answers to these and other questions are critical to proceeding with the build process. Once these questions are answered, the DBA and SA again need to work together to implement the architecture for the specific instance to be built. Getting a database almost built and then running out of disk space would put both the DBA and SA in a decidedly unhappy place; planning is extremely important to the success of a database project.

Once all the decisions have been made, it's time to actually build the database. For a Progress database, a structures file must be created. This file breaks out the database size and structure. Then this file is used to create the database. If After-Imaging (AI) will be utilized, AI must be started before the database is started.

System Access

As noted earlier, the interaction of the DBA and SA does not end with the successful creation of an RDBMS instance. The DBA will need to perform certain routine tasks requiring system-level access rights, and the SA must perform tasks requiring database-level access rights. This issue becomes extremely acute when access is restricted and the DBA and SA groups do not interact. In a particularly bad environment, some routine 10-minute tasks could take as long as two weeks to coordinate and perform.

The first area we'll address here is the access requirements for the SA. In many cases, operations such as shutdowns, startups, and backup procedures cannot be performed on the database instance by the 'root' account; this is usually a default security setting within the RDBMS package. The SA is responsible for system backups, however, which invariably include backing up the database instance. Relational databases are notorious for being uncooperative with backup processes while the database instance is online and available. This leads to a situation where the DBA will need to grant the SA access to shut down the database at a prearranged time, perform a backup of the system and RDBMS data files, and restart the database for normal use. One way to achieve this end is for the SA to simply take that authority by issuing the su command with options to perform the shutdown and startup under the assumed database ID.

Although su is sometimes used as standard operating procedure, it is relatively impolite. A more elegant method to solve this issue would be for the DBA to set up a database administrative login associated with the instance to be backed up for the 'root' account and to grant only those privileges required to perform the necessary tasks. This allows the SA relatively seamless access to the database for backup purposes, but also allows both the SA and DBA to audit backup or other user activity. It also requires cooperation between the DBA and SA, which ultimately leads to a more smoothly functioning database. Next, let's look at where the SA needs to grant system access to the DBA.

For the DBA, an account on the server is created for the database owner (i.e., an 'oracle' ID for an Oracle database or a 'progress' ID for a Progress database). This account is used for DBA procedures such as starting and stopping the database, backups, data exports, and other routine database-instance maintenance tasks. The filesystems for the database binaries and database files should be owned by the database login account and should have read, write, and execute rights for this account. Where appropriate, these files should also grant read and execute permissions to the database group (most often 'dba', the standard default group ID for most RDBMS systems) and 'other' (all other users on the system).

Backups

Getting good backups is perhaps the most important thing to a system administrator, second only to data availability. The first and last things many administrators do is to take a complete system backup, then take a second one in case the first is not usable. With modern relational databases, this task becomes even more critical; the database can no longer be easily restored by a simple (or sometimes complex) combination of the awk, sed, and grep commands as it could when the database was no more than flat ASCII text files. There are three main types of backups that can be performed on a RDBMS system. In increasing order of reliability and efficacy, they are fuzzy backups, warm (or hot) backups, and cold backups.

Fuzzy backups are the backups taken when the database instance is active and the tools for online (aka, warm or hot) backups provided with the RDBMS system are not used. For small databases (i.e., <100k), this type of backup may be effective, but even that is doubtful. The main problem with fuzzy backups is that in the time it takes to proceed through the entire instance being backed up, the likelihood that the instance has changed is approaching 100%. This can result in internal inconsistencies when a restore/recover attempt is made, resulting in a non-recoverable database instance. For that reason, fuzzy backups are generally not recommended for any purpose; they are inefficient, unreliable, and more likely to fail than to succeed.

The next type of backup is a warm (or hot) backup. This backup is achieved using tools provided with the RDBMS package to backup the instance(s) while they are active. Filesystem backups of this type can be performed while the database is open; this usually involves writing all transactions to a log file. This allows the SA and DBA to obtain a good backup despite the fact that the database is open. These backups can be restored such that the database is brought back to the state it was in at a specific point in time, similar to performing a snapshot (or cold, detailed later) backup. Specific examples of enabling warm backups include, but are not limited to, running an Oracle instance in ARCHIVELOG mode and enabling After-Imaging in Progress.

The third type of backup mentioned is a cold backup. This type is also known as an offline backup because it is taken when the database instance is shutdown and offline. The inactive data storage files are not accessed by the database itself during the backup, so it appears to the RDBMS package as if the backup occurred in literally zero time, an instantaneous snapshot of the instance. This is the most effective method of backing up an RDBMS instance, as it involves the storage of data files that are completely static during the backup.

The RDBMS system is effectively placed into stasis for the duration of the backup; so when a recovery is made, all internal consistency checks pass. The cold backup is the most efficient method of backup as well, since you can apply mirroring or other system-level techniques. For example, you can rapidly clone the inactive database instance to be backed up, producing a duplicate image. Then, you can bring the original back online, while the duplicate is backed up at a more leisurely pace. This also assists in increasing data availability and overall system uptime percentages - benefits which SAs, DBAs, and operations managers will all appreciate.

Next, we will look at automating backups so that the SA or DBA doesn't have to be at work at 2:00 in the morning to initiate them manually. One useful feature of the UNIX operating system is its ability to schedule jobs at a later time either once or periodically through the at or cron facilities, respectively. In cooperation with the DBA, the SA should schedule backups of the appropriate type at regular intervals and then insert these backups into the scheduling stream. This can be done any number of ways, including through the at and cron commands, but will most likely involve some scripting effort on the part of the SA. If simplistic scripts are used (e.g., nothing more than stringed command stacks), minimal testing and auditing is necessary. However, if the SA develops more complex scripts with logic decisions and control branches (e.g., if commands, loops, etc.), the DBA and SA should test and audit those scripts until both parties are satisfied that the scripts perform as expected.

The backup of large databases will often involve the use of sophisticated third-party backup software and tape libraries. Most of these packages have modules that integrate with specific RDBMS systems. Here, again, the SA and DBA will need to cooperate to ensure that the backup software is configured properly, and functions as expected.

Growth Planning

Growth planning, as mentioned earlier, involves estimating future requirements for an established RDBMS instance with continued use and normal expansion rates. This is sometimes problematic, for several reasons. The DBA can analyze database usage and determine the load in the database instance; the SA can analyze system load and determine load on a per-disk, per-filesystem, or per-CPU basis; these two sets of numbers do not always match up as nicely as they should. Another issue is that RDBMS systems tend to use either raw storage, in which case an entire disk is either allocated to the database instance or not as a unit and cannot be subdivided into partitions, or preallocated data storage areas. In the first case, expanding the storage space can be accomplished by simply adding another disk to the system and making it available to the database instance.

In the second case, expanding the database is tricky at best. Because the data files are preallocated, the operating system sees the same amount of space usage on disk whether the database instance has 10 MB of information or 600 GB of information. In planning for growth of this form of database, the SA and DBA must not only work closely together, but must be very careful that one expansion does not later create a situation requiring a complete instance recreation and restoration. This situation would involve an undue amount of rework - a concept that is generally abhorrent to administrators.

Conclusion

A systems administrator is not a database administrator, and neither is a database administrator a systems administrator. These two positions were at one point combined, and are still in some cases, but the complexity of both areas has become such that they are distinctly divergent specialties. Despite this divergence, however, these two positions must work closely together throughout the life of a database system or project, from initial system capacity planning through acquisition, installation and creation, into growth planning and routine maintenance. Although a single individual can be knowledgeable in both areas, a truly efficient database system requires two individuals or groups working closely together and cooperating easily: one specializing in the systems aspect and the other specializing in the database aspect.

About the Author

Dana Lee is a UNIX Systems Administrator at BB&T in Wilson, North Carolina. Dana is mostly involved in projects on the Sun Solaris platform and database operations. Dana is a graduate of Western Kentucky University in Bowling Green, Kentucky with a B.S. degree in Computer Information Systems. Dana can be contacted at dlee@bbtnet.com. John Berninger is a UNIX sysadmin at BB&T's main Operations Center in Wilson, North Carolina. He has recently been designated the lead engineer for HP-UX platforms in addition to his other responsibilities on Sun Solaris and IBM AIX platforms. He can be reached at johnwb@earthlink.net.