Microsoft® SQL Server™ 2000 DBA Survival Guide, Second Edition

Microsoft® SQL Server™ 2000 DBA Survival Guide, Second Edition

by Orryn Sledge, Mark Spenik
Released October 2002
Publisher(s): Sams
ISBN: 0672324687

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Microsoft SQL Server 2000 DBA Survival Guide is organized into several parts that comprise the various jobs and tasks the DBA performs. Each chapter is presented with the goal of providing knowledge and "know-how" to Database Administrators of a SQL Server database. The chapters also offer real-world insight and experience by passing on tips, tricks, and suggestions based on what the authors have learned the hard way. The book also takes time to provide checklists and examples for various SQL Server DBA tasks. The book also provides insight into the tasks that make-up a DBA's job including discussions of topics such as the creation of naming standards and conventions which are essential for efficient administration.

Table of contents

  1. Copyright
  2. About the Authors
  3. Acknowledgments
  4. We Want to Hear from You!
  5. Introduction
  6. Overview
    1. Role of the Database Administrator
      1. Hardware
      2. Network
      3. Operating Systems
      4. File/Print Server
      5. Database Server
      6. Who Does What?
      7. What Is a Database Administrator?
      8. Who Are the DBAs?
      9. DBA Responsibilities
      10. Tricks of the Trade
      11. How the DBA Interacts with Other Team Members
      12. Summary
    2. SQL Server Overview
      1. Architecture
      2. Operating System Integration
      3. Visual Administration Tools
      4. SQL Server Companion Products
      5. Nonvisual Administration Tools and Command-Line Tools
      6. Common SQL Server Objects
      7. FAQ
      8. Summary
    3. The Evolution of SQL Server
      1. History of SQL Server
      2. What's New in Version 2000
      3. Summary
  7. Installing and Upgrading SQL Server
    1. Planning an Installation or Upgrade
      1. Developing an Installation Strategy and Plan
      2. Developing an Upgrade Strategy and Plan
      3. Upgrade/Installation Planning FAQ
      4. Summary
      5. The Upgrade Checklist
    2. Installing or Upgrading SQL Server
      1. Different Editions of SQL Server
      2. Installing SQL Server
      3. Starting and Stopping SQL Server
      4. Installation Troubleshooting
      5. Upgrading SQL Server
      6. Removing SQL Server
      7. Installing Client Tools
      8. Configuring Clients
      9. Remote Installation
      10. Installing Multiple Instances of SQL Server
      11. Installation FAQ
      12. Summary
  8. SQL Server Management and Configuration
    1. Enterprise Management Processes
      1. Starting, Pausing, and Stopping SQL Server
      2. Starting the SQL Server Enterprise Manager
      3. Navigating the SQL Server Enterprise Manager
      4. Registering a Server
      5. Connecting to a Server
      6. Disconnecting from a Server
      7. Starting, Stopping, and Configuring SQL Server Agent
      8. Starting, Stopping, and Configuring SQL Mail
      9. Using the Enterprise Manager to Perform Common Tasks
      10. SQL Server Query Analyzer
      11. Starting and Stopping the Distributed Transaction Coordinator (DTC)
      12. FAQ
      13. Summary
    2. Configuring and Tuning SQL Server
      1. Configuring SQL Server
      2. SQL Server 2000 Self-Tuning Features
      3. Configuration Parameters
      4. Advanced Configuration Parameters
      5. Configuration FAQ
      6. Summary
    3. Managing Databases
      1. A Database Primer
      2. Database Basics
      3. Additional Database Information
      4. Filegroups
      5. Database FAQ
      6. Summary
    4. Managing SQL Server Users and Security
      1. Introduction
      2. An Overview of SQL Server's Security Model
      3. Managing Logins
      4. Managing Server Roles
      5. Managing Database Access and Database Roles
      6. Viewing and Modifying Login Information
      7. Removing Logins
      8. Changing a Password
      9. Managing SQL Server Security
      10. Beyond Security Basics: Suggested Strategies
      11. Managing SQL Server Users and Security FAQ
      12. Summary
  9. Database Backup and Recovery
    1. Backup and Recovery
      1. SQL Server 2000 Backups—Simplified with Recovery Models (But Few Changes Under the Covers)
      2. What Is a File/Filegroup Backup?
      3. Creating a Backup Device
      4. Performing Database, Transaction Log, Differential, and File/File Group Backups
      5. Understanding Log Truncation Options
      6. Backup Wizard
      7. Using Multiple Backup Devices (Striped Backups) and Media Sets
      8. Full Recovery Model and Bulk Logged Recovery Model—Using Database Complete Backups and Transaction Log Backups to Restore a Database
      9. Performing a Database Restore
      10. Restoring the master Database
      11. Interactive Example of Losing and Restoring a Database
      12. Creating a Backup Schedule
      13. Log Shipping Overview
      14. Backup and Restore FAQ
      15. Summary
  10. SQL Database Maintenance
    1. Developing a SQL Server Maintenance Plan
      1. Areas of Maintenance
      2. Maintenance Checklist
      3. Maintenance FAQ
      4. Summary
    2. Automating Database Administration Tasks
      1. Introduction
      2. SQL Server Agent
      3. Database Maintenance Plan Wizard
      4. Automating Database Administration Tasks FAQ
      5. Summary
  11. Importing and Exporting Data
    1. Data Transformation Services
      1. DTS and the Data Warehouse
      2. DTS and OLE DB/ODBC
      3. The DTS Framework
      4. Using the DTS Wizard
      5. Using the DTS Designer
      6. What Are Workflow and Batch Processing?
      7. Using DTS Packages
      8. Data Transformation Services FAQ
      9. Summary
    2. Using BCP and BULK INSERT
      1. BCP
      2. BCP Syntax
      3. Permissions Required to Run BCP
      4. Character Mode Versus Native Mode
      5. Interactive BCP
      6. Sample BCP Scripts
      7. Modes of Operation
      8. BCP and Enforcement of Triggers, Rules, Defaults, Constraints, and Unique Indexes
      9. Common BCP Traps
      10. BCP Tips
      11. BULK INSERT
      12. BCP FAQ
      13. Summary
  12. Troubleshooting
    1. Troubleshooting SQL Server
      1. SQL Error Messages
      2. Using the Error Message Number to Resolve the Error
      3. Deciphering the Error Log
      4. Using the Event Viewer
      5. Killing a Process
      6. Viewing Detailed Process Activity
      7. Using DBCC and Trace Statements to Troubleshoot
      8. Troubleshooting Applications
      9. Other Sources of Help and Information
      10. Using the Performance Monitor for Trend Analysis
      11. Troubleshooting FAQ
      12. Summary
  13. Architecture and Database Design
    1. Architecture Features
      1. SQL Server Thread Scheduling
      2. Disk I/O and Data Management
      3. Multiple Database Instances
      4. Federated Database Servers
      5. Other Enhancements
      6. Summary
    2. Database Design Issues
      1. Problems that Can Arise from an Improperly Designed Database
      2. Normalization
      3. Denormalization
      4. FAQ
      5. Summary
  14. Performance and Tuning
    1. Understanding Indexes
      1. General Principle Behind Indexes
      2. Structure of SQL Server Indexes
      3. Data Modification and Index Performance Considerations
      4. How to Create Indexes
      5. Other Index Operations
      6. Suggested Index Strategies
      7. Letting SQL Server Help with Index Selection
      8. Index FAQ
      9. Summary
    2. Query Optimization
      1. What's a Query Optimizer?
      2. What Are Statistics?
      3. Basic Query Optimization Suggestions
      4. Tools to Help Optimize a Query
      5. Reading Showplans
      6. Overriding the Optimizer
      7. Other Tuning Tricks
      8. Query Optimization FAQ
      9. Summary
    3. Multiuser Issues
      1. Locks
      2. Multiuser Configuration Options
      3. Multiuser FAQ
      4. Summary
  15. Transact-SQL
    1. SQL Essentials
      1. An Overview of Basic SQL Statements
      2. SELECT
      3. INSERT
      4. UPDATE
      5. DELETE
      6. CREATE TABLE
      7. SELECT...INTO
      8. Distributed Queries
      9. Summary
    2. Using Stored Procedures and Cursors
      1. What Is a Stored Procedure?
      2. Stored Procedure Pros and Cons
      3. How to Create a Stored Procedure
      4. How to Modify a Stored Procedure
      5. Control-of-Flow Language
      6. Parameters Used with Stored Procedures
      7. Commonly Used Global Variables
      8. How to Debug a Stored Procedure
      9. What Is a Cursor?
      10. Creating a Cursor
      11. Putting It All Together
      12. Stored Procedure and Cursor FAQ
      13. Summary
  16. Advanced DBA Topics
    1. SQL Server 2000 and the Internet
      1. SQL Server 2000 Web Publishing
      2. SQL Server 2000 and Managing Web Assistant Jobs
      3. Advanced Template File Example
      4. XML Integration
      5. Retrieving XML Data
      6. Updating Information via XML
      7. SQL Server and the Web FAQ
      8. Summary
    2. Monitoring SQL Server
      1. Tools for Monitoring SQL Server
      2. Monitoring SQL Server FAQ
      3. Summary
    3. SQL Mail
      1. Setting Up Your SQL Server as a Mail Client
      2. Configuring SQL Mail
      3. Configuring SQL Agent Mail
      4. Using SQL Mail
      5. SQL Mail FAQ
      6. Summary
    4. Using SQL-DMO
      1. SQL Server's Object Model
      2. Why Use SQL-DMO?
      3. Creating Applications with SQL-DMO
      4. Enhancing the SQL Server DBA Assistant
      5. Using SQL-DMO with Stored Procedures
      6. SQL-DMO FAQ
      7. Summary
  17. Replication
    1. Replication
      1. Replication Overview and Terminology
      2. Creating and Assigning the Distribution Database
      3. Configuring Replication Distribution Options
      4. Deleting a Distribution Database
      5. Configuring Replication Publishing
      6. Enabling Replication Subscribers
      7. Using the Disabling Publishing and Distribution Wizard
      8. Adding NonSQL Server (Heterogeneous) Subscribers
      9. Upgrading SQL Server Replication
      10. Replication FAQ
      11. Summary
    2. Transactional Replication
      1. Applicable Uses for Transactional Replication
      2. Replication Agents
      3. Replication Topology
      4. Immediate Updating Subscribers
      5. Recommended Topology for Updating Subscribers
      6. Creating a Transaction Based Publication
      7. Subscriptions
      8. Custom Stored Procedures
      9. Transforming Published Data
      10. Inline Data Validation and Reinitialization
      11. Generating Publication Scripts
      12. Replicating Stored Procedures
      13. Replication Monitor
      14. Transactional Replication FAQ
      15. Summary
    3. Snapshot and Merge Replication
      1. What Is Snapshot Replication?
      2. Setting Up a Snapshot Publication
      3. What Is Merge Replication?
      4. Setting Up a Merge Publication
      5. Merge Replication and Resolving Conflicts—Hands-on Example
      6. Troubleshooting Merge and Snapshot Replication
      7. Additional Publication Options
      8. Alternative Synchronization Partners
      9. Replicating via the Web (Internet)
      10. Replication FAQ
      11. Summary
  18. Data Warehousing
    1. Introduction to Data Warehousing
      1. Why Warehouse?
      2. What Is a Data Warehouse?
      3. Warehouse Data Versus Operational Data
      4. Data Warehousing Components
      5. What Is a Data Mart?
      6. Transforming Operational Data
      7. Planning the Warehouse Design
      8. Important Design Considerations
      9. Managing a Data Warehouse or Data Mart
      10. Microsoft and SQL Server 2000 Contributions to Data Warehousing
      11. Data Warehousing FAQ
      12. Summary
    2. SQL 2000 Analysis Services
      1. What Is OLAP?
      2. Understanding Multidimensional Data
      3. The Microsoft Analysis Manager
      4. Building an OLAP Database
      5. Data Storage in an OLAP Database
      6. Optimizing an OLAP Database
      7. Managing Multidimensional Data
      8. Microsoft SQL Server 2000 Analysis Services FAQ
      9. Summary
  19. Appendixes
    1. Naming Conventions
    2. DBCC Commands
      1. Quick Reference
      2. Reading the Output from DBCC Commands
      3. Resolving Errors Reported by DBCC
      4. Essential DBCC Commands
      5. DBCC Commands for Verification
      6. DBCC Commands to Return Process Information
      7. DBCC Commands to Return Performance Monitor Statistics
      8. Trace Flag Commands
      9. Data Cache Commands
      10. Transaction Commands
      11. Other DBCC Commands
    3. SQL Server Resources
  20. Index

Product information

  • Title: Microsoft® SQL Server™ 2000 DBA Survival Guide, Second Edition
  • Author(s): Orryn Sledge, Mark Spenik
  • Release date: October 2002
  • Publisher(s): Sams
  • ISBN: 0672324687