Chapter 2
The Role of the Database Administrator



In Chapter 1, you read about the new world of client/server computing and the different RDBMS systems. What about the people that manage and maintain these systems? This chapter looks at a small client/server network with 2 servers (a file/print server and a database server) and 15 client workstations that access the servers.

In this chapter, you learn about the different jobs and responsibilities required to maintain a client/server network. Begin by examining the type of tasks required to maintain the client/server network shown in Figure 2.1.

Figure 2.1.
A small client/server network.

Hardware

Figure 2.1 shows a total of 15 client machines and 2 servers. Someone must be responsible for maintaining the physical machines to ensure that they are continually running. This person is responsible for routine maintenance and upgrades such as adding more disk space and memory.

Network

The 15 client machines communicate with the 2 servers over the network. The network consists of the hardware and software that ties all the machines together and includes the cabling, routers, repeaters, and network protocols (TCP/IP, named-pipes, SPX/IPX, and so on). Again, someone must take responsibility to make sure that the network stays up and running. If the network goes down, none of the machines can talk to each other.

Operating Systems

All machines--clients and servers--use some sort of operating system. Because this is a book about NT SQL Server, you can safely assume that the servers shown in Figure 2.1 are running Microsoft Windows NT and that the clients are running Microsoft Windows or Microsoft Windows NT Workstation. Each client and server machine must be properly configured and set up.

File/Print Server

The file/print server needs general maintenance, backups, and upgrades to protect against the loss of data. Someone must be responsible for adding user accounts and installing new applications and maintaining the stability of the file/print server. After all, if the file and print server go down, users cannot perform their jobs.

Database Server

The database server requirements are similar to those of the file/print server. The difference is that the administration occurs with the RDBMS package. The database server must be set up and tuned correctly to produce the best performance. Above all, the data in the databases must be protected; if data is lost, the data must be restored. Someone also has to manage the users' access and security on the database server.

Who Does What?

You may be asking yourself, "For such a small client/server network, there seem to be a lot of different jobs and responsibilities to keep the whole network up and running." Imagine a client/server network 10 to 100 times the size of the relatively small network used in this example! So, who is responsible for which task? The answer to who does what can become quite complex because the size of an organ-ization and the size of the client/server network dictates who is responsible for each task. In some organizations, a single individual may wear many different hats. In other organizations, an individual may be more specialized. The following sections examine some of the general job titles and the responsibilities that accompany each position.

PC and Tech Support

The PC and tech support group is responsible for maintaining and setting up the hardware on the different client machines and sometimes on the server machines. If a new software package is installed or more disk space or memory must be added, the PC and tech support group is called in.

Network Administrator

The network administrator is responsible for maintaining the network. A network administrator makes sure that all the hardware components are working correctly and that the networking software is set up correctly. In many cases, the network administrator is also responsible for maintaining the network operating system.

System Administrator

The system administrator is responsible for maintaining the many different servers in the organization. The responsibilities include backup and recovery, maintaining user access and security, scheduling task and batch runs, and upgrading and maintaining the operating system.

Database Administrator

Someone once told me that the database administrator (DBA for short) is simply responsible for the data. Well, there is a lot more to being a DBA than just being responsible for the data; that is what you will concentrate on for the rest of the chapter!


NOTE: In many cases, you may notice that a fine line separates job responsibilities (where one job starts and ends), such as the possible overlap of the network administrator and the system administrator. Depending on their size, most organizations divide the work realistically (that is, no one person has too many responsibilities). One of the most important things to remember is that it takes a team effort to keep a client/server network healthy. Cooperation among the different individuals is a must.

What Is a Database Administrator?

In a very general sense, a database administrator is the individual responsible for maintaining the RDBMS system (in this book, the Microsoft SQL Server). The DBA has many different responsibilities, but the overall goal of the DBA is to keep the server up at all times and to provide users with access to the required information when they need it. The DBA makes sure that the database is protected and that any chance of possible data loss is minimized.

Who Are the DBAs?

Who are the DBAs and how do you become one? A DBA can be someone who, from the start, has concentrated in the area of database design and administration. A DBA can be a programmer who, by default or by volunteering, took over the responsibility of maintaining a SQL Server during project development and enjoyed the job so much that he or she switched. A DBA can be a system administrator who was given the added responsibility of maintaining a SQL Server. To start your journey to becoming a Microsoft SQL Server DBA, you need the following:


TIP: If you are part of a technical team looking for a Microsoft SQL Server DBA, do yourself a favor and volunteer. It is a great job and good DBAs are in demand.

