Introduction

Updated : October 26, 2001

Abstract

This chapter provides an outline of the entire Microsoft® SQL Server™ 2000 Operations Guide, its intended audience, and how it should be used. It includes a summary of each chapter in the guide, and a description of the Microsoft Operations Framework (MOF). MOF is briefly discussed and the MOF process is related to the operations tasks and life cycle of Microsoft SQL Server 2000. This chapter also includes a section on other resources you can use including books and links to Web sites.

On This Page

Introduction
What is the Microsoft Operations Framework?
The Quadrants and Service Management Functions
Chapter Summaries
Summary

Introduction

This guide is intended for database administrators (DBA) or database system engineers (DSE) who manage Microsoft® SQL Server™, for one or more enterprise-level systems. Throughout this guide, the term DBA will be used to mean either a database administrator or a database system engineer

DBAs are expected to have experience in the management of SQL Server database systems and a thorough understanding of features and functions, as covered in the following sources:

• SQL Server 2000 Books Online

• SQL Server 2000 Resource Kit

• MS-2072 course (Administering a Microsoft SQL Server 2000 Database) or equivalent

• MS-2073 course (Programming a Microsoft SQL Server 2000 Database) or equivalent

How to Use This Guide

This guide is presented within the structure of the Microsoft Operations Framework (MOF) methodology. There are numerous documents that should be read to fully understand MOF and its related Frameworks, and a summary has been provided in the next section. MOF is a representation of the cyclical process that any operation goes through, and it has been broken into four quadrants: changing, operating, supporting, and optimizing.

This guide does not cover installation or upgrade processes, nor does it address performance tuning or application design.

What is the Microsoft Operations Framework?

With the assistance of its customers, Microsoft has developed a collection of methodologies, best practices, and models called Enterprise Services. This was created in response to issues encountered in the planning, deployment, and maintenance of information technology projects and their related software, throughout the IT life cycle. Microsoft Operations Framework (MOF) is an integral part of Enterprise Services, as illustrated in Figure 1.1.

In designing MOF, Microsoft drew on resources from an agency established in the United Kingdom—the Central Computer and Telecommunications Agency (CCTA). The CCTA maintains a library of best practices called the IT Infrastructure Library (ITIL). MOF was developed using these resources and relates them to the deployment of Microsoft products and technologies. In particular, Microsoft used the ITIL vocabulary in presenting MOF and the other Enterprise Services.

Figure 1.1: Enterprise Services Frameworks

This guide focuses on the operations stage, and does not delve into the other areas of Enterprise Services. It is important to recognize that MOF is designed to be an iterative process, or a cycle, that allows you to improve your systems and processes. MOF facilitates continual improvement of the operations environment by identifying changes that will improve operations, helping you implement those changes, and evaluating the result so you can apply the lessons learned. The overall MOF process (shown in Figure 1.2) can be applied to the entire data center, the SQL Server environment, just one project, or just one task that is performed during SQL Server operations.

Figure 1.2: Microsoft Operations Framework

MOF Models

The Microsoft Operations Framework consists of three models: the process model, the team model, and the risk model. MOF was designed to be a structured approach to the IT life cycle, recognizing that rapid change is an integral feature of that life cycle and that review and evaluation are steps in the IT life cycle. In addition, MOF recognizes that risk management—the identification of potential problems and failures and the costs of those IT problems and failures to the whole organization—must be an integral part of the iterative MOF process.

The focus of this guide will be on the process model, which is used by MOF to break down the IT life cycle into identifiable tasks and functions. These tasks and functions are based on the ITIL Service Management Functions (SMFs). These tasks and functions will be used in this guide to focus on key functions with respect to the successful operation of SQL Server 2000. Examples of such processes include change management, system administration, security administration, problem management, and capacity management.

The MOF Quadrants

Each SMF has been assigned to a quadrant of the MOF release life cycle, and this guide will use the SMFs to present the best practices and issues facing a SQL Server 2000 DBA. Connecting each of the four phases are review milestones. Checking for these milestones ensures that the previous phase has been completed before a new one is begun.

Table 1.1 lists the mission of service and the review milestone for each quadrant.

Table 1.1 MOF Quadrant Descriptions and Review Milestones

Service Management Functions

Figure 1.3 shows which MOF quadrant each service management function belongs in. While a system may go through the cycle of MOF as it is implemented and modified, some job roles are focused in a single quadrant of the Operations Framework.

