CICD for Azure SQL Server Using Python

Question

I am looking to create a CICD pipeline for my Azure SQL Database. I have read about the State-based approach and the Migration-based approach that has been described here,

https://devblogs.microsoft.com/azure-sql/devops-for-azure-sql/

However, I want to know if there is an approach I can use to do this in Python. I am looking to deploy both schema and data changes to the other environment through my pipeline. It would be great if I can implement a method that will deploy only chosen data points though. For example, if I can filter on a stage column for production records.

What kind of approach can I take to accomplish this?

It does not matter if I need to trigger this CICD pipeline manually through an API call or something. I believe this is also possible in Azure Pipelines.

Answer 1

What you can do is deploy the sql server normally and then make changes by adding a different task in .yaml file which will execute the changes .

For this you can either use DACPAC or you can directly use sql script .

In both the case you have to create you sql based script before deploying.

for DACPAC you need add the following type to task :

- task: SqlAzureDacpacDeployment@1
  displayName: Execute Azure SQL : DacpacTask
  inputs:
    azureSubscription: '<Azure service connection>'
    ServerName: '<Database server name>'
    DatabaseName: '<Database name>'
    SqlUsername: '<SQL user name>'
    SqlPassword: '<SQL user password>'
    DacpacFile: '<Location of Dacpac file in $(Build.SourcesDirectory) after compilation>' 
  

for sql script add the following type to task :

- task: AzureMysqlDeployment@1
  inputs:
    ConnectedServiceName: # Or alias azureSubscription
    ServerName:
    #DatabaseName: # Optional
    SqlUsername:
    SqlPassword:
    #TaskNameSelector: 'SqlTaskFile' # Optional. Options: SqlTaskFile, InlineSqlTask
    #SqlFile: # Required when taskNameSelector == SqlTaskFile
    #SqlInline: # Required when taskNameSelector == InlineSqlTask
    #SqlAdditionalArguments: # Optional
    #IpDetectionMethod: 'AutoDetect' # Options: AutoDetect, IPAddressRange
    #StartIpAddress: # Required when ipDetectionMethod == IPAddressRange
    #EndIpAddress: # Required when ipDetectionMethod == IPAddressRange
    #DeleteFirewallRule: true # Optional
    

For detailed explanation please refer the following documentation on DACPAC task and refer this documentation for the sql script task.

As of now there are no Api to start-stop preexisting pipeline I have consulted this documentation for this.