<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>

CC BY-SA 4.0
2020-03-16T09:37:47.697Z
2020-03-16T09:37:47.697Z
SimonAx
<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>

CC BY-SA 4.0
2019-12-25T12:02:25.167Z
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
CC BY-SA 4.0
2020-03-13T21:17:01.050Z
You could post the comment as an answer.
030
<azure-devops><sql>
Conditionally stopping SQL replication during deployment
SimonAx
# 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


---

