Conditionally stopping SQL replication during deployment

Question

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.

Answer 1

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