Data comparison in Azure DevOps using SQL Change Automation

BogdanS

Currently we are working on implementing database releases using Azure DevOps and SQL Change Automation.

The problem is that I haven't been able to compare data. What I need to do is Compare two tables in different databases (while ignoring 2 rows).

Is there any way of doing this using Redgate SQL Change Automation Build or Release?

Thank you

Pete_Ruiz

Hello @BogdanS ,

Thanks for your query regarding SQL Change Automation. There are a few ways to include data in your pipeline using SQL Change Automation. If you're using SQL Source Control as your development source, then you have the option to link static data to version control. Please see the documentation below for more details on this.

https://documentation.red-gate.com/soc6/common-tasks/link-static-data

You can also use SQL Change Automation PowerShell to deploy static data as well and the documentation below outlines this.

https://documentation.red-gate.com/sca/deploying-database-changes/automated-deployment-with-sql-source-control-projects/deploying-static-data-with-sql-change-automation-powershell

Lastly, you can also use the SQL Data Compare command line to compare and deploy data. In AzureDevops you can setup an additional release step that calls on the SQL Data Compare command line. Please see the documentation below for more information on the SQL Data Compare command line.

https://documentation.red-gate.com/sdc/using-the-command-line

I hope this is helpful. Please let me know if you have any further questions.

Kind regards,

Pete Ruiz

BogdanS

Hello Pete

I have tried the 3rd approach. 

I tested the script on premises. The problem i got is that the powershell doesn't recognize sqldatacompare.exe in Azure DevOps. 

Are you sure this is possible without previous installation?

Do I have to change folders to make it point to the installation path?

BogdanS

For anyone facing this problem.

You need to install the powershell module:
Install-Module -Name SqlChangeAutomation -AllowClobber -Force -AcceptLicense

And from the powershell task navigate to the install path:
C:\Program Files\WindowsPowerShell\Modules\SqlChangeAutomation\$version\SDC

Then you can call the sqldatacompare application

Pete_Ruiz

Hello @BogdanS ,

Thanks for the update. You are correct, you would need to create a separate PowerShell step and point it to the install path where the SQLDataCompare.exe is located. I'm glad you were able to get this working.

Please let me know if you have any further questions.

Kind regards,

Pete Ruiz