SQL Services: Tips and Tricks for High-Throughput Data-Driven Applications

2. SQL Services: Tips and Tricks for High-Throughput Data-Driven Applications  David Robinson Senior Program Manager Microsoft Corporation

3. Our MissionAnd yes, we need to accept it… • Extremely scalable • High performing • Manage large volumes of data • Low CapEx/OpEx • Silverlight 2.0 Develop a news posting application that has the following characteristics And… You have no hardware budget

4. We Can’t Let Our Fearless Leader Down

5. AgendaHow will we accomplish our mission? We Need • Data modeling and partitioning • Parallel query processing • Concurrency • Utilizing a middle tier • Support for batch processing

6. Azure Services Platform Microsoft SharePoint Services Microsoft Dynamics CRM Services

7. Unit ofgeo-location and billing Tied toDNS name Collectionof Containers Data Model And ACE Concepts Authority Container Entity • Unit of Consistency • Scope for Query and Update • Collectionof Entities • Unit of Storage • Property Bagof Name/Value pairs • No Schema Required

8. Data Modeling and PartitioningIt’s not much different than what you are used to • Static System Data (low # of deltas) • Lookups • Static Content • Tags • User Data (medium # of deltas) • User Profiles • User Preferences • Application Data (high # of deltas) • Content Postings • Wikis • Comments • User to User Interactions

9. Data Modeling and Partitioning Entities and kinds Our application requires more RDBMS features like JOINS, TAKE, and ORDER BY Comment Posting Rating Tags BONUS!!!All fields are automatically indexed

10. Join in SQL • select p.* • from Posting p, • PostingTag t, • Tag m • where p.Id = t.PostingId and • t.TagId = m.Id and • m.Tag = "STRATA"

11. Join in SLINQ • from p in entities.OfKind(“Posting”) • from t in entities.OfKind(“Tag”) • from m in entities.OfKind(“PostingTag”) • where • p.Id == t.Properties["PostingId"] && • t.Properties["TagId"] == m.Id && • m.Properties["Tag"] == “AZURE" • select p

12. Data Modeling and PartitioningHow does SQL Data Services store our data? Containers are scoped to individual backend nodes General Rule of Thumb Spread your data across multiple containers for best performance

13. Data Modeling and PartitioningHow does SQL Data Services store our data? • Static System Data (1 Container) • User Data (36 Containers) Application Data – Remaining Containers

14. Data Modeling and PartitioningTakeaways • Containers are scoped to nodes in the SDS backend • Spread your data out amongst many containers for best performance • Modeling your data via Entities is not much different than modeling with a traditional RDBMS

15. AgendaHow will we accomplish our mission? ü • Data modeling and partitioning • Parallel Query Processing

16. Parallel Query Processing How does data access in SQL Data Services work? All queries are processed via the SDS Front Ends General Rule of Thumb Process your queries in parallel for best performance

17. demo Parallel Query Processing

18. Parallel Query ProcessingTakeaways • SQL Data Services operations are processed by the front end servers • For best performance multithread your calls to take advantage of the many front end servers

19. AgendaHow will we accomplish our mission? ü • Data modeling and partitioning • Parallel query processing • Concurrency ü

20. ConcurrencyHow do we handle multiple updates? SQL Data Services provides immediate consistency Lets consider when two users attempt to update the same record… Posting 123 ViewCount = 134 ViewCount++ ViewCount++ This is easily handled in SQL Data Services

21. Concurrent Operations in SDS • SOAP • Scope myEntityScope = new Scope(); • myEntityScope.EntityId = "AwesomePosting"; • myEntityScope.VersionMatch.Version = 134; • myEntityScope.VersionMatch.MatchType = • VersionMatchType.Match; • REST • WebRequest request = HttpWebRequest.Create(entityUri); • request.Method = "POST"; • request.Headers • [HttpRequestHeader.IfMatch] = "134";

22. Concurrency Key takeaways • SDS provides an immediate consistency model • Use appropriate versioning semantics to prevent unwanted data overwrites • MatchType property in SOAP • IfMatch header in REST

23. AgendaHow will we accomplish our mission? ü • Data modeling and partitioning • Parallel query processing • Concurrency • Caching in our middle tier ü ü

24. Utilizing aMiddle Tier Using a middle tier is nothing new but wait… Don’t forget you have no hardware budget…

25. demo Windows Azure Service Hosting

26. Utilizing a Middle TierKey takeaways • Nothing new to learn to host your services in Windows Azure • By using Windows Azure, your middle tier is located closer to your data • Caching helps combat the latency associated with cloud based data services

27. AgendaHow will we accomplish our mission? ü • Data modeling and partitioning • Parallel query processing • Concurrency • Caching in our middle tier • Support for batch processing ü ü ü

28. Support for Batch ProcessingHow do we queue items to be processed? After someone creates certain types of postings, additional processing needs to be done before posting is made live. This is easily handled with .NET Services

29. demo .NET Service Bus

30. Support for Batch Processing Key takeaways • Large, complex applications can benefit from offline batch processing • .NET Service Bus is an ideal way of enabling queuing and offline processing

31. AgendaHow will we accomplish our mission? ü • Data modeling and partitioning • Parallel query processing • Concurrency • Caching in our middle tier • Support for batch processing ü ü ü ü Mission Accomplished !!! With all these tips and tricks, what can we build?

32. demo Cloud 9

33. Related Sessions

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