Beginning, Microsoft® SQL Server® 2008 Administration

Beginning, Microsoft® SQL Server® 2008 Administration

Released April 2009
Publisher(s): Wrox
ISBN: 9780470440919

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

SQL Server 2008 introduces many new features that will change database administration procedures; many DBAs will be forced to migrate to SQL Server 2008. This book teaches you how to develop the skills required to successfully administer a SQL Server 2008 database; no prior experience is required. The material covers system installation and configuration/architecting, implementing and monitoring security controls, configuring and managing network communications, automating administration tasks, disaster prevention and recovery, performance monitoring, optimizing and ensuring high availability, as well as major SQL Server 2008 components including Integration Services, Reporting Services, Analysis Services, and Service Broker.

Table of contents

  1. Copyright
  2. About the Authors
  3. Credits
  4. Acknowledgments
  5. Introduction
    1. Who This Book Is For
      1. A Note about This Second Edition
      2. Assumptions
    2. What This Book Covers
    3. How This Book Is Structured
    4. What You Need to Use This Book
    5. Conventions
    6. Source Code
    7. Errata
    8. p2p.wrox.com
  6. 1. Introducing SQL Server 2008
    1. 1.1. A Condensed History of SQL Server
      1. 1.1.1. In the Beginning
      2. 1.1.2. The Evolution of a Database
      3. 1.1.3. Microsoft Goes It Alone
      4. 1.1.4. BI for the Masses
      5. 1.1.5. 2008 ... and Beyond!
    2. 1.2. What Is SQL Server 2008?
      1. 1.2.1. Database Engine
      2. 1.2.2. Integration Services
      3. 1.2.3. Analysis Services
      4. 1.2.4. Reporting Services
      5. 1.2.5. Service Broker
      6. 1.2.6. Data Tier Web Services
      7. 1.2.7. Replication Services
      8. 1.2.8. Multiple Instances
      9. 1.2.9. Database Mail
      10. 1.2.10. A Note about Notification Services
    3. 1.3. SQL Server 2008 Editions
      1. 1.3.1. SQL Server Compact 3.5 SP1
      2. 1.3.2. SQL Server 2008 Express Edition
      3. 1.3.3. SQL Server 2008 Web Edition
      4. 1.3.4. SQL Server 2008 Workgroup Edition
      5. 1.3.5. SQL Server 2008 Standard Edition
      6. 1.3.6. SQL Server 2008 Enterprise Edition
    4. 1.4. SQL Server 2008 Architecture
      1. 1.4.1. SQL Server 2008 Communication
        1. 1.4.1.1. Supported Languages
        2. 1.4.1.2. SQL Server Programming Object Models
      2. 1.4.2. SQL Server 2008 Services
        1. 1.4.2.1. MSSQLServer (SQL Server)
        2. 1.4.2.2. SQLServerAgent (SQL Server Agent)
        3. 1.4.2.3. MSSQLServerADHelper100 (SQL Server Active Directory Helper)
        4. 1.4.2.4. MSSQLServerOLAPService (SQL Server Analysis Services)
        5. 1.4.2.5. SQLBrowser (SQL Server Browser)
        6. 1.4.2.6. MSSQLFDLauncher (SQL Full-Text Filter Daemon Launcher)
        7. 1.4.2.7. MSDTSServer100 (SQL Server Integration Services)
        8. 1.4.2.8. ReportingServicesServer (SQL Server Reporting Services)
        9. 1.4.2.9. SQLWriter (SQL Server VSS Writer)
        10. 1.4.2.10. MSDTC (Distributed Transaction Coordinator)
    5. 1.5. SQL Server 2008 Database Objects
      1. 1.5.1. Server
      2. 1.5.2. Database
      3. 1.5.3. Schema
      4. 1.5.4. Object Names
    6. 1.6. SQL Server 2008 Databases
      1. 1.6.1. System Databases
        1. 1.6.1.1. The master Database
        2. 1.6.1.2. The model Database
        3. 1.6.1.3. The msdb Database
        4. 1.6.1.4. The tempdb Database
        5. 1.6.1.5. The resource Database
      2. 1.6.2. User Databases
      3. 1.6.3. Distribution Databases
    7. 1.7. SQL Server 2008 Database Storage
      1. 1.7.1. Data Files and Filegroups
      2. 1.7.2. Log Files
    8. 1.8. SQL Server Security
      1. 1.8.1. Windows Authentication Mode
      2. 1.8.2. SQL Server and Windows Authentication Mode (Mixed Mode)
    9. 1.9. Summary
  7. 2. Installing SQL Server 2008
    1. 2.1. SQL Server Installation Planning
      1. 2.1.1. Hardware Considerations
      2. 2.1.2. Processor Considerations
      3. 2.1.3. Memory Considerations
      4. 2.1.4. Storage Considerations
        1. 2.1.4.1. SAN and NAS versus Local Disk Storage
        2. 2.1.4.2. Storage Area Network (SAN)
        3. 2.1.4.3. Network Attached Storage (NAS)
        4. 2.1.4.4. Local Attached Disk Array
      5. 2.1.5. Virtualization Considerations
      6. 2.1.6. Software Prerequisites
    2. 2.2. SQL Server Installation Center
      1. 2.2.1. Setup Support Rules (for Setup Support Files)
      2. 2.2.2. Setup Support Rules (for Installation)
      3. 2.2.3. Feature Selection
        1. 2.2.3.1. Instance Configuration
        2. 2.2.3.2. Server Configuration
        3. 2.2.3.3. Collation Settings
        4. 2.2.3.4. Database Engine Configuration
        5. 2.2.3.5. Analysis Services Configuration
        6. 2.2.3.6. Reporting Services Configuration
        7. 2.2.3.7. Error and Usage Reporting
        8. 2.2.3.8. Installation Rules
        9. 2.2.3.9. Final Steps
      4. 2.2.4. Installing to a Windows Cluster
      5. 2.2.5. Configuring the Virtual Server Name
      6. 2.2.6. Sample Databases
    3. 2.3. Installation Review
    4. 2.4. Summary
  8. 3. SQL Server 2008 Tools
    1. 3.1. SQL Server Management Studio
      1. 3.1.1. Tool Windows
        1. 3.1.1.1. Object Explorer
        2. 3.1.1.2. Code Editor
        3. 3.1.1.3. Solution Explorer
        4. 3.1.1.4. Properties Window
        5. 3.1.1.5. Registered Servers
        6. 3.1.1.6. Bookmark Window
        7. 3.1.1.7. Toolbox
        8. 3.1.1.8. Error List
        9. 3.1.1.9. Object Explorer Details
        10. 3.1.1.10. Web Browser
        11. 3.1.1.11. Template Explorer
      2. 3.1.2. Toolbars
        1. 3.1.2.1. Database Diagram Toolbar
        2. 3.1.2.2. Debug Toolbar
        3. 3.1.2.3. Debug Location Toolbar
        4. 3.1.2.4. Help Toolbar
        5. 3.1.2.5. Query Designer Toolbar
        6. 3.1.2.6. Source Control Toolbar
        7. 3.1.2.7. SQL Editor Toolbar
        8. 3.1.2.8. SQL Server Analysis Services Editors Toolbar
        9. 3.1.2.9. SQL Server Compact Edition Editor Toolbar
        10. 3.1.2.10. Standard Toolbar
        11. 3.1.2.11. Table Designer Toolbar
        12. 3.1.2.12. Text Editor Toolbar
        13. 3.1.2.13. View Designer Toolbar
        14. 3.1.2.14. XML Editor Toolbar
      3. 3.1.3. SQL Server Management Studio Configuration
        1. 3.1.3.1. Environment
        2. 3.1.3.2. Text Editor
        3. 3.1.3.3. Query Execution
        4. 3.1.3.4. Query Results
        5. 3.1.3.5. SQL Server Object Explorer
        6. 3.1.3.6. Designers
        7. 3.1.3.7. Source Control
    2. 3.2. Log File Viewer
    3. 3.3. SQL Server Business Intelligence Development Studio
    4. 3.4. SQL Server Profiler
      1. 3.4.1. SQL Server Trace
      2. 3.4.2. Trace Properties
        1. 3.4.2.1. General Tab
        2. 3.4.2.2. Events Selection Tab
        3. 3.4.2.3. Events Extraction Settings Tab
    5. 3.5. Database Engine Tuning Advisor
      1. 3.5.1. General Tab
      2. 3.5.2. Tuning Options Tab
    6. 3.6. SQL Server Configuration Manager
    7. 3.7. Reporting Services Configuration Manager
    8. 3.8. Command-Line Tools
      1. 3.8.1. SQLCMD
        1. 3.8.1.1. Dedicated Administrator Connection (DAC)
      2. 3.8.2. Bulk Copy Program (BCP)
        1. 3.8.2.1. Non-XML Format File Example
        2. 3.8.2.2. XML Format File Example
        3. 3.8.2.3. Export a Table to a Flat-File Example
        4. 3.8.2.4. Import Flat-File Example with a Format File
      3. 3.8.3. PowerShell
    9. 3.9. Summary
  9. 4. SQL Server 2008 Storage Architecture
    1. 4.1. The Resource Database
      1. 4.1.1. The sys Schema
        1. 4.1.1.1. Dynamic Management Views and Functions
    2. 4.2. SQL Server Database Physical Structure
      1. 4.2.1. Physical Storage Data Types
        1. 4.2.1.1. Fixed-Length Data Types
        2. 4.2.1.2. Variable-Length and Large Object Data Types
        3. 4.2.1.3. CLR Data Types
        4. 4.2.1.4. In-Row Data
      2. 4.2.2. FILESTREAM Data
      3. 4.2.3. Other Data Types
      4. 4.2.4. SQL Server Database Files
      5. 4.2.5. Data Files
        1. 4.2.5.1. Extents
        2. 4.2.5.2. Pages
          1. 4.2.5.2.1. Data Pages
          2. 4.2.5.2.2. Index Pages
          3. 4.2.5.2.3. Text/Image Pages
          4. 4.2.5.2.4. Global Allocation Map (GAM) and Secondary Global Allocation Map (SGAM) Pages
          5. 4.2.5.2.5. Page Free Space (PFS) Pages
          6. 4.2.5.2.6. Index Allocation Map (IAM) Pages
          7. 4.2.5.2.7. Bulk Changed Map (BCM) Pages
          8. 4.2.5.2.8. Differential Changed Map (DCM) Pages
      6. 4.2.6. Transaction Log
        1. 4.2.6.1. Transactions
          1. 4.2.6.1.1. Auto-Commit
          2. 4.2.6.1.2. Implicit
          3. 4.2.6.1.3. Explicit
        2. 4.2.6.2. Recording Transactions
        3. 4.2.6.3. Transaction Log Physical Characteristics
    3. 4.3. Summary
  10. 5. SQL Server 2008 Databases
    1. 5.1. System Databases
    2. 5.2. User Databases
    3. 5.3. Database Planning
      1. 5.3.1. Capacity Planning
    4. 5.4. Creating Databases
      1. 5.4.1. Getting Started
      2. 5.4.2. Creating a New Database
        1. 5.4.2.1. Database Files
        2. 5.4.2.2. Filegroups
        3. 5.4.2.3. Maintenance or Performance?
        4. 5.4.2.4. File Size
        5. 5.4.2.5. Autogrowth
        6. 5.4.2.6. Path
        7. 5.4.2.7. Database Options
          1. 5.4.2.7.1. Collation
          2. 5.4.2.7.2. Recovery Model
          3. 5.4.2.7.3. Compatibility Level
        8. 5.4.2.8. Other Options
          1. 5.4.2.8.1. ANSI NULL Default
          2. 5.4.2.8.2. ANSI NULLS Enabled
          3. 5.4.2.8.3. ANSI Padding Enabled
          4. 5.4.2.8.4. ANSI Warnings Enabled
          5. 5.4.2.8.5. Arithmetic Abort Enabled
          6. 5.4.2.8.6. Auto Close
          7. 5.4.2.8.7. Auto Create Statistics
          8. 5.4.2.8.8. Auto Shrink
          9. 5.4.2.8.9. Auto Update Statistics
          10. 5.4.2.8.10. Auto Update Statistics Asynchronously
          11. 5.4.2.8.11. Broker Enabled
          12. 5.4.2.8.12. Close Cursor on Commit Enabled
          13. 5.4.2.8.13. Concatenate Null Yields Null
          14. 5.4.2.8.14. Cross-database Ownership Chaining Enabled
          15. 5.4.2.8.15. Database Read-Only
          16. 5.4.2.8.16. Database State
          17. 5.4.2.8.17. Date Correlation Optimization Enabled
          18. 5.4.2.8.18. Default Cursor
          19. 5.4.2.8.19. Encryption Enabled
          20. 5.4.2.8.20. Honor Broker Priority
          21. 5.4.2.8.21. Numeric Round-Abort
          22. 5.4.2.8.22. Page Verify
          23. 5.4.2.8.23. Parameterization
          24. 5.4.2.8.24. Quoted Identifiers Enabled
          25. 5.4.2.8.25. Recursive Triggers Enabled
          26. 5.4.2.8.26. Restrict Access
          27. 5.4.2.8.27. Service Broker Identifier
          28. 5.4.2.8.28. Trustworthy
          29. 5.4.2.8.29. VarDecimal Storage Format Enabled
        9. 5.4.2.9. Generating Database Creation Scripts
      3. 5.4.3. Schemas
        1. 5.4.3.1. Schemas and Name Resolution
        2. 5.4.3.2. Schema Creation
        3. 5.4.3.3. Schema Maintenance
      4. 5.4.4. Tables
        1. 5.4.4.1. Table Collation
        2. 5.4.4.2. Table Architecture
        3. 5.4.4.3. Partitioning Tables
        4. 5.4.4.4. Data Compression
          1. 5.4.4.4.1. Row Compression
      5. 5.4.5. Indexes
        1. 5.4.5.1. Clustered Indexes
        2. 5.4.5.2. Non-Clustered Indexes
          1. 5.4.5.2.1. Non-Clustered Indexes on Heaps
          2. 5.4.5.2.2. Non-Clustered Indexes on Clustered Indexes
        3. 5.4.5.3. Included Columns
        4. 5.4.5.4. Filtered Indexes
        5. 5.4.5.5. Hierarchal Indexes
          1. 5.4.5.5.1. Breadth-First Indexes
          2. 5.4.5.5.2. Depth-First Indexes
        6. 5.4.5.6. Spatial Indexes
        7. 5.4.5.7. XML Indexes
          1. 5.4.5.7.1. Primary XML Indexes
          2. 5.4.5.7.2. Secondary XML PATH Indexes
          3. 5.4.5.7.3. Secondary XML VALUE Indexes
          4. 5.4.5.7.4. Secondary XML PROPERTY Indexes
        8. 5.4.5.8. Maintaining Tables
          1. 5.4.5.8.1. Index Fragmentation
          2. 5.4.5.8.2. Mitigating Fragmentation with Fill-Factor
          3. 5.4.5.8.3. Removing Fragmentation
          4. 5.4.5.8.4. Create Index with Drop Existing
          5. 5.4.5.8.5. Rebuilding Indexes
          6. 5.4.5.8.6. Reorganizing Indexes
          7. 5.4.5.8.7. Statistics
      6. 5.4.6. Enforcing Data Integrity
        1. 5.4.6.1. Primary Key Constraints
        2. 5.4.6.2. Unique Constraints
        3. 5.4.6.3. Foreign Key Constraints
          1. 5.4.6.3.1. Foreign Key Constraint Options
          2. 5.4.6.3.2. WITH CHECK
          3. 5.4.6.3.3. WITH NOCHECK
          4. 5.4.6.3.4. Cascading Constraints
          5. 5.4.6.3.5. ON DELETE CASCADE
          6. 5.4.6.3.6. ON UPDATE CASCADE
          7. 5.4.6.3.7. ON DELETE SET NULL
          8. 5.4.6.3.8. ON UPDATE SET NULL
          9. 5.4.6.3.9. ON DELETE SET DEFAULT
          10. 5.4.6.3.10. ON UPDATE SET DEFAULT
        4. 5.4.6.4. Check Constraints
        5. 5.4.6.5. Default Constraints
    5. 5.5. Database Diagrams
    6. 5.6. Views
      1. 5.6.1. System Views
    7. 5.7. Synonyms
    8. 5.8. Programming Objects
      1. 5.8.1. Stored Procedures
      2. 5.8.2. Functions
      3. 5.8.3. Triggers
        1. 5.8.3.1. DML Triggers
          1. 5.8.3.1.1. After Triggers
          2. 5.8.3.1.2. "Instead Of" Triggers
        2. 5.8.3.2. DDL Triggers
      4. 5.8.4. Assemblies
      5. 5.8.5. Types
        1. 5.8.5.1. User-Defined Data Types
        2. 5.8.5.2. User-Defined Table Types
        3. 5.8.5.3. User-Defined Types
      6. 5.8.6. Defaults
      7. 5.8.7. Rules
    9. 5.9. Summary
  11. 6. SQL Server 2008 Security
    1. 6.1. SQL Server Authentication Modes
      1. 6.1.1. Changing the Authentication Mode from Management Studio
      2. 6.1.2. Using the xp_instance_regwrite Extended Stored Procedure
    2. 6.2. Principals
      1. 6.2.1. Logins
        1. 6.2.1.1. Creating Logins in Management Studio
        2. 6.2.1.2. Creating Logins Using T-SQL
        3. 6.2.1.3. Managing Logins
        4. 6.2.1.4. Using CREATE LOGIN
        5. 6.2.1.5. For More Information
      2. 6.2.2. Credentials
        1. 6.2.2.1. Creating a New Credential
        2. 6.2.2.2. Using Transact-SQL
      3. 6.2.3. Server Roles
        1. 6.2.3.1. Using Fixed Server Roles
        2. 6.2.3.2. For More Information
      4. 6.2.4. Database Users
        1. 6.2.4.1. CREATE USER
      5. 6.2.5. Fixed Database Roles
        1. 6.2.5.1. Creating a New User-Defined Database Role in Management Studio
        2. 6.2.5.2. CREATE ROLE
        3. 6.2.5.3. Application Roles
        4. 6.2.5.4. Using CREATE APPLICATION ROLE
        5. 6.2.5.5. For More Information
    3. 6.3. Permissions
      1. 6.3.1. Server Permissions
      2. 6.3.2. Database Scope Permissions
      3. 6.3.3. Schema Scope Permissions
      4. 6.3.4. Using SQL Server Management Studio for Managing Permissions
    4. 6.4. SQL Server Encryption
      1. 6.4.1. Extensible Key Management (EKM)
      2. 6.4.2. Encryption Tools
        1. 6.4.2.1. Symmetric Keys
        2. 6.4.2.2. Asymmetric Keys
        3. 6.4.2.3. Certificates
        4. 6.4.2.4. Encrypting Data
        5. 6.4.2.5. Transparent Data Encryption
        6. 6.4.2.6. Digital Signatures
    5. 6.5. Best Practices
    6. 6.6. Summary
  12. 7. Configuring SQL Server Network Communication
    1. 7.1. SQL Server 2008 Network Protocols
      1. 7.1.1. Shared Memory
      2. 7.1.2. Named Pipes
      3. 7.1.3. TCP/IP
      4. 7.1.4. Virtual Interface Adapter (VIA)
    2. 7.2. SQL Native Client Configuration
    3. 7.3. SQL Server Endpoints
      1. 7.3.1. Default TSQL Endpoints
        1. 7.3.1.1. TSQL Default TCP
        2. 7.3.1.2. TSQL Default VIA
        3. 7.3.1.3. TSQL Named Pipes
        4. 7.3.1.4. TSQL Local Machine
        5. 7.3.1.5. Dedicated Administrator Connection (DAC)
      2. 7.3.2. TSQL TCP Endpoints
      3. 7.3.3. Database Mirroring Endpoints
      4. 7.3.4. SOAP Endpoints
      5. 7.3.5. Service Broker Endpoints
      6. 7.3.6. Securing Endpoints
    4. 7.4. Summary
  13. 8. Automating Administrative Tasks
    1. 8.1. Policy-Based Management
      1. 8.1.1. Targets
      2. 8.1.2. Facets
      3. 8.1.3. Conditions
      4. 8.1.4. Policies
      5. 8.1.5. Policy Categories
      6. 8.1.6. Effective Policies
    2. 8.2. Central Management Servers
    3. 8.3. Database Mail
      1. 8.3.1. How It Works
      2. 8.3.2. How to Configure Database Mail
      3. 8.3.3. Configuring Database Mail Options
      4. 8.3.4. Managing Profiles and Accounts
        1. 8.3.4.1. sysmail_add_profile_sp
        2. 8.3.4.2. sysmail_add_account_sp
        3. 8.3.4.3. sysmail_add_profileaccount_sp
        4. 8.3.4.4. sysmail_update_profile_sp
        5. 8.3.4.5. sysmail_update_account_sp
        6. 8.3.4.6. sysmail_update_profileaccount_sp
        7. 8.3.4.7. sysmail_add_principalprofile_sp
        8. 8.3.4.8. sysmail_update_principalprofile_sp
        9. 8.3.4.9. sysmail_delete_principalprofile_sp
        10. 8.3.4.10. sysmail_delete_profileaccount_sp
        11. 8.3.4.11. sysmail_delete_account_sp
        12. 8.3.4.12. sysmail_delete_profile_sp
      5. 8.3.5. Guidelines for Deleting Mail Objects
      6. 8.3.6. Sending Mail
        1. 8.3.6.1. sp_send_dbmail
      7. 8.3.7. Managing Messages
    4. 8.4. Event Notifications
    5. 8.5. SQL Server Agent
      1. 8.5.1. Configuring the SQL Server Agent Service
        1. 8.5.1.1. General Properties
        2. 8.5.1.2. Advanced Properties
        3. 8.5.1.3. Alert System Properties
        4. 8.5.1.4. Job System Properties
        5. 8.5.1.5. Agent Connection Properties
        6. 8.5.1.6. Job History Properties
      2. 8.5.2. SQL Server Agent Security
        1. 8.5.2.1. SQLAgentUserRole
        2. 8.5.2.2. SQLAgentReaderRole
        3. 8.5.2.3. SQLAgentOperatorRole
      3. 8.5.3. Creating Jobs
        1. 8.5.3.1. Categories
        2. 8.5.3.2. Creating Job Steps
        3. 8.5.3.3. sp_add_jobstep
        4. 8.5.3.4. Token Replacement
        5. 8.5.3.5. Using Tokens in Job Steps
      4. 8.5.4. Creating Schedules
        1. 8.5.4.1. sp_add_schedule
        2. 8.5.4.2. sp_attach_schedule
      5. 8.5.5. Creating Operators
        1. 8.5.5.1. sp_add_operator
        2. 8.5.5.2. The Fail-Safe Operator
      6. 8.5.6. Creating Alerts
        1. 8.5.6.1. SQL Server Event-Based Alerts
        2. 8.5.6.2. Performance Condition Alerts
        3. 8.5.6.3. WMI Event Alerts
      7. 8.5.7. Creating Proxies
      8. 8.5.8. Multi-Server Jobs
    6. 8.6. Maintenance Plans
      1. 8.6.1. Maintenance Plan Wizard
      2. 8.6.2. Maintenance Plan Designer
    7. 8.7. Best Practices
    8. 8.8. Summary
  14. 9. Disaster Prevention and Recovery
    1. 9.1. Chapter Preparation
    2. 9.2. Database Recovery Models
      1. 9.2.1. Full Recovery Model
      2. 9.2.2. Bulk-Logged Recovery Model
      3. 9.2.3. Simple Recovery Model
    3. 9.3. SQL Server 2008 Database Backup
      1. 9.3.1. Backup Devices
    4. 9.4. SQL Server 2008 Backup Types
      1. 9.4.1. Full Backup
      2. 9.4.2. Differential Backup
      3. 9.4.3. File/Filegroup Backup
        1. 9.4.3.1. File/Filegroup with Differential
      4. 9.4.4. Transaction Log Backup
      5. 9.4.5. Partial Backup
      6. 9.4.6. Copy Only Backup
    5. 9.5. Backup Options
      1. 9.5.1. Backup Stripe
      2. 9.5.2. Mirrored Backup
      3. 9.5.3. Compressed Backup
      4. 9.5.4. WITH Options
    6. 9.6. Backup Strategies
      1. 9.6.1. Full Backup Only
      2. 9.6.2. Full Backup with Differential
      3. 9.6.3. Full Backup with Transaction Log
      4. 9.6.4. Full and Differential Backup with Transaction Log
      5. 9.6.5. File and Filegroup Backup
      6. 9.6.6. Filegroup with Differential
      7. 9.6.7. Partial Backup
      8. 9.6.8. Backup Summary
    7. 9.7. Restoring Databases
      1. 9.7.1. Restore Process
      2. 9.7.2. Delaying Recovery
    8. 9.8. RESTORE Command
      1. 9.8.1. RESTORE DATABASE database_name
        1. 9.8.1.1. FILE
        2. 9.8.1.2. FILEGROUP
        3. 9.8.1.3. READ_WRITE_FILEGROUPS
        4. 9.8.1.4. PAGE
        5. 9.8.1.5. RESTORE LOG database_name
      2. 9.8.2. FROM Options
        1. 9.8.2.1. FROM DISK
        2. 9.8.2.2. FROM TAPE
        3. 9.8.2.3. FROM DATABASE_SNAPSHOT
      3. 9.8.3. WITH Clause
        1. 9.8.3.1. RECOVERY | NORECOVERY
        2. 9.8.3.2. STANDBY
        3. 9.8.3.3. CHECKSUM | NO_CHECKSUM
        4. 9.8.3.4. CONTINUE_AFTER_ERROR | STOP_ON_ERROR
        5. 9.8.3.5. FILE
        6. 9.8.3.6. MOVE...TO...
        7. 9.8.3.7. PARTIAL
        8. 9.8.3.8. REPLACE
      4. 9.8.4. Database Restore Preparation
        1. 9.8.4.1. Isolate the Database
        2. 9.8.4.2. Capture Recent Activity
        3. 9.8.4.3. Gather Backup Information
      5. 9.8.5. Restoring User Databases
        1. 9.8.5.1. Full Restore
        2. 9.8.5.2. Full with Differential Restore
        3. 9.8.5.3. Full with Transaction Log Restore
        4. 9.8.5.4. Full and Differential with Transaction Log Restore
        5. 9.8.5.5. File and Filegroup Restore
        6. 9.8.5.6. Partial Restore
        7. 9.8.5.7. Point-in-Time Restore
      6. 9.8.6. Recovering System Databases
        1. 9.8.6.1. Recovering the Master Database
        2. 9.8.6.2. Orphaned Users
      7. 9.8.7. Database Restore Summary
    9. 9.9. Database Snapshots
      1. 9.9.1. Database Snapshot Limitations
      2. 9.9.2. Disaster Recovery and Database Snapshots
        1. 9.9.2.1. Undoing Updates
        2. 9.9.2.2. Undoing Deletes
        3. 9.9.2.3. Undoing Drops
        4. 9.9.2.4. Restoring from Snapshots
    10. 9.10. Summary
  15. 10. Monitoring SQL Server
    1. 10.1. Performance Monitoring
      1. 10.1.1. Performance Monitoring Strategy
      2. 10.1.2. Creating a Performance Baseline
        1. 10.1.2.1. Performance Counters
          1. 10.1.2.1.1. Processor Counters
          2. 10.1.2.1.2. Disk Counters
          3. 10.1.2.1.3. Memory Counters
          4. 10.1.2.1.4. Network Counters
          5. 10.1.2.1.5. SQL Server Counters
        2. 10.1.2.2. Dynamic Management Views
    2. 10.2. Tools and Techniques for Monitoring
      1. 10.2.1. Log File Viewer
      2. 10.2.2. Activity Monitor
      3. 10.2.3. System Stored Procedures
        1. 10.2.3.1.
          1. 10.2.3.1.1. sp_who and sp_who2
          2. 10.2.3.1.2. sp_lock
        2. 10.2.3.2. SQL Server Locking
          1. 10.2.3.2.1. KILL
      4. 10.2.4. Using Profiler
        1. 10.2.4.1. Detect and Analyze Long-Running Queries with Profiler
      5. 10.2.5. Monitoring Files
        1. 10.2.5.1. Disk Usage Report
          1. 10.2.5.1.1. sp_spaceused
          2. 10.2.5.1.2. sys.sysfiles
        2. 10.2.5.2. Monitoring Files with Performance Monitor
    3. 10.3. Auditing
      1. 10.3.1. SQL Server Audit
        1. 10.3.1.1. Audit Package
        2. 10.3.1.2. Server Audit Specification
        3. 10.3.1.3. Database Audit Specification
        4. 10.3.1.4. Audit Destination
        5. 10.3.1.5. Targeting the Security Log
      2. 10.3.2. Login Auditing
      3. 10.3.3. C2 Audit Mode
      4. 10.3.4. Security Audit Event Category
      5. 10.3.5. SQL Trace
    4. 10.4. Tracking Changes
      1. 10.4.1. Change Data Capture
        1. 10.4.1.1. Configuration
        2. 10.4.1.2. Using Change Data Capture
        3. 10.4.1.3. Job Control
        4. 10.4.1.4. Related Dynamic Management Views
        5. 10.4.1.5. Limitations
        6. 10.4.1.6. Tables
      2. 10.4.2. Change Tracking
        1. 10.4.2.1. Functions and Views
    5. 10.5. Data Collection
      1. 10.5.1. Terminology
      2. 10.5.2. Architecture and Processing
      3. 10.5.3. Configuring Data Collection
      4. 10.5.4. Data Collector Types
      5. 10.5.5. Data Collection Sets
      6. 10.5.6. Error Handling
      7. 10.5.7. Reporting
      8. 10.5.8. Management Data Warehouse
    6. 10.6. Monitoring Database Modifications
      1. 10.6.1. Data Definition Language (DDL) Triggers
        1. 10.6.1.1. EVENTDATA Function
    7. 10.7. Summary
  16. 11. Optimizing SQL Server
    1. 11.1. Hardware Optimization
      1. 11.1.1. CPU Selection
      2. 11.1.2. Hyperthreading
      3. 11.1.3. Memory
      4. 11.1.4. Storage Options
        1. 11.1.4.1. Write Caching
        2. 11.1.4.2. Solid-State Drives
      5. 11.1.5. Network Design
      6. 11.1.6. Virtualizing SQL Server
    2. 11.2. Design Considerations
      1. 11.2.1. Database Recovery Model
      2. 11.2.2. Designing Efficient Tables
        1. 11.2.2.1. Normalization
        2. 11.2.2.2. De-Normalization
        3. 11.2.2.3. Data Type Selection
        4. 11.2.2.4. Handling Complex Object Data
      3. 11.2.3. Declarative Referential Integrity (DRI)
      4. 11.2.4. Constraints versus Triggers
      5. 11.2.5. Deciding What to Index
        1. 11.2.5.1. Primary Key Choice
        2. 11.2.5.2. Cluster Key Choice
        3. 11.2.5.3. GUIDS as Cluster Keys
        4. 11.2.5.4. Other Indexes
      6. 11.2.6. Indexed Views and Filtered Indexes
      7. 11.2.7. Minimizing Blocking
      8. 11.2.8. Hidden Dangers of Time-Outs
    3. 11.3. Query Optimization
      1. 11.3.1. Execution Plans
        1. 11.3.1.1. Plan Caching
        2. 11.3.1.2. Viewing Execution Plans
        3. 11.3.1.3. Analyzing Execution Plans
      2. 11.3.2. Updating Statistics
      3. 11.3.3. Managing Indexes
        1. 11.3.3.1. Identifying Missing Indexes
          1. 11.3.3.1.1. Using Automatic Statistics to Identify Missing Indexes
          2. 11.3.3.1.2. Using XML ShowPlan to Identify Missing Indexes
        2. 11.3.3.2. Index De-Fragmentation
        3. 11.3.3.3. Determining Index Effectiveness
      4. 11.3.4. Query Optimizer Hints
        1. 11.3.4.1. Table Hints
        2. 11.3.4.2. Join Hints
        3. 11.3.4.3. Query Hints
      5. 11.3.5. Plan Guides
        1. 11.3.5.1. Creating Plan Guides
        2. 11.3.5.2. Creating Plan Guides from the Plan Cache
        3. 11.3.5.3. Plan Freezing
      6. 11.3.6. Database Engine Tuning Advisor
        1. 11.3.6.1. Using the DTA with Profiler
        2. 11.3.6.2. Correlating Database Activity with Performance Counters
    4. 11.4. T-SQL Optimization Tips
      1. 11.4.1. Limiting Result Sets
        1. 11.4.1.1. TOP N and SET ROWCOUNT
        2. 11.4.1.2. HAVING versus WHERE
        3. 11.4.1.3. Functions in the WHERE Clause
      2. 11.4.2. ANSI-Style Join Syntax
      3. 11.4.3. Dealing with Null Values
      4. 11.4.4. Alternatives to Cursors
      5. 11.4.5. Merge Joins
      6. 11.4.6. Grouping Sets
      7. 11.4.7. Distinct Aggregation
      8. 11.4.8. How Many Records Are in That Table?
      9. 11.4.9. Temp Tables versus Table Variables
    5. 11.5. Resource Governor
      1. 11.5.1. Configuring the Resource Governor
        1. 11.5.1.1. Configure Resource Pools
        2. 11.5.1.2. Configure Workload Groups
        3. 11.5.1.3. Create a Classifier Function
        4. 11.5.1.4. Assign the Classifier Function and Reconfigure
      2. 11.5.2. Monitoring the Resource Governor
        1. 11.5.2.1. Performance Counters
        2. 11.5.2.2. Events
        3. 11.5.2.3. System Views
    6. 11.6. Summary
  17. 12. SQL Server High Availability
    1. 12.1. Introduction to High Availability
    2. 12.2. Failover Clustering
      1. 12.2.1. Windows Clustering — A Quick Primer
      2. 12.2.2. Clustering Components
      3. 12.2.3. Active/Passive Clustering
      4. 12.2.4. Active/Active Clustering
      5. 12.2.5. Considering Clustering
    3. 12.3. Log Shipping
      1. 12.3.1. Preparing for Log Shipping
      2. 12.3.2. Configuring Log Shipping with SQL Server Management Studio
      3. 12.3.3. Configuring Log Shipping with Transact-SQL
      4. 12.3.4. Configuring Failover
    4. 12.4. Database Mirroring
      1. 12.4.1. Client Redirection
      2. 12.4.2. Database Mirroring Modes
        1. 12.4.2.1. High-Performance Mode
        2. 12.4.2.2. High-Safety without Automatic Failover Mode
        3. 12.4.2.3. High-Safety with Automatic Failover Mode
      3. 12.4.3. Configuring Database Mirroring
        1. 12.4.3.1. Using SQL Server Management Studio
        2. 12.4.3.2. Using Transact-SQL
      4. 12.4.4. Monitoring Database Mirroring
      5. 12.4.5. Managing Database Mirroring
        1. 12.4.5.1. Pausing a Mirror Session
        2. 12.4.5.2. Resuming a Mirror Session
        3. 12.4.5.3. Manual Failover
        4. 12.4.5.4. Forcing Service on the Mirror
        5. 12.4.5.5. Removing a Database Mirror
    5. 12.5. Summary
  18. 13. Introduction to Replication
    1. 13.1. Replication Overview
    2. 13.2. SQL Server Replication Agents
      1. 13.2.1. Snapshot Agent
      2. 13.2.2. Log Reader Agent
      3. 13.2.3. Distribution Agent
      4. 13.2.4. Merge Agent
      5. 13.2.5. Queue Reader Agent
    3. 13.3. SQL Server Replication Types
      1. 13.3.1. Distributed Transactions
      2. 13.3.2. Transactional Replication
        1. 13.3.2.1. Standard Transactional Publication
        2. 13.3.2.2. Transactional Publications with Updatable Subscriptions in a Hierarchical Topology
        3. 13.3.2.3. Transactional Publications in a Peer-to-Peer Topology
      3. 13.3.3. Snapshot Replication
      4. 13.3.4. Merge Replication
      5. 13.3.5. Oracle Replication
    4. 13.4. SQL Server Replication Models
      1. 13.4.1. Single Publisher/Multiple Subscribers
      2. 13.4.2. Multiple Publishers/Single Subscriber
      3. 13.4.3. Multiple Publishers/Multiple Subscribers
    5. 13.5. Replication Tools
      1. 13.5.1. Filtering
        1. 13.5.1.1. Static Row Filters
        2. 13.5.1.2. Column Filters
        3. 13.5.1.3. Parameterized Row Filters
        4. 13.5.1.4. Join Filters
      2. 13.5.2. Replicating Partitioned Tables and Indexes
      3. 13.5.3. New Publication Wizard
      4. 13.5.4. New Subscription Wizard
      5. 13.5.5. Replication Monitor
    6. 13.6. Summary
  19. 14. Introduction to the Common Language Runtime
    1. 14.1. Databases and Programming
    2. 14.2. Is Transact-SQL Going Away?
    3. 14.3. .NET and the CLR
      1. 14.3.1. Assemblies
      2. 14.3.2. Namespaces
      3. 14.3.3. Classes
      4. 14.3.4. Methods
    4. 14.4. SQL Server CLR Objects
      1. 14.4.1. Enabling SQL CLR
      2. 14.4.2. Creating a SQL CLR Assembly
      3. 14.4.3. Adding an Assembly
      4. 14.4.4. Compatible Data Types
      5. 14.4.5. User-Defined Functions
        1. 14.4.5.1. Scalar Functions
        2. 14.4.5.2. Table-Valued Functions
      6. 14.4.6. Stored Procedures
      7. 14.4.7. Triggers
      8. 14.4.8. User-Defined Types
      9. 14.4.9. Aggregates
    5. 14.5. Deployment with Visual Studio
    6. 14.6. Programming Support
      1. 14.6.1. Threading
      2. 14.6.2. Impersonation
    7. 14.7. Security Options
      1. 14.7.1. .NET Security
      2. 14.7.2. Securing SQL CLR
      3. 14.7.3. SQL Server CLR Permission Sets
        1. 14.7.3.1. SAFE Permission
        2. 14.7.3.2. External Access Permission
        3. 14.7.3.3. UNSAFE Permission
        4. 14.7.3.4. Enabling External Access and Unsafe Permissions
    8. 14.8. Summary
  20. 15. An Administrator's Guide to Business Intelligence
    1. 15.1. Understanding BI
    2. 15.2. Performance Management
    3. 15.3. Business Intelligence Components
      1. 15.3.1. Data Goes In, Data Comes Out
      2. 15.3.2. Analyze This!
      3. 15.3.3. Did You Get the Memo about Cover Pages?
    4. 15.4. Beyond SQL
      1. 15.4.1. The BI Side of SharePoint
      2. 15.4.2. ProClarity and PerformancePoint Server
    5. 15.5. So Many Tools, So Little Time
    6. 15.6. Summary
  21. 16. Introduction to SQL Server Integration Services
    1. 16.1. About SSIS
      1. 16.1.1. Integration Services
      2. 16.1.2. Integration Services Object Model
      3. 16.1.3. Integration Services Run Time
        1. 16.1.3.1. Integration Services Packages
        2. 16.1.3.2. Integration Services Tasks
        3. 16.1.3.3. Integration Services Containers
        4. 16.1.3.4. Integration Services Event Handlers
      4. 16.1.4. Integration Services Data Flow
    2. 16.2. Importing and Exporting Data
      1. 16.2.1. Using the Import Wizard
      2. 16.2.2. Using the Export Wizard
    3. 16.3. Transforming Data with SSIS
      1. 16.3.1. Understanding the Development Environment
        1. 16.3.1.1. Toolbox
        2. 16.3.1.2. Solution Explorer
        3. 16.3.1.3. Properties
        4. 16.3.1.4. Connection Managers
      2. 16.3.2. Package Elements
        1. 16.3.2.1. Control Flow
        2. 16.3.2.2. Control Flow Tasks
        3. 16.3.2.3. Precedence Constraints
        4. 16.3.2.4. Task Grouping
        5. 16.3.2.5. Data Flow
          1. 16.3.2.5.1. Data Sources
          2. 16.3.2.5.2. Transformations
          3. 16.3.2.5.3. Data Destinations
        6. 16.3.2.6. Event Handling
        7. 16.3.2.7. Package Explorer
      3. 16.3.3. Creating a Simple Package
        1. 16.3.3.1. Creating the Connection
        2. 16.3.3.2. Creating the Data Flow Task
        3. 16.3.3.3. Defining the Destination
        4. 16.3.3.4. So That's It, Right? Applying Additional Transforms
    4. 16.4. Summary
  22. 17. Introduction to SQL Server Analysis Services
    1. 17.1. Understanding OLAP
      1. 17.1.1. OLAP Terminology
    2. 17.2. Working with SSAS
      1. 17.2.1. Creating the Project
      2. 17.2.2. Defining a Data Source
      3. 17.2.3. Creating the Data Source View
      4. 17.2.4. Defining Dimensions
      5. 17.2.5. Creating the Cube
      6. 17.2.6. Create Hierarchies
        1. 17.2.6.1. Create the Products Hierarchy
        2. 17.2.6.2. Create the Resellers and Geographies Hierarchies
        3. 17.2.6.3. Reseller Attribute Relationships
        4. 17.2.6.4. Build the Calendar Hierarchy
        5. 17.2.6.5. Calendar Attribute Relationships
      7. 17.2.7. Deploying the Project
    3. 17.3. Managing SSAS
      1. 17.3.1. Browsing the Cube
      2. 17.3.2. SSAS Security
        1. 17.3.2.1. Creating a Role
        2. 17.3.2.2. Testing Security
    4. 17.4. Advanced SSAS Concepts
      1. 17.4.1. MDX
      2. 17.4.2. Data Mining
    5. 17.5. Summary
  23. 18. Introduction to SQL Server Reporting Services
    1. 18.1. SQL Server Reporting Services Overview
      1. 18.1.1. Components and Tools
        1. 18.1.1.1. Report Server
        2. 18.1.1.2. Report Processor
        3. 18.1.1.3. Scheduling and Delivery Processor
        4. 18.1.1.4. Extensions
        5. 18.1.1.5. Reporting Services Configuration Manager
        6. 18.1.1.6. SQL Server Management Studio
        7. 18.1.1.7. Business Intelligence Development Studio
        8. 18.1.1.8. Report Builder
        9. 18.1.1.9. Report Manager
    2. 18.2. Installation and Configuration
      1. 18.2.1. Hardware and Software Requirements
      2. 18.2.2. Security Considerations
      3. 18.2.3. Installation Mode
      4. 18.2.4. Multiple Instances and Versions
    3. 18.3. Creating Reports
      1. 18.3.1. Report Designer
      2. 18.3.2. Report Builder
        1. 18.3.2.1. Connecting to Data
        2. 18.3.2.2. Laying out Report Data
        3. 18.3.2.3. Publishing Reports
    4. 18.4. Report Delivery
      1. 18.4.1. Caching
      2. 18.4.2. Snapshots
      3. 18.4.3. Subscriptions
    5. 18.5. Summary
  24. 19. Introduction to Service Broker
    1. 19.1. Service-Oriented Architecture
    2. 19.2. Service Broker Overview
    3. 19.3. Service Broker Elements
      1. 19.3.1. Conversations
        1. 19.3.1.1. Messages
        2. 19.3.1.2. Dialog Conversations
        3. 19.3.1.3. Conversation Groups
        4. 19.3.1.4. Conversation Priorities
      2. 19.3.2. Contracts
      3. 19.3.3. Queues
      4. 19.3.4. Services
      5. 19.3.5. Routes
    4. 19.4. Security Considerations for Service Broker
      1. 19.4.1. Dialog Security
        1. 19.4.1.1. Full Security
        2. 19.4.1.2. Anonymous Security
      2. 19.4.2. Transport Security
    5. 19.5. Creating a Sample Application
      1. 19.5.1. Creating and Preparing the Database
      2. 19.5.2. Creating the Service Broker Objects
      3. 19.5.3. Creating Objects for the TicketInputService
      4. 19.5.4. Creating Objects for the TicketNotifyService
      5. 19.5.5. Testing the Application
    6. 19.6. Managing Service Broker with SSMS
    7. 19.7. Summary

Product information

  • Title: Beginning, Microsoft® SQL Server® 2008 Administration
  • Author(s):
  • Release date: April 2009
  • Publisher(s): Wrox
  • ISBN: 9780470440919