This is probably the last contributor that I am going to add to the DacFx deployment contributors project (https://github.com/DacFxDeploymentContributors/Contributors/) for a little while, if anyone else has one they want to contribute please feel free. I have been pretty pleased with how they went but want to make the testing side better.

This is an example contributor that is different to all the others in that the previous contributors enumerated the list of steps and either modified one or swapped it with another but this one uses the context object that is passed in to find all changes to stored procedures and then based on the dacpac and deployment database made a decision about whether to fail the deployment or not:

Another day another deployment contributor for the project: https://github.com/DacFxDeploymentContributors/Contributors.

This one came about because I was talking to Peter Schott (b|t) over at the deployment contributor gitter room and he has dacpac he wants to deploy to different editions of sql server and enable different options when deploying to enterprise edition.

One of the things I found out is that from a contributor, although you have the source and destination models you do not have access to any information about the server other than the version. To get around this you can either pass in an arg to say which edition the compare is running against or if you leave that off then the create index statement is duplicated and wrapped on an check against @@version to determine the edition which is more t-sql than I would like but should work.

I have added my first actual deployment contributor to my deployment contributor project:

https://github.com/DacFxDeploymentContributors/Contributors

The idea is basically for people to share ideas about writing deployment contributors for SSDT and help each other. If you don’t know what a deployment contributor is then you are missing a real treat. A deployment contributor lets you modify the t-sql that SSDT creates to upgrade a database from one version to the next.

Writing code to generate TSql using the ScriptDom is hard. There is a seemingly bewildering array of objects you can use and where you use different types is unclear, I think this is due to the complexity of the task, it really isn’t simple and dealing with t-sql gets complicated very quickly - to be clear the ScriptDom is a great tool and although is difficult to use is 1 million times better than writing custom parsers / generators for t-sql.

One of the best features of SSDT is deployment contributors. They are so exciting I am going to say it again with more gusto. One of the best features of SSDT is deployment contributors.

What these do is let you examine or modify the deployment script that is generated when SSDT compares a project to a database.

why is that cool?

Please imagine for a minute a scenario where you have used SSDT to generate a deployment script and you have looked at it and scoffed that you could have written a better upgrade script. Perhaps you have seen that it has done a table migration and uses a single insert to move all the rows from one table to the next. You have looked up from your script, gazed out of the window and dreamed of a world where instead of a single insert the insert happened in batches. You throw down your script, grab your keyboard from your desk and manually write the upgrade script. A few days later you need to do the same thing so again gaze out of the window dreaming of a batched insert script at which point you grab your keyboard and bash out another script.

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. To be clear I am already in that camp but wanted to spend some time exploring what upgrade scripts get generated in which scenarios.

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:

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.

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!

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.