Change the Rules

Lap around SQL Azure

Anko Duizer

Change the Rules

Introduction• Anko Duizer

– Chief Software Architect

– Blog: http://www.ankoduizer.nl

– Twitter: ankoduizer

• TellUs– Lead generation

– > 5000 sites

– World wide

– Microsoft platform

Change the Rules

INTRODUCTION

Change the Rules

Place

Type

Service

Data platform: device to cloud

Mobile/Desktop

AnalysisCaching

Integration

Sync

Search

Data ServicesServerDesktop

RelationalBLOB

QueryBackup

Load

MultiDim

Mega-server

InMemory

FileXML

ReportingMiningProtection

RDBMS

Change the Rules

Business AnalyticsReporting Data sync

• The power of the SQL Data Platform in the cloud

• Leverages existing skills and rich tooling ecosystem

• Enables database applications with new, “cloud” capabilities

Change the Rules

Cloud

application

On-premises

application

Data in the

cloud

Change the Rules

Reso

urc

es

Dedicated

Shared

Low HighControl

SQL Azure

Value Props:

Hosted

On-premise

Value Props:

Value Props:

Roll-your-own HA/scale

Change the Rules

SQL AZURE

Change the Rules

SQL Azure

• Mangebility

• Scalability

• Developer empowerment

Change the Rules

SQL Azure

TDS + TSQL Model

Web App

SQL Client*

Windows Azure

Browser

application

Application

REST Client

REST (Astoria)

ADO.Net + EF

Application

SQL Client*

Cloud

HTTP

HTTP

+R

EST

TD

S

Clo

ud

ODBC, OLEDB,

ADO.Net PHP,

Ruby, …

Change the Rules

Account

Server

Database

Server=server1.data.database.windows.netDatabase=testDBLogin=nigele[@server1] (maps to testuser)

Change the Rules

Logical vs. Physical administration

• SQL Azure focus on logical administration

– Schema creation and management

– Query optimization

– Security management (Logins, Users, Roles)

• Service handles physical management

– Automatically replicated with HA “out of box”

– Transparent failover in case of failure

Change the Rules

Architecture

Machine 5

SQL Instance

SQL DBUser

DB1

User

DB2

User

DB3

User

DB4

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

Provisioning (databases, accounts, roles, …, metering, and billing

Machine 6

SQL Instance

SQL DBUser

DB1

User

DB2

User

DB3

User

DB4

Machine 4

SQL Instance

SQL DBUser

DB1

User

DB2

User

DB3

User

DB4

scalability and availability: fabric, failover, replication, and load balancing

Change the Rules

Deployment

• Support for basic deployment options

– SQL scripts work (but not attach database)

• Geo-location of Windows Azure compute

and SQL Azure databases

• Cloud or on-premise is a deployment time

choice

Change the Rules

SQL Azure deployment

Web Portal

(API)

SQL Azure

TDS

DB

Script

Change the Rules

SQL Azure - Accessing databases

Web Portal

(API)

SQL Azure

TDS

Your

App

Change connection string

Change the Rules

Database replicas

Replica 1

Replica 2

Replica 3

DB

Change the Rules

Database monitoring & recoveryWeb Portal

(API)

SQL Azure

TDS

Your

App !

Change the Rules

Database BackupsFailure Scenario Solution When Responsible Party

HW and Media

failures

Multiple replicas of all databases with automatic

failover. System backups for additional protection

Today SQL Azure - Built-in to

the base serviceXCustomer application or

administrator errors leading to data

loss

Use BCP, SSIS or other custom tools to backup data Today Customer

Database clone to create on-demand, backups of

databases in the local or remote SQL Azure region

• Cloning is done with transactional consistency

• When clone operation is complete the replica is

accessible as a normal database

1H 2010 Customer

Continuous backup option with point-in-time restore

capabilities

• Configurable retention and lag period

• Backups available for read operations

• Local or remote region

2H 2010

1. Customer enables

and configures it.

2. Service will

maintain it

Change the Rules

South central US

abc.database.windows.net

Dev1Master

xyz.database.windows.net

prod1Master

CREATE DATABASE xyz.prod2clone AS CLONE OF xyz.prod2 CREATE DATABASE efg.prod2clone AS CLONE OF xyz.prod2

prod2

clone

prod2

clone

prod2

clone

prod2

clone

North central US

efg.database.windows.net

DR1 Masterprod2

clone

prod2

clone

CREATE DATABASE abc.prod2clone AS CLONE OF xyz.prod2

Database clone

Prod2

Change the Rules

PROGRAMMING SQL AZURE

Change the Rules

Programming model

• Small data sets

– Use a single database

– Same model as on premise SQL Server

• Large data sets and/or massive throughput

– Partition data across many databases

– Use parallel fan-out queries to fetch the data

– Application code must be partition aware in v1

Change the Rules

Connection model

• Use existing client libraries

– ADO.NET, ODBC, PHP

• Client libraries pre-installed in Azure roles

• Support for ASP.NET controls

• Clients connect directly to a database

Change the Rules

Compatibility goals

• Support common application patterns

• Logical/policy based administration

• Patterns work from SQL Server to SQL Azure

• Multi-tenancy considerations

– Throttling

– Limits on DB size, transaction duration, …

Change the Rules

Sample of SQL compatibilityv1

• Tables, indexes and views

• Stored procedures

• Triggers

• Constraints

• Table variables, session temp tables (#t)

vNext

• Distributed transactions

• Distributed query

• CLR

• Service broker

• Spatial

• Physical server or catalog DDL and views

Change the Rules

Security model

• Uses regular SQL security model

– Authenticate logins, map to users and roles

– Authorize users and roles to SQL objects

• Limited to standard SQL Auth logins

– Username + password

• Future AD Federation, WLID, etc as alternate authentication protocols

Change the Rules

FUTURE

Change the Rules

Planned

• Partitioned databases

• Spatial data

• Security AD, WLID, etc

• Increased database size

Change the Rules

Requests…

• Add support for profiler, DMVs …

• Full-text search support

• Change tracking support

• Support for CLR

• Cloud based Business Intelligence (BI) solutions

Change the Rules

USEFUL?

Change the Rules

Scenarios for V1• Departmental applications

– Simple application built by individual or department

– Need simple deployment, self-management, IT: “Empowerment and governance”

• Web Applications– Small business or startup that uses the cloud as their IT

– Simple deployment, self-management, scale on demand

• ISV– ISV hosting software on behalf of customer

– Multi-tenant support for billing and isolation

• Data hub– Sharing and aggregating of data across tiers and across enterprises

– Centralized place for data, high scale, sync with existing data sources

Change the Rules

Azure

App Code

Application/

Browser

Change the Rules

On premise

Azure

App Code

Application/

Browser

How much data is

transfered?

Change the Rules

On premise

Azure

backup

SSIS

Source

Change the Rules

Azure

Y

App Code

Application/

Browser

XZ

Change the Rules

Laptop

On-premise/ department

Azure

Data

hubSource

Source

Sync framework

Syn

c fram

ew

ork

Change the Rules

NOT USEFUL!

Change the Rules

On premise

Azure

App Code

Application/

Browser

Too much data

transferred

Change the Rules

On premise

Azure

SSIS

Source

Azure

destination

Change the Rules

Is it cheaper?

Change the Rules

• 1GB Database

• $9.99 / month

• Bandwidth– $0.10 /GB inbound

– $0.15 /GB outbound

• 10GB Database

• $99.99 / month

• Bandwidth– $0.10 /GB inbound

– $0.15 /GB outbound

Change the Rules

Question?