Transcript Microsoft.SqlServer.Dac.DacServices

Advanced SSDT and DACFx
Practical techniques for real world database development and deployment
Darren Hall
M359
Importing existing databases using SSDT
Automating deployments using the DAC Framework
Customising build and deployment
Meet the players
Visual Studio
SSDT
SSMS
SqlPackage
DACFx
SQL Client
dacpac
SQL DB
RefDB
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 TheCulinaryGeek 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 Procs1, the next into Procs2 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:Amada44 (Own work) [Public domain],
via Wikimedia Commons
My DB Project
data-tier application
system database
Artefacts
Artefacts
Artefacts
Artifacts
dacpac
SQL
DB
My Other
DB Project
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:Isachsen2004.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 SqlPackage.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
PowerShell or .NET
Flexible, more easily used in workflows
Microsoft.SqlServer.Dac namespaces
Have full access to DacServices methods and objects
Lots of options – use DacDeployOptions or wrap in cmdlets or functions
Command Line
PowerShell or .NET
SqlPackage.exe
Microsoft.SqlServer.Dac.DacServices
/Action:Extract
Extract(…)
/Action:Publish
Deploy(…)
/Action:DeployReport
GenerateDeployReport(…)
/Action:DriftReport
GenerateDriftReport(…)
/Action:Script
GenerateCreateScript(…)
/p:RegisterDataTierApplication=True
Register(…)
n/a
Unregister(…)
/Action:Export
ExportBacPac(…)
/Action:Import
ImportBacPac(…)
By Doka54 (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 DeployReport instead and explicitly specify
the options you want to include/ignore
Microsoft.SqlServer.Dac.Model.TSqlModel
This is the full model of the database schema.
Example uses
Code generation using T4 templates for DTOs/Validations etc.
CRUD / audit trigger generation
Microsoft.SqlServer.Dac.Deployment
Namespace containing BuildContributor, DeploymentPlanModifier, DeploymentPlanExecutor classes
Example uses
Static Code Analysis during build
Generate schema documentation during build
Customise the behaviour of the deployment plan
BuildContributor
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
DeploymentPlanModifier
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
DeploymentPlanExecutor
Executed when the deployment plan is executed and provides read-only access to the deployment
plan. The DeploymentPlanExecutor performs actions based on the deployment plan
(https://msdn.microsoft.com/en-US/library/dn268597(v=vs.103).aspx)
Dacpac automation
Microsoft.SqlServer.Dac
DacServices
DacPackage
DacDeployOptions
Data-Tier Application management
Microsoft.SqlServer.Management.Dac
Microsoft.SqlServer.Management.Utility
DeployedDac
Utility
Customise build and deployment
Microsoft.SqlServer.Dac.Deployment
Microsoft.SqlServer.Dac.Model
Microsoft.SqlServer.TransactSql.ScriptDom
DeploymentPlanModifier,
DeploymentScriptDomStep
TSqlModel
TSqlScript
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/mt204009.aspx
Build and Deployment Contributors
https://msdn.microsoft.com/en-US/library/dn268597(v=vs.103).aspx
DAC Extensions
https://github.com/Microsoft/DACExtensions
Microsoft and Database Lifecycle
Management (DLM): The DacPac
Dacpac Explorer
https://www.simple-talk.com/sql/database-delivery/microsoft-anddatabase-lifecycle-management-%28dlm%29-the-dacpac/
https://github.com/GoEddie/DacpacExplorer
Ed Elliot’s blog
https://The.AgileSql.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 [M222]
5
Stop Coding on Pigs [M373]
1
2
What's new in Visual Studio 2015 and ALM
2015 – Part 1 [M323]
6
Torment Your Colleagues with the 'Roslyn'
.NET Compiler Platform [M385]
3
What's new in Visual Studio 2015 and ALM
2015 – Part 2 [M332]
Find me later at…
4
SQL Server Execution Plans and the Query
Optimizer [M410]
Fri 10:40am Ballroom 1
Wed 11:55am Ballroom 1
Wed 1:55pm NZ2
Wed 3:10pm NZ2
Thu 10:40am Ballroom 2
Fri 1:55pm NZ2
 Hub Happy Hour Thu 5:30-6:30pm
 Closing drinks Fri 3:00-4:30pm
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/ch9nz
© 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.