introduction to sql storage and azure storage

An Introduction to the SQL Platform in the Cloud :

An Introduction to the SQL Platform in the Cloud Dr. Eduardo Castro Mart ínez Microsoft MVP ecastro@mswindowscr.org http://comunidadwindows.org http://ecastrom.blogspot.com

Session Objectives:

Session Objectives Provide a big-picture view of the SQL Server in the Cloud Provide a big-picture view of the Windows Azure platform Describe typical scenarios for using the Windows Azure platform

An Overview of the Windows Azure Platform:

An Overview of the Windows Azure Platform

Defining Cloud Computing Two broad categories:

Defining Cloud Computing Two broad categories Cloud applications Often called Software as a Service ( SaaS ) Cloud platforms Such as the Windows Azure platform Cloud Application Users Developers Cloud Platform

The Windows Azure Platform:

The Windows Azure Platform SQL Azure Windows A zure Cloud Applications Windows Azure AppFabric On-Premises Applications Others Windows

Windows Azure An illustration:

… Windows Azure An illustration Fabric Compute Storage Config Application SQL Azure Windows Az ure Applications Applications Others Windows Windows Azure AppFabric

Windows Azure Compute Service A closer look:

Fabric Storage VMs VMs Windows Azure Compute Service A closer look … Load Balancer HTTP/ HTTPS IIS Web Role Instance Worker Role Instance Compute Application

Windows Azure Fabric:

Fabric Controller Fabric Agent Storage Fabric Agent Web Role Instance Worker Role Instance Windows Azure Fabric

Windows Azure Storage Service:

Windows Azure Storage Service

Windows Azure Storage Service A closer look:

Windows Azure Storage Service A closer look Fabric … Compute Application Storage Blobs Tables Queues HTTP/ HTTPS

Fundamental Storage Abstractions:

Fundamental Storage Abstractions Tables – Provide structured storage. A Table is a set of entities, which contain a set of properties Queues – Provide reliable storage and delivery of messages for an application Blobs – Provide a simple interface for storing named files along with metadata for the file Drives – Provides durable NTFS volumes for Windows Azure applications to use (new) 11

Windows Azure Storage Tables: Strengths:

Windows Azure Storage Tables: Strengths Massive scalability By effectively allowing scale-out data Perspective: Applied to the right problem, Windows Azure Tables are a beautiful thing But they’re not the optimal solution for a majority of data storage scenarios Amazon, Google, and others provide similar cloud storage mechanisms All of these can be seen as part of the NOSQL movement

Windows Azure Storage Points of interest:

Windows Azure Storage Points of interest Storage types: Blobs: store binary data Tables: provide scale-out, entity-based storage Not relational tables Queues: allow message-based communication Access: Data is grouped into storage accounts Data can be accessed by: Windows Azure apps Other on-premises or cloud apps

Windows Azure Storage A closer look at blobs:

Windows Azure Storage A closer look at blobs Options for accessing blobs: From Windows Azure applications or other applications: via RESTful requests From Windows Azure applications: via Windows Azure Drives Drives make a blob look like an NTFS file system to the application Blobs can also be used with the Windows Azure Content Delivery Network (CDN) For faster access from distributed locations to frequently accessed content

Windows Azure Storage A closer look at tables:

Windows Azure Storage A closer look at tables Name Type Value Table . . . Table Table Storage Accounts Entity . . . Entity Entity Property Property Property

Windows Azure Tables:

Windows Azure Tables Provides Structured Storage Massively Scalable Tables Billions of entities (rows) and TBs of data Can use thousands of servers as traffic grows Highly Available & Durable Data is replicated several times Familiar and Easy to use API ADO.NET Data Services – .NET 3.5 SP1 .NET classes and LINQ REST – with any platform or language 16

Windows Azure Storage Tables: Challenges:

Windows Azure Storage Tables: Challenges No SQL A new approach for developers to learn No aggregates Access via REST You can’t use ordinary ADO.NET An unfamiliar structure for data You can’t easily move relational data in or out Supporting services are scarce, e.g., reporting No schema And no views

Windows Azure Storage A closer look at queues:

Queue Windows Azure Storage A closer look at queues Web Role ASP.NET, WCF, etc. Worker Role m ain() { … } 1) Receive work 2) Put message in queue 3) Get message from queue 4) Do work 5) Delete message from queue

Windows Azure Tables:

Windows Azure Tables

Windows Azure Tables:

Windows Azure Tables Provides Structured Storage Massively Scalable Tables Billions of entities (rows) and TBs of data Can use thousands of servers as traffic grows Highly Available & Durable Data is replicated several times Familiar and Easy to use API ADO.NET Data Services – .NET 3.5 SP1 .NET classes and LINQ REST – with any platform or language 20

Table Storage Concepts :

