COS222 What’s New in Microsoft SQL Azure

2. COS222What’s New in Microsoft SQL Azure David Robinson Senior Program Manager Microsoft Corporation

3. Session Overview • Quick Recap of SQL Azure • What’s New… • New Portal • New Tools • New Features • What’s Coming…

4. Microsoft SQL Azure Relational Database as a Service Managed Service Scale on Demand Innovate Faster • Platform capabilities delivered as a service (Database, Data Sync, Reporting…) • Reduced complexity, greater flexibility • Easy provisioning and deployment • Auto High availability and fault tolerant • Innovate with new data application patterns • Build cloud-based database solutions on a familiar relational model • Build on existing developer skills and familiar Transact-SQL syntax • Database as a utility with pay as you grow scaling • Rely on Business-ready SLAs • Enable multi-tenant solutions • Manage multiple servers

5. Cloud Data ServicesCurrent Capabilities “Dallas” CTP 2010 Launch-PDC09

6. SQL Azure Portal Existing Portal New Portal Silverlight based Clean, intuitive design Integrated experience with other Windows Azure service Wizards to guide you through common tasks Rich filtering & pagination support View all your subscriptions at a single time Database manager (formerly Project ‘Houston’) integrated • ASP.Net based • Not as intuitive as it could be • Disconnected experience with other Windows Azure services • No filtering capabilities • Operate on a single subscription at a time

7. SQL Azure Portal demo

8. SQL Azure Portal • New portal launching this calendar year • Coexistence of both portals through Q1 CY11 • Unified experience across Windows Azure Services • Database Manager (formerly project ‘Houston’) integrated • Rich filtering & pagination support More features to come in 2011 including Reporting Services, visualizations & additional tooling

9. What we have heard.. • “I want a local development environment for SQL Azure” • “How can I be sure my database isn’t using features not yet supported?” • “I want designer support!!!” Introducing Code-Name “Juneau”

10. Codename “Juneau” • High productivity development environment for SQL Azure, SQL Server and Business Intelligence • Connected and offline database development • Edition-aware development environment • Integrated application/database development

11. Codename “Juneau” Application/Database Integration Entity Designer Project Types SQL Server Database Analysis Services Reporting Services Integration Services T-SQL Language Services T-SQL Debugging SQLServer Explorer Database Publish Table Designer PowerBuffer Sandbox Database Static Analysis Schema Compare SQL/CLR

12. Code-Name “Juneau” • Tight integration of language services & engine • Editor-driven debugging • New table designer • “Power Buffer” • Visual control over schema changes • Workflow-appropriate publication/deployment • Cross-project dependencies and language services • Deep EDM/Entity Framework integration • Cross-language debugging

13. Code-Name “Juneau” demo

14. What we have heard… • “I want Reporting Services in the cloud!!!”

15. Introducing SQL Azure Reporting

16. SQL Azure Reporting Developer Agility & Choice • Build reports using familiar design tools • Publish reports to the cloud or embed directly within applications • Use consistent API’s to view, execute and manage reports Extended Reach & Accessibility Secure and reliable access to reports Access reports within an application or via a web browser Render and export to the format desired Elastic Scale & Reliability Off-premises reporting infrastructure lowers TCO Highly available Windows Azure environment Scales to meet the demands of the business as needed

17. SQL Azure Reporting Scenarios • Operational reports over SQL Azure dataCustomers can report over their SQL Azure data, not necessarily with the intent to embed them into an application. • Embedding reports into my Windows or Azure applicationDevelopers can use same patterns and tools they use today to embed reports into their applications in connected mode against Azure RS service.

18. SQL Azure Reporting Services demo

19. What we have heard…. • “How do I store more than 50gb in a single database?” • “How to I architect my application for high scale?” • “How do I scale-out my database?” Introducing SQL Azure Federation Support

20. SQL Azure Federation • Provides Scale-Out Support in SQL Azure • Partition data and load across many servers • Bring computational resources of many to bear • Take advantage of elastic provisioning of databases • Pay as you go benefits • Zero physical administration • Federation includes • Robust Connection Management • Online repartition operations • Split & Merge Databases

21. SQL Azure Federations: Concepts Root • Federation • Represents the data being partitioned • Federation Key • The value that determines the routing of a piece of data • Atomic Unit • All rows with the same federation key value: always together! • Federation Member (aka Shard) • A physical container for a range of atomic units • Federation Root • The database that houses federation directory Federation “CustData” (Federation Key: CustID) Member: PK [min, 100) AUPK=5 AUPK=25 AUPK=35 Member: PK [100, 488) AUPK=105 AUPK=235 AUPK=365 Member: PK [488, max) AUPK=555 AUPK=2545 AUPK=3565

