Microsoft SQL Azure Development Best Practices

1. Required Slide SESSION CODE: COS305 Microsoft SQL Azure Development Best Practices Rick Negrin Lead Program Manager SQL Azure Microsoft Corporation

2. Agenda • Overview • Architecture • Development • Debugging • Deployment • Management • Summary

3. Overview

4. The Windows Azure Platform • Developer Experience • Use existing skills and tools Relational data Management platform Compute Storage Management AppFabric Flexible APIs Connectivity Access control Information Marketplace Reporting & BI Billing & Payments

5. Microsoft SQL Azure • Familiar SQL Server relational model • Uses existing APIs & tools • Friction free provisioning and reduced management • Built for the Cloud with availability and scale Relational Database as a Service for the Windows Azure Platform Focus on combining the best features of SQL Server running at scale with low friction

6. 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 SQL Azure Database (Windows Azure Compute) Browser SOAP/REST HTTP/S Astoria/REST - EDM HTTP/S • 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” App Code (ASP.NET) App Code (ASP.NET) Windows Azure Compute T-SQL (TDS) T-SQL (TDS) SQL Server Reporting Server (on-premises) SQL Azure Database MS Datacenter - AD Federation (LiveId /.NetSvcs ACS)

7. Service Provisioning Model • Each account has zero or more servers • Azure wide, provisioned in a common portal • Billing instrument • Each server has one or more databases • Contains metadata about the databases and usage • Unit of authentication • Unit of Geo-location • Generated DNS based name • Each database has standard SQL objects • Unit of consistency • Unit of multi-tenancy • Contains Users, Tables, Views, Indices, etc. • Most granular unit of billing Account Server Database

8. Architecture

9. Application Topologies SQL Azure access from within MS Datacenter (Azure compute) SQL Azure Access from outside MS Datacenter (On-premises) SQL Azure Access from within and outside MS Datacenter (On-premises & Azure Compute) SQL Server App code/ Tools App code/ Tools Application/ Browser SOAP/REST HTTP/S Astoria/REST - EDM HTTP/S Windows Azure SQL Azure Data Sync Windows Azure App Code (ASP.NET) App Code (ASP.NET) T-SQL (TDS) App Code (ASP.NET) App Code (ASP.NET) T-SQL (TDS) T-SQL (TDS) Windows Azure SQL Azure SQL Azure SQL Azure Code Near Code Far Hybrid

10. Unit of multi-tenancy Account • Account • ISV provides software to End customer • End customer has full control and responsibility over Azure components, direct billing relationship with MS • Typically for ISVs that do not want to run a service • Server/Subscription level • ISV manages owns the account (provisioning and billing) • Customer owns administration of the server and databases • Common for ISVs providing development platforms • Database level • ISV owns account and administration • Each customer gets its own DB(s) but typically does not manage it, simplest programming model • Common case for most ISVs where the data per customer > 500 MB or the biz model allows it • Sub-database level • ISV packs multiple customers into a single DB (may have many DBs though) • Common case where average data per customer is small < 50k Server Database Sub-Database

11. Partitioning, when do I need it? • Partitioned Data • Partitioning based on Application Requirements (IOPS) • Partitioned Data • Partitioning based on Application Requirements (IOPS, Storage or both) High Transactional Requirements • Partitioned Data • Partitioning Based on Application Requirements (Storage) • Single Database • No Partitioning Low Low High Storage Requirements

12. Development

13. Develop Locally • Developing on a local SQL Express instance has some advantages • Easy to get started, you already know how to do it! • Full fidelity with the designer and debugging tools • Reduces latency when using local Azure development tools • Reduces bandwidth and databases costs for development • Some caveats • Remember to alter your VS build settings to switch the connection string when deploying • Use tools (like SQLAzureMW) to keep you within the supported SQL Azure features • Always test in SQL Azure before deploying to production

14. Connecting to SQL Azure • SQL Azure connection strings follow normal SQL syntax • Applications connect directly to a database • “Initial Catalog = <db>” in connection string • No support for context switching (no USE <db>) • Some commands must be in their own batch • Create/Alter/Drop Database & Create/Alter/Drop Login, & Create/Alter USER with FOR/FROM LOGIN • Encryption security • Set Encrypt = True, only SSL connections are supported • TrustServerCertificate = False, avoid Man-In-The-Middle-Attack! • Format of username for authentication: • ADO.Net:Data Source=server.database.windows.net;User ID=user@server;Password=password;... • Setup your firewall rules first!

15. Connections: Use Pooled Connections • Increases efficiency by removing re-login • // When pooling, use connection and return immediately • // Do not hold for a long time – pool ensure fast turnaround • // one second use • using (SqlConnection conn = new SqlConnection(…)) • { • conn.Open(); • using (SqlCommandcmd = conn.CreateCommand()) • { • cmd.CommandText = …; • … • } • } • using (SqlConnection conn = new SqlConnection(…)) • { • conn.Open(); …

16. Resilient Connection Management • Connections can drop for variety of reasons • Idleness (greater than 30 minutes) • Throttling • Long running transactions > 5 minutes • Resource Management • Database failover • Hardware failure • Load Balancing • Upgrade • What to do on connection failure? • Wait, then retry if it is a transient failure • Change your workload if throttled, i.e. break up your transaction

