In this previous article we’ve shown how to deploy in 1 click an Azure Analysis Service tabular model via DevOps and all the benefit of having CI/CD pipelines. Here we are touching the same topic for an Azure Sql Database project.
In this case it won’t be enough to have just a release pipeline but we will need a build pipeline as well. Since the core element is the “.dacpac” file, let’s first see how the logic works starting from the local Db Project in Visual Studio on our machine.
When using a Visual Studio Azure Database project we would see the following:
If we open the location where our project is there are other folders (bin, obj) :
When building the solution on our machine, the .dacpac file will be generated and stored in that location (./bin/debug/):
This is what we actually need to produce dynamically within DevOps exploiting a Build Pipeline. Let’s click on Builds menu under Pipeline in Azure Devops and select “New Build Pipeline”
Select the classic editor:
Select Empty Job and then create 3 tasks exploiting the following templates:
– Visual Studio Build
– Copy Files
– Publish Artifacts
(we can find them by plain text search in the top right corner search bar)
In the Copy Files task, insert:
Source Folder = $(agent.builddirectory)
Target Folder = $(build.artifactstagingdirectory)
On the top menu it is now possible to Save and Queue.
It’s now time for the Release Pipeline. Go to Releases panel and create a new one:
It’s important to choose the right artifact for connecting the Release’s task to the data produced by the build. In the Source (build pipeline) there will be the list of build previously created which can be selected.
We add a stage and we choose the Azure Sql Dacpac Task:
What’s important now, besides the connection strings, is where to take the .dacpac from:
In the 3 dots menu (of DACPAC file session) it will be possible to navigate up to the right folder which is easy to recognize since it has the same structure of the local folder shown before:
When running the release we will recognize that path in the log together with the list of actions that are performed.
Conclusion
It is possible to deploy Azure Sql Database without having admin permission on the server nor deep Database knowledge, by giving access to Devops to the desired user; once the build pipeline has produced the .dacpac file, the release pipeline will read it and exploit it for the deployment. So it is possible to deploy it with a single click as promised
Advantages:
- Operations: if the IT group of the project has to maintain all the releases of every part of a solution it doesn’t mean it also owns deep technical knowledge of the technologies deployed. That knowledge is of the development team.
- Productivity: in this way the deployment is a simple click, and it’s a package prepared for people in charge of this task.
- Password management: passwords are encrypted and no need to know the data sources’ credentials to be able to do a deployment
- Versioning: the Database project is not only a set of tables on a server but it’s a project, a solution in visual studio, and developers can collaborate easily exploiting the Devops Sync features to solve conflicts.
- Environments alignment: historically the SchemaCompare function was the core feature to align Dev/Test/Prod environments. Now CI/CD pipelines will significantly help in this process.
Thanks again for the blog. Really Great.
Hello! I just would like to give you a massive thumbs up with the info you’ve here with this post. I am coming back to your site to get more soon. Step by step explanation on deploying Sql Database via DevOps is what I was really looking for.