SQL Server Yukon James Hamilton General Manager & Security Architect Microsoft SQL Server Business Unit [email protected] | http://research.microsoft.com/~jamesrh.
Download
Report
Transcript SQL Server Yukon James Hamilton General Manager & Security Architect Microsoft SQL Server Business Unit [email protected] | http://research.microsoft.com/~jamesrh.
SQL Server Yukon
James Hamilton
General Manager & Security Architect
Microsoft SQL Server Business Unit
[email protected] | http://research.microsoft.com/~jamesrh
SQL Server Yukon
The scalable, secure, enterprise-class data management platform
AGENDA
Enterprise Data
Management
Enterprise Data Management
Programmability
Programmability
Business
Intelligence
Scalability, Performance, & 64 bit
Online operations & enhanced
availability
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
2
Economic
Scalability
Optimized for Windows Server 2003 & 64 bit H/W
Great performance
Manageability
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
Cost Savings
Large memory addressability (up to 32 TB)
Nearly unlimited virtual memory (up to 8 TB)
I/O savings due to larger memory buffer pools
The highly scalable database platform for memory
intensive, performance-critical business applications
3
Dynamic Online Configuration
Most DB configuration is Dynamic
Memory added W/O restart or reboot
No longer requires a server restart
Only I/O Affinity remains static
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
4
Snapshot Isolation
New transaction isolation level
Increased Data Availability for read
dominated applications
Existing isolation levels unchanged
Serializable, repeatable read, read
committed, & read uncommitted
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
5
Online Index Operations
Online index operations now allow concurrent
modification of the underlying table or index
Online Index Maintenance
Updates, Inserts, Deletes
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
6
Fast Recovery
Restart or Restore
SQL Server 2000
Database is Available after Undo Completes
Redo
Undo
Available
Time
Yukon
Database is Available when Undo Begins
Redo
Undo
Available
7
Database View
Read-only, consistent copy of a database
Unchanging as database is modified
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
Fast & efficient
Copy-on-Write
Storage only required for DB changes
Can be created for any database
8
Database Mirroring
Database Failover – an Instant Standby
Hardware
Works with standard computers and storage
No shared storage components
Impact to transaction throughput
Very Fast … expect well under 8 seconds
Automatic or manual failover
Zero data loss
Automatic re-sync after failover
Zero to minimal
Automatic, transparent client redirect
Use ViewPoint to make mirror database a readable
reporting database
9
SQL Server Yukon
The scalable, secure, enterprise-class data management platform
Enterprise Data
Management
• Server-side programming model
• Multiple languages: T-SQL, VB.NET, C#…
• Existing tools/processes
• Leverage existing development skills
• T-SQL enhancements
Programmability
• Full text search
• Relational-to-XML mapping
• XML native datatype
Business
Intelligence
• SQL Web Services
• SQL Service Broker
11
.NET Framework Integration
Key Features
Server side programming:
Programming environment for:
Mid-tier/data tier deployment decision
Security
Functions, Procedures, Triggers
User Defined Types, Aggregates
In-Proc Data Access (ADO .NET V2)
Symmetric data access
VB.net, C++.net, & C#
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
The Developer Experience
VB,C#,C++ VS .NET
Project
Build
Runtime
hosted by SQL
(in-proc)
Assembly:
“TaxLib.dll”
SQL Data Definition:
create assembly …
create function …
create procedure …
create trigger …
create type …
SQL Server
SQL Queries:
select sum(tax(sal,state))
from Emp
where county = ‘King’
13
.NET Integration
Key Theme: Choice & Control
Choice of where to run logic
Choice of programming language
Database, for logic that runs close to data
Mid-tier, for logic that scales out
Symmetric programming model
Leverage skills mid-tier & server
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)
T-SQL Exception Handling
Recursive Queries
Multiple Active Result Sets
Statement-level recompile
Alternative to Text, Image
Uniform Programming model
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
Administrative integration
Index cost scales near linearly with data size
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 Scenarios
1) 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 Scenarios
2) Semi-structured storage…
First
Name
Address
Phone
XML data
type
XML Datatype
Last
Name
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.
18
OpenXML: XML content available via rowset
SQL Web Services
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
HTTP.SYS
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
APP 1
APP 2
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
20
SQL Server Yukon
The scalable, secure, enterprise-class data management platform
Enterprise Data
Management
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
Programmability
Business
Intelligence
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
21
Summary
Substantial release with 3 major themes:
Continue trademark focus on administrative
ease of use
Enterprise data management
Programmability
Business intelligence
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.
23