Accelerated SQL Server 2008 (eBook)

Michael Coles has more than a decade's experience designing and administering SQL Server databases. A prolific writer of articles on all aspects of SQL Server, particularly on the expert use of T-SQL, he holds MCDBA and MCP certifications. He graduated magna cum laude with a bachelor of science degree in information technology from American Intercontinental University in Georgia. A member of the United States Army Reserve, he was activated for two years following 9/11.

Contents at a Glance 5
Contents 7
About the Authors 20
Acknowledgments 22
Introduction 23
Who This Book Is For 24
Valuable Resources 24
How This Book Is Structured 25
Errata 27
Contacting the Authors 27
Overview of SQL Server 28
SQL Server 2008 Overview 29
The Vision of SQL Server 2008 29
SQL Server 2008 Editions 31
A Word on Server Consolidation 32
Summary 33
SQL Server Installation and Configuration 35
SQL Server Requirements 35
Upgrading to SQL Server 2008 36
Summary 49
Enterprise Data Platform 50
Policy Management 51
Needs Addressed by Policy Management 51
Policy Management Components 52
Sample Policies 62
Policy Management Administration 66
Summary 69
High Availability 70
High Availability Defined 71
Database Mirroring 72
Database Snapshots and Mirroring 94
Windows Clustering in SQL Server 99
SQL Server Replication 99
Reducing Planned Downtime 102
Summary 103
Performance 104
Managing and Monitoring Resources 104
Optimizing Storage 117
Improving Query Performance 124
Summary 131
Security 132
Disabled Database Engine Features 132
Principals and Securables 137
Permissions 147
Code Access Security 151
Auditing in SQL Server 2008 155
Summary 163
SQL Server Encryption 164
Encryption Keys 165
Transparent Data Encryption 179
Extensible Key Management 182
Encryption Without Keys 182
Hashing and Signing Data 183
Security Catalog Views 184
Query Efficiency 185
Summary 186
Automation and Monitoring 187
SQL Server Agent 188
Maintenance Plans 206
SQLCMD 214
PowerShell for SQL Server 217
Database Mail 224
SQL Profiler 230
Extended Events 236
Summary 241
Service Broker 243
What Is Service Broker? 244
Creating Service Broker Applications 247
Service Broker Routing and Security 258
Message Priorities 280
Troubleshooting Service Broker Using SSBDiagnose 283
Summary 284
Integrated Full-Text Search 285
Creating Full-Text Catalogs and Indexes 285
Querying with iFTS 295
Managing Thesaurus Files 300
Using Stoplists 303
Searching Documents 304
Managing iFTS 305
Summary 306
Development in SQL Server 307
New Datatypes in SQL Server 2008 308
Spatial Support in SQL Server 2008 308
How Time Has Changed in SQL Server 314
A New Hierarchical Datatype 320
Filestream Support 325
Summary 335
T-SQL Enhancements for Developers 336
Notable DML Features 336
General Development 371
Summary 387
T-SQL Enhancements for DBAs 388
Locking Enhancements 388
Metadata Views 389
Notable SQL Server Performance Monitor Counters 395
DDL Triggers 395
Indexing and Performance Enhancements 399
Snapshots 416
Data-Integrity Enhancements 421
Summary 422
.NET Integration 423
Introduction to SQL Server .NET Integration 424
SQL Server .NET Programming Model 426
Programming a CLR Stored Procedure 427
Deploying CLR Routines 445
Summary 446
Programming Assemblies 447
CLR User-Defined Types 448
CLR User-Defined Functions 468
CLR User-Defined Aggregates 477
CLR User-Defined Triggers 486
Managing Assemblies 491
Summary 491
SQL Server and XML 492
What Is XML? 492
What Are XPath and the XMLDOM? 493
Getting XML into the Database 498
Getting XML Out of the Database 514
Working with XML Data 522
Programming SQLXML from .NET and COM 525
Summary 532
SQL Server XML and XQuery Support 533
Using the XML Datatype 534
Inserting Data into XML Columns 542
Querying XML Data 545
Modifying XML Data 554
Indexing XML for Performance 555
Full-Text Search and the XML Datatype 558
Catalog Views and XML 559
Applications and XML 559
XML Web Services Support 560
Summary 570
LINQ to SQL 571
Object/Relational Mapping 571
Entity-Generation Tools 573
Running LINQ Queries 582
The DataContext Class 588
Summary 600
Business Intelligence in SQL Server 601
Reporting Services 602
Reporting Services Components 603
Building a Basic Report 616
Report Design Advanced Features 620
Visual Studio Integration and ReportViewer Controls 631
SharePoint Integration 637
End-User Ad Hoc Query and Reporting 638
Report Rendering 640
Data Regions 642
Summary 657
Analysis Services 658
New Analysis Services Features in SQL Server 2008 659
Analysis Services Fundamentals 660
OLAP, OLTP, and Data Warehouses 663
Analysis Services Projects 667
Working with Cubes 685
Using Analysis Services Scripting Language 699
Summary 702
Integration Services 703
An Overview of SSIS Features 703
The SSIS Integrated Development Environment 705
SSIS Fundamentals 707
Control Flow and Data Flow Designer Tasks 726
Change Data Capture 747
Logging 749
Dynamic Package Configuration 753
Variables 756
Precedence Constraints 758
Checkpoints 759
Transactions 759
Debugging 760
SSIS Package Handling 763
Summary 765
Index 766