Pro SQL Server 2005 (Pro)

SQL Server 2005 is next generation of SQL Server database and represents a massive leap forward for SQL Server developers in terms of programming options, productivity, database management and analysis. This book will take a deep look at the full range of SQL Server enhancements that will give meaningful and relevant examples that show not just how these new features work, but also how to architect their applications in order to exploit the new features most effectively. This book is all about getting developers working on their own SQL Server 2005 solutions as quickly and painlessly as possible. Some of the core topics covered in the book are: Enhancements to database programmability features and languages, such as ADO.NET, XML, .NET assemblies, and T-SQL; New Servers, services and tools such as Reporting Services, and the Service Broker and DTS; Enhancements to the BI capabilities such as Analysis Services and Notification services. 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

this Book Gets Straight To The Point And Gives You The Desired Insight Into The New Functionality Of Sql Server 2005.

— Frank Kalis, Sql-server-performance.com

i Really Like This Book And It Definitely Has Value, Especially As A Survey Of New Technology.

— Sean Mccown, Itbookworm

purchase This Book And You'll Get The Free, Fully Searchable Ebooka $25 Value! (details Are Printed Inside The Book.)

sql Server 2005 Is A Dramatically Expanded And Enhanced Version Of Sql Server 2000 That Not Only Makes Sql Server A True Enterprise-level Rdbms, But Also Changes How Sql Server Professionals Design Databases And Develop Database Applications.

pro Sql Server 2005 Provides In-depth Coverage Of New Sql Server Features And Enhancements. It Focuses On What Experienced Sql Server Dbas, Developers, And Business Intelligence Practitioners Need To Know To Get Up To Speed With Sql Server 2005 As Quickly As Possible. It Carefully Presents Each New Feature And Precisely Illustrates The Feature With Examples That Show You How To Use It Effectively.

this Book Offers Incisive, Expert Guidance On All Of The Major New Functionality Of Sql Server 2005, Covering Topics That Are Essential For All Sql Server Professionals, Including

• transact-sql Enhancements


• sql Server Management Studio


• clr Integration And Programming Stored Procedures In C#


• sql Server Reporting Services Enhancements


• sql Server Analysis Services Enhancements


• the New Xml Datatype And Xquery Support


• security Enhancements And Service Broker


• sql Server Integration Services For Data Extraction, Transformation,
  and Loading
  

the Authors Provide Practical, In-depth Coverage Of The Core Topics That They Specialize Inall Supported By Realistic Examples, So You'll Be Able To Design And Implement Your Own Sql Server 2005 Solutions Quickly And Painlessly. This Book Is A Roadmap For Exploring And Exploiting Sql Server 2005. So Fasten Your Seatbelts, Start Your Computers, And Race To Mastery Of Sql Server 2005!

SQL Server 2005 is a dramatically expanded and enhanced version of SQL Server 2000 that not only makes SQL Server a true enterprise-level RDBMS, but also changes how SQL Server professionals design databases and develop database applications. Pro SQL Server 2005 provides in-depth coverage of new SQL Server features and enhancements. It focuses on what experienced SQL Server database administrators, developers, and business intelligence practitioners need to know to get up to speed with SQL Server 2005 as quickly as possible. It carefully presents each new feature and precisely illustrates the feature with examples that show you how to use it effectively. The authors provide practical, in-depth coverage of the core topics that they specialize inall supported by realistic examples, so you’ll be able to design and implement your own SQL Server 2005 solutions quickly and painlessly. This book is a roadmap for exploring and exploiting SQL Server 2005. So fasten your seatbelts, start your computers, and race to mastery of SQL Server 2005! • First book to provide comprehensive, deeply practical coverage on all major new SS2005 features. • Lead author (Tom Rizzo) is a key member of the SQL Server team at Microsoft and a respected author and blogger. • Gets the DBA and developer up and running with SS2005 in the fastest possible time. • The facts, not the evangelism.