Eric NelsonApplication Architect, Microsoft http://bit.ly/ericnelson | http://twitter.com/ericnel Eric.nelson@microsoft.com

Extending SQL Server to the CloudExtending SQL Server to the Cloud

SQL AzureTDS

Your App

Change Connection String

ApplicatioApplicationn

Internet

LBLBTDS (tcp)TDS (tcp)

TDS (tcp)TDS (tcp)

TDS (tcp)TDS (tcp)

Apps use standard SQL client Apps use standard SQL client libraries: ODBC, ADO.Net, PHP, …libraries: ODBC, ADO.Net, PHP, …

Load balancer forwards ‘sticky’ Load balancer forwards ‘sticky’ sessions to TDS protocol tiersessions to TDS protocol tier

Gateway Gateway Gateway Gateway Gateway Gateway

Scalability and Availability: Fabric, Failover, Replication, and Load balancingScalability and Availability: Fabric, Failover, Replication, and Load balancing

SQLSQL SQLSQL SQLSQL SQLSQL SQLSQLSQLSQL

Gateway: TDS protocol gateway, enforces AUTHN/AUTHZ policy; proxy to backend SQLGateway: TDS protocol gateway, enforces AUTHN/AUTHZ policy; proxy to backend SQL

From From Windows AzureWindows Azure

From Outside From Outside Microsoft Datacenter Microsoft Datacenter

From Windows Azure & Outside From Windows Azure & Outside Microsoft Datacenter Microsoft Datacenter

Application / Browser

Windows Azure

SQL Azure

Code NearCode Near

App Code / Tools

SQL Azure

MicrosoftDatacent

er

Code FarCode Far HybridHybrid

Microsoft

Datacenter

SQL Azure

SQL Server

Microsoft Datacenter Windows

Azure

SQL Azure Data Sync

App Code / Tools

Tables require clustered indexes

Sync

SQL

Azure

On-Premises (Headquarters)

Syn

cSyn

c

SyncSync

Remote OfficesData Sync Service For SQL Azure

Retail Stores

SyncSync Sync

Sync

SyncSync

SQL Azure

Database

SyncSync Sync

Sync CTP1

CTP1

CTP2

CTP2

© 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.

Service Reque

st

App Data

App

Consistent and accepted latencies

On premises application

Files

Service Reque

st

App DataLatenc

yApp

Increases in quantity and variability of latencies

Cloud based application

Latency

TIP: Expect Web Role to SQL Azure to take about twice as long

Service Reques

tApp Data

Timeouts?App

Increased likelihood of timeouts

Cloud based application

Service Reques

tApp Data

App

Consistent, reliable connectivity and access

On premises application

Yes

No

Yes

NoTried 5 times?

Connection

Successful?

Try to connect

Continue doing work

Fail

TIP: There is a new code library for all this!

Purchasing Database

Product catalogue

Secure Transaction(Service Bus)

Segmentation

Online Shop

Online Shop

Product catalogue

On Premises

Cloud

Sharding

Customer Credit Card #

Shipping Information

Last Name

First Name

Contact ID

Social Security #

Billing Data

Other Information

On Premises

Customer Credit Card #

Shipping Information

Last Name

First Name

Contact ID

Social Security #

Billing Data

Other Information

On Premises

On Premises

Cloud

Encryption

 Single Blob

Drive Queue TableSingle SQL

Azure Database

Structured Data Y Y

Relational Database Y

Server Side Processing YDirect Access from outside Azure

Y Y Y Y

Messaging Infrastructure Y

Persistent Storage Y Y 1 week Y Y

Size Limit200 GB/ 1

TB1 TB 100 TB 100 TB 50 GB (*)

Feature Azure Table SQL Azure Table

Partitioning & Scale

Implicit based on Partition KeyExplicitly managed by the Application (Sharding – future feature)

Index Capabilities

Table indexed on Partition Key + Row Key0 Secondary Indexes

1 Clustered Index on any column999 Secondary Indexes

Table Limits

Row Size – 1 MBColumn Limit – 255Table Limit – 100TB

Row Size – 8MBColumn Limit – 1024Table Limit - ~50GB

Transactions

Transactions on entities within partitionNo Transactions across tables or partitionsUp to 100 operations in a TransactionPayload upto 4MB per Transaction

Fully compatible transactional semantics with SQL Server.

No cross Database transactions

Consistency Model

Transactionally Consistent Transactionally Consistent

Concurrency

Single Optimistic Concurrency Strategy

Full range of isolation and concurrency models as supported by RDBMS

Feature

Azure Table SQL Azure

Data Access

REST API, ADO .NET, Client Library SDKStandard tools and APIs applySSMS, Visual Studio, ADO .NET, ODBC

Column Types

Basic Types Usual SQL Server Data Types

Portability

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.

Queries

Upto 1000 entities [token pagination]Beyond 5 sec – return continuation token

Queries by partition & row key are fast

No Custom Indexes Today Non key queries are scans

Query capabilities as per standard SQL Server database expectations

Offer Server Side Processing through Stored Procedures and Complex Queries

(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