Basic and Optional Duties of the SQL Server DBA

3 4

The DBA might have a variety of duties, depending on his or her company. There is, however, a set of basic duties that most DBAs are charged with performing. If these duties don't match your job, don't worry; every DBA has different responsibilities. This chapter is designed to introduce you to the duties that you might not be familiar with and to offer the novice a window into the world of the DBA. The following responsibilities and jobs of the SQL Server DBA are divided into categories. These categories are not ordered by importance or by the amount of work that these responsibilities entail.

Installation and Configuration

The SQL Server DBA is often called upon to install new software on the system, to configure hardware and software, or at least to participate in the project. Sometimes the DBA is required only to create the specifications for the installation and configuration. In any case, the DBA should be involved in this process in order to guarantee that the system and the database are configured properly. Part of the DBA's job is to make sure that the system is configured to perform optimally with SQL Server.

Software Installation

The DBA must be involved not only with the installation of SQL Server but also with the installation of Microsoft Windows 2000 and other software components. Make sure that the proper options are set and that unnecessary components are not installed and configured. With the Windows 2000 installation, it is easy for unwanted components to be added unintentionally. Components such as Internet Information Server (IIS), Dynamic Host Configuration Protocol (DHCP) server, Message Queuing, and file and print services add a lot of overhead to the system, even though these services might not even be used or desired.

It is a good idea to create a document for the installation of Windows 2000 that includes a list of the components that you want installed on the system . This document can be used over and over again for installing Windows 2000. This provides a great deal of reproducibility and consistency in your Windows 2000 installation.

In addition to installing or assisting with the installation of Windows 2000, DBAs are responsible for the proper installation of SQL Server 2000. It is important to install SQL Server correctly because certain choices you make at that time are difficult to change without reinstalling the software. These choices include the location of SQL Server binaries and data files.

If it is your first SQL Server 2000 installation, it is a good idea to install the software on a test system before performing the installation on the production system. This allows you to try several options and become familiar with the installation process. As with the Windows 2000 installation, it is a good idea to document the installation process for SQL Server.

Hardware and Software Configuration

The DBA doesn't usually configure server hardware, but in some cases, you might have to perform this function. As with the installation process, if you do not actually configure the hardware and software yourself, you must be involved enough to guarantee that configuration is done to your specifications. As the SQL Server DBA, you must be able to specify the number of disk drives and controllers as well as provide the specification of any RAID controllers. Because you are ultimately responsible for the performance and the stability of the system, you must use your experience and knowledge to optimally configure the SQL Server system for capacity, performance, scalability, and growth.

As with the software installation, it is important to document your configuration. It sometimes helps to also document the reasoning behind your decisions. This will allow you to remember why you made the decisions you did. Remembering why these components were configured as they were will help with future modifications and upgrades. Be sure to document details such as which PCI buses were used and how RAID controllers were configured. Some configuration details require a reboot into diagnostics mode in order for you to find out the parameters, so documenting them as you set up the hardware might save some later time and effort.

Remember, the configuration of the system is ultimately your responsibility because the performance and the stability of the SQL Server database is your responsibility. So make sure that the system is configured properly for SQL Server. By documenting why your configuration choices have been made, you will help others understand and validate your decisions.

Security

Another responsibility of the SQL Server DBA is to monitor the security of the system and to report any problems. Often there are security experts, either at your company or with an outside firm, whom you can call if necessary. The scope of access to your system determines the type and the amount of security that your database needs. A system that is accessed by only a few trusted employees and is not connected to the Internet obviously requires less security than a database that is accessed from the Internet.

The security of the system is important because if someone were to break into your system and destroy or steal data, the expense to your company could be high. The security of the system begins with user management, which is described in the next section. In addition to managing users, you might be involved with designing and implementing the security plan for your network. This task is typically assigned to someone who has extensive experience in network security. If that description fits you, you might be a network security administrator as well as a DBA.

Network Security

Network security includes the purchase, configuration, and deployment of network proxy servers and security gateways. Many companies sell this kind of hardware/software solution. The person in charge of network security for a company is responsible for investigating and choosing the right solution for that company. The subject of network security could be a book by itself, so we won't address it here. Within SQL Server, auditing and user management are the main security tasks that you will be involved in.