22. Distribution of Data: Concepts • Partitioned • Spread across member machines • Each piece is on one machine (+HA) • Most of the data! • Centralized • Only available in one place • Read and write, but not too much • Reference • Copied to all member machines • Can be read anywhere (reference) • Should not be written to often Config Data1 Data2 Data3 Data4 Data5 ref ref ref ref ref

23. Create Database & Federation Create a Federation use a BIGINT as the Federation Key • CREATE DATABASE SalesDB (EDITION='business',MAXSIZE=50GB) • connect to SalesDB… • CREATE FEDERATION Orders_Federation(RANGE BIGINT)

24. Create Partitioned Table • USE FEDERATION Orders_Federation(0) WITH RESET • CREATE TABLE orders( • customeridbigint, • orderidbigint, • odatedatetime, • primary key (orderid, customerid)) • FEDERATE ON (customerid) • CREATE TABLE orderdetails( • customeridbigint, • orderdetailidbigint, • orderidbigint, • partidbigint, • primary key (orderdetailid, customerid)) • FEDERATE ON (customerid) Establish the customerid as the Federation Key Data with the same customerid in these two tables should be treated as an Atomic Unit

25. Insert Some Data • INSERT INTO orders VALUES(10,1,getdate()), (10,2,getdate()), (11,3,getdate()) • INSERT INTO orders VALUES(110,11,getdate()), (110,12,getdate()), (111,13,getdate()) • INSERT INTO orders VALUES(210,21,getdate()), (210,22,getdate()), (211,23,getdate()) • INSERT INTO orders VALUES(310,31,getdate()), (310,32,getdate()), (311,33,getdate()) • INSERT INTO orderdetails VALUES(10,1,1,1), (10,2,1,2), (10,3,1,10),(10,4,2,100),(10,5,2,1000), (11,6,3,101) • INSERT INTO orderdetails VALUES(110,11,11,1), (110,12,11,2), (110,13,11,10),(110,14,12,100),(110,15,12,1000), (111,16,13,101) • INSERT INTO orderdetails VALUES(210,21,21,1), (210,22,22,2), (210,23,21,10),(210,24,22,100),(210,25,22,1000), (211,26,23,101) • INSERT INTO orderdetails VALUES(310,31,31,1), (310,32,31,2), (310,33,31,10),(310,34,32,100),(310,35,32,1000), (311,36,33,101)

26. Simple SELECT • SELECT * FROM ORDERS • RESULTS • -------- • ORDER 10 • ORDER 110 • ORDER 210 • ORDER 310

27. Federation Root houses the Directory Split Database • USE FEDERATION ROOT WITH RESET • ALTER FEDERATION orders_federation SPLIT AT 100 Split the Federation Member into two at the customerid 100 boundary

28. SELECT with Federation Key • USE FEDERATION Orders_Federation(0) WITH RESET • SELECT * FROM ORDERS • RESULTS • -------- • ORDER 10 • USE FEDERATION Orders_Federation(100) WITH RESET • SELECT * FROM ORDERS • RESULTS • -------- • ORDER 110 • ORDER 210 • ORDER 310 Use Federation Member that contains customerids < 100 Use Federation Member that contains customerids > 100

29. Introducing SQL Azure Data Sync”Synchronization of SQL Server and SQL Azure Databases” CTP1 (Now) SQL Azure Database Sync Sync SQL Azure Data Sync Remote Offices Retail Stores Sync Sync Sync Sync Sync CTP2 On-Premises (Headquarters)

30. SQL Azure Data Sync – Key Features • 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

31. Cloud Data ServicesCurrent Capabilities & Future Investments “Dallas” CTP Beyond 2010 Launch-PDC09

32. Related Content COS327 - Migrating Applications to Microsoft SQL Azure COS223- Introducing SQL Azure Reporting New technology solutions and business opportunities enabled on Windows Azure COS211-IS- Introducing SQL Azure Reporting New solutions and businesses opportunities enabled on Azure COS327 - Prepare for SQL Azure Reporting Services DAT314 - SQL Server Development Tools

33. Session Resources SQL Azure on the web http://www.sqlazure.com Register for SQL Azure Reporting CTP http://connect.microsoft.com/sqlazurectps Register for SQL Azure Data Sync CTP2 http://connect.microsoft.com/sqlazurectps SQL Azure Team Blog http://blogs.msdn.com/sqlazure SQL Azure MSDN Developer Center http://msdn.microsoft.com/en-us/windowsazure/sqlazure/default.aspx

34. “Juneau” Resources • MSDN: http://msdn.microsoft.com/en-us/data/tools.aspx • CTP Forum: http://social.msdn.microsoft.com/Forums/en-US/ssdt/threads • Team Blog: http://blogs.msdn.com/b/ssdt/

35. Session Evaluations Tell us what you think, and you could win! All evaluations submitted are automatically entered into a daily prize draw*  Sign-in to the Schedule Builder at http://europe.msteched.com/topic/list/ * Details of prize draw rules can be obtained from the Information Desk.

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