Prompted by some discussion on the SQL Community Slack, I thought I’d revisit this old post on the SSDT Team Blog which outlines how to filter specific objects from a dacpac deployment using the Schema Compare API. In the past, I’ve used Ed Elliott’s filtering deployment contributor for this kind of thing, but in the interest of experimentation I thought I’d have a look at what comes “in the box”, not least because deployment contributors can, ironically, be a bit of a pain to deploy.

It may have been a while coming, at least compared to Jenkins Pipeline, Travis-CI, and friends, but VSTS now offers the facility to specify your build pipeline as YAML, meaning it can be version controlled with your application code. YAML Release Management Pipelines are “on the way”, but not yet publically available. The YAML Build is. The release is not yet. https://t.co/FCHfdfMHd7 — Donovan Brown (@DonovanBrown) March 8, 2018 YAML Build Definitions are currently in public preview, so you’ll need to ensure you have the feature enabled for your account.

Config as environment variables I’m a big fan of the Twelve-Factor App “methodology”1 for building and deploying applications, and whilst much of it is geared towards web apps in Heroku-esque environments, I think the principles - or “factors” - are well worth bearing in mind when considering the delivery of other types of application. Factor 3 of the 12 reads as follows An app’s config is everything that is likely to vary between deploys (staging, production, developer environments, etc).

This came up in a question after a recent talk about database unit testing; I’ve done something similar on a client project in the past, and it was in my “old” talk about testing. I thought I’d write it down here in case it’s useful to anyone, not least the person who was asking the question. A .zip file of the complete solution can be downloaded from here. For many years, Visual Studio Database Projects - in SSDT as well as in its predecessors - have included an additional template for generating SQL Server Unit Tests.

It’s fairly uncontentious to suggest that, all else being equal, providing each developer with an individual “sandbox”, or private development environment, is a worthwhile endeavour. Often, these can be provisioned on the developers individual desktops, but when the application involves PaaS services such as databases, message queues, and other cloud-based services, things become more complicated. It’s generally possible to emulate most things on the desktop, but there are often small gaps in this emulation, not least in the communication and authentication protocols that link the services together.

In my mind, the ability to do this kind of thing is the really big “win” with SQL Server on Linux. In their own words, Vagrant is a tool for building and managing virtual machine environments in a single workflow. With an easy-to-use workflow and focus on automation, Vagrant lowers development environment setup time, increases production parity, and makes the “works on my machine” excuse a relic of the past.

For reasons, you might want your Jenkins Multibranch Pipeline jobs to do a different thing depending on which branch is being built. Fortunately, the multibranch plugin provides us with a built-in variable BRANCH_NAME, which we can use to figure out which branch we are currently building. In such scenarios, it’s not a bad idea to create a minimal Jenkinsfile at the repo root that contains just enough logic to figure out which branch we are on, and then call another groovy script that contains the actual build definition:

This is a short illustration of using a local installation of Jenkins on Windows to build an SSDT project from a local git repo and deploy it to a SQL Server on the same machine. This is probably useful for a quick demonstration or to understand how the various moving parts fit together, but possibly less applicable to “Real Life” production environments. There are no build agents and no git remotes; all the action takes place on the Jenkins master, and the git repo is local to the same machine.

How SSDT can help with restoring a SQL Server database to “just before that last deployment” For as long as I can remember, SSDT and its predecessors have had the option to “Back up database before deployment”, currently available in the “Advanced Publish Settings” dialog, among other places. Regrettably, I’ve never really had much use for this particular option. Whilst restoring from backup might be a valid strategy for recovering from some kinds of deployment disaster, this could add a great deal of time to the deployment process, assuming a database of non-trivial size.

For reasons, I recently found myself in a scenario where I needed to test whether a branch existed before checking it out, and resorting to a sensible default - such as checking out master, if it didn’t. From the command line, this is a simple matter of git branch -l | grep myBranch, but I needed to do this from the context of a Jenkins pipeline job. Preliminaries For simplicity, I’m creating a local repo I can point my Jenkins job at, right inside the JENKINS_HOME folder.