Table Storage Concepts Entities Tables Accounts moviesonline Users Movies Email =… Name = … Email =… Name = … Genre =… Title = … Genre =… Title = … 21

Table Data Model:

Table Data Model Table A storage account can create many tables Table name is scoped by account Set of entities (i.e. rows) Entity Set of properties (columns ) Required properties PartitionKey , RowKey and Timestamp 22

Required Entity Properties:

Required Entity Properties PartitionKey & RowKey Uniquely identifies an entity Defines the sort order Use them to scale your application Timestamp Read only Optimistic Concurrency 23

PartitionKey And Partitions:

PartitionKey And Partitions PartitionKey Used to group entities in the table into partitions A table partition All entities with same partition key value Unit of scale Control entity locality Row key provides uniqueness within a partition 24

Partitions and Partition Ranges:

PartitionKey (Category) RowKey (Title) Timestamp ReleaseDate Action Fast & Furious … 2009 Action The Bourne Ultimatum … 2007 … … … … Animation Open Season 2 … 2009 Animation The Ant Bully … 2006 PartitionKey (Category) RowKey (Title) Timestamp ReleaseDate Comedy Office Space … 1999 … … … … SciFi X-Men Origins: Wolverine … 2009 … … … … War Defiance … 2008 PartitionKey (Category) RowKey (Title) Timestamp ReleaseDate Action Fast & Furious … 2009 Action The Bourne Ultimatum … 2007 … … … … Animation Open Season 2 … 2009 Animation The Ant Bully … 2006 … … … … Comedy Office Space … 1999 … … … … SciFi X-Men Origins: Wolverine … 2009 … … … … War Defiance … 2008 Partitions and Partition Ranges Server B Table = Movies [Comedy- Western) Server A Table = Movies [Action - Comedy) 25 Server A Table = Movies

Table Operations:

Table Operations Table Create Query Delete Entities Insert Update Merge – Partial Update Replace – Update entire entity Delete Query Entity Group Transaction (new)

Table Schema:

Table Schema Define the schema as a .NET class 27 [ DataServiceKey ( " PartitionKey " , " RowKey " )] public class Movie { /// <summary> /// Category is the partition key /// </summary> public string PartitionKey { get ; set ; } /// <summary> /// Title is the row key /// </summary> public string RowKey { get ; set ; } public DateTime Timestamp { get ; set ; } public int ReleaseYear { get ; set ; } public string Language { get ; set ; } public string Cast { get ; set ; } }

Table SDK Sample Code:

Table SDK Sample Code 28 StorageCredentialsAccountAndKey credentials = new StorageCredentialsAccountAndKey ( “ myaccount " , “ myKey " ); string baseUri = "http://myaccount.table.core.windows.net" ; CloudTableClient tableClient = new CloudTableClient ( baseUri , credentials); tableClient .CreateTable ( “Movies" ); TableServiceContext context = tableClient. GetDataServiceContext (); CloudTableQuery < Movie > q = ( from movie in context.CreateQuery < Movie >( “Movies" ) where movie.PartitionKey == “ Action " && movie.RowKey == "The Bourne Ultimatum" select movie). AsTableServiceQuery < Movie >(); Movie movieToUpdate = q.FirstOrDefault (); // Update movie context.UpdateObject ( movieToUpdate ); context.SaveChangesWithRetries (); // Add movie context.AddObject (new Movie( “ Action " , movieToAdd )); context.SaveChangesWithRetries ();

Key Selection: Things to Consider:

Key Selection: Things to Consider Scalability Distribute load as much as possible Hot partitions can be load balanced PartitionKey is critical for scalability Query Efficiency & Speed Avoid frequent large scans Parallelize queries Entity group transactions (new) Transactions across a single partition Transaction semantics & Reduce round trips 29

Key Selection: Case Study 1:

Key Selection: Case Study 1 Table for listing all movies Home page lists movies based on chosen category 30

Movie Listing – Solution 1:

Movie Listing – Solution 1 Why do I need multiple PartitionKeys ? Account name as Partition Key Movie title as RowKey since movie names need to be sorted Category as a separate property Does this scale? 31 PartitionKey (Account name) RowKey (Title) Category … moviesonline 12 Rounds Action … moviesonline A Bug’s Life Animation … 100,000,000 more rows … … … moviesonline Office Space Comedy … moviesonline Platoon War … 50,000,000 more rows … … … moviesonline WALL-E Animation …

Movie Listing – Solution 1:

Server A Movie Listing – Solution 1 Single partition - Entire table served by one server All requests served by that single server Does not scale PartitionKey (Account name) RowKey (Title) Category … moviesonline 12 Rounds Action … moviesonline A Bug’s Life Animation … 100,000,000 more rows … … … moviesonline Office Space Comedy … moviesonline Platoon War … 50,000,000 more rows … … … moviesonline WALL-E Animation … Client Client Request Request Request Request 32

