Transcript DBI316 Pain Points Goals Building and Maintaining Applications with relational and non-relational data is hard Complex integration Duplicated functionality Compensation for unavailable services Reduce the cost of.

DBI316
Pain Points
Goals
Building and Maintaining Applications with
relational and non-relational data is hard
Complex integration
Duplicated functionality
Compensation for unavailable services
Reduce the cost of managing all data
Simplify the development of applications over all data
Provide management and programming services for all
data
Structured and unstructured
Search
Related/”Semantic” Search
Programmability
T-SQL
Query
Structured
Data
B-trees
Manageability
Availability
Files
Programmability
T-SQL
Query
Search
Structured
Data
Unstructured
Data
B-trees
Manageability
Availability
Files
Programmability
Spatial, XML,
HierarchyID
T-SQL/Data Types
Win 32
Query and
Type Operations
Structured
Data
XQuery
Spatial ops
Search
Semi-structured
Data/XML
Unstructured
Data
XML, FTS, Spatial
Indices
B-trees
Filestream
Manageability
Availability
Files
Rich Data
Programming
Capabilities
Programmability
Spatial, XML,
HierarchyID
T-SQL/Data Types
Rich Query and
Search Services
over all Data
Efficient Storage
for BR Data
Win 32
Search
Query and Type Operations
XQuery
Spatial ops
Structured
Data
Semantic
Platform
Semi-structured
Data/XML
Unstructured
Data
XML, FTS, Spatial
Indices
B-trees
Filestream
Manageability& Availability
Files
SQL Server
2005
Full Text Indexing
Rich unstructured
Data & Services
Spatial
Semistructured Data &
Documents
Reliable
Messaging
XML Data Type
XQuery
XML Schema
Service Broker
SQL Server 2008R2
SQL Server
Denali
Remote BLOB Store
API over FileStream
Filestream with RCSI
Integrated FTS
FileTable (Win 32 I/O)
Scale-up FileStream
Scale-up Search
Search functionality
Semantic Similarity
Fully supported Geometry and
Geography data types and
Functions
Reporting Services support
FullGlobe
2D Extensions
Pervasive Spatial
XML Upgrades
Large UDTs
Sparse Columns
Wide Table/ColumnSet
Filtered Indices
HierarchyID
Poison-Message handling
Multi-cast
Enqueue time
Transactional Access
StreamingWin32
Win32Access??
Access
Streaming
Database Applications
Win32 Applications
Blobs
Database
FileTable
FileStreams
FileStream
API
Scale-up
Solutions
Semantic Key
Phrase Extraction
Rich Services
Fulltext Search
SMB Share
Files/Folders
Disk1
Disk2
Disk3
Multiple Containers
Integrated
IntegratedAdministration?
Administration
Remote BLOB Storage
Customer Application
SQL RBS API
DB
DB FileStre
Azure lib
Centera lib
SQL
FILESTREAM lib
FileStreams
Integrated
Backup/Replication/AlwaysOn
Azure
Centera
SQL DB
Machine Boundary
Application
1
Write BLOB(Photo)
2
Return Blob ID
3
Write Blob ID to PhotoRef field
2
RBS Client Library
BLOB Store Provider
Library
1
RBS
Services
• Create
• Fetch
• GC
• Delete
3
BLOB Store
SQL Server
ClaimID
ClaimDate
PhotoRef
4390
6/5/2007
<Binary(20)>
FileTable Namespace Root Folder Hierarchy
Filestream Share
MSSQLSERVER
\\my_machine\
MSSQLSERVER\
DBDirectory2\Documents
Database
Directories
DB_Directory1
(Database1)
DB_Directory2
(Database2)
FileTable Directories
Media
(FileTable)
Documents
(FileTable)
LogFiles
(FileTable)
900
800
Filestream Win32
(Filesystem) Access
Throughput (Mbps)
700
600
Filestream T-SQL
500
400
Varbinary
300
Filesystem Win32 Access
Gain (%)
200
100
0
240 KB
480 KB
1 MB
2 MB
4 MB
8 MB
Insert
600
Filestream Win32
(Filesystem) Access
500
400
Throughput (Mbps)
Filestream T-SQL
300
Varbinary
200
Filesystem Win32
Access Gain (%)
100
0
240 KB
-100
-200
480 KB
1 MB
2 MB
4 MB
8 MB
Create/Alter Database
max_size
DBCC Shrinkfile Emptyfile
• Use of multiple spindles for achieving better I/O Scalability
File Stores /
External Blob
Stores (CAS)
Streaming
Performance
Win32 App Compat
Depends on
external store
Depends on
external store
Link Level
Consistency
Data Level
Consistency
Integrated Query &
Management
Non-local Windows
File Servers
n/a
External Blob Stores
n/a
SQL BLOBs
Remote Blob
API
Depends on
external store
Depends on
external store
FILESTREAM
FILETABLE
SELECT * FROM candidates
WHERE CONTAINS(resume,’”SQL Server”’)
AND ZipCode = ‘98052’
Keyphrases
Source Table
Title
Document
D1
Annual Budget
…
D2
Corporate Earnings
…
D3
Marketing Reports
…
…
…
…
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1
Key
KeyphraseDocuments
ID
Keyword
ID
DocID
T1
revenue
T1 (revenue)
D1 (Annual Budget)
T2
growth
T2 (growth)
D2 (Corporate Earnings)
T3
Windows
T3 (Windows)
D3 (Marketing Reports)
T4
Azure
…
…
…
…
T1 (revenue)
D7 (Finance Report)
…
…
T3 (Windows)
D11 (Azure Strategy)
T4 (Azure)
D11 (Azure Strategy)
Full-Text and Semantic Processing
quarter, record,
revenue…
2a
DocumentSimilarity
Keyword Index (Full-Text)
DocID
MatchedDocID
D1 (Annual Budget)
D2 (Corporate Earnings)
ID
Keyword
Colid
…
compDocid
CompOc
CompPid
D1 (Annual Budget)
D7 (Finance Report)
K1
revenue
1
…
10,23,123
(1,4),(5,8),(1,34)
2,5,6,8,4,3
D3 (Marketing Reports)
D11 (Azure Strategy)
K2
growth
1
…
10,23,123
(1,5),(5,9),(1,34)
2,5,6,8,5,4
…
…
…
…
…
…
…
…
2 min
Populating an index of 20 million rows of 1K data on
identical hardware (time in minutes)
1 min
Queries over 350M documents database and random DMLs running in background.
Beating SQL Server 2005 with a scale factor more than 2x and with avg 60x times better throughput
Query avgExecTime (ms) under various number of connections (50 ~ 2000 users) for customer playback
benchmark
SQL CAT Whitepaper what is new in Denali
SELECT TOP(5) *
FROM Restaurants r
WHERE r.type = ‘Italian’
AND r.pos.STDistance(@me) IS NOT NULL
ORDER BY r.pos.STDistance(@me)
Find the closest 50 business points (22 million in total)
http://www.sqlserverlaunch.com/
http://sqlcat.com/whitepapers/archive/2010/11/09/new-spatial-features-in-sql-server-codenamed-denali-community-technology-preview-1.aspx
https://connect.microsoft.com/SQLServer/Downloads/DownloadDetails.aspx?DownloadID
=35226
[email protected]
http://sqlblog.com/blogs/michael_rys
Visit the updated website for SQL Server® Code Name “Denali” on
www.microsoft.com/sqlserver and sign to be notified when the next
CTP is available
Follow the @SQLServer Twitter account to watch for updates
Try the new SQL Server Mission Critical BareMetal Hand’s on-Labs
Visit the SQL Server Product Demo Stations in the DBI Track section of the
. Bring your questions, ideas and conversations!
• Microsoft® SQL Server® Security & Management
• Microsoft® SQL Server® Programmability
• Microsoft® SQL Server® Mission Critical
• Microsoft® SQL Server® Optimization and Scalability
• Microsoft® SQL Server® Data Warehousing
• Microsoft® SQL Server® Data Integration
http://northamerica.msteched.com
www.microsoft.com/teched
www.microsoft.com/learning
http://microsoft.com/technet
http://microsoft.com/msdn