DBA Responsibilities

The following sections examine some of the responsibility of the database administrator and how they translate to various Microsoft SQL Server tasks.

Installing and Upgrading a SQL Server

The DBA is responsible for installing SQL Server or upgrading an existing SQL Server. In the case of upgrading SQL Server, the DBA is responsible for making sure that, if the upgrade is not successful, the SQL Server can be rolled back to an earlier release until the upgrade issues can be resolved.

Monitoring the Database Servers Health and Tuning Accordingly

Monitoring the health of the database server means making sure that the following is done:

Using Storage Properly

Maintaining the proper use of storage means making sure that databases and transaction logs are created correctly, monitoring space requirements, and adding new storage space when required.

Performing Backup and Recovery Duties

Backup and recovery are the DBA's most critical tasks; they include the following aspects:

Managing Database Users and Security

The DBA is responsible for setting up user's database server login IDs and determining the proper security level for each user. Within each database, the DBA is responsible for assigning permissions to the various database objects such as tables, views, and stored procedures.

Working with Developers

It is important for the DBA to work closely with development teams to assist in overall database design, such as creating normalized databases, helping developers tune queries, assign proper indexes, and aiding them in creating triggers and stored procedures.


TIP: I have too often seen DBAs who were content to sit back and watch developers make bad design and SQL Server decisions. I have also seen situations in which the DBA wanted to be involved in design decisions but management prevented it because it was not the DBA's "job." Don't be underutilized. If you are in this situation, show your management this tip! Take an active role in new project development. The entire team will benefit from your insight and knowledge!

Establishing and Enforcing Standards

The DBA should establish naming conventions and standards for the SQL Server and databases and make sure that everyone sticks to them.

Transferring Data

The DBA is responsible for importing and exporting data to and from the SQL Server. In the current trend to downsize and combine client/server systems with mainframe systems, importing data from the mainframe to the SQL Server is a common occurrence.

Replicating Data

SQL Server version 6.x has added a new requirement and responsibility for the DBA: setting up and maintaining data replication throughout the workplace. Replication is a tremendous feature that will play a big part in many organizations.

Scheduling Events

The database administrator is responsible for setting up and scheduling various events using Windows NT and SQL Server to aid in performing many tasks such as backups and replication.

Providing 24-Hour Access

Although you may say to yourself, "No way!," the database server must stay up and the databases must always be protected and online. Be prepared to perform some maintenance features and upgrades after hours. If the database server should go down, be ready to get the server up and running. After all, that's your job.

Learning Constantly

To be a good DBA, you must continue to study and practice your mission-critical procedures, such as testing your backups by recovering to a test database. In this business, things change very fast so you must continue learning about SQL Server, available client/servers, and database design tools. It is a never-ending process.

Tricks of the Trade

Now that you understand the different responsibilities of a DBA, how can you learn the tricks of the trade? You are off to a good start by reading this book. The following sections examine some other ways to learn the tricks of the trade.

Classes and Training

Taking a Microsoft-certified SQL Server training class is very good way to get started. Find a class that gives you hands-on classroom training. The class can introduce you to many of the concepts and procedures required to maintain SQL Server. To find out about authorized Microsoft SQL Server training centers near you, call 1-800-SOL-PROV and ask for information on Microsoft Solution Provider Authorized Technical Education Centers. On CompuServe, GO MECFORUM.


TIP: If you go to class, make sure that when you return, you start practicing immediately what you learned in class. Most classes are three to five days; to retain the information, you must start practicing and using it immediately.

On the Job

The real school of how to be a DBA is on the job; for many DBAs, that is where they learned. On-the-job training can be difficult when you are the only one learning a system with which no one else is familiar. You may have the luxury of having a seasoned DBA teach you the ropes. Ultimately, we all learn on the job.


TIP: Practice, practice, practice. Constantly practice different procedures and tasks, such as backing up and recovering data or importing data on a nonproduction server. When the day comes to perform the task, you will be well prepared.

Microsoft TechNet, Microsoft Developers Network, and Online Services

Take advantage of the vast knowledge base of articles Microsoft makes available on the TechNet and Microsoft Developers Network (MSDN) CDs. Many times, you can solve a problem simply by searching the two CDs for the problem and possible resolution. Use online services that provide a Microsoft SQL Server forum. On the forum, you can post problems and get help from other DBAs or you can scan through the various messages posted and learn how to solve problems you have not yet encountered. The CompuServe SQL Server forum is GO MSSQL. Microsoft's Web page for SQL Server is http://www.Microsoft.com/SQL. You can learn more about Microsoft TechNet by calling 1-800-344-212, navigating the Internet to technet@microsoft.com, or using CompuServe's forum, GO TECHNET. To learn more about the Microsoft Developers Network (MSDN), call 1-800-759-5474 or use CompuServe's forum (GO MSDN).

