4

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?

  • What issue are you having? – Anthony Genovese Nov 11 at 15:21
  • Are you using azure VMs? or is the only azure part the azure dev ops? – Anthony Genovese Nov 11 at 15:22
  • @AnthonyGenovese, The issue is the Azure Devops agent can't connect to our SQL Servers to both create a release (by comparing the build artifact to the target DB) and to deploy the release to the same target DB. We are using a mixture of on-prem SQL Servers (in VMWare) and Azure SQL VMs (no public IP). – PeterDaniels Nov 11 at 22:55
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.

  • IMO, MS documentation has gone to crap in the past few years. The transition over to a "community based support model" has not proven to be beneficial in my mind, but the flip side is you can make some changes to the docs if you find they need improvements. – John Eisbrener Nov 15 at 15:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.