AzureSQL Automation - Target Elastic database pool excluding certain databases

Question

question

SimonHolland-1962 asked Feb 25, '22 SimonHolland-1962 answered Mar 30, '22

AzureSQL Automation - Target Elastic database pool excluding certain databases

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' ,@mode='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' ,@mode='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' ,@mode='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' ,@mode='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' ,@mode='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' ,@mode='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 azure-automation

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2022-02-28T20:19:31.58Z

OuryBa-MSFT answered Feb 28, '22 OuryBa-MSFT converted comment to answer Mar 30, '22

Hi @SimonHolland-1962 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' ,@mode='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

· 3

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OuryBa-MSFT · Mar 01 at 09:31 PM

Hi @SimonHolland-1962 Please mark as accept answer if the reply was helpful. It will help other community members facing the same problem.

Regards,
Oury

0 ·

SimonHolland-1962 OuryBa-MSFT · Mar 02 at 06:01 PM

Hi OuryBa-MSFT,

Many thanks for your reply however there are a couple of problems with this as far as i can tell, the Documentation link provided doesn't show how to target an elastic pool directly, only how to collect the database names within a pool and store them within the elastic job table which also requires setting credentials in all individual databases

At any rate the sample script you provided looked like it would do exactly what i was aiming for, so i modified a test runbook and took out the lines that referred to each individual database and set the variables for the resource group and elastic pool name, however i get a strange error on the test pane when editing the runbook:

Upcoming breaking changes in the cmdlet 'Get-AzSqlDatabase' :
- The output type 'Microsoft.Azure.Commands.Sql.Database.Model.AzureSqlDatabaseModel' is changing
- The following properties in the output type are being deprecated : 'BackupStorageRedundancy'
- The following properties are being added to the output type : 'CurrentBackupStorageRedundancy' 'RequestedBackupStorageRedundancy'
- The change is expected to take effect from the version : '3.0.0'
Note : Go to https://aka.ms/azps-changewarnings for steps to suppress this breaking change warning, and other information on breaking changes in Azure PowerShell.

No subscription found in the context. Please ensure that the credentials you provided are authorized to access an Azure subscription, then run Connect-AzAccount to login.

Any ideas?

Many thanks, Simon

0 ·

OuryBa-MSFT SimonHolland-1962 · Mar 10 at 11:07 PM

Hi @SimonHolland-1962 Sorry for the delay in my response.

Could you please open a support ticket so we can further investigate this?
Let me know if you don't have a support plan.

Regards,
Oury

0 ·

Answer 2 · 2022-03-22T15:49:24.087Z

SimonHolland-1962 answered Mar 22, '22

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?

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question