SQL Server Yukon

1. SQL Server Yukon James Hamilton General Manager & Security Architect Microsoft SQL Server Business Unit JamesRH@microsoft.com | http://research.microsoft.com/~jamesrh

2. SQL Server YukonThe scalable, secure, enterprise-class data management platform AGENDA • Enterprise Data Management • Scalability, Performance, & 64 bit • Online operations & enhanced availability • Programmability • Industry leading server-side programming model • Native XML datatype support • Web services • Service Broker • Business Intelligence • OLAP Server • Data mining • Reporting Server • Data Transformation Services Enterprise Data Management Programmability Business Intelligence

3. Economic Cost Savings Scalability Manageability • Optimized for Windows Server 2003 & 64 bit H/W • Great performance • Large memory addressability (up to 32 TB) • Nearly unlimited virtual memory (up to 8 TB) • I/O savings due to larger memory buffer pools • T-SQL code-compatibility with SQL Server 2000 • 8 node shared disk fail-over support • Same on-disk format as 32-bit for easy migration • One setup for database & OLAP based on Windows Installer technology • Compelling alternative to expensive Unix solutions The highly scalable database platform for memory intensive, performance-critical business applications

4. Dynamic Online Configuration • Most DB configuration is Dynamic • No longer requires a server restart • Only I/O Affinity remains static • Memory added W/O restart or reboot • Eliminates one cause of planned failover • Requires: Appropriate H/W & Windows Server 2003 • Address Windowing Extensions (AWE) • Changes to physical size don’t require downtime • Dynamically configurable (Min / Max) • Maximum limited by physical memory • Dynamically adjusts to “hot-add” memory • Requires Windows Server 2003

5. Snapshot Isolation • New transaction isolation level • Existing isolation levels unchanged • Serializable, repeatable read, read committed, & read uncommitted • Increased Data Availability for read dominated applications • Non-Blocking consistent reads in OLTP environment • Writers don’t block readers • Readers don’t block writers • Allows writes, which can cause conflicts • Includes mandatory conflict detection

6. Online Index Operations • Online index operations now allow concurrent modification of the underlying table or index • Updates, Inserts, Deletes • Online Index Maintenance • Create, Rebuild, Drop • Reorganize (including BLOBs) • Index-based constraints (PK, Unique) • Simple DDL for both Online/Offline operations • Updates incur some additional cost during an online index operation • Maintains old & new indexes during maintenance

7. Fast RecoveryRestart or Restore • SQL Server 2000 • Database is Available after Undo Completes • Yukon • Database is Available when Undo Begins Redo Undo Available Time Redo Undo Available

8. Database View • Read-only, consistent copy of a database • Good for reporting state of DB at single point in time • Supports fast primary DB rollback to view • Correction of DBA or user/programmer error • Unchanging as database is modified • Fast & efficient • Copy-on-Write • Storage only required for DB changes • Can be created for any database

9. Database Mirroring • Database Failover – an Instant Standby • Very Fast … expect well under 8 seconds • Automatic or manual failover • Zero data loss • Automatic re-sync after failover • Hardware • Works with standard computers and storage • No shared storage components • Impact to transaction throughput • Zero to minimal • Automatic, transparent client redirect • Use ViewPoint to make mirror database a readable reporting database

10. SQL Server YukonThe scalable, secure, enterprise-class data management platform • Server-side programming model • Multiple languages: T-SQL, VB.NET, C#… • Existing tools/processes • Leverage existing development skills • T-SQL enhancements • Full text search • Relational-to-XML mapping • XML native datatype • SQL Web Services • SQL Service Broker Enterprise Data Management Programmability Business Intelligence

11. .NET Framework IntegrationKey Features • Server side programming: • VB.net, C++.net, & C# • Programming environment for: • Functions, Procedures, Triggers • User Defined Types, Aggregates • In-Proc Data Access (ADO .NET V2) • Symmetric data access • Mid-tier/data tier deployment decision • Security • Three levels of code access security • Safe, External-Access (verifiable), Unsafe • Tight integration with Visual Studio • Authoring, debugging, deployment, profiling • Tool support includes T-SQL

12. VB,C#,C++ Build Assembly: “TaxLib.dll” SQL Data Definition:create assembly … create function … create procedure … create trigger … create type … SQL Server The Developer Experience VS .NET Project Runtime hosted by SQL (in-proc) SQL Queries: select sum(tax(sal,state)) from Emp where county = ‘King’

13. .NET IntegrationKey Theme: Choice & Control • Choice of where to run logic • Database, for logic that runs close to data • Mid-tier, for logic that scales out • Symmetric programming model • Leverage skills mid-tier & server • Choice of programming language • C#, VB.NET, & Managed C++, for a safe, modern execution environment • TSQL continues to be supported & enhanced • Good choice for data-intensive procedures • Safe extended stored proc replacement

14. T-SQL Enhancements • Varchar(max), Varbinary(max) • Alternative to Text, Image • Uniform Programming model • T-SQL Exception Handling • Recursive Queries • Multiple Active Result Sets • Statement-level recompile • Fewer recompiles & less costly • Other • Pivot, UnPivot, Top (expression) • DDL triggers • Queuing primitives

15. Full-text Enhancements • Performance & Scalability • Index and Query Performance • Early testing to over 2B rows • Index cost scales near linearly with data size • Administrative integration • Transportability via database attach/detach • Integrated backup, restore & recovery • Full-Text DDL for easier administration • Functionality • Thesaurus • Diacritic sensitivity/insensitivity • Multi-column full-text queries supported • CONTAINS((col1,col2), ‘Yukon’) • Support for XML data type

16. XML Scenarios1) Data Exchange… XML data Manufacturer Supplier • Platform independent transport format • Loosely-coupled systems • B2B, B2C, work flow, … • Solution: • For XML: Return SQL Query results as XML • Annotated views: Define XML view over relational data

17. XML Scenarios2) Semi-structured storage… • XML Datatype • Loosely structured data or dynamic schema • Solution: • Native XML datatype • Column, variable, or paramater with optional schema (XSD) • Query(): Include xquery over XML as part of SQL Statements • Fully integrated supporting correlations etc. • OpenXML: XML content available via rowset

18. SQL Web Services HTTP.SYS • Native SOAP access • Standards based access to SQL Server • SOAP 1.1 and 1.2, WSDL 1.1, inlined XSD • 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

19. Service Broker SQL Engine SQL Engine • Framework for building Reliable, asynchronous, distributed applications • Communication infrastructure • Two-way “Dialogs” • Reliable delivery to local, remote queues • Exactly-once, In-order semantics • Large Message Fragmentation APP 1 APP 2

20. SQL Server YukonThe scalable, secure, enterprise-class data management platform • Online Analytical Processing Server • UDM integrating relational & MD • MOLAP & ROLAP with caching MOLAP • OLE/DB for OLAP & XML/A APIs • Data Mining Support • Integrated into OLAP server • Algorithms supported: Association rules, time series, regression trees, sequence clustering, neural nets, Naïve Bayes • Reporting Server • Sources: OLE/DB, ODBC, Oracle, Custom, … • Delivery Targets: Email, file share, custom, … • Formats: HTML, Excel, PDF, Custom, … • Data Transformation Services • Enterprise ETL on commodity hardware Enterprise Data Management Programmability Business Intelligence

21. Summary • Substantial release with 3 major themes: • Enterprise data management • Programmability • Business intelligence • Continue trademark focus on administrative ease of use • This focus has been picked up across industry • Yukon begins the next DB industry wave • Ease of database development with great tools • More datatypes under management • More value from data under management

22. © 2003 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.