The Future of Database Development with SQL Azure

1. SVC27 The Future of Database Development with SQL Azure David Robinson Senior Program Manager Microsoft Corporation

2. Agenda • SQL Azure Overview • Database Lifecycle Overview • How do we develop our Databases today? • What are Data-Tier Applications? • Managing Multiple Database Environments • Project Houston

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. Database Lifecycle Overview

5. How do we develop our data tiers today? • Develop locally using SQL Server • Rich design tools available • SQL Server Management Studio • Visual Studio • Script out our Schema Artifacts • Install in SQL Azure Newly added support in SSMS R2 is changing the game

6. SQL Server Management Studio 2008 R2 demo

7. Challenges • Development • No uniform DB project system and workflow • No ability to capture deployment intent • Lack of T-SQL designers for breadth developers • Deployment • DBAs and Developers work in silos • No clean app containment once deployed • Time consuming and error prone • Management • Difficult to centrally control the environment • SQL Server sprawl on the rise • Impact analysis & resource planning is tricky, difficult

8. Near Term Investments • Development • Data-tier Application project • New T-SQL IDE in VS 2010 • Integrated editor, debugger, IntelliSense, language service, policy designer • Static code analysis, build service Near Term Long Term • Deployment • Easier to deploy and upgrade Data-tier Apps from VS and SSMS • Single unit of deployment has app + developer intent • Management • Manage @ Scale: SQL Server Control Point • Visualize resource utilization at the instance & application level • Trending analysis helps improve resource utilization, reduce costs

9. Concepts • Data-tier Application Project • New project type in VS 2010; Leverages Database project system code base • First class T-SQL/DAC development experience in Visual Studio • Developers can declare deployment intent • Build output = .dacpac • Data-tier Application Component • Improves collaboration between developer and DBA • .dacpac= single unit of deployment (data-tier application + developer intent) • Moves developers from a procedural model to a declarative model • No need to create alter statements + scripts that need to be run in the correct order • Contains the desired shape of the application • Allows tooling to enable scenarios like drift detection • V1 is targeted at Departmental apps Data-tier Application Component Schema PHYSICAL Users, Logins, Indexes LOGICAL Tables, Views, Constraints, SProcs, UDFs, Unit of Deployment DAC Deployment Profile Deployment Requirements, Management Policies, Failover Policies

10. SQL Azure & Data-Tier Applications demo

11. Managing Multiple Database EnvironmentsDev, Test, Prod What we do today What is wrong with this? Phase transitions are typically a manual process guided by a development methodology Manual Processes are error prone Backups mitigate risks but… • Developers make changes during development • Developer manually creates update scripts to give to Testers • Testers then give update scripts to Operations • Operations deploy to production How can we do this better?

12. Quick Trivia Question… • A smoke test is a non exhaustive software test to ensure that recent changes did not break the high level features of the product If an electronic device didn’t catch fire, it was free to proceed for further testing Smoke bombs were placed inside chambers to ensure they were airtight

13. Managing Multiple Database EnvironmentsWhere we are headed Dev Test Prod Having Best Practices built in makes a happy customer Utilizing a DAC handles all the schema upgrades automatically When ready, changes are packaged and smoke tested Only after smoke tests pass are the changes allowed Higher quality gates prevent changes to production Our developer goes about their day writing new code At the same time, our tester is writing smoke tests 010101010101 010101010101 Tests are uploaded and form an enforcement boundary DAC SQL Azure Dev DB SQL Azure Test DB SQL Azure UAT DB SQL Azure Prod DB

14. SQL Azure Database Lifecycle • Combine Existing Technologies Together • Business Rules • MSTest • DACPACs • Ensure that all updates are fully tested • Prevent any fat-fingering • Allows you to have “Best Practices” built in

15. How will we develop SQL Azure Databases in the Future? “I want an easy way to just get in there and start developing” “My database is in the cloud, why can’t my database development tools be?”

16. Introducing Code Name “Houston” demo

17. Wrap Up • SQL Azure is a powerful and symmetrical relational database as a service • You can develop and interact w/ SQL Azure databases in that same way and leveraging the same skill set you are have for SQL Server • Data-Tier Applications Streamline • Development • Deployment • Management • Project Houston will provide a easy to use cloud based database development experience • We continue to invest in development and admin tooling across the breadth of the SQL data platform

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

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