Application and Multi-Server Management Part III – Managing Change in Data-Tier Applications

1. Application and Multi-Server ManagementPart III – Managing Change in Data-Tier Applications SQL10R2UPD04-DECK-03 [Presenter Name][Presenter Title] [Company Name]

2. Module Overview • Introducing Application and Multi-Server Management • Working with Data-Tier Applications • Managing Change in Data-Tier Applications

3. Agenda • Application Life-Cycle: Modifying Data-Tier Applications • Data-Tier Application Management and Futures • Demo: Modifying a Data-Tier Application and Deploying the Changes • Lab: Modifying a Data-Tier Application and Deploying the Changes

4. Viewing .dacpac Files • Important to view .dacpac files before deployment • Extra important for externally produced files • Do not deploy files from unknown or untrusted sources • Test unknown files on a separate, isolated instance • View a .dacpac file by using one of these methods: • Creating a Data-tier Application project in VS and importing it • Unpacking the .dacpac file into a folder if VS unavailable • Deploying the .dacpac file to an isolated test instance

5. Unpacking .dacpac Files • Unpack Data-tier Application dialog • Right-click .dacpac file and select Unpack option • Scripts and files placed into a folder • When unpacked, it contains • T-SQL script (ObjectName.sql) (for all objects) • XML files • Server-selection policy (can be viewed in SSMS) • Extra files section (pre/post-deployment files)

6. Comparing .dacpac Files • Schema compare tool in VS can compare two DACs • Tool has been repurposed from Database project • Available in Premium and Ultimate editions • If VS is unavailable: • Unpack both .dacpac files into separate folder structures • Use a differencing tool (e.g., WinDiff) to compare

7. Upgrading Data-Tier Applications • Upgrade Data-Tier Application Wizard • Changes schema and properties of deployed applications • New database created with new schema • Application name must match currently deployed file • SQL authentication logins stored without password and logins are created disabled • Windows authentication logins OK as they are • Data is migrated • Original database set to read-only • Must consider space usage • Both databases then renamed • Can provide further tailoring via PowerShell

8. Database Drift • Important to avoid direct modifications to deployed databases • Objects in current database and not in .dacpac file are not transferred • Data in new tables added after deployment is not transferred • Original database is retained but renamed

9. Table Drift

10. Agenda • Application Life-cycle: Modifying Data-Tier Applications • Data-Tier Application Management and Futures • Demo: Modifying a Data-Tier Application and Deploying the Changes • Lab: Modifying a Data-Tier Application and Deploying the Changes

11. Version and Environment Migrations • Data-tier Applications simplify upgrades • Eliminate the need for two versions of scripts (install vs. upgrade) • Declarative (allow focus on the required target schema, not on the code required to achieve the change) • Delete Data-Tier Application Wizard removes applications prior to redeployment elsewhere

12. Registering Data-Tier Applications • Registering a DAC requires • ALTER ANY LOGIN • VIEW DEFINITION on database • SELECT on sys.sql_expression_dependencies • dbcreator fixed server role membership • Can only be performed • Once • On SQL Server 2008 R2 • Optimized for applications with fewer than 1000 objects

13. Managing Data-Tier Applications • Entries made in msdb for installed DACs • Must back up • Database for DAC • msdb • Modifying database name will destroy association with msdb entries • No further upgrade via DAC • No utilization reports in SQL Server Utility

14. Data Tools Roadmap • SQL Azure support • Visual Designers • Single deployment engine • Expanded app support • Expanded support for SQL objects • Project system enhancements • ALM feature enhancements • Introduction of Data-tier Application projects • Data-tier Application projects and Database projects operate side by side VS 2010 VS 2005 VS 2008 “GDR” vNext • SQL Server 2008 R2 support • SQL IntelliSense • Support for 3rd-partydatabase platforms • Offline change management • SQL Server 2000/2005 • ALM support for databases vNextVision: Combine the best of Data-tier Application and Database projects • SQL Server 2008 support • Separated Build and Deploy • No “DesignDB” required

15. Modifying a Data-Tier Application and Deploying the Changes SQL10R2UPD04-DEMO-05 Demo

16. Resources • Data-tier Application Tutorials http://msdn.microsoft.com/en-us/library/ee210554(SQL.105).aspx • Data-tier Applications in SQL Server 2008 R2 whitepaper http://msdn.microsoft.com/en-us/library/ff381683.aspx

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