I started working on this bit of code a few months ago, and it’s served me really well. Just about every command you run against a SQL Database requires you to supply the server name and the resource group name at parameters. And in order to get the list of server names you have to do it for each for resource group.
This code is pretty simple and looks for an Azure SQL Server in each resource group, and then looks for the databases that aren’t master on each server. In this example I’m setting the storage account for Azure Threat Detection, but you could do anything you wanted in that last loop.
$rg=(Get-AzResourceGroup).ResourceGroupName
foreach ($rgs in $rg)
{
$svr=(get-azsqlserver -ResourceGroupName $rgs).ServerName
#write-host 'rg:'$rgs
foreach ($svrs in $svr)
{
#write-host 'server:'$svrs
if ($svr.Location -eq 'West US' ) {set-variable $stg='storage2'}
{
$dbs=(Get-azSqlDatabase -ResourceGroupName $rgs -ServerName $svrs|Where-Object {$_.DatabaseName -NE 'master'}).DatabaseName|Set-AzSqlDatabaseThreatDetectionPolicy -ResourceGroupName $rgs -ServerName $svrs -DatabaseName $dbs -NotificationRecipientsEmails "bob@contoso.com" -EmailAdmins $True -StorageAccountName $stg
else ($svr.Location -eq 'West US 2') {set-variable $stg='storage1'}
$dbs=(Get-azSqlDatabase -ResourceGroupName $rgs -ServerName $svrs|Where-Object {$_.DatabaseName -NE 'master'}).DatabaseName|Set-AzSqlDatabaseThreatDetectionPolicy -ResourceGroupName $rgs -ServerName $svrs -DatabaseName $dbs -NotificationRecipientsEmails "bob@contoso.com" -EmailAdmins $True -StorageAccountName $stg
}
}
}
The last bit of complication in this code, is specifying the storage account based on the location of the Azure SQL Server, which is a property of the server’s object.
Pingback: Running Powershell Against your Azure SQL DBs – Curated SQL
Pingback: Managing Statistics in Azure SQL Database Serverless – The SQL Herald
Pingback: Building a Data as a Service Platform on Azure SQL Database – The SQL Herald