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

SQL AZUREExtending SQL Server to the Cloud

SQL Azure DatabaseFamiliar SQL Server relational database modelSupport for existing APIs & toolsBuilt for the cloud with high availability & fault toleranceEasily provision and manage databases across multiple datacenters

Data Sync Service (CTP)Provides two-way sync of SQL Azure Databases across datacentersExample service available in the SQL Azure Labs environment

SQL Azure Reporting (CTP)Reporting Services running on the Windows Azure Platform

Database Sync Service Reporting

SQL AZURE DATABASE

SQL AZURE DATABASE SERVICE

• SQL Azure provides logical SQL Server• Looks like SQL Server to TDS Client• Actual data stored on multiple backend data nodes

• Logical optimisations supported• Indexes, Query plans etc..

• Physical optimisations not supported• File Groups, Partitions etc…

• Transparently manages physical storage

WORKING WITH SQL AZURE DATABASES

SQL AzureTDS

Your App

Change Connection String

BEHIND THE SCENES OF SQL AZURE

Application

Internet

LBTDS (tcp)

TDS (tcp)

TDS (tcp)

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

Load balancer forwards ‘sticky’ sessions to TDS protocol tier

Security Boundary

Gateway Gateway Gateway Gateway Gateway Gateway

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

SQL SQL SQL SQL SQLSQL

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

APPLICATION TOPOLOGIES

From Windows Azure

From Outside Microsoft Datacenter

From Windows Azure & Outside Microsoft Datacenter

Application / Browser

Windows Azure

SQL Azure

Code Near

App Code / Tools

SQL Azure

MicrosoftDatacent

er

Code Far Hybrid

Microsoft Datacenter

SQL Azure

SQL Serve

r

Microsoft Datacenter Windows

Azure

SQL Azure Data Sync

App Code / Tools

DEMO

CONNECTING TO SQL AZURE

• SQL Azure connection strings follow normal SQL syntax• Applications connect directly to a database

• “Initial Catalog = <db>” in connection string• No support for context switching (no USE <db>)

• Encryption security• Set Encrypt = True, only SSL connections are supported• TrustServerCertificate = False, avoid Man-In-The-Middle-Attack!

• Format of username for authentication:• ADO.Net:

Data Source=server.database.windows.net;User ID=user@server;Password=password;...

• Setup your firewall rules first!

SQL AZURE COMPATIBILITY

Currently Supported

• Tables, indexes and views

• Stored Procedures• Triggers• Constraints• Table variables,

