1 / 22

SQL Server Yukon

SQL Server Yukon. James Hamilton General Manager & Security Architect Microsoft SQL Server Business Unit JamesRH@microsoft.com | http://research.microsoft.com/~jamesrh. SQL Server Yukon The scalable, secure, enterprise-class data management platform. AGENDA Enterprise Data Management

dezso
Download Presentation

SQL Server Yukon

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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.

More Related