Transcript SQL Azure

Eric Nelson
Application Architect, Microsoft
http://bit.ly/ericnelson | http://twitter.com/ericnel
[email protected]
Extending SQL Server to the Cloud
Your App
SQL Azure
TDS
Change Connection String
Apps use standard SQL client libraries:
ODBC, ADO.Net, PHP, …
Application
Internet
TDS (tcp)
Load balancer forwards ‘sticky’ sessions
to TDS protocol tier
LB
TDS (tcp)
Gateway
Gateway
Gateway
Gateway
Gateway
Gateway
Gateway: TDS protocol gateway, enforces AUTHN/AUTHZ policy; proxy to backend SQL
TDS (tcp)
SQL
SQL
SQL
SQL
SQL
Scalability and Availability: Fabric, Failover, Replication, and Load balancing
SQL
From
Windows Azure
From Outside
Microsoft Datacenter
Application / Browser
App Code / Tools
From Windows Azure & Outside
Microsoft Datacenter
App Code / Tools
SQL Azure
Data Sync
Windows
Azure
Microsoft
Datacenter
SQL Azure
Code Near
Microsoft
Datacenter
Windows
Azure
SQL Azure
SQL Azure
Code Far
Hybrid
Microsoft
Datacente
r
Tables require clustered indexes
SQL
Azur
e
Sync
Remote Offices
CTP2
Sync
Retail Stores
Data Sync
Service For
SQL Azure
CTP1
SQL Azure
Database
On-Premises (Headquarters)
© 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a
commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS
PRESENTATION.
On premises application
App
Data
Service
Request
App
Consistent and accepted latencies
Cloud based application
Service
Request
App
App
Data
Increases in quantity and
variability of latencies
TIP: Expect Web Role to SQL Azure to take about twice as long
On premises application
Try to connect
Service
Request
App
Data
App
Consistent, reliable connectivity and
access
Connection
Successful?
Yes
Continue doing work
Cloud based application
Service
Request
No
Timeouts?
App
App
Data
No
Increased likelihood of
timeouts
Tried 5 times?
Yes
Fail
TIP: There is a new code library for all this!
Segmentation
Online Shop
Online Shop
Product
catalogue
Purchasing
Database
Product
catalogue
Secure Transaction
(Service Bus)
Sharding
Contact ID
Contact ID
First Name
Last Name
First Name
Cloud
Last Name
Shipping Information
Shipping Information
Customer Credit Card #
Social Security #
Billing Data
Other Information
On Premises
Contact ID
Customer Credit Card #
Social Security #
Other Information
On Premises
Encryption
Contact ID
Contact ID
First Name
Last Name
First Name
Cloud
Last Name
Shipping Information
Shipping Information
£!”$&$%!&£%
Customer Credit Card #
!£$&!%£&%*
Social Security #
£”$&!%*^(&
Billing Data
^$”$&%$”&
Other Information
On Premises
On Premises
Single Blob
Drive
Queue
Structured Data
Table
Single SQL Azure
Database
Y
Y
Relational Database
Y
Server Side Processing
Y
Direct Access from outside Azure
Y
Y
Messaging Infrastructure
Persistent Storage
Size Limit
Y
Y
Y
Y
Y
1 week
Y
Y
200 GB/ 1 TB
1 TB
100 TB
100 TB
50 GB (*)
Feature
Partitioning &
Scale
Azure Table
SQL Azure Table
Implicit based on Partition Key
Explicitly managed by the Application (Sharding –
future feature)
Index Capabilities
Table indexed on Partition Key + Row Key
0 Secondary Indexes
1 Clustered Index on any column
999 Secondary Indexes
Table Limits
Row Size – 1 MB
Column Limit – 255
Table Limit – 100TB
Row Size – 8MB
Column Limit – 1024
Table Limit - ~50GB
Transactions
Transactions on entities within partition
No Transactions across tables or partitions
Up to 100 operations in a Transaction
Payload upto 4MB per Transaction
Fully compatible transactional semantics with SQL
Server.
Consistency
Model
Transactionally Consistent
Transactionally Consistent
Concurrency
Single Optimistic Concurrency Strategy
Full range of isolation and concurrency models as
supported by RDBMS
No cross Database transactions
Feature
Azure Table
SQL Azure
Data Access
REST API, ADO .NET, Client Library SDK
Standard tools and APIs apply
SSMS, Visual Studio, ADO .NET, ODBC
Column Types
Basic Types
Usual SQL Server Data Types
Data portability coming with Windows Azure Appliance
Data in SQL Azure similar to SQL Server
- Easy migration in and out of the cloud
- Use multi stream transfer to mitigate
network latency.
Upto 1000 entities [token pagination]
Beyond 5 sec – return continuation token
Query capabilities as per standard SQL Server
database expectations
Queries by partition & row key are fast
Offer Server Side Processing through Stored
Procedures and Complex Queries
Portability
Queries
No Custom Indexes Today
Non key queries are scans
(Aggregation, Joins, Sorts, Filters, etc.)
A traditional set of data that is traditionally stored on the local environment is
configuration and user settings. Commonly this is stored in the registry, xml or ini files.
Options
Best Case Usage
Blob storage
Configuration file that is read only
once during app load
SQL database
Used in environments where a SQL
database already exists
Windows Azure table
User settings that may be changed
externally from the environment
ID
Name
Start IP
End IP
Create
Modify
1
Office
12.1.2.0
12.1.2.255
2009-09-18 …
2009-09-18 …
2
Home
12.2.2.5
12.2.2.5
2009-09-20 …
2009-09-21 …
Time
Database
Direction
Class
Time_period
Quantity
2009-09-17 19:00
TPCH
Egress
Internal
Peak
55598
2009-09-17 19:00
TPCH
Ingress
Internal
Peak
76026
…
…
…
…
…
…