SQL Server 2005 Overview

2. SQL Server 2005 Overview Lucas Wensing lucasw@microsoft.com

3. SQL Server RoadmapHistory of Innovation 1st Generation 2nd Generation 3rd Generation SQL Server 6.0/6.5 SQL Server 7.0 SQL Server 2000 SQL Server 2005 • Differentiation from Sybase SQL Server • Replication Re-architecture • First to integrate Replication • Windows integration • Re-architecture of relational server • Extensive auto resource management • First to integrate OLAP & ETL • Deeper Windows integration • Windows 2000 Availability • First integrated XML • First integrated Data Mining Engine • First integrated Notification • Scalable Analysis • SQL Server 64-bit • SQL Server CE • Database Mirroring, & VLDB • Multi-language Programmability & Extensibility • Native XML support • First to integrate Reporting,Enterprise ETL & Deep Data Mining • Performance & Scalability • Integrated Business Intelligence • Lowest TCO • Automatic Tuning Cross-release objectives

4. Reporting Services Enterprise Reporting Management Tools Development Tools Analysis Services OLAP & Data Mining Notification Services Notifications & Alerts Replication Services Data Replication Data Transformation Services ETL SQL Server Engine Relational Database Engine The SQL Server Stack

5. Scalability, availability, security advancements • Database mirroring, online operations for increased availability • Scales as the business grows: partitioning, snapshots • Secure by default, by design, by deployment • Share data across any platform • Native support for XML, Web Services • Interoperability with any platform, application • Industry standards: HTTP, SOAP, WSDL, XQuery, XSD • Focus on manageability • Integrated management toolset for relational and OLAP • New management programming model • Expanded self tuning

6. Leverage existing development skills • Use familiar tools/processes (VS, SourceSafe, etc) • Multiple languages: T-SQL, VB.NET, C#… • One development framework for relational, XML, OLAP Provide end-to-end development tools • Integrated tools: SQL Server “Studio” • Simplified and consistent source control environment • Integrated in-line debugging • Extensibility • Native XML data type, user defined data types • Enhanced data access with ADO.NET v2 • New distributed application framework: SQL Server Broker

7. Scalable, comprehensive solution • Extensive data modeling capabilities • New data mining algorithms for advanced analytics • Integrated with Visual Studio for rapid BI development • Real-time decision making tools • New extensible reporting platform • Scalable and reliable for 24*7 support • Integration with operational systems • Extend information to employees, partners • Supports heterogeneous data sources • Enterprise-class ETL (DTS) • Native XML Web Services

8. .NET Framework • Common Language Runtime Integration • User-defined Aggregates • User-defined Data Types • User-defined Functions • SQL Server .NET Data Provider • Extended Triggers Data Types • Managed SQL Types • New XML Datatype • Varchar (MAX) Varbinary (MAX) SQL Server Engine • New Message Service Broker • HTTP Support (Native HTTP) • Database Tuning Advisor • Multiple Active Result Sets • Persisted Computed Columns • Snapshot Isolation Level • Scale Up Partitioning • VIA support • NUMA support Database Failure and Redundancy • Fail-over Clustering (up to 8 node) • Enhanced Multi-instance Support • Database Mirroring • Database Snapshots XML • XQUERY Support • XML Data Manipulation Language • FOR XML Enhancements • XML Schema (XSD) Support • MSXML 6.0 (Native) • .Net XML Framework Full-text Search • Indexing of XML Datatype Database Maintenance • Backup and Restore Enhancements • Checksum Integrity Checks • Dedicated Administrator Connection • Dynamic AWE • Fast Recovery • Highly-available Upgrade • Online Index Operations • Online Restore • Parallel DBCC • Parallel Index Operations Management Tools • New Management Studio • MDX Query Editor • Version Control Support • XML/A • SQLCMD Command Line Tool Performance Tuning • Profiler Enhancements • Profiling Analysis Services • Exportable Showplan • Exportable Deadlock Traces MDAC • SNAC • Microsoft Installer base setup • Support for Active Directory Deployment SQL Client .NET Data Provider • Server Cursor Support • Asynch Security • All Permissions Grantable • Fine Grain Administration Rights • Separation of Users and Schema • Data encryption primitives Replication • Auto-tuning Replication Agents • Oracle Publication • Improved Blob Change Tracking • Replication Monitor OLAP and Data Mining • Analysis Management Objects • Windows Integrated Backup and Restore • Web Service/XML for Analysis • DTS and DM Integration • New Data Mining Algorithms • Auto Packaging and Deployment Data Transformation Services • New Architecture (DTR + DTP) • Complex Control Flows • Control Flow Debugging • For Each Enumerations • Property Mappings • Full Data Flow Designer • Full DTS Control Flow Designer • Graphical Presentation of Pkg Execution • Immediate Mode and Project Mode • Package (Advanced) Deployment Tools • Custom Tasks and Transformations Reporting Services • Multiple Output Formats • Parameters (Static, Dynamic, Hierarchical) • Bulk Delivery of Personalized Content • Support Multiple Data Sources • STS (Web Parts, Doc Libraries) • Visual Design Tool • Charting, Sorting, Filtering, Drill-Through • Scheduling, Caching • Complete Scripting Engine • Scale Out architecture • Open XML Report Definition Notification Services & SQL Server CE

9. Big Bet Availability Features • Huge number of new availability features • Database Mirroring • Database Snapshots • Online database restore • File & Page • Dedicated Admin Connection • Online Index Build and Reorganize • Snapshot Isolation

