You are previewing Microsoft® SQL Server™ 2000 Operations.
O'Reilly logo
Microsoft® SQL Server™ 2000 Operations

Book Description

This guide provides real-world, task-based information and best practices for database administrators and system engineers who manage SQL Server 2000 operations, including server monitoring, backup, capacity planning, and developer and user support.

Table of Contents

  1. Microsoft® Prescriptive Guidance: SQL Server 2000 Operations Guide
    1. SPECIAL OFFER: Upgrade this ebook with O’Reilly
      1. Content Lead
      2. Key Authors
      3. Reviewers
    2. 1. Guide Overview
      1. Introduction
      2. Audience
      3. Prerequisites
      4. Executive Summary
      5. Structure
      6. What is the Microsoft Operations Framework?
        1. MOF Models
      7. MOF Quadrants and Service Management Functions
        1. Changing Quadrant
        2. Operating Quadrant
        3. Supporting Quadrant
        4. Optimizing Quadrant
      8. Chapter Introductions
        1. Chapter 1 – Introduction
        2. Chapter 2 – Change, Configuration, and Release Management
        3. Chapter 3 – Security Administration
        4. Chapter 4 – System Administration
        5. Chapter 5 – Monitoring and Control
        6. Chapter 6 – Capacity and Storage Management
        7. Chapter 7 – Problem and Incident Management
        8. Chapter 8 – Service Management
      9. Style Conventions
    3. 2. Change, Configuration, and Release Management
      1. Introduction
        1. Prerequisites
      2. Process Flowchart
      3. Managing Change
        1. Managing the Environment
          1. Managing Database Change for Packaged Products
            1. Development and Unit Testing
            2. Quality Assurance, Integration Testing, and Staging
          2. Managing Database Change for Custom-built Applications
            1. Development and Unit Testing
            2. Staging
        2. Levels and Types of Change
          1. Levels of Change
          2. Types of Change
      4. The Process of Change
        1. Development
        2. Implementation Scripts
          1. Deployment Disaster Recovery
        3. Reconfigurations and Upgrades
        4. Management of the Development Environment
        5. Emergency Fixes
        6. Impact Analysis
        7. Testing
        8. Change Notification
        9. Alternate System Access During Changes
        10. Unmanaged Change
        11. Documenting Change
      5. Change and Release Management
        1. Organizing Scripts
          1. Script-based Installations
        2. Version Control
          1. Source Storage Methods
          2. Source Code Tree
        3. Change Records
          1. Request For Change Form
        4. Contingency Plans
          1. Rollback Strategies
        5. Release and Deployment
          1. Determining an Implementation Window
          2. Planning the Implementation
        6. Release Readiness Review
        7. Coordinating Application Changes
        8. Deployment and Implementation
          1. Documenting Unplanned Steps and Procedures
          2. Rollback Plan Documentation
          3. When Your Deployment Is Complete
        9. Implementation Review
      6. Configuration Management
        1. Configuration Baseline
        2. Creating a Run Book
          1. SQL Server Administrative Information
          2. Application System Information
          3. Database Components
          4. Storage Components
          5. Server Components
          6. Offsite Storage
          7. The Importance of Standardization
          8. Configuration Management Database
      7. Summary
    4. 3. Security Administration
      1. Introduction
        1. Audience
      2. Basic Security Issues
        1. Security Policy
        2. Authentication
      3. Roles, Permissions, and Passwords
        1. Roles
          1. Fixed Server Roles
          2. Fixed Database Roles
          3. User-Defined Database Roles
          4. Application Roles
        2. Permissions
          1. Types of Permissions
          2. Granting, Revoking, and Denying Permissions
          3. Effective Permissions
        3. Passwords
          1. Application Role Passwords
          2. Resetting Passwords
        4. Logging Changes
      4. Standard Security Design
        1. sa Account
        2. Passwords
        3. Creating Objects
        4. Permissions
        5. Documentation
        6. Other
        7. Extended Stored Procedures
        8. Script Security
        9. Other Issues
        10. Two-tier Security
        11. Three-tier Security
        12. Physical Security
        13. Network Security
        14. Registry Security
      5. User and Account Management Issues
        1. Failover Clustering
        2. Log Shipping
        3. Local Administrators, Domain Administrators, and BUILTIN Administrators
        4. Login Accounts
        5. Auditing
        6. Proxy Accounts
        7. Restoring Login Accounts and Users to Another SQL Server
      6. Managing a Secured Environment
        1. Linked Server Security
        2. DTS Package Security
      7. Summary
    5. 4. System Administration
      1. Introduction
      2. The DBA Team
        1. Rotation
        2. Effective Use of Spare Time
        3. Communication
        4. Alert Messages
        5. Team Web Site
        6. Continuous Learning
        7. Service Level Agreement
      3. Administrative Topics
        1. Documentation and Standardization
        2. Tools, Functionality, and Technologies
        3. Scripting
          1. Transact-SQL
          2. Windows Scripting Host
        4. Indexes and Statistics
          1. Clustered vs. Non-clustered Indexes
          2. Composite and Covering Indexes
          3. FILLFACTOR Settings for Indexes
          4. Index Fragmentation
          5. Statistics
          6. Index and Statistics Recommendations
        5. Backup and Restore
        6. Types of Backups
          1. Full Backups
          2. Differential Backups
          3. Transaction-log Backups
        7. Database Recovery
          1. Point-in-time Recovery
          2. File and Filegroup Strategies
          3. Backup Strategies
          4. Recovery Models
            1. Full Recovery Model
            2. Bulk-logged Recovery Model
            3. Simple Recovery Model
          5. Backup Performance
          6. Backup Validity
        8. Automation
          1. Jobs
          2. Alerts
          3. Operators
        9. Data Movement
          1. Data Transformation Services
          2. DTS Connections and Tasks
          3. Replication
          4. Latency as a Factor in Replication
          5. BCP and BULK INSERT
          6. Linked Servers
          7. Log Shipping
          8. Using Backup and Restore to Move Data
          9. Detaching and Attaching Databases
        10. Comparison of Data-moving Strategies
          1. Moving Databases
          2. Keeping Logins Synchronized
          3. Moving/Importing/Exporting Data—Large or Small Amounts
        11. Starting and Stopping Services
        12. Operations That Require a SQL Server Restart
        13. DBCC
      4. Administrative Schedules
        1. Daily Tasks
        2. Weekly Tasks
        3. Monthly Tasks
        4. Database Maintenance Plan Wizard
      5. Advanced Administrative Topics
        1. Availability and Performance
        2. High Availability Techniques
          1. Log Shipping
            1. Setting Up Log Shipping
          2. Failover Clustering
          3. Backing Up and Restoring
          4. Backing Up to Disk and Tape
            1. Snapshot Backups
            2. Backing Up an Entire Clustered System
          5. Log Shipping and Clustering
        3. Memory Management
        4. Advanced Memory Management
          1. Memory Allocation with Address Windowing Extensions and Physical Addressing Extensions
          2. Memory and Processor Management with Multiple Instances
      6. Summary
    6. 5. Monitoring and Control
      1. Introduction
        1. Design Considerations
        2. Resource Requirements
        3. System Prerequisites
      2. Process Flowchart
      3. Approaches to Monitoring
        1. Proactive Monitoring
        2. Proactive Monitoring Regimen
          1. Background Problems
          2. Creating the Baseline – Suggested Counters
          3. Creating a Baseline Chart
          4. Ongoing Use of Charts
          5. Alerts
          6. SQL Server Log
          7. SQL Agent Log
          8. Event Viewer
          9. Monitoring Job Run-time Trends
          10. Other Monitoring Applications
          11. Microsoft Operations Manager
        3. Proactive Hardware Monitoring
          1. Monitoring for Capacity Planning
          2. Use of Performance Log
          3. The Monitoring Process
        4. Exception Management
        5. Exception Management Regimen
          1. Performance Monitoring
          2. Alerts
          3. SQL Server Error Log
        6. Hardware Exception Management
          1. Reacting to Hardware Exceptions
          2. Identifying CPU and Memory Constraints
          3. Clustering Hardware
      4. Reacting to Problems
        1. Tools
        2. SQL Server Error Log and SQL Agent Log
        3. SQLDiag.exe
        4. Profiler
        5. Current Activity
        6. SP_WHO
      5. Summary
    7. 6. Capacity and Storage Management
      1. Introduction
        1. Resource Requirements
          1. Monitoring Data
      2. Process Flowchart
      3. System Classification
      4. Capacity Management
        1. CPU and Memory Management
          1. CPU Planning
          2. Memory Planning
        2. Disk Planning
          1. Tips
          2. Disk Controllers
            1. Cache
            2. Channels
            3. Windows NT File System (NTFS) Allocation Unit
        3. Data Storage Subsystem Management
          1. RAID
          2. Standardize Your Configuration
          3. Changing the Subsystem
        4. Database File Placement
          1. Log File Placement
          2. tempdb File Placement
          3. Other File Placement
        5. Monitoring for Capacity Management
          1. Essential System Monitor Counters
          2. Note on Disk Queue Length
        6. Monitoring for Problems
      5. Summary
    8. 7. Problem and Incident Management
      1. Introduction
        1. Design Considerations
        2. Resource Requirements
        3. System Prerequisites
      2. Process Flowcharts
        1. Application Flowchart
        2. The Data Tier Flowchart
        3. Data Tier Operations Flowchart
      3. Problem Management
        1. Terminology Used
        2. High-level Process
        3. Detailed Analysis Process
        4. Problem Management vs. Incident Management
      4. Problems That Appear As Database Problems
        1. Connectivity
        2. Clustering
      5. Summary
    9. 8. Service Management
      1. Introduction
        1. Design Considerations
        2. Resource Requirements
      2. Process Flowchart
      3. Negotiating the SLA
        1. Elements of the SLA
      4. Delivery of Service
        1. Trouble Ticketing
        2. Bug Tracking
      5. Service Levels
        1. Operational States
        2. Timeframes
        3. Responses
        4. Maintaining Service Levels
      6. Summary
    10. Index
    11. SPECIAL OFFER: Upgrade this ebook with O’Reilly