17. Resilient Connection NameTitleCompany DEMO

18. Short Transactions NameTitleCompany DEMO

19. SELECT INTO temp tables • SELECT * • INTO #Destination • FROM Source • WHERE [Color] LIKE 'Red‘ • To work around this you need to create your destination table then call INSERT INTO. Here is an example: • CREATE TABLE #Destination (Id int NOT NULL, [Name] nvarchar(max), [Color] nvarchar(10)) • INSERT INTO #Destination(Id, [Name], [Color]) • SELECT Id, [Name], [Color] • FROM Source • WHERE [Color] LIKE 'Red';

20. Debugging

21. Tracking Connectivity Problems • Each session assigned a unique ‘sessionId’ • Tracks session state and service errors • Retrievable from CONTEXT_INFO() • Save this with each new connection • Also comes back with *some* error messages • If you need support, support personnel will greatly appreciate that value

22. SessionId Helper Pattern Guid? sessionId = null; using (SqlConnection conn = new SqlConnection(…)) { // Grab sessionId from new connection using (SqlCommandcmd = conn.CreateCommand()) { conn.Open(); cmd.CommandText = "SELECT CONVERT(NVARCHAR(36), CONTEXT_INFO())"; sessionId = new Guid(cmd.ExecuteScalar().ToString()); } // continue processing ... }

23. Other Techniques • DMVs • sys.dm_exec_* • Connections, query_plan, query_stats, requests, sessions, sql_texttext_query_plan • sys.dm_tran_* • active_transactions, database_transactions • Test Connectivity button • Future features • Profiler-like trace • Deadlock graphs

24. Deployment

25. Deployment Options • Generate Script Wizard • Produce a SQL script compatible with SQL Azure, Schema and/or data • Great for initial deployment • SQLAzureMW • Useful for catching unsupported features in SQL Azure • Moves data efficiently • Unofficially supported Data-tier Application Component Schema • Data-tier Application Component (DAC) • New unit of deployment for T-SQL apps. • Supports Install, Uninstall, and in the future Upgrade and Repair. • Contains developer intent as policies. LOGICAL Tables, Views, Constraints, SProcs, UDFs, PHYSICAL Users, Logins, Indexes Unit of Deployment Future - DAC Deployment Profile Deployment Requirements, Management Policies, Failover Policies

26. Management

27. SQL Azure Firewall • What is it? • A way to restrict access to your database • How does it work? • UI • Programmatic access • Common scenarios • I need my development machine to be able to access it • I need access from my laptop when I am not in the office • I want to restrict access to only my application running in Windows Azure

28. Summary • Many SQL Server patterns apply to SQL Azure • Use SQL best practices wherever possible • Think carefully about your level of multi-tenancy and need for partitioning • Make your code robust, use retry logic for your connections • Develop locally

29. Required Slide Speakers, please list the Breakout Sessions, Interactive Sessions, Labs and Demo Stations that are related to your session. Related Content • Breakout Sessions • DAT209 – What’s New in Microsoft SQL Azure • COS305 – Microsoft SQL Azure Development Best Practices • COS311 – Migrating Applications to Microsoft SQL Azure • Interactive Sessions • COS07-INT – Using SQL Azure as a Datahub to connect SQL Server and Silverlight clients • COS13-INT – Database Performance in a Multi-tenant Environment • Hands-on Labs • COS04–HOL – Introduction to Microsoft SQL Azure

30. Required Slide Track PMs will supply the content for this slide, which will be inserted during the final scrub. Track Resources • Team Blog – http://blogs.msdn.com/sqlazure • MSDN Dev Center – http://msdn.microsoft.com/azure/sqlazure • SQL Azure Labs – http://www.sqlazurelabs.com • Follow us on Twitter - @sqlazure Windows Azure Platform Training Kit - http://bit.ly/dgvMv0

31. Required Slide Track PMs will supply the content for this slide, which will be inserted during the final scrub. Track Resources • Join a user group and network with peers to Connect, Share and Learn • Become a PASS Member (Professional Association for SQL Server) - www.sqlpass.org • Attend the PASS Summit 2010 – largest SQL Server conference http://www.sqlpass.org/summit/na2010 • Join the SQL Server conversation on Facebook, Twitter and YouTube • Find out what top SQL Server bloggers are saying on the Community Tab • Learn about applications running on SS08 R2 on the Application Showcase Tab • Ask questions and get answers, straight from SQL Server experts • Visit the Microsoft SQL Server Forums - http://social.technet.microsoft.com/forums/en-us/category/sqlserver

32. Track Resources • Windows Azure, Microsoft SQL Azure, Windows Azure platform AppFabric: www.windowsazure.com/techedus • Learn More: Visit the Windows Azure Boot Camp, Room 396

33. Required Slide Resources Learning • Sessions On-Demand & Community • Microsoft Certification & Training Resources www.microsoft.com/teched www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers • http://microsoft.com/technet • http://microsoft.com/msdn

34. Required Slide Complete an evaluation on CommNet and enter to win!

35. Sign up for Tech·Ed 2011 and save $500 starting June 8 – June 31st http://northamerica.msteched.com/registration You can also register at the North America 2011 kiosk located at registrationJoin us in Atlanta next year

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.

37. Required Slide