Movie Listing – Solution 2:

Movie Listing – Solution 2 PartitionKey (Category) RowKey (Title) Action Fast & Furious … 10000 more Action movies Action The Bourne Ultimatum … 100000 more Action & Animation movies Animation Open Season 2 … 100000 more Animation movies Animation The Ant Bully Comedy Office Space … 1000000 more Comedy & SciFi movies SciFi Star Trek … 100000 more SciFi & War movies … 100000 more War movies War Defiance All movies partitioned by category Allows system to load balance hot partitions Load distributed Better than single partition Client Client Request Server A Request Request Request 33 Server B Request Request Request Request PartitionKey (Category) RowKey (Title) Action Fast & Furious … 10000 more Action movies Action The Bourne Ultimatum … 100000 more Action & Animation movies Animation Open Season 2 … 100000 more Animation movies Animation The Ant Bully Comedy Office Space … 1000000 more Comedy & SciFi movies SciFi Star Trek … 100000 more SciFi & War movies … 100000 more War movies War Defiance

Key Selection: Case Study 2:

Key Selection: Case Study 2 Log every transaction into a table for diagnostics Scale Write Intensive Scenario Logs can be retrieved for a given time range 34

Logging - Solution 1:

Logging - Solution 1 Timestamp as Partition Key Looks like an obvious choice It is not a single partition as time moves forward Append only Requests to single partition range Load balancing does not help Server may throttle PartitionKey (Timestamp) Properties 2009-11-15 02:00:01 … 2009-11-15 02:00:11 … 100000 more rows … 2009-11-17 05:40:01 … 2009-11-17 05:40:01 … 80000 more rows … 2009-11-17 12:30:00 … 2009-11-17 12:30:01 … Applications Client Request Server A Request 2009-11-17 12:30:01 … Request 2009-11-17 12:30:02 … Request 2009-11-17 12:30:03 … Server B 35

Logging Solution 2 - Distribute "Append Only”:

Server A Server B PartitionKey ( ID_Timestamp ) Properties 01_2009-10-12 05:10:00 … … … 100000 more rows … 09_2009-11-15 12:31:00 … … … 20000000 more rows … 10_2009-10-05 05:10:10 … 5000000 more rows … … … 900000 more rows … 19_2009-11-17 12:20:02 … Applications Client Request Request Request Request Logging Solution 2 - Distribute "Append Only ” Prefix timestamp such that load is distributed Id of the node logging Hash into N buckets Write load is now distributed Better throughput To query logs in time range Parallelize it across prefix values 15_2009-11-17 12:30:01 … 09_2009-11-17 12:30:22 … 19_2009-11-17 12:30:10 … 01_2009-11-17 12:30:01 … 36

Key Selection: Query Efficiency & Speed:

Key Selection: Query Efficiency & Speed Select keys that allow fast retrieval Reduce scan range Reduce scan frequency 37

Slide 38:

Single Entity Query Server A PartitionKey (Category) RowKey (Title) Action Fast & Furious … 10000 more Action movies Action The Bourne Ultimatum … 100000 more Action & Animation movies Animation Open Season 2 … 100000 more Animation movies Animation The Ant Bully Comedy Office Space … 1000000 more Comedy & SciFi movies SciFi Star Trek … 100000 more SciFi & War movies … 100000 more War movies War Defiance Client Server B W here PartitionKey =‘ SciFi ’ and RowKey = ‘Star Trek’ Efficient processing No continuation tokens 38 Request Result

Slide 39:

Client Server A Server B Table Scan Query Request PartitionKey (Category) RowKey (Title) Rating Action Fast & Furious 5 … 999 more movies rated > 4 … … Action and Anim. movies here with rating < 4 … Animation A Bug’s life 2 … 100 more movies < 4 here … Animation The Ant Bully 3 Comedy Are we there yet? 2 … More movies here … Comedy Office Space 5 … 800000 more movies here … Drama A Beautiful Mind 5 … 1200000 more movies here … War Defiance 4 Cont. Select * from Movies where Rating > 4 Returns Continuation token 1000 movies in result set Partition range boundary Serial Processing: Wait for continuation token before proceeding Request Cont. Cont. Request Cont. Cont. 39 Returns 1000 movies Partition range boundary hit Return continuation

Slide 40:

Client Server A Server B Make Scans Faster Request PartitionKey (Category) RowKey (Title) Rating Action Fast & Furious 5 … More movies here … Comedy Office Space 5 … More movies here … Documentary Planet Earth 4 … More movies here Drama Seven Pounds 4 Horror Saw 5 3 … More movies here … Music 8 Mile 2 … More movies here … SciFi Star Trek 5 … More movies here … Cont. Split “Select * from Movies where Rating > 4” into Where PartitionKey >= “A” and PartitionKey < “D” and Rating > 4 Where PartitionKey >= “D” and PartitionKey < “I” and Rating > 4 Etc. Execute in parallel Each query handles continuation Cont. 40 Request Request Cont.

