Advanced SSDT and DACFx Practical techniques for real

Advanced SSDT and DACFx Practical techniques for real world database development and deployment Darren Hall M 359

Importing existing databases using SSDT Automating deployments using the DAC Framework Customising build and deployment

Meet the players Visual Studio SSDT SSMS Sql. Package DACFx SQL Client dacpac SQL DB

Ref. DB Auth Route Shard I/face Shard “Global” databases. 1 each of these on a single SQL instance per environment BLOB Shard Horizontally partitioned “Shards” ~100 of these across multiple SQL instances

§ By The. Culinary. Geek from Chicago, USA (Spaghetti with Meatballs 02 Uploaded by the wub) [CC BY 2. 0 (http: //creativecommons. org/licenses/by/2. 0)]

Using SQL Server Management Studio to extract a dacpac resulted in 16% SUCCESS!

Invalid objects Old objects referencing non-existent columns / encrypted objects / invalid synonyms / cross database references / circular references / unsupported objects for target platform Engage a Subject Matter Expert: Talk to your DBA to help clean up these issues Validation turned on Turn validation off for initial import! Use the latest version of the tools Import everything and fix issues within Visual Studio Lots of objects SSDT imports the first 1000 stored procedures into Procs 1, the next into Procs 2 etc You may need to add some folder structure to your project to keep developers sane. Having many 1000’s of objects affects SSDT performance

Reference external databases Break up a larger database into logical units Separate by schema / functional area Decrease build time – build smaller pieces By User: Amada 44 (Own work) [Public domain], via Wikimedia Commons

My DB Project data-tier application system database Artefacts Artifacts project dacpac SQL DB My Other DB Project

DEV CI QA UAT PROD

Changes are applied forwards DEV CI QA UAT PROD

Measure of change to a database from a known state By Jan Kåre Rafoss from Norway, original uploader was RX-Guru at de. wikipedia (Based on Image: Isachsen 2004. jpg) [GFDL (http: //www. gnu. org/copyleft/fdl. html) or CC-BY-SA-3. 0 (http: //creativecommons. org/licenses/by-sa/3. 0/)], via Wikimedia Commons

Command line using Sql. Package. exe The Swiss army knife of database deployment tooling for dacpacs There are LOTS of options - Wrap calls in. cmd , use publish profiles / response files Outputs are files so can be limiting for automation / workflow Power. Shell or. NET Flexible, more easily used in workflows Microsoft. Sql. Server. Dac namespaces Have full access to Dac. Services methods and objects Lots of options – use Dac. Deploy. Options or wrap in cmdlets or functions

Command Line Power. Shell or. NET Sql. Package. exe Microsoft. Sql. Server. Dac. Services /Action: Extract(…) /Action: Publish Deploy(…) /Action: Deploy. Report Generate. Deploy. Report(…) /Action: Drift. Report Generate. Drift. Report(…) /Action: Script Generate. Create. Script(…) /p: Register. Data. Tier. Application=True Register(…) n/a Unregister(…) /Action: Export. Bac. Pac(…) /Action: Import. Bac. Pac(…)

By Doka 54 (Own work) [Public domain], via Wikimedia Commons

A Drift Report compares using default values This means a drift report will: Ignore Index Padding but detect changes to Index Options Ignore Fill Factor but detect change to other Table Options Ignore Ansi Nulls This might matter to you and/or your DBA! Consider using a Deploy. Report instead and explicitly specify the options you want to include/ignore

Microsoft. Sql. Server. Dac. Model. TSql. Model This is the full model of the database schema. Example uses Code generation using T 4 templates for DTOs/Validations etc. CRUD / audit trigger generation Microsoft. Sql. Server. Dac. Deployment Namespace containing Build. Contributor, Deployment. Plan. Modifier, Deployment. Plan. Executor classes Example uses Static Code Analysis during build Generate schema documentation during build Customise the behaviour of the deployment plan

Build. Contributor Executed when SQL project is built after project model has been validated. Can access the completed model, in addition to all properties of the Build task and any custom arguments Deployment. Plan. Modifier Executed when SQL project is deployed, as part of the deployment pipeline, after the deployment plan has been generated, but before the deployment plan is executed. Can modify the deployment plan by adding or removing steps. Can access deployment plan, comparison results, and source and target models Deployment. Plan. Executor Executed when the deployment plan is executed and provides read-only access to the deployment plan. The Deployment. Plan. Executor performs actions based on the deployment plan (https: //msdn. microsoft. com/en-US/library/dn 268597(v=vs. 103). aspx)

Dacpac automation Microsoft. Sql. Server. Dac. Services Dac. Package Dac. Deploy. Options Data-Tier Application management Microsoft. Sql. Server. Management. Dac Microsoft. Sql. Server. Management. Utility Deployed. Dac Utility Customise build and deployment Microsoft. Sql. Server. Dac. Deployment Microsoft. Sql. Server. Dac. Model Microsoft. Sql. Server. Transact. Sql. Script. Dom Deployment. Plan. Modifier, Deployment. Script. Dom. Step TSql. Model TSql. Script

SQL Server Data Tools provide a rich database development experience from source code to deployment The Data-Tier Application Framework enables powerful automation and customisation options Together SSDT and DACFx offer a compelling Database Lifecycle Management story

SQL Server Data Tools Team Blog http: //blogs. msdn. com/b/ssdt/ Download Latest SQL Server Data Tools https: //msdn. microsoft. com/en-us/library/mt 204009. aspx Build and Deployment Contributors https: //msdn. microsoft. com/en-US/library/dn 268597(v=vs. 103). aspx DAC Extensions https: //github. com/Microsoft/DACExtensions Microsoft and Database Lifecycle Management (DLM): The Dac. Pac Dacpac Explorer https: //www. simple-talk. com/sql/database-delivery/microsoft-anddatabase-lifecycle-management-%28 dlm%29 -the-dacpac/ https: //github. com/Go. Eddie/Dacpac. Explorer Ed Elliot’s blog https: //The. Agile. Sql. Club TSQL Smells https: //github. com/davebally/TSQL-Smells Dave Ballantyne’s blog http: //dataidol. com/davebally/

SSDT for Visual Studio: Bet you can’t tell what your database looked like 6 months ago [M 222] 5 Stop Coding on Pigs [M 373] 1 2 What's new in Visual Studio 2015 and ALM 2015 – Part 1 [M 323] 6 Torment Your Colleagues with the 'Roslyn'. NET Compiler Platform [M 385] 3 What's new in Visual Studio 2015 and ALM 2015 – Part 2 [M 332] Find me later at… 4 SQL Server Execution Plans and the Query Optimizer [M 410] Fri 10: 40 am Ballroom 1 Wed 11: 55 am Ballroom 1 Wed 1: 55 pm NZ 2 Wed 3: 10 pm NZ 2 Thu 10: 40 am Ballroom 2 Fri 1: 55 pm NZ 2 § Hub Happy Hour Thu 5: 30 -6: 30 pm § Closing drinks Fri 3: 00 -4: 30 pm

Free Online Learning http: //aka. ms/mva Subscribe to our fortnightly newsletter http: //aka. ms/technetnz http: //aka. ms/msdnnz Sessions on Demand http: //aka. ms/ch 9 nz

© 2015 Microsoft Corporation. All rights reserved. Microsoft, Windows and other product names are or may be registered trademarks and/or trademarks in the U. S. and/or other countries. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.