microsoft sql server management developing managed data tier applications l.
Skip this Video
Loading SlideShow in 5 Seconds..
Microsoft SQL Server Management: Developing Managed Data-Tier Applications PowerPoint Presentation
Download Presentation
Microsoft SQL Server Management: Developing Managed Data-Tier Applications

Loading in 2 Seconds...

play fullscreen
1 / 31

Microsoft SQL Server Management: Developing Managed Data-Tier Applications - PowerPoint PPT Presentation

  • Uploaded on

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

Microsoft SQL Server Management: Developing Managed Data-Tier Applications

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
microsoft sql server management developing managed data tier applications

Required Slide


Microsoft SQL Server Management: Developing Managed Data-Tier Applications

Charlie Carson

Senior Development Lead

Microsoft Corporation

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





why do we need a model
Why do we need a model?

Find problems early:





Msg 208, Level 16, State 1, Procedure p2, Line 3

Invalid object name 'employeee'.


why do we need a model4
Why do we need a model?

Allow for planning:

createproc myProc







why do we need a model5
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:

but change happens
But, change HAPPENS

There are (at least) two types of change:



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


having a model helps with change
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:

change happens9
  • 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


having a model helps with change10
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:

having a model helps with azure
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:


DAC is a contract

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

two personas
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)
two personas14
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
two personas15
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
two personas16
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
dac v1 focus
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



App Sophistication

dac is a contract
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
dac is a contract19
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.
dac is a contract20
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.

dac overview
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
dac v1 overview
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
dac project system visual studio 2010
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”
dac project system visual studio 201024
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

sql server 2008 r2 ssms
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

dat track scratch 2 win

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

Required Slide



  • Sessions On-Demand & Community
  • Microsoft Certification & Training Resources

  • Resources for IT Professionals
  • Resources for Developers

Required Slide

Complete an evaluation on CommNet and enter to win!


Sign up for Tech·Ed 2011 and save $500 starting June 8 – June 31st

You can also register at the North America 2011 kiosk located at registrationJoin us in Atlanta next year


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