1. Changes in the Database Paradigm: How SQL Server 2005 Redefines the Role of a DBA.
SQL Server Philosophy
Enterprise Suitability
Developer Friendliness
Cost of Ownership
Completeness
Ubiquity
History
SQL Server 2005 and Database Administrators
One Small Step for a DBA
A Giant Leap for DBA-kind
Where No One Has Gone Before
2. SQL Server 2005 Database Engine Architectural Changes.
The SQL Server Operating System (SQLOS)
User Mode Scheduler (UMS)
Introducing SQLOS
Non-Uniform Memory Access (NUMA) Architecture Support
Query Optimization and Execution
Statistics Management in SQL Server 2005
Statement-Level Recompilation
Calculating Query Plan Cost
Parallel Query Processing
Row Versioning
System Metadata Access and the Security Architecture
The Resource Database
Metadata Access and Visibility
User-Schema Separation
Storage Engine and I/O Changes
3. SQL Server 2005 Setup and Deployment.
SQL Server 2005 Editions
Hardware and Software Requirements for Installing SQL Server 2005
SQL Server 2005 Enterprise Edition 32-Bit Requirements
SQL Server 2005 Enterprise Edition 64-Bit Requirements
SQL Server 2005 Setup Enhancements
Upgrading to SQL Server 2005
Deprecated and Discontinued Database Engine Functionality
Breaking and Behavior Changes to the Database Engine
SQL Server 2005 Upgrade Advisor
4. A Tour of New Features in SQL Server 2005.
Setup Enhancements
Database Administration and Management
Introducing SQL Server Management Studio
SQL Server Configuration Manager
SQLCMD and Dedicated Administrator Connection
DDL Triggers and Event Notifications
Database Mail
Management APIs: SMO, RMO, AMO, and WMI
Database Engine Enhancements
The New xml Data Type
Large Value Data Types
.NET Framework Integration
The New Catalog Views
Scalability, Performance, and Reliability Enhancements
XML Web Services Support
SQL Server 2005 Express and Mobile Editions
T-SQL Enhancements
Recursive and Non-recursive Common Table Expressions (CTEs)
Improved Error Handling
Limiting a Result Set by Using TOP and TABLESAMPLE
Ranking Functions
PIVOT and UNPIVOT
SQL Server 2005 Security
Password Policy Enforcement and Strengthening of SQL Authentication
User-Schema Separation
Encryption Support
Module Execution Context and Impersonation
Performance Monitoring and Tuning Enhancements
Profiler Enhancements
Database Engine Tuning Advisor
Dynamic Management Objects
The Blocked Process Threshold Setting
High-Availability Features
Failover Clustering Changes
Database Mirroring and Snapshot
Online Index Operations
Instant File Initialization
Online, Piecemeal, and Page Restore
Replication
Business Intelligence Redefined
Introducing Business Intelligence Development Studio
Notification Services
Full-Text Search Enhancements
Documentation Improvements
5. SQL Server 2005 Tools and Utilities.
Tools and Utilities Overview
Management and Authoring
SQL Server Management Studio
Business Intelligence Development Studio
Express Manager
Performance Monitoring, Tuning, and Optimization Tools
SQL Server 2005 Profiler
New Performance Monitor Counters
Database Engine Tuning Advisor
Operations and Configuration
SQL Server Configuration Manager
The SQLCMD Utility
Introducing Database Mail
Management APIs
SQL Server Management Objects (SMO)
Windows Management Instrumentation (WMI)
6. Transact-SQL Enhancements.
T-SQL Versus SQLCLR
The TOP Operator
The TABLESAMPLE Clause
Common Table Expressions (CTEs)
Large Object Data Type Enhancements
T-SQL Error Handling
DDL Triggers
Event Notifications
Snapshot Isolation
Ranking Functions
The ROW_NUMBER Ranking Function
The RANK and DENSE_RANK Ranking Functions
The NTILE Ranking Function
New Relational Operators: PIVOT, UNPIVOT, and APPLY
The OUTPUT Clause
The BULK Rowset Provider
New Declarative Referential Integrity Actions
Metadata Views and Dynamic Management Views
Miscellaneous T-SQL Enhancements
The ALTER INDEX Statement
Statement-Level Recompilation
New Server Configuration Options
Synonyms
The EXCEPT and INTERSECT Operators
The SET SHOWPLAN_XML and SET STATISTICS XML Statements
7. SQL Server 2005 Security.
An Introduction to Security in SQL Server 2005
Authentication
Endpoint-Based Authentication
SQL Browser Service and the New Security Model
Strengthening SQL Authentication
Authorization
SQL Server 2005 Security Terminology
Granular Permissions Control
Module Execution Context
User-Schema Separation
Secure Metadata
Auditing Enhancements
Cryptography Support
Data Encryption
Signed Modules
Surface Area Configuration (SAC)
SQL Server Agent Security Overview
SQLCLR Security Overview
Service Broker Security Overview
8. Reliability and High Availability in the Database Engine.
Reliability Enhancements
Mirrored Backup Media
Backup and Restore Media Checks
Checksum I/O Validation
Online, Piecemeal, and Page Restorations
The EMERGENCY Database State Option
The ATTACH_REBUILD_LOG Clause
Availability Enhancements
Database Mirroring
Database Snapshots
Failover Clustering Enhancements
Replication Enhancements
Online and Parallel Index Operations
Enhanced Multi-instance Support
Dedicated Administrator Connection
Early Restore Access
Instant File Initialization
9. Performance Analysis and Tuning.
Physical Database Design, Analysis, and Tuning
Creating Indexes with Included Columns
Creating Indexes on Computed Columns
Indexing XML Data
Optimizing Physical Design Structures by Using Database Engine Tuning Advisor
Troubleshooting Locking and Blocking
Using the Blocked Process Threshold Option
Row Versioning and Snapshot Isolation
Analyzing Deadlocks by Using SQL Profiler
Locking and Blocking Monitoring Tools
Query Performance Tuning
Plan Guides
New Query Hints
New Database Options
Statement-Level Recompilation
Designing Applications for Performance
Service Broker and Asynchronous Messaging
.NET Runtime Integration
T-SQL Enhancements
Multiple Active Result Sets
Query Notifications
HTTP SOAP Support
Performance Monitoring and Tuning Tools
10. XML and Web Services Support in SQL Server 2005.
XML Primer
Well-Formed XML Document
Valid XML Documents
XML Namespaces
Navigating XML by Using Xpath
The xml Data Type
Validating XML Data by Using Typed XML
Bulk Loading XML Data
Introduction to XQuery
XML Type Methods
Indexes on XML Type Columns
Modifying XML Data
FOR XML Enhancements
Native XML Web Services Support
Introduction to SOAP, Web Services, and WSDL
Creating HTTP Endpoints
Administering HTTP SOAP Endpoints
11. SQL Server 2005 and .NET Integration.
What Is the .NET Framework?
What Is the CLR?
.NET Framework Integration
Extending the T-SQL Language
SQLCLR Design Goals
SQLCLR Integration Benefits
SQLCLR Integration Challenges
SQLCLR Integration Examples
Saving XML Data to a File
Base-64 Encoding and Decoding
SQLCLR Metadata
Monitoring SQLCLR Activity
12. SQL Server Integration Services Overview.
New SSIS Features
The SSIS Architecture
SSIS Clients
SSIS Service
The SSIS Runtime Engine
The Data Flow Engine
The SSIS Toolkit
Containers
Tasks
Business Intelligence Development Studio
SQL Server Management Studio
Command-Prompt Utilities
Designing SSIS Packages
The SSIS Object Model
SSIS Expressions
Logging
SSIS Package to Import Flat Files into Tables
The World of Wizards
The SSIS Import/Export Wizard
The SSIS Configuration Wizard
The SSIS Migration Wizard
SSIS and Data Warehousing
SSIS ETL Concepts
Data Source Views
13. What's New in SQL Server Analysis Services 2005.
OLAP, Data Warehousing, Business Intelligence, and Data Mining
OLAP Terminology
OLAP Storage Modes
Analysis Services Fundamentals
Data Explosion and Data Sparsity Handling
Analysis Services 2005 Enhancements
Integrated Tools and the Enhanced User Interface Experience
Profiler Support
Multiple Instance Support
Failover Clustering Support
Unified Dimension Model (UDM) and Proactive Caching
Cube Enhancements
Dimension Enhancements
Data Mining Enhancements
XMLA Support
Analysis Management Objects (AMO)
ADOMD.NET
Performance Tuning for Analysis Services
Usage-Based Optimization (UBO)
Partitioning Enhancements
The Optimize Schema Option
Administering Analysis Services
Implementing UBO
Analysis Services Database Backup
Cube and Mining Model Processing
Analysis Services Security Overview
Granular Permissions
Dimension-Level Security
Role-Based Security
Migrating from SQL Server 2000 Analysis Services to SQL Server 2005 Analysis Services
Using the Migration Wizard
Starting from Scratch
SQL Server 2005 Reporting Services (SSRS) Overview
Reporting Services Components and Tools
Reporting Services 2005 Enhancements
14. SQL Server Service Broker.
An Introduction to Asynchronous Messaging
Asynchronous Messaging in a Database
Understanding Service Broker
Service Broker Terminology
Common Service Broker Tasks
Service Broker Scenarios
Service Broker and Other Messaging Technologies
Service Broker Operations and Troubleshooting
Troubleshooting Tips
Service Broker Security Overview
15. SQL Sever 2005 Support for 64-Bit Processors.
64-Bit Processor Usage Scenarios
Improving Performance of Memory-Constrained Applications
Server Consolidation
High-Performance Data Warehousing Applications
SQL Server 2005 (64-Bit) in Detail
Index.
