Beginning SQL Server 2008 Administration

Beginning SQL Server 2008 Administration

Released October 2009
Publisher(s): Apress
ISBN: 9781430224136

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

Beginning SQL Server 2008 Administration is essential for anyone wishing to learn about implementing and managing SQL Server 2008 database. This book will bring all readers up to speed on the enterprise platform Microsoft SQL Server 2008.

Table of contents

  1. Copyright
  2. About the Authors
  3. About the Technical Reviewer
  4. Acknowledgments
  5. Introduction
    1. Who This Book Is For
    2. How This Book Is Structured
    3. Getting the Most from This Book
    4. Errata
    5. Contacting the Authors
  6. 1. The Database Administration Profession
    1. 1.1. Why Do We Need a DBA? Access Runs Great!
    2. 1.2. What Exactly Does a DBA Do Anyway?
      1. 1.2.1. Database Maintenance
      2. 1.2.2. Security and Regulatory Compliance
      3. 1.2.3. Disaster Recovery
      4. 1.2.4. Design and Performance Improvement
      5. 1.2.5. Documentation
    3. 1.3. Salary Information
    4. 1.4. Words from Real-World DBAs
      1. 1.4.1. Grant Fritchey, Principal DBA
      2. 1.4.2. Roman Rehak, Principal Database Architect
    5. 1.5. Summary
  7. 2. Planning and Installing SQL Server 2008
    1. 2.1. Understanding the Editions
      1. 2.1.1. Demystifying 32-Bit and 64-Bit Architectures
      2. 2.1.2. Server Editions
        1. 2.1.2.1. Enterprise Edition (x86, x64, and IA64)
        2. 2.1.2.2. Standard Edition (x86 and x64)
        3. 2.1.2.3. Specialized Editions
        4. 2.1.2.4. Developer Edition (x86, x64, and IA64)
        5. 2.1.2.5. Workgroup (x86 and x64)
        6. 2.1.2.6. Web (x86 and x64)
        7. 2.1.2.7. Express (x86 and x64)
        8. 2.1.2.8. Compact (x86 Only)
        9. 2.1.2.9. Evaluation (x86, x64, and IA64)
      3. 2.1.3. SQL Server Terminology
    2. 2.2. Planning Your Installation
      1. 2.2.1. Assessing the Environment
      2. 2.2.2. Choosing the Right Edition
      3. 2.2.3. Validating Hardware and Software Requirements
    3. 2.3. Installing SQL Server Evaluation Edition
      1. 2.3.1. Step 1: Downloading SQL Server Evaluation Edition
      2. 2.3.2. Step 2: Understanding the SQL Server Installation Center
        1. 2.3.2.1. Planning Tab
        2. 2.3.2.2. Installation Tab
        3. 2.3.2.3. Maintenance Tab
        4. 2.3.2.4. Tools Tab
        5. 2.3.2.5. Resources Tab
        6. 2.3.2.6. Advanced Tab
        7. 2.3.2.7. Options Tab
      3. 2.3.3. Step 3: Installing an Instance of SQL Server
        1. 2.3.3.1. Feature Selection Page
        2. 2.3.3.2. Instance Configuration Page
        3. 2.3.3.3. Disk Space Requirements Page
        4. 2.3.3.4. Server Configuration Page
        5. 2.3.3.5. Database Engine Configuration Page
        6. 2.3.3.6. Error and Usage Reporting Page
        7. 2.3.3.7. Installation Rules Page
        8. 2.3.3.8. Ready to Install Page
    4. 2.4. Upgrading SQL Server
    5. 2.5. A Word from the SQL Server Setup Team
    6. 2.6. Summary
  8. 3. What's in the Toolbox?
    1. 3.1. SQL Server Management Studio
      1. 3.1.1. Connecting to SQL Server
      2. 3.1.2. Issuing Queries Using SSMS
        1. 3.1.2.1. Object Explorer
        2. 3.1.2.2. Writing the Query
        3. 3.1.2.3. Document Windows
        4. 3.1.2.4. Results Pane
      3. 3.1.3. Managing Multiple Servers
        1. 3.1.3.1. Registered Servers
        2. 3.1.3.2. Queries Against Multiple Servers
        3. 3.1.3.3. Policy-Based Management
      4. 3.1.4. Monitoring Server Activity
        1. 3.1.4.1. Activity Monitor
        2. 3.1.4.2. Performance Data Collector
        3. 3.1.4.3. Reports
    2. 3.2. Other Tools from the Start Menu
      1. 3.2.1. Analysis Services Folder
      2. 3.2.2. Configuration Tools Folder
        1. 3.2.2.1. SQL Server Installation Center
        2. 3.2.2.2. SQL Server Error and Usage Reporting
        3. 3.2.2.3. Reporting Services Configuration Manager
        4. 3.2.2.4. SQL Server Configuration Manager
      3. 3.2.3. Documentation and Tutorials Folder
        1. 3.2.3.1. Microsoft SQL Server Samples Overview
        2. 3.2.3.2. SQL Server Books Online
        3. 3.2.3.3. SQL Server Tutorials
      4. 3.2.4. Integration Services Folder
        1. 3.2.4.1. Data Profiler Viewer
        2. 3.2.4.2. Execute Package Utility
      5. 3.2.5. Performance Tools Folder
        1. 3.2.5.1. Database Engine Tuning Advisor
        2. 3.2.5.2. SQL Server Profiler
        3. 3.2.5.3. Import and Export Data Wizard
        4. 3.2.5.4. Business Intelligence Development Studio (BIDS)
    3. 3.3. Command-Line Tools
      1. 3.3.1. SQLCMD
        1. 3.3.1.1. Connecting to SQL Server
        2. 3.3.1.2. Passing Variables
      2. 3.3.2. PowerShell Provider for SQL Server
    4. 3.4. Summary
  9. 4. Creating Tables and Other Objects
    1. 4.1. Navigating the Object Explorer Tree
    2. 4.2. Scripting the Actions of an SSMS Dialog Box
    3. 4.3. Transact-SQL (T-SQL) Primer
      1. 4.3.1. Data Definition Language (DDL)
      2. 4.3.2. Data Manipulation Language (DML)
      3. 4.3.3. Data Control Language (DCL)
    4. 4.4. Creating Tables
      1. 4.4.1. Creating Tables from the Table Designer
      2. 4.4.2. Issuing the CREATE TABLE Statement
    5. 4.5. Altering Tables
    6. 4.6. Adding Constraints
      1. 4.6.1. NULL Constraints
      2. 4.6.2. CHECK Constraints
      3. 4.6.3. Primary Key and Unique Constraints
      4. 4.6.4. Foreign Key Constraints
    7. 4.7. Dropping Tables
    8. 4.8. Creating Indexes
    9. 4.9. Summary
  10. 5. Transact-SQL
    1. 5.1. The VetClinic Sample Database Revisited
    2. 5.2. Data Types
      1. 5.2.1. Unicode vs. ANSI
      2. 5.2.2. Living with NULL
    3. 5.3. Data Manipulation Language
      1. 5.3.1. SELECT
      2. 5.3.2. INSERT
      3. 5.3.3. UPDATE
      4. 5.3.4. DELETE
    4. 5.4. Transactions
      1. 5.4.1. Execution
      2. 5.4.2. Transaction Isolation
      3. 5.4.3. Deadlocks
    5. 5.5. Stored Procedures
      1. 5.5.1. Creating Stored Procedures Using Templates
      2. 5.5.2. Modifying Stored Procedures
      3. 5.5.3. System Stored Procedures
    6. 5.6. Functions
      1. 5.6.1. Creating a Function
      2. 5.6.2. Invoking a Function
        1. 5.6.2.1. System-Defined Functions
    7. 5.7. Triggers
    8. 5.8. Summary
  11. 6. SQL Server Internals
    1. 6.1. Databases
      1. 6.1.1. Master Database
      2. 6.1.2. Tempdb Database
      3. 6.1.3. Model Database
      4. 6.1.4. MSDB Database
      5. 6.1.5. Resource Database
    2. 6.2. Repairing Corrupt System Databases
    3. 6.3. Writing Data to Disk
    4. 6.4. SQL Server Services
    5. 6.5. Single-User Mode
      1. 6.5.1. Placing an Already-Started Database into Single-User Mode
      2. 6.5.2. Starting SQL Server in Single-User Mode
    6. 6.6. Summary
  12. 7. Storage Management Strategies
    1. 7.1. Storage Systems
    2. 7.2. Storage System Interfaces
      1. 7.2.1. IDE ATA
      2. 7.2.2. SATA
      3. 7.2.3. SCSI
      4. 7.2.4. Serial Attached SCSI
      5. 7.2.5. Fibre Channel Direct Attached Storage
      6. 7.2.6. Fibre Channel Storage Area Network
      7. 7.2.7. iSCSI
      8. 7.2.8. InfiniBand
    3. 7.3. Storage System Types
      1. 7.3.1. Direct Attached Storage
      2. 7.3.2. Storage Area Network
        1. 7.3.2.1. Logical Unit Numbers (LUNs)
      3. 7.3.3. Network Attached Storage
    4. 7.4. Disk Configuration: RAID
      1. 7.4.1. RAID 0 (Striping)
      2. 7.4.2. RAID 1 (Mirroring and Duplexing)
      3. 7.4.3. RAID 10 (Stripe of Mirrors)
      4. 7.4.4. RAID 5 (Striping with Parity)
    5. 7.5. Hardware and Software RAID
    6. 7.6. Selecting a Storage System for SQL Server 2008
      1. 7.6.1. I/O Performance
      2. 7.6.2. Redundancy Is Critical
    7. 7.7. Configuring Your Database
      1. 7.7.1. Data Compression
      2. 7.7.2. Disk Volume Alignment
    8. 7.8. Summary
  13. 8. Database Backup Strategies
    1. 8.1. Defining the Types of Backups
      1. 8.1.1. Full Backups
        1. 8.1.1.1. Full Backup Using SQL Server Management Studio
        2. 8.1.1.2. Full Backup Using T-SQL
      2. 8.1.2. Differential Backups
      3. 8.1.3. File and Filegroup Backups
    2. 8.2. Backing Up the Logs
      1. 8.2.1. Initiating Fully Recovery Mode
      2. 8.2.2. Log Backup Using Management Studio
      3. 8.2.3. Log Backup Using T-SQL
      4. 8.2.4. Backing Up a Copy of the Database
    3. 8.3. Automating the Backup Process
    4. 8.4. Encrypting Backup Files for Security
    5. 8.5. Compressing Backups to Save Space
    6. 8.6. Designing a Backup Plan
    7. 8.7. Summary
  14. 9. Database Restore Strategies
    1. 9.1. Restoring a Database from the Management Studio GUI
    2. 9.2. Specifying Restore Options from the GUI
    3. 9.3. Restoring a Database Using T-SQL
      1. 9.3.1. Executing a Simple Restore
      2. 9.3.2. Creating a Copy of a Database
      3. 9.3.3. Retrieving Logical and Physical File Names
      4. 9.3.4. Displaying General Information About a Backup
      5. 9.3.5. Cleaning Up
    4. 9.4. Restoring Differential Backups
    5. 9.5. Restoring Log Backups
    6. 9.6. Restoring File Backups
    7. 9.7. Testing Backups
    8. 9.8. Developing a Full Disaster Recovery Plan
      1. 9.8.1. Gathering Information About the Business
      2. 9.8.2. Establishing the Disaster Plan
        1. 9.8.2.1. Scenario 1: Large Commercial Insurance Company
        2. 9.8.2.2. Scenario 2: Small Online Retail Business
      3. 9.8.3. Testing the Disaster Plan
    9. 9.9. Summary
  15. 10. Common Database Maintenance Tasks
    1. 10.1. Backing Up and Restoring
    2. 10.2. Checking the Database Integrity
    3. 10.3. Shrinking the Database
    4. 10.4. Reorganizing and Rebuilding Indexes
      1. 10.4.1. Detecting Undue Fragmentation
      2. 10.4.2. Rebuilding an Index
      3. 10.4.3. Reorganizing an Index
    5. 10.5. Getting Updated Statistics
      1. 10.5.1. Checking the Status of Automatic Statistics Gathering
      2. 10.5.2. Manually Updating Statistics
    6. 10.6. Monitoring Logs
    7. 10.7. Creating SQL Server Agent Jobs
      1. 10.7.1. Connecting to SQL Server
      2. 10.7.2. Creating an Agent Job
    8. 10.8. Defining Alerts
    9. 10.9. Summary
  16. 11. Automation Through SQLCMD and PowerShell
    1. 11.1. Scripting in SQLCMD
    2. 11.2. Executing Commands Interactively
    3. 11.3. Executing Script in Batch Mode
    4. 11.4. Testing SQLCMD Scripts
    5. 11.5. Using SQLCMD to Back Up a Database
    6. 11.6. Generating Scripts from Management Studio
    7. 11.7. Scripting in PowerShell
      1. 11.7.1. Configuring PowerShell for Use With SQL Server
      2. 11.7.2. Understanding the Components
      3. 11.7.3. Starting PowerShell in Interactive Mode
      4. 11.7.4. Specifying an Execution Policy
      5. 11.7.5. Executing a PowerShell Script Interactively
      6. 11.7.6. Running PowerShell Scripts in Batch Mode
      7. 11.7.7. Running PowerShell from SQL Server Agent
      8. 11.7.8. Running PowerShell Scripts from Maintenance Plans
      9. 11.7.9. Using the PowerShell Integrated Scripting Environment
    8. 11.8. Summary
  17. 12. Database Maintenance Plans
    1. 12.1. Understanding the Fundamentals
    2. 12.2. Creating a Maintenance Plan
    3. 12.3. Starting the Maintenance Plan Wizard
      1. 12.3.1. Specifying Plan Properties
      2. 12.3.2. Specifying Job Schedule Properties
      3. 12.3.3. Selecting Maintenance Tasks
      4. 12.3.4. Selecting Maintenance Task Order
    4. 12.4. Configuring Individual Tasks
      1. 12.4.1. Check Database Integrity
      2. 12.4.2. Shrink Database
      3. 12.4.3. Reorganize Index
      4. 12.4.4. Rebuild Index
      5. 12.4.5. Update Statistics
      6. 12.4.6. History Cleanup
      7. 12.4.7. Back Up Database (Full)
      8. 12.4.8. Maintenance Cleanup
      9. 12.4.9. Select Report Options
    5. 12.5. Completing the Maintenance Plan Wizard
    6. 12.6. Modifying an Existing Maintenance Plan
    7. 12.7. Summary
  18. 13. Performance Tuning and Optimization
    1. 13.1. Measuring SQL Server Performance
      1. 13.1.1. Performance Counters
      2. 13.1.2. Performance Monitor
      3. 13.1.3. Dynamic Management Views
      4. 13.1.4. Data Collector
      5. 13.1.5. Setting Up the Data Collector
      6. 13.1.6. Viewing the Data Collector Data
        1. 13.1.6.1. Server Activity History
        2. 13.1.6.2. Disk Usage Summary
        3. 13.1.6.3. Query Statistics History
    2. 13.2. Tuning Queries
      1. 13.2.1. Understanding Execution Plans
      2. 13.2.2. Gathering Query Information with Profiler
        1. 13.2.2.1. Working with Trace Data
        2. 13.2.2.2. Using Trace to Capture Execution Plans
      3. 13.2.3. Using the Database Engine Tuning Advisor
    3. 13.3. Managing Resources
    4. 13.4. Limiting Resource Use
      1. 13.4.1. Leveraging Data Compression
    5. 13.5. Summary
  19. 14. SQL Server Security
    1. 14.1. Terminology
      1. 14.1.1. Authentication
      2. 14.1.2. Authentication Mode
      3. 14.1.3. Authorization
      4. 14.1.4. Server Instance vs. the Database
    2. 14.2. SQL Server Instance Security
      1. 14.2.1. Creating a SQL Server Login
      2. 14.2.2. Server Roles
      3. 14.2.3. Server Permissions
      4. 14.2.4. Endpoints
    3. 14.3. Database Security
      1. 14.3.1. Database Users
      2. 14.3.2. Schemas
        1. 14.3.2.1. An Example of the "Wrong" Way
        2. 14.3.2.2. The "Right" Way
        3. 14.3.2.3. Four-Part Naming Convention
        4. 14.3.2.4. Default Schema
        5. 14.3.2.5. Reassigning Schema Ownership
      3. 14.3.3. Fixed Database Roles
        1. 14.3.3.1. Database Permissions
      4. 14.3.4. Flexible Database Roles
      5. 14.3.5. Security Functions
        1. 14.3.5.1. fn_my_permissions() Function
        2. 14.3.5.2. HAS_PERMS_BY_NAME Function
    4. 14.4. Summary
  20. 15. Auditing, Encryption, and Compliance
    1. 15.1. Auditing in SQL Server 2008
      1. 15.1.1. Auditing Objects
      2. 15.1.2. Server Audit Object
      3. 15.1.3. Server Audit Specification Object
      4. 15.1.4. Database Audit Specification Object
    2. 15.2. Encryption
      1. 15.2.1. Encryption Primer
      2. 15.2.2. Password-Based Encryption
      3. 15.2.3. Certificate-Based Encryption
      4. 15.2.4. Transparent Data Encryption
    3. 15.3. Validating Server Configuration
      1. 15.3.1. The Need for a Policy
      2. 15.3.2. Create Policy on a Local Server
      3. 15.3.3. Evaluate the Policy
      4. 15.3.4. Using the Central Management Server
    4. 15.4. Summary
  21. 16. SQL Server in the Enterprise
    1. 16.1. Systems Center Operations Manager 2007
    2. 16.2. Installing the SQL Server SCOM Management Pack
    3. 16.3. Getting Familiar with the SQL Server Management Pack
    4. 16.4. Finding and Resolving an Issue
    5. 16.5. Using System Center Data Protection Manager
      1. 16.5.1. Setting Up a Protection Group
      2. 16.5.2. Executing DPM Reports
      3. 16.5.3. Recovering a Databases
    6. 16.6. Summary
  22. 17. Where to Go Next?
    1. 17.1. Visit SQL Server Books Online
    2. 17.2. Keep Up with Public SQL Server Websites
    3. 17.3. Attend a Conference
    4. 17.4. Find a Local Users Group
    5. 17.5. Create a Lab System and Practice
    6. 17.6. Get Certified!

Product information

  • Title: Beginning SQL Server 2008 Administration
  • Author(s):
  • Release date: October 2009
  • Publisher(s): Apress
  • ISBN: 9781430224136