Magazines and Books

Subscribe to various database magazines that keep you abreast of topics such as the latest database design and development tools, relational database concepts, and SQL Server. Also search the book store for books on database design and SQL Server (like this book). Pinnacle Publishing publishes a magazine called the Microsoft SQL Server Professional; they can be reached at CompuServe 76064,51, on the Internet at 1119390@mcimail.com, or by fax at 1-206-251-5057. Another magazine that provides very good articles on SQL Server and Windows NT is Windows NT Magazine; reach the publishers at 1-800-621-1544 or on the Internet at winntmag@duke.com.

Certification

Microsoft offers product certification for SQL Server database administration and SQL Server database implementation. It is strongly encouraged that you take the test to become certified. Certification does not replace experience and training but it will help point out possible weakness in your understanding of SQL Server and give you credibility (because you understand the concepts and procedures to maintain SQL Server). To find out more about Microsoft certification, look on CompuServe at GO MECFORUM or on the Internet at http://www.Microsoft.com. You also can call 1-800-636-7544 and ask for the Certification Roadmap.


TIP: We are often asked, "What do I need to do to pass certification? Will this book help?" Of course this book will help! Here are some words of advice about the certification test: Before taking the test, review the test outline for SQL Server (which is part of the Certification Roadmap). The certification test asks questions on only the topics listed in the test outline. Make sure that you are familiar with each of the topics. Although managing a Microsoft SQL Server is a graphical experience, the test will more than likely ask you about the command to create a database (instead of how to create a database using the Enterprise Manager), so make sure that you are familiar with the commands required to perform various administration tasks. Last but not least, take the practice test that comes with the Certification Roadmap. The practice test gives you a general idea of the types of questions asked on the test. Do not take the real test until you do well on the practice test.

How the DBA Interacts with Other Team Members

Now that you have decided to become a DBA, how will you interact with other team members such as the system administrator, network administrator, developers, and users? Many times, these relationships are hard to determine because each organization has people filling one or many different roles. Based on earlier job descriptions, however, the following sections quickly examine the types of interaction to expect.

System Administrator and Network Administrator

A DBA's interaction with the network administrator is mostly concerned with the type of network protocols that can be used and the network address or port number that can be used for the server. If users are complaining about query times and SQL Server is executing the queries very fast, you and the network administrator should examine possible networking problems.

The interaction of the system administrator and the DBA is much tighter than the interaction of the network administrator and the DBA. The system administrator is responsible for tuning the Windows NT server on which your SQL Server runs. The system administrator is responsible for adding the hard drives and storage space required for you to create database devices. If you choose to use integrated user security with SQL Server, you must work with the system administrator to set up the correct NT user accounts and groups. The different types of backup and recovery procedures for the NT Server and the SQL Server should be worked out by both parties because, in some cases, the system administrator may have to restore a system drive that contains a database.

Developers

The interaction of DBAs with developers is where I have seen the greatest differences in organizations' definitions of a DBA. In some organizations, the DBA works very closely with the developers; in other organizations, the DBAs work very little with the developers and are stuck maintaining the developers' systems and designs without any input. It is my firm belief that the DBA should work very closely with the developers; after all, the DBA is the one maintaining the database side of the application; in many cases, the DBA has the most experience in relational database design and tuning. The DBA should design, aid, or review any and all database designs for the organization. The DBA should also provide assistance in helping the developers select proper indexes, optimize queries and stored procedures, as well as being a source of information to the developers.

Users

In most organizations, the DBA's interaction with the users of the system is limited to user account maintenance, security, and database recovery requirements.

Summary

The role of the database administrator is very important in an organization. The job can be challenging and exciting. If you are a DBA or want to be a DBA, remember that it is important to constantly study SQL Server and database tools. Become certified and practice your backup and recovery procedures. Following is a quick list of the many duties and responsibilities of a DBA:



DISCLAIMER


To order books from QUE, call us at 800-716-0044 or 317-361-5400.

For comments or technical support for our books and software, select
Talk to Us.

© 1997, QUE Corporation, an imprint of
Macmillan Publishing USA, a Simon and Schuster Company.