Transcript SSDT_Deployments_March_2016x

Continuous Deployments using SSDT
How I Learned to Stop Worrying and Love the DACPAC
Who am I?
Christopher Wolff
Twitter: @tickytong
Email: [email protected]
Blog: https://chriswolffdba.wordpress.com/
Objectives
 Continuous
 Of
Deployments
DACPACs and Publish Profiles
 SqlPackage.exe
 Using
and you!
your tools
 Automation
 Questions?
Nation
Assumptions

Basic knowledge of SQL Server Data Tools for Visual Studio (SSDT)

Basic knowledge of XML for Publish Profile manipulation

Basic knowledge of how a relational database schema works

Specifically Microsoft SQL Server in this case
Continuous Deployments
Synergizing synergies
Why Continuous Deployments?

To cut down on the amount of code in each release


Give development team ownership of their own releases


Makes sprints shorter and is easier to troubleshoot issues on release
Take Ops out of the way so if issues arise, the development team can
troubleshoot the issue first and then use Ops as a last resort
Catch issues earlier in the process

Testing is done constantly during development instead of at the end so
fixes are done right away instead of waiting for the next sprint
Steps for Continuous Integration


Every developer will have a version of the DB on there own machine

In order for them to have ownership, they must test it themselves

It’s also good to do pull requests so the fellow developers can check each others work
When they are complete with their individual tasks they check the SSDT project
into a source control system



Github or Team Foundation Server
A deployment manager or project manager will typically make an integration
branch in the source control

This is for teams to commit their finished code to before the release happens

Typically this will also deploy to a Stage environment so that QA can do further testing
Once a release has been declared, the manager will make a release branch

All changes will then be merged to this branch in preparation for the release to Prod
On to Continuous Deployment


Once all code has been merged deploy the code to a
testing environment

Often called Livestage or just Test

It will contain a recent version of a prod backup to test against live
data
QA’s commences testing


This usually happens a week before release so QA can flesh out any
issues
Release to Live
Of DACPACs and Publish Profiles
Tools of the trade
DACPACs

Data Tier Applications

Contains all the schema information in any one database

Gives a single entity for deploying changes to a production or test
database


As apposed to multiple SQL scripts

Can also include security credentials if needed

Idempotent so they will never rerun code, just the code that is missing
A close relative is the BACPAC which contains all the data
in the database along with the schema information
Publish Profiles

A collection of all the property key-value pairs that are
used by the DACPAC as settings when deploying to a target
database


Include properties like:

Database name

Connection string

SQLCMD variables

How to handle change data capture (CDC) and replication
All of these properties come from SqlPackage.exe
SqlPackage.exe and you!
The one .exe to rule them all
SqlPackage.exe


A command line tool provided by SQL Server that automates extraction,
exporting, importing, publishing, and scripting of a database

It is automatically installed with every version of SQL Server

Usually stored in C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin
Built in parameters that switch on or off the property of the deployment

Examples

/p:IgnorePermissions={True | False} - Specifies whether permissions should be ignored
during deployment.

/p:DoNotAlterChangeDataCaptureObjects={True | False} - If true, Change Data Capture
objects are not altered during deployment.

/p:DropObjectsNotInSource={True | False} - Specifies whether objects that do not exist
in the database snapshot (.dacpac) file will be dropped from the target database when
you publish to a database.
Using your tools
How to do the needful
Basic Setup

Have a deployment server to dispatch the code from


Have the dacpac and published profile checked into a versioning system


Typically called a terminal server
Such as Github or Team Foundation Server
Using SQLPackage.exe, execute a command line script to publish the dacapac
and publish profile to the server

Example


"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /a:Publish
/TargetConnectionString:"application name=DBDeployment;data source=MyProdDB;initial
catalog=MyProdDB;Integrated Security=True"
/SourceFile:"C:\Deployment\Input\1.0\MyProdDB\MyProdDB.Database.dacpac"
/pr:"C:\Deployment\Input\1.0\MyProdDB\MyProdDB.Database.publish.xml“
When you execute the scripts it will print a log file to the screen of what was
actually executed in the dacpac

Optionally you can have it output to a log file instead

> "C:\Deployment\Output\1.0\MyProdDB\01012015_0700_MyProdDB_1_0.log"
DEMO
Automation Nation
Making your job easier, one script at a time
Why Automate?

Consistency


Less Errors


Using the same release process every time will cut down on errors
Makes it easier to troubleshoot and quicker to recognize where the errors
occur
Speed and Ease of Use

Single call to execute the release as apposed to running multiple scripts
Assumptions Part 2
The search for more money

You are doing regular backups



Weekly full, daily differentials, and 15 min transaction log
You have DB testing already set up in your release process

tSQLt

T.S.T

Redgate Tools
Taking snapshots before the release occurs

Optional but recommended

Make sure to drop when final testing is complete
Automation Tools

Source Control  Continuous Integration Software

Scripting software

GitHub

TeamCity

PowerShell

TFS

Jenkins

Ruby

Bamboo

Bash

Octopus Deploy

Cmd Line
Steps to Automate

Check in Release


Check for New Release


Usually through a release branch in the source control software
The CI software can be used to look for new releases in source control or
have a release manager initiate the release through the CI software
Run the Release

The CI software will then run the predesigned script to deploy the release
Questions?
Comments?
Crude remarks?
Credits

Data-tier Application Overview


Publish Profile Files in SQL Server Data Tools (SSDT)


http://sqlblog.com/blogs/jamie_thomson/archive/2012/05/09/publish-profilefiles-in-sql-server-data-tools-ssdt.aspx
SqlPackage.exe


https://msdn.microsoft.com/en-us/library/vstudio/ee362011%28v=vs.100%29.aspx
https://msdn.microsoft.com/library/hh550080.aspx
Deploying a DACPAC with SqlPackage

http://blogs.msmvps.com/deborahk/deploying-a-dacpac-with-sqlpackage
THANK YOU SPONSORS!!!