kevchant / azuredevops-dedicatedsqlpooltofabricdw

Example of a state-Based deployment that can create a dacpac file based on an existing database within a dedicated SQL Pool and then deploy it to a Microsoft Fabric Data Warehouse

AzureDevOps-dedicatedSQLPoolToFabricDW

Example of a state-Based deployment that can create a dacpac file based on an existing database within a dedicated SQL Pool and then deploy it to a Microsoft Fabric Data Warehouse.

It is based on a blog post I wrote called 'Migrate dedicated SQL Pool objects to a Microsoft Fabric Data Warehouse'.

You can find the YAML file which you can use as a template for the Azure Pipeline in the AzureDevOpsTemplates folder.

Note that you need a recent version of SQLPackage for this to work.

The 'dedicatedSQLPool-sqlpackage.yml pipeline' extracts the contents of a database in a dedicated SQL Pool to a dacpac. It then deploys the contents of the dacpac to a Microsoft Fabric Data Warehouse.

Please note that you need the below variables created for this YAML pipeline to work: I recommend doing this by creating at least one variable group

• agentpool - The name of the agent pool you want to use (ideally a self-hosted one with latest sqlpackage installed). Otherwise you must put additional logic in this pipeline to deploy latest version of sqlpackage onto the agent
• TargetFile - The name of the dacpac that you want to be created
• TargetFolder - The name of the Folder the Azure Pipelines works with behind the scenes
• SourceSQLPoolEndPoint - The name of your serverless SQL Pool endpoint (which you can get in the Azure Synapse overview)
• SourceDB - The name of database in the serverless SQL Pool you want to extract the schema from
• SQLPooluser - A SQL login that can access the serverless SQL Pool
• SQLPoolpw - The p/w for the above SQL login
• SQLPoolartifactname - A name for the created artifact
• AzureSubscription - The Azure subscription that contains the Azure Active Directory tenant used by your Microsoft Fabric environment
• aadSqlUsername - An Azure Active Directory account
• aadSqlpw - P/w for the Azure Active Directory account
• DestSQLConnString - The connection string used by the Microsoft Fabric Data Warehouse. As shown 'here'
• DestinationDW - The name of your Microsoft Fabric Data Warehouse

Note that you can extend this however you see fit.

azuredevops-dedicatedsqlpooltofabricdw's Issues

PowerShell code cleanup

The only big thing I noticed

• match and notmatch are RegEx comparisons and as such the "exclude lines starting with 'WITH'" part may have unexpected issues. Replacing the RegEx with '^\s*WITH' should solve this. If you want it to be only capital WITH, and not match lower case with, you may use the -cnotmatch operator as well.
  

Minor things:

• Using Select on line 66 only limits the properties sent on through the pipeline. We can clean up the code by using Select-Object -ExpandProperty and have a string array right away instead
  
• Twice you're doing cd.. ; cd a. One of them, in the dotnet new sqlproj task, doesn't seem to do anything and can be removed, The other one could be replaced by Set-Location $(Build.ArtifactStagingDirectory)? Otherwise a change of working directory may mess up the workflow.