Search code examples
azureazure-devopsazure-sql-databasecontinuous-deploymentazure-devops-server-2019

What are the standards of Azure Sql development with Azure DevOps?

What are the industrial standards for developing a CI/CD pipelines for Azure SQL database? I have an existing Azure SQL database (DEV instance, includes Schemas, Tables, Functions, Stored Procedures, etc. ) the code for these are hardcoded (meaning, not generated using SSDT compare nor generating script from existing table/SP/Function nor DACPAC/BACPAC file, it's just the code which developers wrote) and maintained in Git repo, Now, my users want to create another Database using the scripts which were uploaded into Git by developers (Use the code which was uploaded by developers in Git (bitbucket), meaning identifying all the dependencies of DB objects and executing them in order to create new Database, Is this the correct approach? consider this as approach 1), upon investing lots of time on deployments, I am confused/convinced that it is advised to follow below approach, let's call it as approach 2,

  1. create a solution and clone your existing Git repo in Visual Studio
  2. Import the DB objects from solution explorer and push the solution to Git.
  3. Create a pipeline includes steps as build solution/copy/publish artifact
  4. Create a new release pipeline and use "Azure SQL Data Warehouse deployment" task and link DACPAC file (which is generated from above step dynamically)
  5. Now, for incremental changes, my assumption is, Change the code-> upload in git->generate solution-> build release (the DACPAC file generated from build pipeline will be compared with current QA db and only new changes will be applied, behind the scenes, sqlpackage will be used to compare at release "Azure SQL Data Warehouse deployment task" )

Links I have gone thru: Configure CD of Azure SQL database using Azure DevOps and Visual Studio

Please correct me if my understanding is wrong,

Thanks a ton, A DevOps newbie here.

Solution

  • Azure DevOps services provide the Azure SQL database deployment task to deploy an Azure SQL database.

    So the approach 2 is the common way. With the task we can deploy an Azure SQL Database using DACPAC or run scripts using SQLCMD.

    You can also reference the following links: