Running SQL against multiple DB in azure pipeline

Question

I want to set up the step in release process for azure pipeline where I can run the SQL from the file checked into the repo against multiple databases.

In my environment, I have one central db, one of the table for e.g "Control" table, keeps the connection to all the databases against which I need to run the sql during the deployment.

What I dont want to do is to setup the deployment step for each database. Is Powershell my only friend? If so, how can I let powershell know what is the SQL in the file checked in during the deployment?

Answer 1

What I dont want to do is to setup the deployment step for each database. Is Powershell my only friend? If so, how can I let powershell know what is the SQL in the file checked in during the deployment?

In your case, Powershell seems to be the most appropriate.

In order to get the SQL in the file checked in during the deployment, we could use the powershell scripts to get the file names and paths, like:

$files=$(git diff HEAD HEAD~ --name-only)
$temp=$files -split ' '
$count=$temp.Length
echo "Total changed $count files"
For ($i=0; $i -lt $temp.Length; $i++)
{
  $name=$temp[$i]
  echo "this is $name file"
  Copy-Item "$name" -Destination "$(System.DefaultWorkingDirectory)\SQLDeploymentFolder"
}

Check the ticket for some more details.

Then, we could use the extension Run SQL Server Scripts or Run SQL / SQLCMD Scripts passing multiple SQLCMD variables to all SQL Scripts in a folder.

Hope this helps.