System Auditing

System auditing involves monitoring both the SQL Server error log and the Windows 2000 event log, as well as using SQL Server Profiler to monitor activity within SQL Server. The SQL Server log and the event log contain valuable information about SQL Server, Windows 2000, and security. You should monitor these logs closely for any signs of trouble.

As mentioned, you can audit your system using SQL Server Profiler. Profiles can be created that log events such as failed login attempts. In addition, you can profile events such as data definition language (DDL) statements and INSERT, UPDATE, and DELETE operations. By using SQL Server Profiler, you can monitor specific events, as well as log times, user names, and activities.

Operations

The most time-consuming activities of the SQL Server DBA are probably the day-to-day operational jobs. These thankless jobs will probably bore you at times, but remember that these tasks are probably the most important activities that must be done. The DBA is responsible for the uptime of the system, and tasks such as backup and restore are critical to that uptime.

Backup and Restore

Many consider the backup and restore operations to be the most crucial tasks that the DBA is charged with. These operations ensure that the database will survive a massive hardware failure. If such a failure occurs, you must depend on the backup to return the database to service. The backup operation is consistent and straightforward, but you must frequently repeat it. To guarantee that the backups are good, you must also test and validate them regularly. By paying full attention to this activity, you can avoid mistakes and guarantee the safety of your system.

User Management

Another day-to-day task is user management. This consists of managing SQL Server logins and database roles. This is an important duty of the DBA because everyone who wants to use the database must have authorized access. This access is granted by the DBA, usually after a human resources department approves the change. Be sure to get approval before you grant access to each object within the database, and grant only as much access as the user needs. Avoid the temptation to grant blanket access to the database; the use of database roles is useful for granting specific access based on the needs of each department.

Other Routine Maintenance

Other routine maintenance might include tasks such as monitoring database space usage, rebuilding indexes, checking the validity of database objects, and monitoring the overall health of the system. Monitoring the system for changes is important. Any change, no matter how small, might be an indication of problems that are emerging. Many routine tasks are important enough that you should monitor them carefully. You can automate tasks such as rebuilding indexes and running consistency checks, but you should still check them occasionally.

Service Levels

Ensuring that the system provides a particular level of service is an important task. The service level that your system must provide might be specified in a contractual service level agreement (SLA). Even if it isn't, enabling the highest level of service possible is a responsibility and duty of the DBA. This task is accomplished by working for maximum uptime and by working for maximum performance via performance tuning, sizing, and capacity planning.

Performance Tuning and Monitoring

You should constantly monitor the performance of the system and take notes of any changes. If the system suddenly experiences higher response times, higher CPU usage, more context switches, and so forth, you might be seeing signs of an emerging problem. You need to monitor each system differently, and how you interpret the results of that monitoring will differ as well. You must determine, based on your system, how to judge whether it is running well or not. If performance problems are indicated, you must troubleshoot those problems and develop solutions.

You must periodically monitor the system's resource usage and performance. By monitoring the system, you can expand the system before performance degrades.

Once the system capacity has been reached, expansion might be much more expensive in terms of both money and downtime. SQL Server offers several tools for monitoring the system, which are described here along with other monitoring tools:

• System Monitor Used to monitor SQL Server and Windows 2000 resource usage. System Monitor is a Windows 2000 feature that is accessible through the Start menu.
• SQL Server Enterprise Manager Provides both resource usage information and some limited performance information.
• Third-party RDBMS monitors Provide a combination of monitoring and alerting capabilities for relational database management systems (RDBMSs).
• Network monitors Used for occasional network monitoring; they include Microsoft Systems Management Server (SMS) and third-party utilities.
• User surveys Used to gather information about how users feel about the performance of the system. It is important to stay in touch with the user community and determine whether users are satisfied. Too often the only interaction between DBA and user is when there's a problem.
• Tools for monitoring disk space usage Include Microsoft Windows Explorer and third-party monitoring tools. Some tools can monitor Windows 2000 as well as SQL Server.

The system might require tuning as the workload increases.

Sizing and Capacity Planning

