Power BI on-prem data gateway for Azure devops pipeline?

Question

I'm building a POC for DB devops using redgate SCA and an azure devops pipeline. Need to connect to our SQL Servers that don't have public IPs. Was hoping power BI on-prem gateway would facilitate. Is there a way to make that work, or do I need local build agents?

To clarify, I have :

1. Azure devops (cloud) on org.VisualStudio.Com
2. RedGate SQL Change Automation project in: TFSVC based repo (in azure devops services in org.VisualStudio.Com
3. Integration DB on SQL Server instance in company network - no public IP.

I now have a azure devops build pipeline triggered by source commit. The SCA build artifact is created and stored. I want to deploy to our integration SQL Server DB.

At this point, I believe I need to install and configure a local windows build agent to enlist in the pipeline. It seems unfortunate, though, since we already have power BI on-premises data gateway. It would be nice to leverage that connectivity in our deployment pipeline instead of having to configure a local build agent.

It's also unfortunate because I'm finding the online docs for configuring a local build agent to be challenging and unclear.

Can you help me get over this hurdle?

Answer 1

I have succeeded in getting the Azure devops pipeline to create releases and deploy to on-prem resources (sql servers) .

The overall solution was indeed to install and configure a self-hosted windows agent for azure pipeline. This agent vm has the redgate powershell components installed on it.

Some interesting hurdles that had to be overcome :

1) when running the .\config.cmd to install and configure the agent, it has to be run in a regular powershell cmd shell. ISE doesn't work.

2) copy and paste didn't work to get my PAT into the powershell window when running config.cmd. I actually had to type it in.

I'd love to see the MS docs get cleaned up a bit. They were pretty unclear in relation to what I saw on my screen in .visualstudio.com

Cheers.