10. Data Partitioning • Partitioning breaks a single object into multiple manageable pieces • Transparent to the application • Allows easy management of very large tables and indexes • The row is the unit of partitioning • All partitions run on a single SQL Server database • Partitions can be created or dropped with virtually no loss of availability to the table • Table fully available while loading, indexing a new partition • Create new and drop old partition quickly • Fastest possible load rates • If all indexes are “aligned”, moving partitions in or out of a table is possible • “Sliding window” scenarios

11. XML Datatype • Native SQL type • Use for column, variable or parameter • Can represent • XML 1.0 documents • XML 1.0 fragments (0 to n element nodes and text nodes at top) • Can be constrained by XML Schema collection • Methods on XML data type • query(), value(), exist(), modify(), nodes() • Storage of XML instances • As LOB (2 GB) in efficient binary representation • XML encoding transformed to UTF-16 • Well-formed and validation checks

12. XML Schema Support • XML Schema (World Wide Web Consortium [W3C] standard) • Rich mechanism for type definitions and validation constraints • Can be used to constrain XML documents • Benefits of typed data • Guarantees shape of data • Allows storage and query optimizations • XML type system • Store XML schemas in system metadata • Does not preserve annotations

13. XML Query • XQuery: query XML documents and data • Standards-based: W3C working draft • In document 123, return section heading of section 3 and later SELECT id, xDoc.query(' for $s in /doc[@id = 123]//sec[@num >= 3] return <topic>{data($s/heading)}</topic> ') FROM docs

14. XML Index • Create primary XML index on XML column CREATE PRIMARY XML INDEX idx_1 ON docs (xDoc) • Creates secondary XML indexes on values (VALUE), paths (PATH), properties (PROPERTY) CREATE XML INDEX idx_1_Tag ON docs (xDoc) USING XML INDEX idx_1 FOR PATH • Speeds up queries • Results can be served directly from index • Entire query is optimized • Same award winning cost based optimizer • Indexes are used as available

15. XML Data Modification • Insert, update, & delete XQuery extensions • XML sub-tree modification: • Add or delete XML sub-trees • Update values • Add a new section after section 1: UPDATE docs SET xDoc.modify('insert<section num=''2''> <heading>Background</heading> </section>after (/doc/section[@num=1])[1]')

16. Client XML Data Access • Native Access • SQLOLEDB • As DBTYPE_WSTR • Large string • As ISequentialStream • SAX or DOM can be built from server character stream • Down Level, ADO and ODBC: large string • Managed Access & CLR (XML datatype) • XML Type exposed as SqlXml (System.Data.SqlTypes) • XmlReader can be obtained using sqlxml.CreateReader()

17. XML Integration

18. Hosting layer provides coordination of: Assembly Loading Memory management Security Model Reliability Threads & Fibers Deadlock detection Execution context Allows System Extension Scalar Functions Table Value Functions Data Types Aggregates Procedures Data Access Triggers SQL Engine CLR Hosting Layer SQL OS Windows CLR Integration

19. .NET Integration • Choice of where to run logic • Database, for logic that runs close to data • Mid-tier, for logic that scales out • Symmetric programming model • Choice of programming language • T-SQL, for data-intensive stored procedures • CLR languages, for a safe, modern execution environment and for extending the engine

20. .NET Integration • Provides a programming environment for developing: • Functions • Procedures • Triggers • User Defined Types • Aggregates • In-Process Data Access provider (ADO .NET v2) • Symmetric data access • Mid-tier/data tier deployment decision • Security • Integration of SQL and CLR security • Three levels of code access security • Safe, External-Access (verifiable), Unsafe • Tight integration with Visual Studio • Authoring, debugging, profiling

21. Visual Studio Integration • Visual Studio supports a new SQL Server 2005 Project system • Easy to develop, debug and deploy .NET code for the database • Integrated debugging experience across mid-tier and database tier • Seamlessly step cross-language – TSQL and .NET • Set breakpoints anywhere, inspect anything • Customer benefit : Increased Productivity

22. .NET Integration

23. SOA Features • SQL Server 2005 implements Service Oriented Architecture (SOA) and Scale Out Services • Service Broker – Service Based, decoupled, asynchronous, distributed environment • Query Notifications – Develop responsive multi-tier data backed caches • Web Services access to SQL Server (HTTP)

24. Service Broker • Asynchronous, Distributed, Decoupled programming environment • Fully integrated into the database engine • Programming Model - DDL and DML • Integrated Management and Deployment • Dependent SQL Server 2005 features – Events, Query Notifications • Customer Usage, benefits: Enables new scenarios - async SPs, Scalable distributed multi-db applications

25. Transaction Persisted Dialog Transaction Synchronous & Asynchronous Processing Client App Launched Stored Proc • Stored proc runs on: • Different transaction • Different thread • Different security • Different time

26. Asynchronous Programming

27. Query Notifications • Ability to subscribe to results of a SQL query • Integrated change detection notifies client as soon as results of query changed • Effectively off-loading query workload from the server to caching clients • “Don’t call us – we call you!”

28. Business Scenario – Web Caching Query/ Subscription Request Query Processor Response Result Service Broker Request Notification Response Dispatcher SQL Server 2005 IIS / ASP.Net

29. SQL Web Services HTTP.SYS • Native SOAP access • Standards based access to SQL Server • No client dependency • Improved Interoperability • New “HTTP ENDPOINT” object • Configure connection info • Configure authentication • Expose Functions & SPs • Expose TSQL Batches • Leverages kernel-mode listener http://myserver/sql Port: 80 Integrated Auth WebMethod = myDb.dbo.mySP HTTP Endpoint

30. HTTP Endpoints SQL Server Management

31. SummarySQL Server 2005 an Integrated Data Platform • Extensions & Semi-Structured Data • CLR & XML • Scale out services • Service Broker, Web Services, Query Notifications • End to end development • Tight integration with Visual Studio 2005 • and much more…