In addition to tuning and monitoring the system, you will be called upon to determine whether it can handle the anticipated workload. By regularly performing sizing and capacity-planning tasks, you will be able to schedule capacity increases well in advance of any potential problems. Sizing and capacity planning are not easy jobs. You should call in outside help if you are concerned that the system might soon be reaching its capacity and you are not sure how to address that issue.

System Uptime

As mentioned before in this chapter, the uptime of the system is the responsibility of the DBA. If the system is not functioning optimally, your customers (the user community) will suffer. Any downtime can be costly to your company as well as to your users. Therefore, keeping the system up as much as possible is one of your main duties.

Planning and Scheduling of System Downtime

One way to reduce downtime is to carefully plan scheduled downtime. By scheduling downtime in advance, you can sufficiently warn the user community and staff so they can make other arrangements. The downtime should be scheduled so that there is sufficient time to perform all of the tasks that the downtime was planned for. In addition, you must make sure that all of the people that are affected by the outage are sufficiently notified. Of course, if your database serves the Internet, other arrangements might need to be made.

You might find it more convenient to schedule the downtime to occur on a regular basis, such as the first Sunday of every month. This arrangement will allow the user community to always be prepared for the downtime. Usually, this downtime is scheduled for off hours, in order to inconvenience as few people as possible.

Disaster Recovery

Another way of reducing downtime is to be prepared to recover in the event of a system failure. You must be prepared to recover in the event that the system is down for an extended time. Disasters take several forms. The system might crash because of a hardware failure. Replacing the failed component and rebooting the system usually solves this kind of problem. If the problem is a disk drive, a RAID array might save you from loss of service. If the entire array has failed, the database might require a restore from backup. In any case, this type of failure is usually solved in a matter of hours.

Rebooting the system might solve failures caused by a software problem, or the problem might require a database restore if the database has become corrupt. It is rare for software to fail in this way. However, database corruption can frequently be devastating.

A more severe problem would be the destruction of your data center. This can occur because of a natural disaster such as an earthquake, a flood, or a hurricane. This could result in the loss of computer systems and possibly power for days or even weeks. Another critical problem resulting from a natural disaster could be the loss of communications. So even though the data center might be back up and running, your system might not be back on the Internet for days or even weeks.

Creating a standby data center solves some of these problems. In the event that a disaster disables your main data center, the standby systems can easily be enabled in order to get your company back in business. This standby center might not be able to recover all of the transactions that had been submitted to the primary data center at the point of failure, but it might still be able to keep your business running until such time that you can recover the primary database. The DBA should be involved in planning and implementing the standby data center.

Documentation

The DBA is responsible for documenting every aspect of the database system, including hardware and software configuration, installation procedures, maintenance tasks, software updates, and any changes to the applications. This record can be used to help rebuild the system if necessary.

Documenting the system isn't exciting and requires a lot of discipline, but recording system facts and plans is really worth it in the long run. It is important for everyone involved with the development, deployment, and administration of a production system to document the work that is done. This policy allows others to determine what the current configuration of the system is, as well as what changes have been made in the past. You can use documentation when you clone systems or perform sizing and capacity planning. You can also use documentation as a construction manual if the system needs to be rebuilt. Many types of documentation can benefit from DBA involvement, as will be shown in this section.

The documentation can be maintained in either paper or electronic format, and exactly how it's done at your company is really up to you as the DBA. There are several methods you might use:

• A single document can be kept for the entire company. This document contains sections about each system in the company. System administrators, DBAs, and system operators should be able to access and modify this document.
• A single document can be kept for each system in the company. Again, everyone involved with the system must be able to make log entries in this document.
• System administrators, DBAs, and system operators can keep separate files. Only one segment of the administration community would be able to modify each type of log, but everybody must be able to view each type.

In the event of a system failure or data loss, having the complete history of the system can help pinpoint where the original failure might have occurred as well as help you reconstruct the system. After all, the reason for tracking failures is to avoid future failures. But if the documentation is to be effective, it must be complete. Never delete any documentation; only add to it.

The following lists are a guide to help you organize your documentation in a way that is effective for you. Most documentation should be divided into two major categories: configuration documentation and the system log.

Configuration Documentation

