0

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.

Santhosh
  • 9
  • 4

1 Answers1

1

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:

Andy Li-MSFT
  • 24,802
  • 2
  • 22
  • 40
  • Thank you so much for the links and helping me to decide from the two approaches, my biggest confusion was, as we are in the middle of development, are we suppose to ignore the scripts from version control and make the actual database as a source of truth and proceed (state-based approach as per https://devblogs.microsoft.com/azure-sql/devops-for-azure-sql/) – Santhosh Jul 13 '20 at 08:41
  • Actually it depends entirely on your wishes. If the current scripts work well, then that's OK. However we recommend that you could follow the common way to deploy the Azure SQL DB, so you can easily find more references when you encounter problems during the deployment. – Andy Li-MSFT Jul 13 '20 at 09:58
  • Sure, Thank you so much @andy-li-msft, one last doubt, just making my self clear on the Migration-based approach as well. I understand that for incremental changes like alter and all, We have Third-party tools that might help us in finding out the new changes but how we deploy for the first time user opt for migration based approach, is it still the same Azure SQL Deployment with DACPAC as input then using third party tools?? – Santhosh Jul 13 '20 at 10:13
  • Sorry, no any experience on your scenario. However in my opinion it should be the same Azure SQL Deployment with DACPAC as input. – Andy Li-MSFT Jul 15 '20 at 01:52
  • oh okay, thank you so much for all your inputs though! – Santhosh Jul 15 '20 at 08:16