SQL Server
SQL Server
SQL Server
Peter Koen
koen@kema.at
http://www.kema.at
Peter Koen
CEO
KEMA – Koen Electronic Media Agency
Consultant
Author
SQL Server Usergroup Austria
www.sqlexperts.at
MCP, MCAD, MCSD.NET, MCDBA, MCT
MVP – Visual Developer C#
CAI R/S, CASE R/S, IAT
2
1
Agenda
SQL Server – Where are we?
The 4th Wave
DBA Features
Development Features
BI Features
2
First 3 Waves of Database
ERP
1960 2000
5
Word Processing
“Born Digital”
POS Data
Programming on Package
Punched Cards Tracking
0%
1960 2000
6
3
Big Trend: Schema
01 SAMPLE-REC.
03 SA-PAC-DEC-1 PIC 9(7)V99 COMP-3.
03 FILLER PIC X(5).
03 SA-PAC-DEC-2 PIC 999 COMP-3.
03 SA-PAC-DEC-3 PIC S9(3) COMP-3.
XML
COBOL Records
Mapping Files/Memory
1960 2000
7
4
Key Observations
People’s time is precious
Cost of managing systems is HUGE!
Can consume CPU, Disk, & Bandwidth to
save people’s time
Allows people to concentrate on more
interesting tasks…
Data, Schema, Processing & Bandwidth
We have the capability to provide end-to-end
digital business (Autonomous Computing)
5
• .NET CLR hosting • Comprehensive ETL platform
• Native XML technology • Real time analytics
• Integrated web services • Accessible, easy data mining
• Distributed application framework • Rich, integrated reporting
New Features
.NET Framework Database Maintenance Replication
Common Language Runtime Integration Backup and Restore Enhancements Auto-tuning Replication Agents
User-defined Aggregates Checksum Integrity Checks Oracle Publication
User-defined Data Types Dedicated Administrator Connection Improved Blob Change Tracking
User-defined Functions Dynamic AWE OLAP and Data Mining
SQL Server In-Proc Data Provider Fast Recovery Analysis Management Objects
Extended Triggers Highly-available Upgrade Windows Integrated Backup and Restore
Data Types Online Index Operations Web Services/XML for Analysis
Online Restore DTS and DM Integration
File Stream Storage Attribute
Parallel DBCC Eight new DM algorithms
Managed SQL Types
Parallel Index Operations Auto Packaging and Deployment
New XML Datatype
SQL Server Engine Management Tools Data Transformation Services
MDX Query Editor New Architecture (DTR + DTP)
New Message Service Broker
MDX Intellisense Complex Control Flows
HTTP Support (Native HTTP)
T-SQL Intellisense Control Flow Debugging
Database Tuning Advisor
Version Control Support For Each Enumerations
Enhanced Read ahead & scan
XML/A Property Mappings
Extended Indexes
SQLCMD Command Line Tool Full Data Flow Designer
Multiple Active Result Sets
Persisted Computed Columns Performance Tuning Full DTS Control Flow Designer
Queuing Support Profiler Enhancements Graphical Presentation of Pkg Execution
Snapshot Isolation Level Profiling Analysis Services Immediate Mode and Project Mode
Scale Up Partitioning Exportable Showplan Package (Advanced) Deployment Tools
VIA support Exportable Deadlock Traces Custom Tasks and Transformations
NUMA support Full-text Search Reporting Services
Database Failure and Redundancy Indexing of XML Datatype Multiple Output Formats
Fail-over Clustering (up to 8 node) MDAC Parameters (Static, Dynamic, Hierarchical)
Enhanced Multi-instance Support Side by Side installation Bulk Delivery of Personalized Content
Database Mirroring Microsoft Installer base setup Support Multiple Data Sources
Database Viewpoints Support for Active Directory Deployment Sharepoint Support
XML Visual Design Tool
SQL Client .NET Data Provider Charting, Sorting, Filtering, Drill-Through
XQUERY Support (Server & Mid Tier) Server Cursor Support Scheduling, Caching
XML Data Manipulation Language Asynch Complete Scripting Engine
FOR XML Enhancements
XML Schema (XSD) Support Security Scale Out architecture
All Permissions Grantable XML Report Definition
MSXML 6.0 (Native)
XQuery Designer Fine Grain Administration Rights Notification Services
Separation of Users and Schema
12
6
SQL Server Yukon Timeline
Beta 2 - Summer 2004
Feature complete
Very good quality
Engine perf ~90% of SQL 2K
Beta 3 – Q4 2004
RC Level quality and perf.
RTM – 2005
13
7
Fast Recovery
Restart or Restore
SQL Server 2000
Database is Available after Undo Completes
Redo Undo
Time Available
Redo Undo
Available
15
Database Mirroring
Fault Tolerant Virtual Database
Witness
Clients
Principal Mirror
16
8
Database Mirroring
How It Works
Transaction Shipping
Witness
Principal Mirror
Application
1 5
2
SQL Server SQL Server
2 4 3
17
18
9
New in SQL Server 2005
Three new application frameworks:
SQL Service Broker
SQL Server Notification Services
SQL Server Reporting Services
New server-side programming support
Stored procedures, triggers, & functions in
C++, C#, or VB
Server-side Abstract Data Types
Industry leading programming tools
19
Service Queue
Message
Message
Shared Private
Stuff State
20
10
Notification Services Architecture
Subscription Management Application
Subscribers
Data Subscriber Devices Notifications External
Data Events Subscriptions External
Changes
Changes Delivery
Delivery
21
Report Control
Report Processing
Report Processing
Scheduling and
Rendering
Delivery
Output Formats Delivery Targets
(HTML, Excel, (E-mail, SharePoint,
SQL Server Catalog Custom) 22
PDF, Custom)
11
.NET Framework Integration
Key Features
Programming environment for Functions, Stored
Procedures, Triggers
User Defined Types, Aggregates
In-Proc Data Access (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, deployment, & profiling
23
Build
SELECT tax(salary)
FROM Employees
WHERE … CREATE ASSEMBLY
CREATE FUNCTION
CREATE TYPE
SQL Server
24
12
Assembly & Class Loading
Harddisk
sys.assemblies
Execute
Load FX Validate
version assembly
Assembly
25
.NET Integration
Key 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
T-SQL continues to be supported & enhanced
Remains good choice for data-intensive procedures
Safe extended stored proc replacement
26
13
TSQL vs. C# - data
conversion
if @bin is null return null
declare @len int, @b tinyint, @lowbyte tinyint, @hibyte tinyint, @index int, @str nchar(2), @result nvarchar(4000)
set @len = datalength(@bin)
set @index = 1
set @result = '0x'
while @index <= @len
begin
set @b = substring(@bin, @index, 1)
set @index = @index + 1
set @lowbyte = @b & 0xF
set @hibyte = @b & 0xF0
if @hibyte > 0
set @hibyte = @hibyte / 0xF
set @result = @result +
((case
when @hibyte < 10 then convert(varchar(1), @hibyte)
when @hibyte = 10 then 'A'
when @hibyte = 11 then 'B'
when @hibyte = 12 then 'C'
when @hibyte = 13 then 'D'
when @hibyte = 14 then 'E'
when @hibyte = 15 then 'F'
else 'Z'
end)
if (value == null) return null; +
(case
StringBuilder sb = new StringBuilder(); when @lowbyte < 10 then convert(varchar(1), @lowbyte)
when @lowbyte = 10 then 'A'
foreach (byte b in value) when @lowbyte = 11 then 'B'
when @lowbyte = 12 then 'C'
sb.Append(b.ToString("X2")); when @lowbyte = 13 then 'D'
when @lowbyte = 14 then 'E'
return sb.ToString(); when @lowbyte = 15 then 'F'
else 'Z'
end))
end
27
28
14
Yukon XML Support
XML data type & XML index support
Unified XML & relational store
Both SQL & XQuery supported by same
industrial strength infrastructure
Leverages existing SQL engine & optimizer
XQuery with data modification extensions
XML views
Declarative Mapping Files
XML schema enforcement
Client access using ADO.NET & SOAP
29
XML Scenarios
Data Exchange…
XML data
Point-of-Sales Supplier
15
XML Scenarios
Semi-structured storage…
XML Datatype
Loosely structured data
Data with a dynamic schema
XML Views
Mixed data – structured/unstructured
XML stores w/o relational support challenged 31
32
16
Native XML Store
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 2:
UPDATE docs SET xDoc::modify(
‘insert <section num=“2”>
<heading>Background</heading>
</section>
after /doc/section[@num=1]')
33
HTTP/SOAP Features
SQL & stored proc calls via HTTP/SOAP
Easy, standards based connectivity from Unix
platforms to SQL Server
Provides native HTTP listening
HTTP endpoint specifying URL, port, reqs
Publish WSDL for endpoints
Standard-based
SOAP 1.1 and 1.2, WSDL 1.1, inlined XSD
Windows and SQL authentication (SSL only)
Stored Proc can return result as DataSet
Compatible with mid-tier prog. model
34
17
Data Access
API Enhancements: ADO .NET V2
Multiple active result sets (MARS)
Object Persistence FX (ObjectSpaces)
Query notifications – for cache invalidation
Functional parity with ADO (ADOX, etc)
Server cursors (SqlResultSet)
Asynchronous client access
Bulk update, paging, and batching
XML Data type support
User Defined Type (UDT) support
35
Reporting Services
Development Tools
Management Tools
Analysis Services
OLAP & Data Mining
Data Transformation
Services
ETL
SQL Server
Relational Engine
36
18
Analysis Services
Unified Dimensional Model
Integrating relational and OLAP views
Pro-active caching
Bringing the best of MOLAP to ROLAP
Advanced Business Intelligence
MDX scripts, translations, currency…
Web services
Native XML
37
Data Mining
Significant investment
Important, new algorithms
Association rules, time series, regression
trees, sequence clustering, neural nets, …
Embedded, integrated, complete
38
19
Microsoft Office System Integration
Office Document Formats
Excel, Word, outlook
Import reports from Access
HTML rendering
Interactive Charts, PivotTables, Spreadsheets
SharePoint Portal Server Integration
Report Web Parts
Report delivery to document libraries, lists
Report Library Template
Searchable
39
Questions, Discussions?
40
20