Query Speed:

Query Speed Fast Single PartitionKey and RowKey with equality Medium Single partition but a small range for RowKey Entire partition or table that is small Slow Large single scan Large table scan “OR” predicates on keys => no query optimization => results in scan Expect continuation token for all except in 1 41

Make Queries Faster:

Make Queries Faster Large Scans Split the range and parallelize queries Create and maintain own views that help queries “Or” Predicates Execute individual query in parallel instead of using “OR” User Interactive Cache the result to reduce scan frequency 42

Slide 43:

Expect Continuation Tokens – Seriously! Maximum of 1000 rows in a response At the end of partition range boundary Maximum of 5 seconds to execute the query 43

Entity Group Transactions (EGT) (new):

Entity Group Transactions (EGT) (new) Atomically perform multiple insert/update/delete over entities in same partition in a single transaction Maximum of 100 commands in a single transaction and payload < 4 MB ADO.Net Data Service Use SaveChangesOptions.Batch 44

Key Selection: Entity Group Transaction:

Key Selection: Entity Group Transaction Case Study Maintain user account information Account ID, User Name, Address, Number of rentals Maintain information of checked out rentals Account ID, Movie Title, Check out date, Due date Solution 1 – Maintain two tables – Users & Rentals Handle Cross table consistency Insert into Rentals table succeeds Update to Users table fails Queue to maintain consistency PartitionKey ( UserId ) RowKey () Timestamp ReleaseDate Action Assault on Precinct 13 … 2005 … … … … Action Face off … 1997 Action Fast & Furious … 2009 … … Hundreds of thousands of movies here … … Action Four Brothers … 2005 Animation Open Season 2 … 2009 Animation The Ant Bully … 2006 45

Solution 2:

Solution 2 Store Account Information and Rental details in same table Maintain same PartitionKey to enforce transactions Account ID as PartitionKey Update total count and Insert new rentals using Entity Group Transaction Prefix RowKey with “Kind” code: A = Account, R = Rental Row key for account info: [Kind Code ]_[ AccountId ] Row Key for rental info: [Kind Code]_[Title ] Rental Properties not set for Account row and vice versa PartitionKey ( AccountID ) RowKey (Kind_*) Kind TotalRentals Name Address CheckOutOn Title DueOn … … … … … … … … … Sally A_Sally Account 8 Sally Field Ann Arbor , MI Sally R_Jaws Rental 2009/11/16 Jaws 2009/11/20 Sally R_Taxi Rental 2009/11/16 Taxi 2009/11/20 … … … … … … … … … 46

Best Practices:

Best Practices Select PartitionKey and RowKey that help scale Efficient for frequently used queries Supports batch transactions Distributes load Distribute “Append only” patterns using prefix to PartitionKey Always Handle continuation tokens Client can maintain their own cache/views instead of frequent scans Future Feature - Secondary Index Execute parallel queries instead of “OR” predicates Implement back-off strategy for retries 47

Windows Azure Queues:

Windows Azure Queues Queue are performance efficient, highly available and provide reliable message delivery Simple, asynchronous work dispatch Programming semantics ensure that a message can be processed at least once Access is provided via REST 48

Queue Storage Concepts :

Queue Storage Concepts Messages Queues Accounts sally thumbnailjobs traverselinks 128 x 128 http://... 256 x 256 http://... http://... http://... 49

Account, Queues and Messages:

Account, Queues and Messages An account can create many queues Queue Name is scoped by the account A Queue contains messages No limit on number of messages stored in a queue Set a limit for message expiration Messages Message size <= 8 KB To store larger data, store data in blob/entity storage, and the blob/entity name in the message Message now has dequeue count 50

Queue Operations:

Queue Operations Queue Create Queue Delete Queue List Queues Get/Set Queue Metadata Messages Add Message (i.e. Enqueue Message) Get Message(s) (i.e. Dequeue Message) Peek Message(s) Delete Message 51

Queue Programming Api:

Queue Programming Api 52 CloudQueueClient queueClient = new CloudQueueClient ( baseUri , credentials); CloudQueue queue = queueClient.GetQueueReference ( "test1" ); queue. CreateIfNotExist (); // MessageCount is populated via FetchAttributes queue. FetchAttributes (); CloudQueueMessage message = new CloudQueueMessage ( "Some content" ); queue. AddMessage (message); message = queue. GetMessage ( TimeSpan .FromMinutes (10) /*visibility timeout*/ ); //Process the message here … queue. DeleteMessage (message);

Removing Poison Messages:

2 1 1 1 C 1 C 2 Removing Poison Messages 1 1 2 1 3 4 0 Producers Consumers P 2 P 1 3 0 2. GetMessage(Q, 30 s)  msg 2 1. GetMessage(Q, 30 s)  msg 1 1 1 2 1 53 1 0 2 0