Figure 1.3: MOF Process Model and Service Management Functions

While a system may go through the cycle of MOF as it is implemented and modified, some job roles are focused in a single quadrant of the Operations Framework.

The Quadrants and Service Management Functions

Changing

This cycle begins at the point just after a proposed change is released into the target environment. At this point, it has been determined through a rigorous review in the previous quadrant that the staff, systems, and so on have been tested and are ready for this release. This is when the change-, configuration-, and release-management processes begin. (Note: the definitions of these service management functions have been taken verbatim from the Microsoft Operations Framework Executive Overview white paper. See the For More Information section of this chapter for a link to the white paper.)

• Change management. Identifies all affected systems and processes before the change is implemented in order to mitigate or eliminate any adverse effects.

• Configuration management. Identifies, records, tracks, and reports on key IT components or assets.

• Release management. Facilitates the introduction of software and hardware releases, and ensures they are planned, tested, and implemented. Works closely with the change and configuration management processes to ensure that the shared configuration management database (CMDB) is up to date.

When the release is complete, the release-readiness review evaluates the service management function's effectiveness.

This guide covers best practices for managing the implementation of system changes or new software, hardware, and processes. Because change management requires system documentation, this section also addresses configuration management.

Operating

After a successful deployment, the release is now operational. The following SMFs become important in the daily activities required to run the system:

• Security administration. Responsible for maintaining a safe computing environment by developing, implementing, and managing security controls.

• System administration. Responsible for day-to-day tasks of keeping enterprise systems running, and for assessing the impact of planned releases.

• Network administration. Responsible for the design and maintenance of the physical components that make up the organization's network, such as servers, routers, switches, and firewalls.

• Service monitoring and control. Observes the health of an IT service, and acts when necessary to maintain compliance.

• Directory services administration. Responsible for day-to-day operations, maintenance, and support of the enterprise directory.

• Storage management. Deals with on-site and off-site data storage for the purposes of data restoration and historical archiving, and ensures the physical security of backups and archives.

• Job scheduling. Assigns batch processing tasks at different times to maximize the use of system resources while not compromising business and system functions.

• Print/output management. Manages the costs and resources associated with business output, and ensures security of sensitive output.

The operations review happens periodically. It is an inwardly focused review of the IT staff's ability to maintain a given service and to document its experience in a local knowledge base.

This quadrant includes the most significant sections of this guide. In particular, this guide addresses best practices in the operational areas of security administration, system administration, storage management, job scheduling, and monitoring.

Supporting

Problems and issues inevitably arise after daily operations begin. The objective of the following service management functions (SMFs) is the timely resolution of incidents, problems, and inquiries for end users:

• Service desk. Provides first-line support to the user community for problems associated with the use of IT services.

• Incident management. Manages the entire course of problem solving for all incidents that occur.

• Problem management. Investigates and resolves the root causes of faults and disruptions that affect large customer populations.

The service level agreement (SLA) review happens periodically and evaluates the staff's ability to meet the service level requirements defined in the service level agreement. The staff takes corrective action to address those areas that fail the review and/or negotiates changes in the service levels agreements. In addition, the incident management and problem resolution processes drive changes to specific operational processes, tools, and procedures.

This is an area that gets less coverage, since the best practices for supporting SQL Server database applications do not differ significantly from those for other IT applications or services. Since the focus of this guide is on best practices for SQL Server 2000, the coverage of this quadrant's service management functions is limited to those that are unique to SQL Server 2000.

Optimizing

The SMFs in the optimizing quadrant are proactive in nature, evaluating current performance, forecasting future requirements, and looking for a way to apply lessons learned. Accordingly, ITIL categorizes the service management functions in the other quadrants as operational, and categorizes the SMFs of the Optimizing quadrant as tactical:

• Service level management. Manages the quality of IT services by negotiating, monitoring, and maintaining service level agreements between the IT service provider and its customers.

• Capacity management. Plans, sizes, and controls service solution capacity to satisfy user demand within the performance levels stated in the service level agreement.

• Availability management. Describes, manages, directs, and proactively maintains the availability of information and services at a reasonable cost and in accordance with agreed quality levels.

• Financial management. Manages monetary resources to support organizational goals. Financial management may include cost accounting, budgeting, project investment appraisals, and in some organizations, cost recovery.

• Workforce management. Recommends best practices to recruit, retain, maintain, and motivate the IT workforce.

• Service continuity management. Previously known as contingency planning, this SMF plans how to cope with, and recover from, an IT disaster.

