Pro SQL Server 2005 (Pro)

CONTENTS......Page 5
ABOUT THE AUTHORS......Page 17
ABOUT THE TECHNICAL REVIEWERS......Page 19
ACKNOWLEDGMENTS......Page 21
INTRODUCTION......Page 22
Evolution of SQL Server......Page 27
Editions......Page 29
Features......Page 30
Minimum System Requirements......Page 34
Installation Process......Page 36
Server Registration......Page 45
Sample Databases......Page 48
Summary......Page 49
Connecting to and Managing Your SQL Servers......Page 50
Context-Sensitive Menus for Each Object Type......Page 51
Mobile Database Support......Page 52
User Interface Enhancements......Page 53
Nonmodal and Resizable Dialog Boxes......Page 54
Script and Schedule Enhancements......Page 55
Code Authoring......Page 56
Results Pane......Page 58
Summary Views......Page 59
Dedicated Administrator Connection......Page 60
Deadlock Visualization......Page 61
Performance Monitor Correlation......Page 62
Maintenance Plan Designer......Page 63
Database Mail......Page 64
Catalog Views and Dynamic Management Views......Page 66
Default Server Trace......Page 68
SQL Configuration Manager......Page 69
Surface Area Configurator......Page 70
Enhanced Help and Community Integration......Page 72
SQLCMD......Page 73
Server Management Objects......Page 74
Summary......Page 77
Enhancements Affecting DML......Page 78
Old-Style Outer Joins Deprecated......Page 79
Common Table Expressions......Page 80
TOP......Page 88
Extensions to the FROM Clause......Page 91
OUTPUT......Page 101
Ranking Functions......Page 102
EXCEPT and INTERSECT......Page 108
Synonyms......Page 110
General Development......Page 111
Error Handling......Page 112
.WRITE Extension to the UPDATE Statement......Page 119
Code Security Context......Page 121
.NET Declarations......Page 124
Summary......Page 126
Metadata Views......Page 127
Catalog Views......Page 128
Dynamic Management Views and Functions......Page 130
DDL Triggers......Page 131
Creating and Altering DDL Triggers......Page 132
Enumerating DDL Triggers Using Catalog Views......Page 133
Programming DDL Triggers with the eventdata() Function......Page 134
Online Indexing......Page 136
Creating Indexes with Additional Columns Included......Page 137
Altering Indexes......Page 138
Using Statistics for Correlated DATETIME Columns......Page 141
Improving Performance of Ordering for Tertiary Collations......Page 142
Table and Index Partitioning......Page 143
Partition Schemes......Page 144
Creating Partitioned Tables and Indexes......Page 145
Adding and Removing Partitions......Page 146
Modifying Partition Functions and Schemes......Page 147
Switching Tables into and out of Partitions......Page 148
Enhancements to Indexed Views......Page 149
Persisted Computed Columns......Page 150
SNAPSHOT Isolation Level......Page 151
Database Snapshots......Page 154
Putting a Database into an Emergency State......Page 156
Summary......Page 157
Introduction to SQL Server .NET Integration......Page 158
When Not to Use CLR Routines......Page 159
SQL Server .NET Programming Model......Page 160
Overview of the New .NET Namespaces for SQL Server......Page 161
Programming a CLR Stored Procedure......Page 162
Starting a Visual Studio 2005 SQL Server Project......Page 163
Anatomy of a Stored Procedure......Page 166
Defining the Problem......Page 167
Using the SqlPipe......Page 169
Putting It All Together: Coding the Body of the Stored Procedure......Page 171
Testing the Stored Procedure......Page 174
Debugging the Procedure......Page 175
Throwing Exceptions in CLR Routines......Page 176
Deploying CLR Routines......Page 180
Summary......Page 182
CLR User-Defined Types......Page 183
Adding a User-Defined Type to a SQL Server Project......Page 184
Parts of a User-Defined Type......Page 186
A Simple Example: The PhoneNumber Type......Page 189
Another Example: The StringArray Type......Page 197
Managing User-Defined Types......Page 204
CLR User-Defined Functions......Page 205
The SqlFunction Attribute......Page 206
Scalar User-Defined Functions......Page 207
Table-Valued User-Defined Functions......Page 210
Managing CLR User-Defined Functions......Page 214
Adding a User-Defined Aggregate to a SQL Server Project......Page 215
Parts of a User-Defined Aggregate......Page 217
Adding a CLR User-Defined Trigger to a SQL Server Project......Page 222
Programming CLR Triggers......Page 223
Managing Assemblies......Page 227
Summary......Page 228
What Is XML?......Page 229
What Are XPath and the XMLDOM?......Page 230
XPath Syntax......Page 232
The XMLDOM–XML Document Object Model......Page 233
The XPathDocument, XPathNavigator, and XPathExpression Classes......Page 234
Getting XML into the Database......Page 235
Configuring SQL Server......Page 236
OPENXML......Page 237
SQLXML: XML Views Using Annotated XML Schemas......Page 242
SQLXML Updategrams......Page 248
XML BulkLoad......Page 250
FOR XML (Server-Side)......Page 252
Using Templates......Page 258
Enhancements to FOR XML......Page 259
SqlXmlCommand......Page 260
SqlXmlParameter......Page 261
Code Samples......Page 262
Using an XMLTextReader......Page 264
Using Parameters with SQLXML......Page 265
Interoperating with the ADO.NET Dataset......Page 266
Summary......Page 267
CHAPTER 8 SQL Server 2005 XML and XQuery Support......Page 268
Using the XML Datatype......Page 269
Understanding How XML Is Stored by SQL Server......Page 270
Creating XML Columns......Page 271
Setting Permissions for Schema Creation......Page 275
Constraining XML Columns......Page 276
Examining the XML Datatype Limitations......Page 277
Using SSIS with XML Data......Page 278
Writing a Custom Query or Application......Page 280
XQuery 101......Page 281
Basic XML Query Methods......Page 287
Cross-Domain Queries......Page 289
Modifying XML Data......Page 290
Indexing XML for Performance......Page 291
Understanding How XML Indexing Works......Page 292
Examining Secondary XML Indexes......Page 293
Full-Text Search and the XML Datatype......Page 294
Applications and XML......Page 295
XML Web Services Support......Page 296
Creating an Endpoint......Page 297
Using Advanced Web Services......Page 301
Monitoring Performance of XML Web Services......Page 306
Summary......Page 307
Reporting Services Components......Page 308
Report Designer......Page 310
Report Manager Web Application......Page 312
Reporting Services Security......Page 313
Working with Data Sources and Datasets......Page 314
Working with Expressions......Page 315
Upgrading from SQL Server 2000 Reporting Services......Page 316
Licensing Changes for Reporting Services......Page 317
SQL Server Management Studio Integration......Page 318
Walkthrough: Management Studio and Reporting Services......Page 319
Management Changes......Page 325
WMI Provider......Page 326
Reporting Services Configuration Tool......Page 328
Report Design and Execution Improvements......Page 329
Expression Editor......Page 330
Multivalued Parameters......Page 331
DatePicker for Date Values......Page 333
Interactive Sorting......Page 334
Analysis Services Integration......Page 335
Walkthrough: Building a Report in BIDS......Page 336
Floating Headers......Page 343
Data Source Changes: Expressions, XML/Web Services, SSIS, and SAP......Page 344
Custom Report Items......Page 349
Using WinForm Controls......Page 350
Working with the ReportViewer Controls Programmatically......Page 353
LocalReport and ServerReport Objects......Page 357
End-User Ad Hoc Query and Reporting......Page 358
The Semantic Model Definition Language......Page 359
Walkthrough: Report Builder......Page 360
Summary......Page 367
SSAS 2005 Enhancements......Page 368
Architecture......Page 369
Usability......Page 370
Development......Page 371
Installation......Page 372
OLAP, OLTP, and Data Warehouses......Page 373
Cubes......Page 374
Hierarchies......Page 375
Analysis Services Projects......Page 376
Defining Data Sources......Page 377
Designers vs. Wizards......Page 380
Defining Data Source Views......Page 381
Defining Cubes......Page 384
Deploying Projects and Configuring Projects for Deployment......Page 388
Cube Operations......Page 390
Browsing Cubes......Page 391
Browsing Cubes with Hierarchies......Page 393
Managing Displayed Data......Page 395
Calculations and MDX......Page 397
Key Performance Indicators (KPIs)......Page 400
Example ASSL Object: A Data Source View with a Named Query......Page 403
ASSL Drilldown......Page 405
Summary......Page 406
Surface Area Configuration......Page 407
.NET Framework......Page 408
Service Broker......Page 409
OLE Automation XPs......Page 410
Principals......Page 411
Securables......Page 418
Types of Permission......Page 421
Managing Permissions......Page 423
Code Access Security......Page 425
Using CAS with SQL Server......Page 426
Encryption......Page 430
Encryption with a User-supplied Password......Page 431
Encryption with a Symmetric Key......Page 432
Asymmetric Key Encryption......Page 433
Encryption with a Certificate......Page 434
Certificates and Web Services......Page 435
Summary......Page 436
What Is Service Broker?......Page 438
Service Broker Architecture......Page 439
Service Broker Scenarios......Page 441
Enabling Service Broker......Page 442
Creating Queues......Page 443
Creating Service Broker Stored Procedures......Page 444
A Simple Service Broker Example......Page 447
Creating Distributed Service Broker Applications......Page 453
Distributed Service Broker Example......Page 456
Summary......Page 476
SQL Server Agent......Page 477
Step 1: Connect to SQL Server......Page 478
Step 2: Create the Agent Job......Page 479
Security Enhancements......Page 483
Agent Subsytems......Page 491
Sharing Job Schedules......Page 492
WMI Events and Agent Alerts......Page 494
Agent Performance Counters......Page 496
Agent Upgrade......Page 497
Maintenance Plans......Page 498
Creating a Maintenance Plan......Page 499
Toolbox......Page 500
Maintenance Plan Designer Document Window......Page 501
Connecting to SQL Server......Page 506
Passing Variables......Page 507
Creating Scripts......Page 508
Database Mail......Page 509
Overview......Page 510
Configuring Database Mail......Page 511
Sending Mail......Page 515
SQL Profiler......Page 516
Performance Monitor Correlation......Page 519
ShowPlan......Page 520
Deadlock Visualization......Page 522
Summary......Page 523
CHAPTER 14 Integration Services......Page 524
What's New in SSIS?......Page 525
Connecting to SSIS in Management Studio......Page 526
SSIS Fundamentals......Page 528
Control Flow Design Surface......Page 529
Data Flow Design Surface......Page 531
Event Handlers Design Surface......Page 543
Package Explorer......Page 545
Properties Window......Page 546
Control Flow Toolbox Tasks......Page 547
Containers......Page 548
Data Flow Task......Page 552
Execute SQL Task......Page 553
File System Task......Page 554
Message Queue Task......Page 555
Scripting Tasks......Page 556
Data Flow Designer Tasks......Page 559
Destination Adapters......Page 560
Transformations......Page 561
Logging......Page 567
Using the Package Configuration Organizer......Page 571
Variables......Page 573
Checkpoints......Page 576
Control Flow Visual Debugging......Page 577
Breakpoints Window......Page 579
The SSIS Package Deployment Utility......Page 580
Scheduling an SSIS Package......Page 581
Summary......Page 582
CHAPTER 15 Database Mirroring......Page 583
High Availability Defined......Page 584
Database Mirroring Overview......Page 585
Database Mirroring in Context......Page 587
Setting Up Database Mirroring......Page 588
Prerequisites, Connectivity, and Security......Page 589
Back Up and Restore the Principal Database......Page 592
Changing Transaction Safety Levels......Page 593
Database Mirroring States, Heartbeats, and Quorums......Page 594
Suspending and Resuming Mirroring......Page 595
Service Broker and Database Mirroring......Page 596
Setting Up Mirroring Using Management Studio......Page 597
Client Applications and Database Mirroring......Page 603
Catalog Views......Page 604
Performance Monitor Counters......Page 608
Windows Event Log and SQL Server Error Log......Page 609
Limitations of Database Mirroring......Page 610
Sample Application Walk- Through......Page 611
Database Snapshots Overview......Page 617
Working with Snapshots in T-SQL......Page 619
Using, Monitoring, and Managing Database Snapshots......Page 620
Limitations of Database Snapshots......Page 621
Windows Clustering in SQL Server 2005......Page 622
Summary......Page 623
CHAPTER 16 Notification Services......Page 624
Notification Services Architecture......Page 625
Events......Page 626
Building a Notification Services Application......Page 627
Defining an NS Instance: The Instance Configuration File......Page 628
Defining the NS Application: The Application Definition File......Page 632
Compiling and Running Your NS Application......Page 648
Monitoring and Troubleshooting Your NS Application......Page 651
Programming NS from Visual Studio......Page 654
Managing NS Programmatically......Page 657
Summary......Page 658
A......Page 659
C......Page 661
D......Page 664
E......Page 666
F......Page 667
H......Page 668
I......Page 669
M......Page 670
N......Page 672
P......Page 673
R......Page 675
S......Page 677
T......Page 684
U......Page 685
V......Page 686
X......Page 687
Z......Page 688