Transcript Slammer in Depth

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 clustering 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