How to Deploy Azure SQL database using dacpac and Azure DevOps

-
 



Prerequisites 

  1. Azure SQL Server with a source and target database instance
  2. GitHub repo
  3. Visual code for SQL Server Database Objects
  4. Familiarity about dacpac
  5. Azure DevOps Account
Things I didn't check 
Since I was an admin on azure, DevOps, I am not sure on the permissions required. Ideally contrubutor access should be enough to implement this solution. 

Problem Statement and Solution

We are using dacpac for deploying our SQL artifacts from source to target server/database. The SQL server code, as well as the dacpac, is checked inside the GitHub repo. We need to develop an azure DevOps pipeline that uses the dacpac and does the deployment. 

I have found if you change the dacpac location to a custom folder and sync it to the Github repo, we could achieve what we had set out to. 

Logical Steps

1. Create a SSDT project 

2. Link SSDT to github repo with a local repo

3. Create a folder for dacpac in local repo

4. Add settings in visual studio to point dacpac creation to this folder

5. Build the solution

6. Dacpac should be created in the new folder

7. Push the code and sync it to github repo

8. Verify the new folder is available in github repo

9. Create a devops pipeline with dacpac option 

10. Configure and link it to github repo/branch

11. Test and run the pipeline on target server/database

12. SQL Objects should be deployed and should be available on the target server/database

DEMO

1. Create a new repo inside GitHub



 




2. Create a new SQL Server Database Project









3. Import source database 














4. Link it to GitHub repo and push the code to the newly created repository.


















5. Confirm if the code is checked in 











6. Change the build output path (this will be the path where dacpac will be created)












7. Create a new folder and specify this as build path













8. Build the solution to create dacpac and confirm if its created.
























9. Push and Sync these changes to GitHub repo












10. Login to azure DevOps account. Navigate to org-->project-->release pipeline










11. Click on create a pipeline and select GitHub as the source

















12. Link it to your GitHub repo and branch












13. Create an empty job















14. Save the pipeline











15. Add a step for azure sql server deployment





16. Configure all the details such as subscription, server user name and password, target database (which SQL objects need to be deployed)













17. Run the pipeline
















18. It's good that we got this error, we need to change the project settings to azure SQL Server project













19. Run the pipeline again and ensure it is successful. 














20. Compare target database with the source to verify if the deployment was successful.















Hope this was helpful, let me know your suggestions or any blockers where you might have got stuck!

Happy programming!

Comments

  1. Lokesh11 May 2021 at 08:24

    Full demo available here

    https://www.youtube.com/watch?v=rKhEWQK-VQo

    ReplyDelete

Post a Comment

Popular posts from this blog

SQL QUERY NIGHTMARE

-
Image
Last week, we faced a basic and yet the only major problem that sql developers/admin had to deal with and yes your guess is right, It was with SQL QUERY RUNNING SLOW.  I will be explaining the problem below, however I want everyone reading this to help me find answers because I still haven't found one! One more amazing aspect of the issue was that it worked fairly quickly in Development but was running like a tortoise in Production even though the number of records were practically the same. I'll start by throwing some light on to the execution plan, so that we narrow down to the code which was the culprit here. The code is enclosed into a stored proc. On contacting my dba to enquire about the issue, I got the following replay:- I must hand it to our dba to provide such a detailed response.  So my job was now to look at the code and modify it. What I did was to shrink the calender table into a temp table consisting of few rows that would speed up the operation. Be...
Read more

Visual Studio Git Error | "terminal prompts disabled"

-
Image
I was trying to sync our team's database project and was getting the below error git: 'credential-manager-core' is not a git command could not read Username for 'https://github.com': terminal prompts disabled One thing to call out here is that I had not synced the changes in the database project for over a month and there were no issues before.  My guess is that somehow the OWA authentication got wiped out from my system and this might be the reason for this issue.  In terms of the Solution I tried searching online on  terminal prompts disabled and got a plethora of solutions mostly suggesting some git commands. I tried a few of the suggestions and didn't get any luck. This morning I started searching for  git: 'credential-manager-core' is not a git command   and came across the following issue reported on Microsoft forums  Warning/error message when running git fetch within Visual Studio 2019 It basically suggests downloading/updating the latest versi...
Read more

Issues Integrating Azure Data Factory with GITHUB | IN spite of admin rights on repository

-
Image
Although I have been aware of GitHub for quite a while but got a chance to use it as a repository in the project for storing artifacts only over the past few months.  We use GitHub as the sole cloud repo for storing SQL, Databricks, and Azure Data Factory artifacts.  Got an opportunity to set up GitHub with ADF right from scratch and all the excitement to learn more about GitHub took a back seat when I stumbled upon the following issue Problem Statement - You are an admin on a repo in GitHub and even then get a permission error while linking the ADF to GitHub Solution - After scratching my hairs and going on a searching spree on google for some days, I was able to figure out what needed to be done, however was not aware of how to implement it. Finally thanks to Microsoft for getting on a call and helping me resolve this issue. What needed to be done - Need to authorize AzureDataFactory service so that Github can communicate with it.  How it should be done -  Basicall...
Read more