Removing Poison Messages:

C 1 C 2 Removing Poison Messages 3 4 0 Producers Consumers P 2 P 1 1 1 2 1 2. GetMessage(Q, 30 s)  msg 2 3. C2 consumed msg 2 4. DeleteMessage(Q, msg 2) 7. GetMessage (Q, 30 s)  msg 1 1. GetMessage(Q, 30 s)  msg 1 5. C 1 crashed 1 1 2 1 6. msg1 visible 30 s after Dequeue 3 0 54 1 2 1 1 1 2

Removing Poison Messages:

C 1 C 2 Removing Poison Messages 3 4 0 Producers Consumers P 2 P 1 1 2 2. Dequeue(Q, 30 sec)  msg 2 3. C2 consumed msg 2 4. Delete(Q, msg 2) 7. Dequeue(Q, 30 sec)  msg 1 8. C2 crashed 1. Dequeue(Q, 30 sec)  msg 1 5. C 1 crashed 10. C 1 restarted 11. Dequeue(Q, 30 sec)  msg 1 12. DequeueCount > 2 13. Delete (Q, msg1) 1 2 6. msg1 visible 30s after Dequeue 9. msg1 visible 30s after Dequeue 3 0 55 1 3 1 2 1 3

Best Practices & Summary:

Best Practices & Summary Make message processing idempotent No need to deal with failures Do not rely on order Invisible messages result in out of order Use Dequeue count to remove poison messages Enforce threshold on message’s dequeue count Use message count to dynamically increase/reduce workers Use blob to store message data with reference in message Messages > 8KB Batch messages Garbage collect orphaned blobs 56

Storage Summary:

Storage Summary 57 Table Scalable & Reliable Structured Storage System Partitioning is critical to scalability Entity Group Transactions (new) Queue Scalable & Reliable Messaging System Dequeue count returned with message (new) Use back-off strategy on retries Official Storage Client Library (new)

An Introduction to the SQL Platform in the Cloud :

An Introduction to the SQL Platform in the Cloud Dr. Eduardo Castro Mart ínez Microsoft MVP ecastro@mswindowscr.org http://comunidadwindows.org http://ecastrom.blogspot.com

What is “SQL Azure”?:

What is “SQL Azure”?

Review – Conceptual model:

Review – Conceptual model Subscription Used to map service usage to the billing instrument Users may have many subscriptions Logical Server Akin to SQL Server Instance Unit of Geo-Location & Billing 1:1 Subscription & server User Database Restricted T-SQL surface area Additional catalog views provided e.g. sys.billing , sys.firewall_rules , etc

SQL Azure A relational DB in the cloud:

SQL Services .NET Services Windows Az ure Live Services Applications Applications Others Windows Mobile Windows Vista/XP Windows Server SQL Azure A relational DB in the cloud SQL Azure Database Others (Future) Data Hub Relational database as a service Highly available, automatically maintained Extension of the SQL Server Data Platform

SQL Azure Database :

SQL Azure Database - Based on SQL Server 2008 R2 engine Use same tools and data access frameworks Six global datacenters High Availability & Redundancy Reads are completed at the primary Writes are replicated to a quorum of secondaries Replica 1 Replica 2 Replica 3 DB Single Logical Database Multiple Physical Replicas Single Primary Multiple Secondaries

Extending SQL Server Data Platform to the Cloud:

Extending SQL Server Data Platform to the Cloud Database Data Sync Reference Data Symmetric Programming Model Data Hub Aggregation Initial services – core RDBMS capabilities with SQL Azure Database, Data Sync Future Offerings Additional data platform capabilities: Reporting, BI New services: Reference Data

The New SQL Data Services:

The New SQL Data Services Familiar SQL Server relational model Uses existing APIs & tools Built for the Cloud with availability and scale Accessible to all from PHP, Ruby, and Java Clear Feedback: “I want a database in the Cloud” Focus on combining the best features of SQL Server running at scale with low friction

The Evolution of SDS:

The Evolution of SDS Evolves SDS Next 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+REST TDS * Client access enabled using TDS for ODBC, ADO.Net, OLEDB, PHP-SQL, Ruby, … Data Center ODBC, OLEDB, ADO.Net PHP, Ruby, … SDS Current REST/SOAP + ACE Model Web App REST Client Windows Azure Browser Application Application REST Client HTTP HTTP+REST Data Center Cloud

SQL Azure:

SQL Azure Windows Az ure Applications Applications Others Windows SQL Azure Database Others (Future) SQL Azure Windows Azure AppFabric

SQL Azure Database An Illustration:

SQL Azure Database Others (Future) SQL Azure Database An Illustration Database Database Database SQL Azure Database TDS Application

SQL Azure Database Using one or multiple databases:

Database Database Database Application Application Database SQL Azure Database SQL Azure Database Using one or multiple databases Max database size is 50 GB

SQL Azure Network Topology:

SQL Azure Network Topology Application Internet Azure Cloud LB TDS (tcp) TDS (tcp) TDS (tcp) Applications use standard SQL client libraries: ODBC, ADO.Net , PHP, … Load balancer forwards ‘sticky’ sessions to TDS protocol tier Security Boundary SQL SQL SQL SQL SQL SQL Gateway Gateway Gateway Gateway Gateway Gateway Gateway: TDS protocol gateway, enforces AUTHN/AUTHZ policy; proxy to CloudDB Scalability and Availability: Fabric, Failover, Replication, and Load balancing

TDS Gateway:

TDS Gateway TDS Listener Capability negotiation TDS Packet inspection Security Logical->Physical mapping via metadata catalog Enabler for multi-tenet capabilities Isolation layer

TDS Gateway Layering:

TDS Gateway Layering Gateway Process TDS Endpoint AdminSvc Endpoint Protocol Parser Provisioning Endpoint Business Logic Services Connection Mgmt SQL SQL SQL SQL SQL SQL Scalability and Availability: Fabric, Failover, Replication, and Load balancing

Provisioning :

Provisioning Subscription Coordinated across all Azure services Executed in parallel w/retries Server May occur between data centers Point where Geo-location is established Database Always occurs within a single data center Cross node operations executed during this process e.g. add new db to sys.databases on the master

Server Provisioning:

Server Provisioning Driven by administrator Portal Provision request is sent to Gateway Metadata catalog entry created DNS record (CNAME) created within LiveDNS service Master DB created On completion metadata catalog updated

SQL Azure Server Provisioning:

Datacenter (Sub-Region) SQL Azure Server Provisioning Customer Browser Portal LB 1 Front-end Node Live DNS Cluster Live DNS Svc 2 4 Gateway Backend Node Mgmt. Services SQL Server Fabric Backend Node Backend Node Mgmt. Services SQL Server Fabric Mgmt. Services SQL Server Fabric Front-end Node Gateway Front-end Node Admin Portal Front-end Node Admin Portal 3 Gateway LB 5 6 7

Database Provisioning:

Database Provisioning Gateway performs stateful TDS packet inspection Picks out subset of messages Parses out args for create database Makes entry into Gateway metadata catalog Unused replica set located and reserved Replica set ( UserDB ) is prepped for use Metadata catalog is updated

SQL Azure Database provisioning:

SQL Azure Database provisioning TDS Gateway Front-end Node Protocol Parser Gateway Logic Master Cluster Master Node Master Node Components 1 2 3 5 6 7 Scalability and Availability: Fabric, Failover, Replication, and Load balancing Scalability and Availability: Fabric, Failover, Replication, and Load balancing 4 8 TDS Session Backend Node 1 SQL Instance SQL DB Backend Node 2 SQL Instance SQL DB Backend Node 3 SQL Instance SQL DB

SQL Azure Login Process:

SQL Azure Login Process Login request arrives at the Gateway Gateway locates MasterDb & UserDb replica sets Credentials are validated against MasterDb TDS session is opened to UserDB and requests are forwarded

SQL Azure Login Process:

SQL Azure Login Process TDS Gateway Front-end Node Protocol Parser Gateway Logic Global Partition Map Master Node Master Node Components 1 2 4 5 6 Scalability and Availability: Fabric, Failover, Replication, and Load balancing Scalability and Availability: Fabric, Failover, Replication, and Load balancing 3 7 TDS Session Backend Node 1 SQL Instance SQL DB Backend Node 2 SQL Instance SQL DB Backend Node 3 SQL Instance SQL DB 8

Service Resilience:

Service Resilience Provisioning State machines used to coordinate activities across node (and datacenter) boundaries Failed provisioning attempts cleaned automatically after 10 minutes Login Failovers during the login will be transparent (<30 seconds) Metadata catalog refresh occurs automatically Active Session Surface as connection drops (due to state)

Monitoring Service Health:

Monitoring Service Health Metrics Cluster wide performance counters gather key metrics on the service Used to alert Operations to issues before they become a problem Early warning system Code issues Capacity warnings Health Exercises the service routinely looking for problems When issues are encountered runs deep diagnostics Network connectivity at the node level Validate all dependent services (Live DNS, Live ID, etc) Monitoring from other MSFT DC’s Validates accessibility from multiple geographic locations Alerts fired automatically when test jobs fail

Security/Attack Considerations:

Security/Attack Considerations Service Secure channel required (SSL) Denial Of Service trend tracking Packet Inspection Server IP allow list (Firewall) Idle connection culling Generated server names Database Disallow the most commonly attacked user id’s (SA, Admin, root, guest, etc) Standard SQL Authn / Authz mode