These service management functions define changes (a new release) that would improve service levels or reduce costs. Changes that are approved begin the process again, with the SMFs of the Changing quadrant.

This guide addresses issues relating to capacity management, availability management, and service-continuity management, to the extent these topics were not already touched on elsewhere.

Chapter Summaries

This section provides an overview of the material covered in each of the other chapters in this guide.

Chapter 2 – Change, Configuration, and Release Management

Chapter 2 categorizes changes by type and by level, and describes the change-management process. This begins when a change is proposed and continues through the development, testing, and release steps. The chapter also recommends the documentation that should be kept in a run book.

Chapter 3 – Security Administration

Chapter 3 discusses the critical security issues that DBAs and SQL Server administrators should address, including user- and account-management issues, password practices, usage of permissions and roles, and management in various settings such as linked servers and DTS. Best practices are emphasized.

Chapter 4 – System Administration

Chapter 4 focuses on system administration and the basic administration tasks that every DBA should perform, organizing them based on whether they are performed daily, weekly, or monthly. This chapter does not cover monitoring and performance tuning (see Chapter 5), nor does it cover troubleshooting (see Chapter 7). The following topics are included:

• Stopping and restarting the server

• Backup and restore issues

• Password and other security issues

• Moving data with DTS rather than using log shipping, replication, backup/restore, the bcp utility, or bulk insert

• Issues related to log shipping, clustering, and memory management, including Address Windowing Extensions (AWE)

Chapter 5 – Monitoring and Control

Chapter 5 discusses two monitoring strategies: proactive monitoring and exceptionmonitoring. Key counters are listed, and the steps to create a log file are shown. Specialized monitoring situations that show how to distinguish clustering problems from hardware problems, such as in a clustering environment, are discussed at the end of the chapter.

Chapter 6 – Capacity and Storage Management

Chapter 6 describes the DBA's responsibilities with regard to configuring and maintaining physical storage components to meet requirements of capacity, throughput, and performance.

Chapter 7 – Problem and Incident Management

Chapter 7 describes the procedures used in problem and incident management, including documentation. A review of log files and the tools for documenting problem resolution are presented. A section is included on how to identify problems that appear to be database problems but could be connectivity problems or, when clustering is used, problems that could be clustering issues

Chapter 8 – Service Management

Chapter 8 discusses the process of negotiating a service level agreement, the individuals who should be involved in this process, and the points that should be included in the agreements.

Summary

Microsoft Operations Framework (MOF) offers guidance for system management in general, and also can be applied as a project cycle. MOF provides structure to the roles and processes that apply to both the DBA and the IT department as a whole.

The MOF white papers focus on specific service management functions (SMFs), while the SQL Server 2000 Operations Guide focuses on the practical application of the recommendations described in the MOF white papers. The white papers deal with each SMF in isolation, while the Operations Guide groups topics logically in relation to the entire process of database-system management. For example, the considerations and implications involved in choosing data-storage and backup options overlap with availability management and service continuity management (contingency planning), but also relate to capacity management and database system administration. Effectively planning and handling the implementation of data storage requires knowledge of each of these topics in context, and the knowledge of how to deploy, operate, and support the chosen options.

This document identifies situations in which database administrators (or database system engineers) can apply known solutions, thus avoiding the repetition of common errors and also leveraging lessons learned at other sites. The goal of this guide is to provide the database administrator (or database system engineer) with guidance for improving management practices based on current best practices tested in other companies.

For More Information

See these links and books for more information:

Microsoft Solutions Framework

https://www.microsoft.com/technet/itsolutions/msf/default.mspx

This Web site provides links not only to the Framework white papers, but also to case studies and other information on the IT life cycle.

Microsoft Servers - SQL Server

https://www.sqlpass.org/

Microsoft Operations Framework Executive Overview White Paper

Books

Delaney, Kalen. Inside Microsoft SQL Server 2000. Microsoft Press. 2000

https://www.microsoft.com/mspress/books/4297.asp

Garcia, Marcelina, ed.; James Reding, Edward Whalen, and Steve Adrien DeLuca. Microsoft SQL Server 2000 Administrator's Companion. Microsoft Press. 2000.

https://www.microsoft.com/mspress/books/4519.asp

Microsoft Corporation. Microsoft SQL Server 2000 Resource Kit. Microsoft Press. 2001.

https://www.microsoft.com/mspress/books/4939.asp