This blog is part 4 of a series. For the introduction post, click HERE
A couple years ago, everyone at Just-BI was focusing on SAP. Oh boy, have we been spoiled with all those nice change management tracking tools! In SAP ECC and BW through the transport management system, and in HANA as well with Lifecycle Manager (HALM). When starting the first developments in a small Azure Synapse instance, migration of code was the least of our concerns. But after developing for a while we found ourselves needing a three-tier landscape with development, quality and production. And moving definitions of database objects between them with Export/Import was no longer a good solution. Especially because we were working on the same project with six developers. A quick search led us to Microsoft’s excellent documentation on source control integration for SQL pool. Within a day we were happily branching, pushing, pulling and merging. (For some basic Git terminology, check this link.) Let’s take a step back and give an overview of what Azure DevOps is.
Within Azure DevOps there are several services to collaborate on code development, deploy applications and more! Repos are there to store your object definitions in, and pipelines deploy them on, in our case, the database. Next to that, Azure boards allow for issue tracking, test plans are for continuous testing and Azure artefacts allows you to integrate package sharing into your pipelines. Because we were using Jira for issue tracking, we decided to use Repos and Pipelines for now.
We started out by using Repos to store the definitions of our tables, views, procedures and other database objects. It’s a Git repository so the way of work is familiar for app developers. Being database developers, we had some doubts when we started to work with this. Our usual way of working was to run CREATE and ALTER statements directly in the database. Now we would have to store the definitions in a project in Visual Studio and then run a pipeline to deploy them. Luckily, some developers at Microsoft came to the rescue and helped us set up a process that works for all of us.
Now we have the following setup: for each project or release a team of developers is working on, we set up a separate quality branch in DevOps. From this, each developer creates his/her own branch to work in. There is no CI/CD connected to the development database. This means that a developer can create changes on a development database, and when they are good to go, add them into his/her own SQL project and development branch. In our case we did that using Visual Studio with SQL Server Development tools, which connects to the Azure DevOps Repo. From there we use the web interface to create a pull request into the relevant quality branch, and finally pull everything into the master branch as well. Of course, this can only be done with the relevant approvals from team members.
We set up the quality and master branches in such a way that whatever is added to them is deployed automatically on the database. The build server checks the collection of objects for consistency first, so that views do not refer to non-existent fields, for instance. When deployment starts, the job is executed on servers that run free for the first 1800 minutes a month. Considering that our deployments take about three minutes on average, that means we can do 600 deployments a month. Other than that, the cost for adding users from the sixth user onwards is about five euros a month so that should not be a big deal. Setting up the pipeline was also just a matter of filling in a script template to point the pipeline to the right branch and database. This configuration is stored in a YAML file which is stored along with the database artefacts.
It has now been about two months since we started using Azure DevOps, and we have done two large releases (and some bugfixes using cherry picking). So far, we are very happy that we don’t have to copy object definitions from one database to another, and we ensure that we have one single code base in our Azure Git Repos. Coming up is the integration of other Azure artefacts into the same repository such as Azure Logic Apps, Function Apps or even system configurations. So far so good!
Are you interested in using Azure DevOps for your version control in the Azure Cloud and are looking to exchange thoughts? Don’t hesitate to contact us!