Answers: <p>I eventually found the solution :) </p>
  
  <p>In an Azure Devops Release pipelines I've created a Command Line task that executes <code>SqlPackage.exe /Action:DeployReport /p:DoNotAlterReplicatedObjects=false ...</code> which creates an XML file that contains info on which tables etc would be changed by a deployment. It's important to note that <code>DoNotAlterReplicatedObjects</code> should be set to false, otherwise SqlPackage will fail.</p>
  
  <p>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</p>
  
  <pre><code>Write-Host "##vso[task.setvariable variable=haltReplication;isOutput=true]$haltReplication"`
  </code></pre>
  
  <p>Third step is yet another Powershell script that will stop the replication. This third script will only run if <code>$haltReplication</code> evaulates to true. In the Control Options, the custom condition is <code>and(succeeded(), eq(variables['haltReplication.haltReplication'],'1'))</code></p>
  
  <p>**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 <a href="https://stackoverflow.com/questions/36417189/ignore-namespace-in-xpath-as-this-can-be-dynamic?rq=1">https://stackoverflow.com/questions/36417189/ignore-namespace-in-xpath-as-this-can-be-dynamic?rq=1</a> </p>
  
Answers: CC BY-SA 4.0
Answers: 2020-03-16T09:37:47.697Z
Answers: 2020-03-16T09:37:47.697Z
Answers: SimonAx
Body: <p>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. </p>
  
  <p>The manual intervention is annoying me. Has anyone already solved this issue in an Azure Devops Release Pipeline? </p>
  
  <p>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. </p>
  
Comments: CC BY-SA 4.0
Comments: 2019-12-25T12:02:25.167Z
Comments: 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?
Comments: 030
Comments: CC BY-SA 4.0
Comments: 2020-03-13T21:17:01.050Z
Comments: You could post the comment as an answer.
Comments: 030
Tags: <azure-devops><sql>
Title: Conditionally stopping SQL replication during deployment
UserDisplayName: SimonAx
ThreadText: # Conditionally stopping SQL replication during deployment
  
  (Asked by: username_0 on 2019-07-22)
  
  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.
  
  **Tags:** azure-devops, sql
  
  > **Comments:**
  > **username_1**: 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?  
  > **username_1**: You could post the comment as an answer.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2020-03-16. Score: 2 | Accepted answer
  
  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>
  
  
  ---
  
  