Configuration documentation should give you all the information you need to rebuild your system in the event of a major breakdown. That information should include the following:

• Hardware configuration A detailed record of what hardware you have, how the hardware was configured, and any equipment that was added, such as additional disk drives or memory.
• Software components A detailed record of which software components were added to the system and how each component was configured. Details such as which subcomponents were installed and what options were chosen are vital.
• Database configuration Should include the database layout and schema, the names and locations of all data files, which filegroups each file belongs to, and how the groups were created. This information gives you a reference by which to identify what data files have been lost in the event of a disk array failure.
• Software tuning Should include all system and database configuration parameters. When tuning changes are made, the new settings should be logged.

System Log

The system log is critical in the event of a system failure or performance degradation. You can use the following information to determine the sequence of events leading up to the failure and to aid recovery:

• Observations An important part of the DBA's job is to notice changes in the system and to anticipate problems. Observations of unusual activity should be noted. Even a note as simple as "The system seems sluggish" might hold valuable clues in the event of a subsequent system failure.
• System changes The DBA should record all changes made to the hardware, the operating system, and the database system. Entries should be in chronological order and should be complete but without unnecessary details.
• System failures Anytime a disk drive or other component fails, the event should be documented in the system log. This information can be valuable in determining trends of component failures.
• Backup and restore operations It is not necessary to update the log every time the system is backed up. However, requests for data to be restored should be logged to show patterns in user behavior as well as trouble spots in an application or a database schema.
• Scheduled maintenance When scheduled maintenance is performed, the DBA should make a note of what was done to the system. This information can be the starting point of investigating a system failure that occurs soon after a scheduled maintenance.

By keeping track of critical events as well as of configuration information, you can determine where a problem has occurred and know how to get the system back to where it should be.

Design Documentation

Depending on how your company's information-technology department is organized, you might be required to participate in the original design of the system. In any case, you should become familiar with the design documentation because it will be your guide to the hows, whats, and whys of your system.

Operational Maintenance Plans

The general operational procedures should be carefully documented so that other DBAs or system operators can easily perform them. In many companies, the DBAs perform the day-to-day operational procedures, such as backups, restores, and user account maintenance. At other companies, system operators are charged with this task. The instructions should explain each step clearly enough for a novice to understand. Backup operations are usually done in off hours. As the DBA, you don't want to be bothered in the middle of the night in order to answer questions about the backup procedures, so it is to your benefit to make these instructions as complete as possible. Include information about areas that can be problems and how to solve them. The more complete the document, the more it will help you avoid having to answer questions and debug minor problems yourself.

Disaster Recovery Plans

The disaster recovery plan is used as a guideline for recovering from a loss of the production server. This document is critical if you need to quickly restore a failed system. Whenever the primary system fails, it is the responsibility of the DBA to get the system back on line as soon as possible. The primary system can fail at any time on any day. If the system fails on a weekend or an evening when you are not available, the disaster recovery plan will be a resource for the other DBAs or operators to recover the system as soon as possible.

In order to create the disaster recovery plan, you must analyze the system uptime requirements and risks to the system. With small installations that do not require near 100 percent uptime and that can tolerate some downtime caused by a major disaster, it might be sufficient to simply back up the system and rely on the backup tapes for recovery. Systems that require more uptime will require a solution with a higher degree of fault tolerance, such as running Microsoft Cluster Services (MSCS). For systems whose downtime costs millions of dollars per day, you must implement a more comprehensive approach. This solution usually involves creating a failover site in another area of the country so operations can continue even after a natural disaster. Failover sites can maintain system uptime even if the primary data center is off line for days or weeks. This type of plan must be documented carefully so that any member of the technical staff can begin implementing the failover.

Design and Development

Some companies utilize their DBAs for system development in addition to the more traditional database responsibilities. The DBA is familiar with the needs and operational loads of the current system and thus can provide valuable insight into the design of a new system. Design and development responsibilities might include any of those described in the following sections.

Data Modeling and Analysis

Data modeling is an important part of design. It involves planning the logical design of the database, including specifying data relationships and referential integrity constraints. To make this difficult process easier, you can graphically display the structure of the database schema to see how individual components are related. The data model shows the logical view of the database, which can then be abstracted to the physical database design. By properly modeling the database—that is, by creating an efficient logical and physical database layout—you can greatly enhance performance.

