Database Projects Within Visual Studio SQL Server Data Tools Ronnie Hicks Consultant http://about.me/ronniehicks Agenda • • • • • Database ALM Intro to Database Projects SQL Server Data Tools (SSDT) DAC Framework (DACFx) Data-Tier Application Package (DACPAC) Database management nirvana is within your grasp with these features! Database ALM is too hard! Refactoring pain Development cycle Database promotion across environments Application/ Database Synchronization Database Projects (and SSDT) to the rescue! Buffered Declarative editing Model-based design and validation SQL Unit Tests Pre/Post deploy scripts Local testing and promotion Source Control Snapshots Drift detection and comparisons What is a Database Project? Offline development of SQL Server Databases Project-based development Developer isolation Rich T-SQL support Automatic script generation Pre-Deploy Post-Deploy Refactor Log Verification against the “Model” Coding standards enforcement Intro to Database Project Demo SQL Server Data Tools (SSDT) SQL Server Object Explorer SQL Language Services Schema Comparison SQL Static Code Analysis Table Designer Database Publishing Database Unit Testing SQL/CLR Isolated Local Database Runtime Data Comparison Buffered Declarative Editing SQL Debugging DAC Framework (DACFx) Introduced in SQL Server 2008 R2 Suite of tools, APIs and services Handles all DACPAC and BACPAC processing Microsoft.SqlServer.Dac (DAC API) Data Tier Application Package (DACPAC) Self-contained unit of SQL Server database deployment Supported by SQL Server 2005 & above, including SQL Azure Continuous Deployment DAC properties within SQL DAC Versioning Demos Create a project from an existing database Make some changes to this new project Verify that the changes work Use SQL Debugger to test these changes in LocalDB Write some Unit tests Deploy this database to SQL Azure and update the existing database That’s why Database Projects rock! • • • Source Control for SQL Server Database versions coincide with application, not just a resource Side-by-side SQL/CLR DACFx and DACPACs SQL Server Data Tools (SSDT) Multi-team collaboration Data-Tier Application (DAC) Questions? Thanks!