AzureSQL Automation - Target Elastic database pool excluding certain databases

Simon Holland 6 Reputation points
2022-02-25T16:01:08.937+00:00

Hi there,

Like many I have needed to Automate certain tasks across several databases, (in this instance the databases are all part of a single elastic database pool). My first task was to schedule the rebuilding of database indexes, however the 'elastic jobs' are complex in it's setup and doesn't allow for changes such as additional databases being added, without having to make changes to each individual database when they are created or added, as this is a frequent occurrence for me I then looked at an Azure Automation account, this seemed to me to be a way around some of the limitations and is much simpler to setup, for example I can define and store the SQL server name and the SQL server credentials in the Automation account itself, which is much better if I ever need to change the SQL server or it's login details, I also don't need to create or manage credentials across the individual databases, what I couldn't find though was anyway of targeting all databases within an elastic database pool (or even better target all databases within a pool excluding a single database) now Azure Automation relies on runbooks the same as elastic jobs does so I guess this comes down to if it is possible to define the elastic database pool as the target within the PowerShell script / runbook?

here is my working script using an Automation account to rebuild the indexes on schedule, using the database stored procedure AzureSQLMaintenance.

$AzureSQLServerName = Get-AutomationVariable -Name "SqlServer"
$AzureSQLServerString = $AzureSQLServerName + ".database.windows.net"
$Cred = Get-AutomationPSCredential -Name "Credential1"

$AzureSQLDatabaseName1 = "my_database_name1"
$AzureSQLDatabaseName2 = "my_database_name2"
$AzureSQLDatabaseName3 = "my_database_name3"
$AzureSQLDatabaseName4 = "my_database_name4"
$AzureSQLDatabaseName5 = "my_database_name5"
$AzureSQLDatabaseName6 = "my_database_name6"

Invoke-Sqlcmd -ServerInstance $AzureSQLServerString -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database $AzureSQLDatabaseName1 -Query "exec [dbo].[AzureSQLMaintenance] @operation ='all' ,@Mohamed Naseem ='smart',@LogToTable=1" -QueryTimeout 65535 -ConnectionTimeout 60
Invoke-Sqlcmd -ServerInstance $AzureSQLServerString -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database $AzureSQLDatabaseName2 -Query "exec [dbo].[AzureSQLMaintenance] @operation ='all' ,@Mohamed Naseem ='smart',@LogToTable=1" -QueryTimeout 65535 -ConnectionTimeout 60
Invoke-Sqlcmd -ServerInstance $AzureSQLServerString -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database $AzureSQLDatabaseName3 -Query "exec [dbo].[AzureSQLMaintenance] @operation ='all' ,@Mohamed Naseem ='smart',@LogToTable=1" -QueryTimeout 65535 -ConnectionTimeout 60
Invoke-Sqlcmd -ServerInstance $AzureSQLServerString -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database $AzureSQLDatabaseName4 -Query "exec [dbo].[AzureSQLMaintenance] @operation ='all' ,@Mohamed Naseem ='smart',@LogToTable=1" -QueryTimeout 65535 -ConnectionTimeout 60
Invoke-Sqlcmd -ServerInstance $AzureSQLServerString -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database $AzureSQLDatabaseName5 -Query "exec [dbo].[AzureSQLMaintenance] @operation ='all' ,@Mohamed Naseem ='smart',@LogToTable=1" -QueryTimeout 65535 -ConnectionTimeout 60
Invoke-Sqlcmd -ServerInstance $AzureSQLServerString -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database $AzureSQLDatabaseName6 -Query "exec [dbo].[AzureSQLMaintenance] @operation ='all' ,@Mohamed Naseem ='smart',@LogToTable=1" -QueryTimeout 65535 -ConnectionTimeout 60

does anyone know if it is possible to target the elastic database pool , excluding a single database within that pool in PowerShell?

I'll also need this for other tasks, such as setting column data classification and sensitivity labels, data collection and reporting etc so being able to target an elastic group would really help

Many thanks, Simon

Azure SQL Database
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Oury Ba-MSFT 20,106 Reputation points Microsoft Employee
    2022-02-28T20:19:31.58+00:00

    Hi @Simon Holland Thank you for posting you question on Microsoft Q&A and for using Azure services.

    As per the description above, you are trying to create an automation to rebuild database indexes using elastic jobs.

    Elastic jobs can target all databases in a server or Elastic pool. Moreover, it does dynamic enumeration of databases at run time, an important consideration for SaaS customers who keep adding/dropping databases when new customers are added/dropped. Job scripts will enumerate the list of DBs at the run time and will pick them up automatically without having to change the script. That facilitates the automation by not having to change the script when new customers/DBs are added/dropped from a server/pool. In addition, you can also specify exclude list to exclude individual databases in a server/pool. Azure Documentation has more details here.

    • if you want to execute Invoke-SqlCmd on all the DBs in a pool, without having to individually write separate lines of code per DB, one possible way is to use the PowerShell Get-AzSqlDatabase cmdlet and then loop through the DBs within that pool. Further, you could exclude DBs by providing an array of such DB names to exclude:

    Get-AzSqlDatabase -ResourceGroupName $VARIABLE_FOR_RESOURCE_GROUP_NAME -ServerName $AzureSQLServerName | Where-Object ElasticPoolName -eq $VARIABLE_FOR_ELASTIC_POOL_NAME | Where-Object DatabaseName -NotIn @("DB_NAME_TO_EXCLUDE", "DB_NAME_TO_EXCLUDE") | % { Invoke-Sqlcmd -ServerInstance $AzureSQLServerString -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database ($_.DatabaseName) -Query "exec [dbo].[AzureSQLMaintenance] @operation ='all' ,@Mohamed Naseem ='smart',@LogToTable=1" -QueryTimeout 65535 -ConnectionTimeout 60 }

    All the Elastic Job T-SQL APIs also have corresponding Powershell and REST APIs that can be used for creating and executing jobs, including ability to target all DBS in a server/pool and also selectively exclude certain DBs if needed. Some examples of Power Shell APIs can be found in the documentation also.

    Hope that helps

    Regards,
    Oury

    1 person found this answer helpful.

  2. Simon Holland 6 Reputation points
    2022-03-22T15:49:24.087+00:00

    Apologies for the delay in replying I missed the email saying you'd replied,

    at any rate I've now fixed that problem by adding "Connect-AzAccount -Identity" to the beginning of the script, this now allows the script to use a managed identity with the correct role assigned.

    now it's been running for a few weeks though I've come across another issue, there doesn't seem to be any way of telling the automation account to use the virtual network, so when it's IP address changes I have to update the server firewall

    how do I make it so that it is using the Virtual network or private endpoint so that it doesn't get rejected by the SQL server firewall when the automation accounts IP address changes?

    1 person found this answer helpful.

  3. Mohamed bouddi 0 Reputation points
    2023-07-16T18:02:42.5833333+00:00

    HAW cih bank open m'y attach

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.