Closed RichardBorges-AlintaEnergy closed 1 year ago
erik-bershel
commented
1 year ago Hey @RichardBorges-AlintaEnergy! It looks very strange. Please provide links to the latest green and first bad pipelines runs. And may I ask you to check if SqlAzureDacpacDeployment task version changed between green and red runs?
RichardBorges-AlintaEnergy
commented
1 year ago Hi Erik, I am not sure I fully understand your question. The Azure DevOps pipeline uses the Azure hosted agent. The dacpac file contains External Tables. The pipeline task is:
- task: SqlAzureDacpacDeployment@1
displayName: "Generate the database deployment script"
inputs:
azureSubscription: "$(subscriptionName)"
AuthenticationType: "server"
ServerName: "$(sqlServerName)"
DatabaseName: "synapse-database-name"
SqlUsername: "synapse-user-name"
SqlPassword: "$(synapse-user-password)"
deployType: "DacpacTask"
DeploymentAction: "Script"
DacpacFile: "$(rootDirForCode)/Azure SQL DBs/synapse-db/bin/Release/synapse-db.dacpac"
PublishProfile: "$(rootDirForAdhCode)/Azure SQL DBs/synapse-db/synapse_prod.publish.xml"
AdditionalArguments: "/V:Environment=${{parameters.environment}} /P:CommandTimeout=180"
IpDetectionMethod: "AutoDetect"
Previously (26 Apr 2023) This SqlAzureDacpacDeployment@1 task would generate the SQL script which is used in subsequent tasks.
The Azure hosted agent uses "C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe" version 162.0.52 (latest) This fails when I run it locally as well i.e. with version 162.0.52 (latest) However, when I use SqlPackage.exe version 161.8089.0 or below, the SqlPackage.exe generates the script file using the same dacpac file. This works locally as well as on a self hosted agent (earlier version of sqlpackage.exe) via the pipeline .
dafad-dew
commented
1 year ago This is likely a bug with DacFX rather than the image - please don't revert the image! There are problems with versions of DacFX earlier than 162.0.52 when deploying External Tables to SQL Server - they take hours to run. There's a bug in the schema comparison step, and every external table gets reinstalled every time :-(
I had to work around the issue by installing/using the .net core version of DacFx (using dotnet tool install), and calling SqlPackage direct.
Wouldn't be surprised if DacFx fixing the bug I encountered before 162.0.52 caused problems for deploying external tables to Synapse - but it sounds like DacFx is the place to raise a bug on this... In fact, one might already have been raised: https://github.com/microsoft/DacFx/issues/270
erik-bershel
commented
1 year ago Hey @RichardBorges-AlintaEnergy and @dafad-dew! We will not roll back the DacFX version, as the previous version has more issues than the current one. As @dafad-dew correctly pointed out, it would be better to reach out to the development team of the module to expedite a release with a fix for the problem or to seek recommendations on how to work around the issue instead of installing previous versions of the tool from the gallery.
RichardBorges-AlintaEnergy
commented
1 year ago Thanks Erik. I will chase up the DavFX team.
Description
When deploying a Synapse database dacpac containing External Tables to an Azure hosted Synapse database, the Win 2019 image (which includes DacFx 162.0.52.1), simply takes a very long time ( 3 to 4 hours) when running the step for Schema compare:
The previous version DacFx 161.8089.0 proceeds to successfully complete the SqlSchema check. The DacFx 162.0.52.1 version does not.
The successful command used is
Both Azure hosted runner 'Windows 2019' and 'Windows 2022' are failing the External Tables SqlSchema check.
Platforms affected
Runner images affected
Image version and build link
Starting: Initialize job Agent name: 'Hosted Agent' Agent machine name: 'fv-az905-107' Current agent version: '3.220.5' Operating System Runner Image Runner Image Provisioner Current image version: '20230620.1.0' Agent running as: 'VssAdministrator' Prepare build directory. Set build variables. Download all required tasks. Downloading task: DownloadBuildArtifacts (1.220.0) Downloading task: Bash (3.224.0) Downloading task: AzureKeyVault (1.223.0) Downloading task: SqlAzureDacpacDeployment (1.223.3) Downloading task: SqlAzureDataWarehouseDacpacDeployment (1.0.2) Downloading task: PublishPipelineMetadata (0.216.0) Checking job knob settings. Knob: DockerActionRetries = true Source: $(VSTSAGENT_DOCKER_ACTION_RETRIES) Knob: AgentToolsDirectory = C:\hostedtoolcache\windows Source: ${AGENT_TOOLSDIRECTORY} Knob: AgentPerflog = c:\vsts\perflog Source: ${VSTS_AGENT_PERFLOG} Knob: ContinueAfterCancelProcessTreeKillAttempt = true Source: $(VSTSAGENT_CONTINUE_AFTER_CANCEL_PROCESSTREEKILL_ATTEMPT) Finished checking job knob settings. Start tracking orphan processes. Finishing: Initialize job
Is it regression?
No
Expected behavior
The task: SqlAzureDacpacDeployment@1 should create the database deployment script
Actual behavior
The task: SqlAzureDacpacDeployment@1 hangs for hours at Microsoft.Data.Tools.Diagnostics.Tracer Verbose: 25 : 2023-06-27T16:59:01 : Perf: Operation started (name, details): CompareAllElementsForOneType,SqlSchema
Repro steps
Create a database project in VS2019 Containing External tables Generate the dacpac file Use SQLPackage.exe to generate the database base script (comparing to a target Azure hosted Synapse database)