Scaling out Web Applications with Microsoft SQL Azure Database

1. SVC06 Scaling out Web Applications with Microsoft SQL Azure Database David Robinson Senior Program Manager Microsoft Corporation

2. Session Overview • Brief SQL Azure Recap • Why scale your workload • Application Scaling techniques • Considerations when scaling out • Integrated Scale-Out Support

3. SQL Azure Database Highly scaled out relational database as a service • Relational database service • SQL Server technology foundation • Highly symmetrical • Highly scaled • Database “as a Service” – beyond hosting Browser SQL Azure Database (Windows Azure Compute) SOAP/REST HTTP/S Astoria/REST - EDM HTTP/S App Code (ASP.NET) SQL Server Reporting Server (on-premises) • Customer Value Props • Self-provisioning and capacity on demand • Symmetry w/ on-premises database platform • Automatic high-availability and fault-tolerance • Automated DB maintenance (infrastructure) • Simple, flexible pricing – “pay as you grow” Windows Azure Compute T-SQL (TDS) T-SQL (TDS) SQL Azure Database MS Datacenter - AD Federation (LiveId /.NetSvcs ACS)

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

5. Scale-out Support • Database 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

6. Real World Scenarios Kelly 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

7. customer Chris Auld Director of Strategy & Innovation Intergen

8. TicketDirect Architecture Client Applications Azure Roles .Net Service Bus • Castellan.Azure • Box Office sales • Ticket Printing • System Administration • Venue/Event Management • Partitioning WCF Distributed Cache Worker MemCache http:// TicketDirect .* Dynamic Worker (tasks uploaded as blobs) PartitionerWorker On PremiseSQL Server Azure Storage -- - --- - - Blobs to store web and worker role resources Tables to record server & partition information Queues for communication between clients and roles -- - --- - - Castellan Venue Castellan.old (VB6) SQL Azure Castellan Venue DB Castellan Venue DB Castellan Venue DB Castellan Venue DB’s Castellan Venue DB’s Castellan Venue DB’s Castellan Venue 1 Partition(s) Venue 2 Partition(s) Venue N Partition(s) ... One application DB, many venue DB’s – each partitioned in to many parts (40+)

9. What are your application’s requirements?Storage and Transactional throughput • Partitioned Data • Partitioning based on Application Requirements (IOPS) • Partitioned Data • Partitioning based on Application Requirements (IOPS, Storage or both) High Transactional Requirements • Single Database • No Partitioning • Partitioned Data • Partitioning Based on Application Requirements (Storage) Low Low Storage Requirements High

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

11. Fabrikam FishOur Scenario • Online reseller of exotic aquarium and pond fish • Peak sales periods • Nov – Dec • May – July • 726,000 different varieties of fish

12. Fabrikam’s Schema Product • Stock table can become hot • Range Partitioned • Utilizes 1gb databases

13. Product Implementation code walkthrough

14. Fabrikam’s Schema Product Customer Order Need to evenly distribute load Hash Partitioned Start with 1gb databases Move to 10gb databases should additional capacity required • Stock table can become hot • Range Partitioned • Utilizes 1gb databases • Certain reference data replicated to Customer Order databases

15. Customer Order Implementation code walkthrough

16. Application PartitioningConsiderations • Schema Design & Management • Reference Data & Synchronization • Request Routing • Fan Out Queries

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

18. Resilient DDL • IF OBJECT_ID('dbo.tbl_directory') IS NULL • BEGIN • CREATE TABLE dbo.tbl_directory • ( • … • ) • END • GO • IF NOT EXISTS • (SELECT * FROM SYSCOLUMNS • WHERE ID = OBJECT_ID('dbo.tbl_directory') • AND NAME = 'last_updated') • ALTER TABLE dbo.tbl_directory ADD • last_updated DATETIME NULL • GO

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

20. 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?

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

22. Coming Scale-out Support • Better tooling for developers and administrators: • Dynamic database partition splits • Ability to merge database partitions • Improved schema management across database partitions • Database Features for scale-out application patterns: • Connection management • Fan-out query support

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

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

25. YOUR FEEDBACK IS IMPORTANT TO US! Please fill out session evaluation forms online at MicrosoftPDC.com

26. 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/learn Built by Developers for Developers….