session temp tables (#t)

• Spatial types

Not Currently Supported

• Data Types• XML,

HierarchyId, Sparse Columns, Filestream

• Partitions• Full-text indexes• SQL-CLR

Tables require clustered indexes

DATABASE EDITIONS

• You specify Web or Business Edition• Web: EDITION = web• Business: EDITION = business

• You specify MAXSIZE• Web: MAXSIZE = 1GB | 5GB• Business: MAXSIZE = 10GB | 20GB | 30GB | 40GB | 50GB

• This is the maximum size we will not let you grow beyond• You will only be charged for the actual peak size in any one day

rounded up• For example, a 3.4 GB Web Edition will be charged 5GB rate.

• CREATE DATABASE foo1 (EDITION='business', MAXSIZE=50GB);• CREATE DATABASE foo2 (EDITION='business', MAXSIZE=30GB);• ALTER DATABASE foo2 MODIFY (EDITION='web', MAXSIZE=5GB);

Up to 50 GB10 GB increment

s

Business Edition

1 GB or 5 GBWeb Edition

DATABASE EDITIONS

• Two SQL Azure Database SKUs: Web & Business• Web Edition:

• 1 GB @ £6.055/month • 5 GB @ £30.275/month

• Business Edition: • 10 GB @ £60.604 • 20 GB @ £121.208 • 30 GB @ £131.812 • 40 GB @ £242.416• 50 GB @ £303.20

Up to 50 GB10 GB increment

s

Business Edition

1 GB or 5 GBWeb Edition

DEMO

SQL AZURE DATA SYNC

SQL AZURE DATA SYNC

• Powers movement of data• Cloud cloud• On-premises cloud

• Getting data where you need it• Sync SQL Azure

instances• Sync SQL Server to SQL

Azure• Sync offline apps to SQL

Azure• Enable geo-replication of

data

Sync

SQL Azur

e

SQL AZURE DATA SYNC – ROADMAP

On-Premises (Headquarters)

Syn

c

Sync

Remote OfficesData Sync Service For SQL Azure

Retail Stores

Sync Sync

Sync

SQL Azure

Database

Sync Sync CTP1

CTP2

TOP FEATURES

• Service scales as resources requirements grow• No-Code Sync Configuration• Schedule Sync• Conflict Handling• Logging and Monitoring• Data sub-setting

DEMO

SQL AZURE REPORTING

SQL AZURE REPORTING

• CTP – you can register • Based on SQL Server Reporting Services 2008 R2

• Exactly same report formats (no custom extensions yet)• Use BI Development Studio

• Reports run on Windows Azure Platform• Report Viewer control using remote processing mode

• Only reports against SQL Azure Database

SUMMARY

• SQL Azure Database: hassle free fault tolerance with little to learn

• SQL Azure Data Sync: Helping build geographically disperse or hybrid applications

• SQL Azure Reporting: Reporting Services in the Cloud

APPENDIX

LATENCY AND TIMEOUTS

LATENCY

Service

Request

App Data

App

Consistent and accepted latencies

On premises application

Files

Service

Request

App DataLatenc

yApp

Increases in quantity and variability of latencies

Cloud based application

Latency Blob

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

TIMEOUT

Service

Request

App 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

TIMEOUT RETRY CODE EXAMPLE

TIMEOUT RETRY CODE EXAMPLE

TIMEOUT RETRY CODE EXAMPLE

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

DATA SENSITIVITY

Purchasing Database

Product catalogue

Secure Transaction(Service Bus)

Segmentation

Online Shop

Online Shop

Product catalogue

Contact ID

Social Security #

Customer Credit Card #

Other Information

Shipping Information

Last Name

First Name

Contact ID

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

£!”$&$%!&£%

£”$&!%*^(&

!£$&!%£&%*

^$”$&%$”&

Shipping Information

Last Name

First Name

Contact ID

On Premises

Cloud

Encryption

DATA CHOICES

YOU HAVE MANY CHOICES FOR DATA

Relational Database

Structured Storage

Unstructured Storage

NTFS Drive

Service Communication

SQL Azure

Azure Table

Azure Blob

Azure Queue

Azure Drive

SIDE BY SIDE EVALUATION

 Single Blob Drive Queue Table

Single SQL Azure

DatabaseStructured 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 Limit 200 GB/ 1 TB 1 TB 100 TB 100 TB 50 GB (*)

(*) Scale out across multiple databases using partitioning for better scalability and performance

AZURE TABLE AND SQL AZURE TABLE

Feature Azure Table SQL Azure Table

Partitioning & Scale

Implicit based on Partition Key Explicitly 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

AZURE TABLE AND SQL AZURE TABLE

Feature Azure Table SQL Azure

Data Access REST API, ADO .NET, Client Library SDK

Standard 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.)

EXAMPLE: REGISTRY/CONFIGURATION/USER SETTINGS

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

REPORTING

SERVER: NETWORK ACCESS CONTROL

• Each server defines a set of firewall rules• Determines access policy based on client IP• By default, there is NO ACCESS to server

• Controlled using Firewall API (masterDB)• sys.firewall_rules, sys.sp_merge_firewall_rule

and sys.sp_delete_firewall_rule

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 …

SERVER: BILLING AND REPORTING• Usage metrics from views:• sys.bandwidth_usage• sys.database_usage

• Bandwidth shows ingress/egress/type in KB

• Database shows number/type

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

… … … … … …