Microsoft SQL Server Management: Developing Managed Data-Tier Applications

1. Required Slide SESSION CODE: DAT310 Microsoft SQL Server Management: Developing Managed Data-Tier Applications Charlie Carson Senior Development Lead Microsoft Corporation

2. Introducing a new concept:Data-tier Application Component (DAC)A model of a database from the application’s point-of-view A DAC is to a database as a blueprint is to a house App : : ::

3. Why do we need a model? Find problems early: createprocp2 as select* fromdbo.employeee Msg 208, Level 16, State 1, Procedure p2, Line 3 Invalid object name 'employeee'. Whoops!

4. Why do we need a model? Allow for planning: createproc myProc as mergetable_target usingtable_source … or or ?

5. Why do we need a model? • Captures what the database is SUPPOSED to look like, according to the Application Developer • We can do a comparison between the model and the database and look for inconsistencies • We call deviation from the model DRIFT • Unintentional Drift is bad! Source of truth:

6. But, change HAPPENS There are (at least) two types of change: Upgrade Maintenance

7. Change HAPPENS • May be scheduled • Index defrag • Backups • Just-In-Time • Add an index • Delete some bogus data • In either case, the goal is for the system to behave the same before the maintenance as after Maintenance:

8. Having a model helps with Change • I can check for drift after performing some maintenance and • Get confidence that I didn’t break the application • Detect accidental changes objects that were left behind by JIT work on the database • If I made an intentional change (a fix) then • I can update the model so that if someone else undoes my fix, I’ll get a new drift warning A model helps with Maintenance:

9. Change HAPPENS • Upgrades are a big deal. They need to be: • Intentional • Repeatable • Fast • Consider some counter-examples: • Accidentally dropping a table (and the data) • Upgrade works great in test, but gets an error when applied to production • Production is down for 2 days while an alter statement executes Upgrades:

10. Having a model helps with Change • Intentional • By comparing the new model to the existing database, I can visualize what’s about to happen. If something doesn’t look right I can abort the upgrade. • Repeatable • No drift gives me confidence that the upgrade I’ve spent weeks testing will perform the same way in production. • Fast • Ok, doesn’t help with this one  A model helps Upgrades be:

11. Having a model helps with Azure • Scripts are brittle, and opaque • The create database statement which runs on-premise might fail with SQL Azure because less options are supported • System cannot act on my behalf or provide guidance – it’s limited to execute the script and pass or fail • The DAC allows the system to help me • It can give me a warning or error early if I specify something that’s likely wrong • Target appropriate Transact-SQL can be generated that achieves my intent Deployment Intent:

12. DAC is a contract The DAC is a contract between the two personas involved with application lifecycle

13. Two personas Competing goals • App developer wants to be agile • Paid to innovate • Uses cool new tools • Uses Visual Studio (probably beta something) • DBA wants to be reliable • Paid to provide stability • Gets yelled at whenever something bad happens • Sleeping through the night without getting paged is a plus • NEVER uses Visual Studio (won’t even install it in production)

14. Two personas Competing goals • But they have to interact throughout the lifecycle of the application • Planning • Deployment • Troubleshooting • Upgrades • This friction leads to angst for both sides

15. Two personas Vast majority of apps are small, not mission critical • Departmental DBA’s are overwhelmed • Managing 100s (or 1000s) of databases • Getting worse, not better • 1 more app = 1 more headache • Departmental DBA’s are forced to be reactionary • Limited to JIT support from the DBA and that only begrudgingly • Push back against new deployments and change in general

16. Two personas Developers innovate around the central IT • Run apps on a “desktop database server” in their office • This is a (very) bad thing: • SQL Server sprawl • Probably not appropriate hardware • Probably not correctly maintained (backups) • Eventually, it’s going to end badly • Lost data (hardware fails) • Orphaned legacy systems

17. DAC v1 focus Focus of DAC v1 • 100-1,000s of small apps • Not targeting LOB apps • Examples: • Work item ticketing • Process automation • Blog • Wiki Number of Apps CRM ERP App Sophistication

18. DAC is a contract Developer can: • Specify what is important to him • tables, columns, views, etc. • NOT specify things that are going to make the DBA ANGRY • The file path for the mdf & log file • The recovery model for the database

19. DAC is a contract Target Selection Policy • Specify requirements on the server for a successful deployment • Major Version >= 10.0 • These are baked into the .dacpac and validated by the system prior to deploying • Way better than a comment in a Transact SQL script or a readme.txt • Way, way better than a failed deployment and a “What the heck?” call from the DBA at 3 a.m.

20. DAC is a contract Target Selection Policy can be reasoned over too A clever DBA, with 20 production servers to choose from, can write a PowerShell script to find the servers which a given dacpac can be deployed to successfully.

21. DAC Overview Big Picture / Vision • Contains the desired shape of the application • Allows tooling to enable scenarios like drift detection • Target server analysis and validation • Moves developers from a procedural scriptto a declarative model • No need to create alter statements + scripts that need to be run in the correct order • Captures Deployment Requirements via PBM Condition • Validated by the system and can be accessed programatically • Single artifact as unit of deployment for the data-tier • .dacpac is extensible and can contain other files so that it can be incorporated into existing deployment processes

22. DAC v1 Overview Visual Studio 2010 / SQL Server 2008 R2 • Limited object support • Supported: Tables, Views, Functions, Indexes, Procs, Triggers, Users • Not supported: Permissions , Service Broker Queues, Certificates, etc. • Extract and Register will block you if you have unsupported objects • Limited validation depth • We catch many but not all errors that prevent a successful deploy • Drift is limited to the objects we support • Upgrade is SxS instead of in-place • Works for small databases but has issues for the hosted scenarios and for large databases • Limited tooling support in SSMS • Can deploy, upgrade, but model compare (and drift visualization) requires Visual Studio Ultimate

23. DAC Project System (Visual Studio 2010) Create DACs • .SQL files as input • No new language to learn • But declarative (out-of-order ok) • High fidelity for source based objects (views, procs, etc.) • With IntelliSense for editing • Across the entire project, not just the current file • Code snippets • Static code analysis • Best practices, etc. • Policy editor • To specify target server requirements • With a Build Verb • Deeper validation • Provides higher confidence in the resulting .dacpac being “correct”

24. DAC Project System (Visual Studio 2010) • Schema Compare • Borrowed from VSTS-Data • Compare schema of database to project and vice versa • Publish project to SQL Server instance (or Azure) • Streamlined for agile development, automatically installs new database vs. upgrades existing database • Reverse Engineer • Create DAC project (.sql files) from existing database or .dacpac • Integrated debugging • Execute sql and view query plans Other features

25. SQL Server 2008 R2 - SSMS • Deploy DAC to a new database • Extract DAC from an existing database • View Data-tier Applications already deployed to the system • i.e. database’s which has a DAC associated with them • Upgrade existing Data-tier Applications • Warns if drift has occured • Uninstall existing Data-tier Applications Lifecycle Management

26. Required Slide Track PMs will supply the content for this slide, which will be inserted during the final scrub. DAT Track Scratch 2 Win • Find the DAT Track Surface Table in the Yellow Section of the TLC • Try your luck to win a Zune HD • Simply scratch the game pieces on the DAT Track Surface Table and Match 3 Zune HDs to win

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

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

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

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

31. Required Slide