What’s New In SQL Azure CTP Enhancements:

What’s New In SQL Azure CTP Enhancements SQL Azure Database SQL Azure Reporting CTP Reporting and BI on SQL Azure databases Based on SQL Server Reporting Services SQL Azure Data Sync CTP2 Synchronize SQL Azure databases Synchronize SQL Azure with on-premises SQL Server Windows Azure Platform – Developer Portal Enhanced user experience and reporting Database manager for SQL Azure

What’s New In SQL Azure CTP Enhancements:

What’s New In SQL Azure CTP Enhancements SQL Azure Database SQL Azure Reporting CTP Reporting and BI on SQL Azure databases Based on SQL Server Reporting Services SQL Azure Data Sync CTP2 Synchronize SQL Azure databases Synchronize SQL Azure with on-premises SQL Server Windows Azure Platform – Developer Portal Enhanced user experience and reporting Database manager for SQL Azure

What’s New In SQL Azure CTP Enhancements:

What’s New In SQL Azure CTP Enhancements SQL Azure Database SQL Azure Reporting CTP Reporting and BI on SQL Azure databases Based on SQL Server Reporting Services SQL Azure Data Sync CTP2 Synchronize SQL Azure databases Synchronize SQL Azure with on-premises SQL Server Windows Azure Platform – Developer Portal Enhanced user experience and reporting Database management

SQL Azure Reporting CTP :

SQL Azure Reporting CTP “Introduction to SQL Azure Reporting” Web Role Reporting Features: Based on SQL Server Reporting Services Interactive and tabular reporting Data visualizations: charts, graphs, mapping, gauges SQL Azure Database data sources Utilizes BI Developer Studio (free) to author reports Export to Excel, PDF, CSV formats Core scenarios: Operational reporting on SQL Azure data Embed reports into Windows Azure or on-premises applications

What’s New In SQL Azure Enhancements:

What’s New In SQL Azure Enhancements SQL Azure Database SQL Azure Reporting CTP Reporting and BI on SQL Azure databases Based on SQL Server Reporting Services SQL Azure Data Sync CTP2 Synchronize SQL Azure databases Synchronize SQL Azure with on-premises SQL Server Windows Azure Platform – Developer Portal Enhanced user experience and reporting Database management

SQL Azure Data Sync CTP2 :

SQL Azure Data Sync CTP2 Elastic Scale Service scales as resources requirements grow No-Code Sync Configuration Easily define data to be synchronized Schedule Sync Choose how often data is synchronized Conflict Handling Handle issues where same data is changed in multiple locations Logging and Monitoring Administration capabilities for tracking data and monitoring potential issues “Introduction to SQL Azure Data Sync” CTP 1 CTP 2

Slide 88:

On Premises Windows Azure Data Sync Service SQL Azure TDS SQL Server Local Agent SQL Server Sync Provider SQL Server Proxy Provider Sync Orchestrator SQL Server Provider Sync Orchestrator HTTPS SQL Azure Data Sync – A Closer Look CTP 2

SQL Azure Data Sync – Example Use Cases:

SQL Azure Data Sync – Example Use Cases Move workloads in stages preserving existing infrastructure Move part of the application and sync its data Meet compliance and regulations Control data synchronized off-premises Enable scale-out read or read/write Multiple synchronized databases for scalability Preserve data – geo replication of data Enable new scenarios Spanning enterprise , cloud and remote offices/retail stores

SQL Azure Data Sync – Roadmap:

SQL Azure Data Sync – Roadmap On-Premises (Headquarters) Sync Sync Remote Offices Data Sync Service For SQL Azure Retail Stores Sync Sync Sync SQL Azure Database Sync Sync Now CTP2 – End of CY10

New SQL Azure Usage Scenarios :

New SQL Azure Usage Scenarios Web Role Reporting Data Sync Fully featured Windows Azure Platform Application

New SQL Azure Usage Scenarios :

New SQL Azure Usage Scenarios Web Role Reporting Data Sync Fully featured Windows Azure Platform Application Data Sync Synchronize multiple on-premises SQL Server databases in different locations Datacenter Corporate Office Branch Office or Retail Geo-replication of data across Windows Azure Platform datacenters Globally available, c loud & on-premises d ata synchronization

Roadmap :

Roadmap Now Visit www.microsoft.com/sqlazure to register for upcoming CTP access 2010 New Windows Azure Developer Portal; Database Manager Limited CTPs of Data Sync CTP2, Reporting CTP Watch for PDC Announcements; visit the SQL Azure blog and website 2011 Backup & Restore SQL Azure Reporting SQL Azure Data Sync

Windows Azure AppFabric Infrastructure in the cloud:

Windows Azure AppFabric Infrastructure in the cloud SQL Azure Windows Az ure Applications Applications Others Windows Service Bus ? Access Control Windows Azure AppFabric

