Looking at SSDT upgrade scripts

I have been using SSDT seriously for a couple of years now and have really been impressed with the code that is generated to upgrade schemas and over that time have learnt to trust that SSDT generates good upgrade scripts. It is however a concern that a tool generates the deployment script rather than a person and to move to a continuous deployment environment we need to be sure that the tooling is better than a person.

Dir2Dac and github2dacpac - create dacpacs from script folders or the internet

I like writing go (golang not the batch separator) and one of the things that I like is that if you want to use something from github you literally type “go get https://github.com/user/repo” and the code is downloaded and installed ready to be used. I also like write t-sql using ssdt and sometimes I want to reference other things like tSQLt or maybe a set of admin scripts or something but unlike go, to reference them what I need to do is:

New SSDT Projects default to the latest version of SQL Server

When you create a new SSDT project it is created (at the moment) as a SQL Server 2014 project which is annoying when you are on something else. However, if after you have created the project you import the code from an existing database and check the box “Import Database Settings” it will set it to the correct version, which is much less annoying than trying to deploy, failing and fixing it and the deploying.

Video of sqlrelay talk - build a ci pipeline in 55 minutes

My session yesterday was recorded, apart from the fact that me and mark were having a chat for about 25 minutes before it kicked off it seemed to work quite well: Skip forward to 24:32 to get to the actual content. The lovely Mark at SQLRelay has edited the video and put it on youtube so you miss our chat at the beginning: https://www.youtube.com/watch?v=9YJQTx3bPek Any questions please shout!

How do you choose a source control system for your SQL Server database

If you already have a source code repository for your app code or other databases or you have another team that uses source control then just use theirs. If you don’t then use whatever the members of your team have used most, they all basically do the same thing and having your database under source control is a million times better than not having it under source control. There is an exception, if you use visual source safe and I know people do still use it then use something else, it hasn’t had a release in over 10 years and in my opinion is no longer fit for purpose, especially when you consider the other free options available to you.

SQLRelay Reading - Build a CI Pipeline in 55 Minutes Talk

I had a lot of fun yesterday presenting my talk on how to go from not having your database in version control to deploying to a CI database, running unit tests and generating deployment scripts for a QA/Prod database so effectively how to build a fully functioning continuous delivery process for SQL Server databases in less than an hour (my wittering was about 40 minutes and the demo just over 10).

Deployment Contributor Logger

Niche tool of the day If you know what a deployment contributor is and have wanted to see what steps were in a plan and have got bored setting a breakpoint in visual studio and using the watch window or something to display the steps in a plan, you might find this useful. It is really simple, all it does it look through all the steps in a deployment plan and dumps (Console.

Deployment Contributor KeepTableColumns Filter

To help with this scenario (it is pretty specific): You have a table that has columns that you do not know about when you create the dacpac, possibly you have a customer who modifies your schema after you deploy. If you deploy your dacpac those columns will be dropped which is bad and you don’t want to completely ignore all changes to a table what do you do? You can now use the new version of my deployment contributor with a filter like SqlPackageFilter=KeepTableColumns(Employees)

Inside an SSDT Deployment Contributor

In my deployment filter I have been asked a couple of times to allow columns that exist on a table that do not exist in a dacpac to be ignored so that they are deleted. The typical scenario is that a vendor supplies a database and then customers can add extra columns (I know what a horrible place to be but it happens). I had a look at this and my first thought was that if we use the deployment filter and add an IgnoreType(SqlColumn) that it would work but it obviously doesn’t.

Setting up a new CI server to build SSDT projects

I am going to ignore the fact that people don’t want to install visual studio on a build server, in the future there will likely be a way to install just the required components rather than everything. Today, the way I see it is that I develop in Visual Studio, I am happy to install Visual Studio on the build server for me Visual Studio is as dependency for SSDT.