scaling out web applications with microsoft sql azure database
DESCRIPTION
SVC06. Scaling out Web Applications with Microsoft SQL Azure Database. David Robinson Senior Program Manager Microsoft Corporation. Session Overview. Brief SQL Azure Recap Why scale your workload Application Scaling techniques Considerations when scaling out Integrated Scale-Out Support. - PowerPoint PPT PresentationTRANSCRIPT
Scaling out Web Applications with Microsoft SQL Azure DatabaseDavid RobinsonSenior Program ManagerMicrosoft Corporation
SVC06
Session Overview> Brief SQL Azure Recap> Why scale your workload> Application Scaling techniques> Considerations when scaling out> Integrated Scale-Out Support
SQL Azure Database
Customer Value PropsSelf-provisioning and capacity on demandSymmetry w/ on-premises database
platformAutomatic high-availability and fault-
toleranceAutomated DB maintenance
(infrastructure)Simple, flexible pricing – “pay as you
grow”
Browser
MSDatacenter
SOAP/RESTHTTP/S
SQL Azure Database (Windows Azure Compute)
Windows Azure Compute
T-SQL (TDS)
App Code(ASP.NET)
SQL Server Reporting Server
(on-premises)
Astoria/REST - EDMHTTP/S
- AD Federation (LiveId /.Net Svcs ACS)
SQL AzureDatabase
T-SQL (TDS)
Relational database service
• SQL Server technology foundation• Highly symmetrical• Highly scaled
Database “as a Service” – beyond hosting
Highly scaled out relational database as a service
Why scale your application> Increase an application’s ability to
process and store data> Usually because of heavy resource
consumption> Increased workloads> Increased CPU / IO requirements> Increased storage requirements
Scale-out SupportDatabase and workload partitioning is a classic technique for harnessing the power of many computers to achieve:
• Better price/performance • Levels of throughput not possible with a single machine
Classic scale-out challenges addressed by SQL Azure: Highly available service on top of commodity hardware Zero administration cost of cluster HW, OS and SW Access to elastic pool of resources Pay as you grow No-friction provisioning of databases Create databases without ever running out of HW
Real World ScenariosKelly Blue Book
>Provider of vehicle valuation data>> 13 million visitors to their site per month>2.5 GB Database Size>Data refreshed every week>Replicate data across 5 databases for
increased perf>Increase/Decrease database count based on
demand
customerChris AuldDirector of Strategy & InnovationIntergen
SQL Azure
Castellan
Castellan Venue
DBCastellan
Venue DB’s
Venue 1Partition(
s)
Castellan Venue
DBCastellan
Venue DB’s
Venue 2 Partition(
s)
Castellan Venue
DBCastellan
Venue DB’s
Venue N Partition(
s)One application DB, many venue DB’s – each partitioned in to many parts (40+)
...
Azure Roles
http:// TicketDirect .* Dynamic Worker(tasks
uploaded as blobs)
Partitioner Worker
Azure StorageQueues for communication between clients and roles
-- - --- - --- - --- - -
Tables to record server & partition information
Blobs to store web and worker role resources
Client Applications
Castellan.old (VB6)
Castellan.Azure• Box Office sales• Ticket Printing • System Administration • Venue/Event Management• Partitioning
.Net Service Bus
WCF
On PremiseSQL Server
Castellan Venue
Distributed Cache Worker
MemCache
TicketDirect Architecture
What are your application’s requirements?Storage and Transactional throughput
Storage RequirementsLow High
Tran
sact
iona
l Req
uire
men
tsLo
wH
igh
• Single Database• No Partitioning
• Partitioned Data• Partitioning Based on
Application Requirements (Storage)
• Partitioned Data• Partitioning based on
Application Requirements (IOPS)
• Partitioned Data• Partitioning based on
Application Requirements (IOPS, Storage or both)
Scale Out Patterns> Multiple ‘standard’ scale out patterns> Range – break range into chunks
> Ranges can be variable in size> Good for range based queries> Can suffer from hotspots depending on
workload> Hashing – apply hash to partitioning keys
> Good for distributing values > Poor for range queries (needs full fan-out)> Need to accurately estimate workload
requirements
Fabrikam FishOur Scenario> Online reseller of exotic aquarium and
pond fish> Peak sales periods
> Nov – Dec> May – July
> 726,000 different varieties of fish
Fabrikam’s Schema
Product> Stock table can become
hot> Range Partitioned> Utilizes 1gb databases
Product Implementation
code walkthrough
Fabrikam’s Schema
Product> Stock table can become
hot> Range Partitioned> Utilizes 1gb databases> Certain reference data
replicated to Customer Order databases
Customer Order> Need to evenly distribute
load > Hash Partitioned> Start with 1gb databases> Move to 10gb databases
should additional capacity required
Customer Order Implementation
code walkthrough
Application PartitioningConsiderations> Schema Design & Management> Reference Data & Synchronization> Request Routing> Fan Out Queries
Schema Design & Management> Schema design should avoid cross
database joins and transactions> DDL should be upgrade resilient and
idempotent> Application code should either:
> Support multiple schemas during an upgrade
> Stop processing requests during an upgrade
Resilient DDLIF OBJECT_ID('dbo.tbl_directory') IS NULLBEGIN
CREATE TABLE dbo.tbl_directory(
…)
ENDGOIF NOT EXISTS
(SELECT * FROM SYSCOLUMNSWHERE ID = OBJECT_ID('dbo.tbl_directory') AND NAME = 'last_updated')
ALTER TABLE dbo.tbl_directory ADDlast_updated DATETIME NULL
GO
Request Routing> Application needs to be “Partition”
aware> Partition is a unit of transactional
consistency> Multiple partitions are independent of
each other> Function is used to locate a partition
Reference Data & Synchronization> Avoid Cross Database Joins> Replicate Reference Data for
Performance Reasons> Option 1 Sync Framework
> Works Great!!!> Host in Azure Worker Role
> Option 2 – Manual Update Scripts> Manual operations task> How frequent do these updates occur?> If frequent is it really reference data?
Fan Out Queries> Issue query to multiple databases in
parallel and aggregate the results> Use multiple connections and
multithreading for increased performance
> Useful in many scenarios
>>FUTUREComing Scale-out Support
Better tooling for developers and administrators:o Dynamic database partition splitso Ability to merge database partitionso Improved schema management across database
partitions
Database Features for scale-out application patterns:o Connection managemento Fan-out query support
Summary> Brief SQL Azure Recap> Scaling out provides virtually
unlimited storage and better performance
> Range and Hash Scale-Out Patterns> Considerations when scaling out
> Schema Design and Management> Request Routing> Reference Data> Fan Out Queries
> Future Scale-Out Support
Must See SQL Azure Sessions> Microsoft SQL Azure Database: Under
the Hood (SVC12)> The Future of Database Development
with SQL Azure (SVC27)> Using the Microsoft Sync Framework
to Connect Apps to the Cloud (SVC23)
YOUR FEEDBACK IS IMPORTANT TO US! Please fill out session evaluation
forms online atMicrosoftPDC.com
Learn More On Channel 9> Expand your PDC experience through
Channel 9
> Explore videos, hands-on labs, sample code and demos through the new Channel 9 training courses
channel9.msdn.com/learnBuilt by Developers for Developers….
© 2009 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.