Illustrating Service Bus:

Access Control Service Bus Service Bus Registry 4) Invoke operation on Service Bus endpoint 3) Discover Service Bus endpoint Client (On-premises or cloud) WCF Service (On-premises) 2) Expose Service Bus endpoint Illustrating Service Bus 5) Invoke operation on service endpoint 1 ) Register service endpoint

Windows Azure Platform Pricing A summary (in US dollars):

Windows Azure Platform Pricing A summary (in US dollars ) Compute: $0.12 to $0.96/hour for each role instance depending on instance size Storage: Windows Azure blobs and tables: Data: $0.15/GB per month Access: $0.01/10,000 operations SQL Azure relational: $9.99/month per GB Bandwidth: Americas/Europe: $0.10/GB in, $0.15/GB out Asia/Pacific: $0.30/GB in, $0.45/GB out

Service Level Agreements Key points: Windows Azure compute:

Service Level Agreements Key points: Windows Azure compute When you deploy two or more role instances in different fault and upgrade domains, your Internet-facing roles will have external connectivity at least 99.95% of the time Microsoft will detect within two minutes when a role instance’s process is not running and initiate corrective action

Service Level Agreements Key points: Windows Azure storage:

Service Level Agreements Key points: Windows Azure s torage At least 99.9% of the time, Microsoft will successfully process correctly formatted requests to add, update, read and delete data At least 99.9% of the time, your storage accounts will have connectivity to Microsoft’s Internet gateway

Service Level Agreements Key points: SQL Azure database:

Service Level Agreements Key points: SQL Azure database SQL Azure will maintain a Monthly Availability of 99.9% during a calendar month. Monthly Availability percentage for a specific customer database is the ratio of the time the database was available to customer to the total time in a month. An interval is marked as unavailable if the customer’s attempts to connect to a database are rejected by the SQL Azure gateway.

Using the Windows Azure Platform: Scenarios:

Using the Windows Azure Platform: Scenarios

Applying Azure (1) Some characteristics of Azure-appropriate applications:

Applying Azure (1) Some characteristics of Azure-appropriate applications Apps that need massive scale Example: A Web 2.0 application Apps that need high reliability Example: A Software as a Service ( SaaS ) application Apps with variable load Example: An on-line ticketing application Apps with a short or unpredictable lifetime Example: An app created for a marketing campaign

Applying Azure (2) Some characteristics of Azure-appropriate applications:

Applying Azure (2) Some characteristics of Azure-appropriate applications Apps that do parallel processing Example: A financial modeling application Apps that must fail fast or scale fast Example: Start-ups Apps that don’t fit well in an organization’s data center Example: A business unit that wishes to avoid its IT department Apps that can benefit from external storage Example: An application that archives data

Building on Azure A non-Azure app using Windows Azure storage:

Blobs Building on Azure A non-Azure app using Windows Azure storage SQL Azure On-Premises or Hosted Application

Building on Azure A massively scalable web app exposed on the public Internet:

Web Role Instance Tables Building on Azure A massively scalable web app exposed on the public Internet Users

Building on Azure A massively scalable web app with background processing:

Blobs Queues Tables Users Worker Role Instance Web Role Instance Building on Azure A massively scalable web app with background processing

Building on Azure A web app with relational storage:

Web Role Instance SQL Azure Users Building on Azure A web app with relational storage

Building on Azure A web app using cloud and on-premises data:

Web Role Instance SQL Azure Users Building on Azure A web app using cloud and on-premises data Service Bus On-premises Database

Building on Azure A parallel processing application:

User Blobs Web Role Instance Queues Worker Role Instance Building on Azure A parallel processing application

The Windows Azure Platform:

The Windows Azure Platform AppFabric Developer Experience Use existing skills and tools. Compute Storage Management Relational data Management Connectivity Access control platform CDN

Conclusions:

Conclusions Cloud platforms are here Microsoft is placing a big bet with the Windows Azure platform A new world is unfolding Prepare to be part of it

Related Content:

Related Content Comunidad Windows http://comunidadwindows.org http://www.slideshare.net/ecastrom http://ecastrom.blogspot.com Introducing the Windows Azure Platform http://go.microsoft.com/fwlink/?LinkId=158011 Introducing Windows Azure http://go.microsoft.com/?linkid=9682907

Presentation Sources Resources:

Presentation Sources Resources SQL Azure website: http://www.microsoft.com/sqlazure Blog: http://blogs.msdn.com/sqlazure Dev Center: http://msdn.microsoft.com/windowsazure/sqlazure Frameworks: http://msdn.microsoft.com/data

An Introduction to the Windows Azure Platform:

An Introduction to the Windows Azure Platform Dr. Eduardo Castro Mart ínez Microsoft MVP ecastro@mswindowscr.org http://comunidadwindows.org http://ecastrom.blogspot.com

Slide 114:

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