2

The project that i recently started working on uses a dacpac to deploy / update a database in MS SQL Server. The database is replicated, whenever the dacpac contains modifications to any table, we manually terminate the replication before continuing with the deployment. After the deployment, we run a script to start with a fresh replication. On the other hand, if only stored procedures have been modified, the replication can continue during the deployment.

The manual intervention is annoying me. Has anyone already solved this issue in an Azure Devops Release Pipeline?

The fallback is to use SqlPackage.exe /action;deployreport, but that means I have to write a file to disk to get the diff and subsequently inspect that outcome.

  • Thank you for posting a question. Could you modify the question so that it contains the attempts you have done in order to solve the issue? – 030 Dec 25 '19 at 12:02
  • You could post the comment as an answer. – 030 Mar 13 at 21:17
0

I eventually found the solution :)

In an Azure Devops Release pipelines I've created a Command Line task that executes SqlPackage.exe /Action:DeployReport /p:DoNotAlterReplicatedObjects=false ... which creates an XML file that contains info on which tables etc would be changed by a deployment. It's important to note that DoNotAlterReplicatedObjects should be set to false, otherwise SqlPackage will fail.

In a second task in the Azure Release pipeline, I analyze the generated DeployReport (e.g. the XML file) using Powershell and XPath**. The Powershell task will create an boolean output variable that will indicate whether replication needs to be stopped or not. In my case, I analyze the XML and if a table is created/altered/dropped, the replication will be stopped. The syntax for setting the output variable is

Write-Host "##vso[task.setvariable variable=haltReplication;isOutput=true]$haltReplication"`

Third step is yet another Powershell script that will stop the replication. This third script will only run if $haltReplication evaulates to true. In the Control Options, the custom condition is and(succeeded(), eq(variables['haltReplication.haltReplication'],'1'))

**The XML file generated by SqlPackage.exe contains namespaces, which makes working with XPath a bit cumbersome. The following SO answer helped me a lot https://stackoverflow.com/questions/36417189/ignore-namespace-in-xpath-as-this-can-be-dynamic?rq=1

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.