Contents
Contents at a Glance v
Contents vii
Acknowledgments xv
Introduction xvii
Who Is This Book For? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xvii
How Is This Book Organized? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xvii
Pre-Release Software . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xviii
Support for This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix
We Want to Hear from You . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix
Database Administration 1
C HA PT E R 1 3
SQL Server 2008 R2 Editions and Enhancements 3
SQL Server 2008 R2 Enhancements for DBAs . . . . . . . . . . . . . . . . . . . . . . . . . 3
Application and Multi-Server Administration Enhancements 4
Additional SQL Server 2008 R2 Enhancements for DBAs 8
Advantages of Using Windows Server 2008 R2 . . . . . . . . . . . . . . . . . . . . . .10
SQL Server 2008 R2 Editions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11
Premium Editions 12
Core Editions 12
Specialized Editions 13
Hardware and Software Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Installation, Upgrade, and Migration Strategies . . . . . . . . . . . . . . . . . . . . . . 16
The In-Place Upgrade 16
Side-by-Side Migration 18
C HA PT E R 2 2 1
Multi-Server Administration 21
The SQL Server Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
SQL Server Utility Key Concepts 23
UCP Prerequisites 25
UCP Sizing and Maximum Capacit y Specifications 25
Creating a UCP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .26
Creating a UCP by Using SSMS 26
Creating a UCP by Using Windows PowerShell 28
UCP Post-Installation Steps 29
Enrolling SQL Server Instances . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .29
Managed Instance Enrollment Prerequisites 30
Enrolling SQL Server Instances by Using SSMS 30
Enrolling SQL Server Instances by Using
Windows PowerShell 32
The Managed Instances Dashboard 32
Managing Utility Administration Settings . . . . . . . . . . . . . . . . . . . . . . . . . . .33
Connecting to a UCP 33
The Policy Tab 34
The Security Tab 37
The Data Warehouse Tab 39
C HA PT E R 3 4 1
Data-Tier Applications 41
Introduction to Data-Tier Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .41
The Data-Tier Application Life Cycle 42
Common Uses for Data-Tier Applications 43
Supported SQL Server Objects 44
Visual Studio 2010 and Data-Tier Application Projects . . . . . . . . . . . . . . . . 45
Launching a Data-Tier Application Project Template in Visual Studio 2010 45
Importing an Existing Data-Tier Application Project into Visual Studio 2010 47
Extracting a Data-Tier Application with SQL Server Management Studio 49
Installing a New DAC Instance with the Deploy
Data-Tier Application Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .52
Registering a Data-Tier Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Deleting a Data-Tier Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .56
Upgrading a Data-Tier Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59
C HA PT E R 4 6 3
High Availability and
Virtualization Enhancements 63
Enhancements to High Availability with
Windows Server 2008 R2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .63
Failover Clustering with Windows Server 2008 R2 . . . . . . . . . . . . . . . . . . . .64
Traditional Failover Clustering 65
Guest Failover Clustering 67
Enhancements to the Validate A Configuration W izard 68
The Windows Server 2008 R2 Best Practices Analyzer 71
SQL Server 2008 R2 Virtualization and Hyper-V . . . . . . . . . . . . . . . . . . . . . . 72
Live Migration Support Through CSV 72
Windows Server 2008 R2 Hyper-V System Requirements 73
Practical Uses for Hyper-V and SQL Server 2008 R2 74
Implementing Live Migration for SQL Server 2008 R2 . . . . . . . . . . . . . . . . . 75
Enabling CSV 76
Creating a SQL Server VM with Hyper-V 76
Configuring a SQL Server VM for Live Migration 79
Initiating a Live Migration of a SQL Server VM 83
C HA PT E R 5 8 5
Consolidation and Monitoring 85
SQL Server Consolidation Strategies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .85
Consolidating Databases and Instances 86
Consolidating SQL Server Through Virtualization 87
Using the SQL Server Utility for Consolidation and Monitoring . . . . . . . .89
Using the SQL Server Utility Dashboard . . . . . . . . . . . . . . . . . . . . . . . . . . . . .90
Using the Managed Instances Viewpoint . . . . . . . . . . . . . . . . . . . . . . . . . . .95
The Managed Instances List View Columns 96
The Managed Instances Detail Tabs 97
Using the Data-Tier Application Viewpoint . . . . . . . . . . . . . . . . . . . . . . . . .100
The Data-Tier Application List View 102
The Data-Tier Application Tabs 102
Business Intelligence Development 107
C HA PT E R 6 1 09
Scalable Data Warehousing 109
Parallel Data Warehouse Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . .109
Data Warehouse Appliances 109
Processing Architecture 110
The Multi-Rack System 110
Hub-and-Spoke Architecture 115
Data Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .115
Shared Nothing Architecture 115
Data Types 120
Query Processing 121
Data Load Processing 121
Monitoring and Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .122
Business Intelligence Integration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .123
Integration Services 123
Reporting Services 123
Analysis Services and PowerPivot 123
C HA PT E R 7 1 25
Master Data Services 125
Master Data Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .125
Master Data Challenges 125
Key Features of Master Data Services 126
Master Data Services Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .127
Master Data Ser vices Configuration Manager 128
The Master Data Services Database 128
Master Data Manager 128
Data Stewardship . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .129
Model Objects 129
Master Data Maintenance 131
Business Rules 132
Transaction Logging 134
Integration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .135
Importing Master Data 135
Exporting Master Data 136
Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .137
Versions 137
Security 138
Model Deployment 142
Programmability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .142
The Class Library 142
Master Data Services Web Service 143
Matching Functions 143
C HA PT E R 8 1 45
Complex Event Processing with StreamInsight 145
Complex Event Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
Complex Event Processing Applications 145
StreamInsight Highlights 146
StreamInsight Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .146
Data Structures 147
The CEP Server 147
Deployment Models 149
Application Development . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .150
Event Types 150
Adapters 151
Query Templates 154
Queries 155
Query Template Binding 162
The Query Object 163
The Management Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .163
Diagnostic Views 163
Windows PowerShell Diagnostics 164
C HA PT E R 9 1 6 5
Reporting Services
Enhancements 165
New Data Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .165
Expression Language Improvements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
Combining Data from More Than One Dataset 166
Aggregation 168
Conditional Rendering Expressions 169
Page Numbering 170
Read/Write Report Variable 170
Layout Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .171
Pagination Properties 172
Data Synchronization 173
Text Box Orientation 174
Data Visualization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .175
Data Bars 175
Sparklines 176
Indicators 176
Maps 177
Reusability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .178
Shared Datasets 179
Cache Refresh 179
Report Parts 180
Atom Data Feed 182
Report Builder 3 .0 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .183
Edit Sessions 183
The Report Part Gallery 183
Report Access and Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .184
Report Manager Improvements 184
Report Viewer Improvements 186
Improved Browser Support 186
RDL Sandboxing 186
SharePoint Integration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .187
Improved Installation and Configuration 187
RS Utility Scripting 187
SharePoint Lists as Data Sources 187
SharePoint Unified Logging Service 188
C HA PT E R 10 1 8 9
Self-Service Analysis with PowerPivot 189
PowerPivot for Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .190
The PowerPivot Add-in for Excel 190
Data Sources 191
Data Preparation 193
PowerPivot Reports 196
Data Analysis Expressions 199
PowerPivot for SharePoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .201
Architecture 201
Content Management 204
Data Refresh 205
Linked Documents 205
The PowerPivot Web Service 205
The PowerPivot Management Dashboard . . . . . . . . . . . . . . . . . . . . . . . . . .206
About the Authors 215