Database Design

Database design is usually carried out by database designers, but they often incorporate feedback from the DBA. Sometimes the DBA plays the role of database designer. Day-to-day interaction with the database gives the DBA a unique perspective that can help improve future designs.

Stored Procedure Development

From time to time, the DBA might be required to design or even develop stored procedures. Being so familiar with the database and the data, the DBA is uniquely suited for these tasks. Stored procedures might be simple, or they might require a great deal of effort to create, depending on the application and your company's needs.

Application Development

In some companies, the DBA participates in the development of the application that will access the company's database. This application, like the stored procedures, will benefit from your familiarity with your database. By helping to develop your company's application, you will enhance the application and facilitate data access.

Information Sharing

The DBA might be called upon to act as a consultant to developers, designers, and end users. This consulting might include the following tasks:

• Assisting end users individually with specific problems, developing a training curriculum, or even teaching that curriculum. In many cases, ad hoc SQL is used as well as packaged queries for decision support systems (DSSs).
• Providing developers with information about how the system has been used in the past and how new development can benefit users. This discussion might precede informing users about new tables and indexes available to them as well as any other new features users might find handy.
• Providing designers with input about how different design features can benefit users. The application that designers have developed might lack some features that the users want or need. Passing this information to developers can help future development. You are the most likely person for the users to come to with questions about how to use certain features, which makes you a good source of feedback to developers.
• Analyzing the data in the database and how that data is accessed. This information can help you with the capacity-planning and tuning process. It might also help you improve the database schema.

Miscellaneous

There are several miscellaneous responsibilities that the DBA might have to perform. These duties are explained in the following sections.

Cluster Administration

If you're running MSCS in conjunction with SQL Server, you might need to perform cluster maintenance and cluster administration tasks. Typically, the cluster will run on its own, but some administration tasks might be required as hardware is added and the cluster changes. Clustering is currently used for server failover, but in future versions of Microsoft Windows and SQL Server, scalable clustering will be available. This capability will increase the complexity of both the setup and the administration of the cluster. Until then, cluster administration is fairly straightforward.

Replication Administration

If you are using SQL Server replication, you might have to perform periodic maintenance tasks as the cluster evolves. These tasks include modifying the cluster attributes and upgrading software on the systems. Other maintenance tasks might involve adding memory or disk capacity to the system. Any changes to the cluster should be made only as a coordinated effort between the system administrator and the DBA.

Help Desk

In addition to the responsibilities mentioned above, the DBA has some miscellaneous duties such as participating in the help desk. Support specialists usually staff the help desk; however, you might be asked to help on occasion. The DBA might be a consultant to the help desk on occasion, or the DBA might be requested to train the support specialists in the use of new applications as well as database functions.

Purchasing Input

Often the DBA will be involved in hardware and software evaluation. This process might require reviewing specifications or creating and administering benchmarks. In many cases, you are called on to get copies of the software, install them on test systems, and evaluate the product on its usefulness to a particular user community. In other cases, you might be asked to review specification sheets on hardware and software and to make an evaluation based on these specifications alone.

Capacity Monitoring

The DBA must monitor the system regularly and plan for growth. In some cases, you can bring in specialists to size the system and to conduct capacity-planning exercises. Normally, however, the DBA is responsible for determining whether the capacity of the system is being reached and when performance will be degraded or when resources will be depleted. The DBA then recommends what additional resources are needed to achieve the desired system performance.

If you don't plan carefully, the system could run out of memory or disk space, or it could even overburden the processor. In any of these situations, performance would suffer. If the system's storage capacity is completely filled, transaction processing might even be halted. Diligent monitoring by the DBA can avert such problems.

Web Site Administration

In smaller companies, it isn't unusual for the DBA to be charged with running the company's Web site. Bigger companies often have large staffs that do nothing but Web site maintenance and development. This duty might be far removed from your DBA duties or closely related, depending on what database access your Web site requires. SQL Server is ideal for providing data to Web sites, and many tools and APIs are available within SQL Server and Windows 2000 for distributing information in that way.