This month’s T-SQL Tuesday is being hosted by James Anderson (b | t). T-SQL Tuesday (#TSQL2sDAY) is a blog party founded by Adam Machanic (b | t). Each month a member of the community hosts the party and selects a topic for us to write about.
James invites us to write about database deployments. I have worked a lot on continuous integration projects and dealt with tools such as SQL Server Data Tools, msbuild, Release Manager, Jenkins, and Octopus. I want to spread the message that, continuous integration is not a set of tools. CI is process, CI is development practices, CI is a concept put into action.
I have experience working on both sides of the fence. Most of my career has been working as an operations DBA where I have performed a lot of production and non-production database deployments. I have established deployment and automation standards for DevOps teams and I have been a developer who not only wrote the code but was responsible for the builds and was point man for troubleshooting any of the deployment issues.
From these experiences, I have seen things work very well but I have also seen a lot of issues and failures. The worst problems come from a misunderstanding of continuous integration. I have known some developers who feel that they just need to add tools to their toolbox and those tools will provide them automated deployments, desired state configurations, and increased reliability. The vision is not realized without the developers learning how to write rerunnable code and fully understanding what these tools are doing so that they can create release packages which will be rock solid.
What am I even talking about?
SQL Server Data Tools (SSDT) is a tool that I am particularly familiar with and will become the subject of my examples. SSDT database projects shift the source of truth from your database to your source control. The intent is that the project and its build artifact, the dacpac, is the desired state of your database. SSDT will then generate the code necessary for you to migrate from your current state to the desired state.
The problem with my description is that it is similar to saying, “hammers drive nails into wood,” and then expecting that you won’t have to learn how to swing the hammer, aim at the head of the nail, or regulate how hard you hit it. Tools like SSDT are not magic and they can have problems. A solid understanding of how they work can mitigate or completely avoid these issues, however.
One project I have seen had a single database but two database projects with two different dacpacs. When I first saw it, I was a bit confused because this is contrary to the concept behind a database project being a database, not a part of one. After learning more about what they were trying to do I found out that they did monthly marketing releases which were data only and did not want to have to run the schema package because they feared it could make unexpected changes. So, they wrote individual scripts and put all of them in the pre-deployment script, serialized them, and wrote the serial numbers to a table in the database so that a re-run of the dacpac would detect that they already ran and would not run again.
In addition to the split project demonstrating their ignorance of the concepts involved, they also were taking a migration script methodology and cramming it into a desired state tool. Finally, they feared their own tool. They were trying to protect themselves from unexpected changes because they did not understand what triggered the project to make changes. They did not know how the tool generated its code and misunderstood the deployment work flow.
The concepts behind DevOps and continuous integration is a shifting to the left in the life cycle of your code. Developers are becoming more and more involved with tasks which were typically only on ops’ shoulders. DevOps cannot be a team of people responsible for making deployments automatic. DevOps is a blending of ops and the devs to work more closely together. This means that developers need to learn and adapt to more modern-day coding styles. The concepts and processes which result in continuous integration are the back bone and the tools are just there to make things easier and more consistent with less risk of human error.
Do not expect the tool to be the craftsman.