Azure Function Database Maintenance

I had a maintenance task to run on some Sitecore databases. In the past, I used an automation account, but this time I used a different approach. I created an Azure Function and enabled its identity. Then, I created the database user and configured the script. This script uses the Az and SqlServer modules.

This is how to enable the Identity:

To import the PowerShell module, you can edit the requirements.psd1 file and add the following values:

You need to create the user in the database, this is the script to creation:

CREATE USER [hqew1ww-rg-p04-281306-dbmaintenance] FROM EXTERNAL PROVIDER;

ALTER ROLE db_owner ADD MEMBER [hqew1ww-rg-p04-281306-dbmaintenance];

These is the PowerShell script to run:

param($Timer)
$query = @"
DECLARE @TableName varchar(255) 
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN TableCursor 
    FETCH NEXT FROM TableCursor INTO @TableName 
    WHILE @@FETCH_STATUS = 0 
    BEGIN
        DBCC DBREINDEX(@TableName,' ',80) 
        FETCH NEXT FROM TableCursor INTO @TableName 
    END
CLOSE TableCursor 
DEALLOCATE TableCursor
"@

# Define the connection parameters
$serverName = "sqlserver.database.windows.net"
$databaseNameCore = "core-db"
$databaseNameMaster = "master-db"
$databaseNameRefData = "refdata-db"
$databaseNameReporting = "reporting-db"
$databaseNameWeb = "web-db"

$token = (Get-AzAccessToken -ResourceUrl https://database.windows.net -AsSecureString).Token
$plainToken = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto(
    [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($token)
)

Invoke-SqlCmd -ServerInstance $serverName -Database $databaseNameCore -AccessToken $plainToken -Query $query

Invoke-SqlCmd -ServerInstance $serverName -Database $databaseNameMaster -AccessToken $plainToken -Query $query

Invoke-SqlCmd -ServerInstance $serverName -Database $databaseNameRefData -AccessToken $plainToken -Query $query

Invoke-SqlCmd -ServerInstance $serverName -Database $databaseNameReporting -AccessToken $plainToken -Query $query

Invoke-SqlCmd -ServerInstance $serverName -Database $databaseNameWeb -AccessToken $plainToken -Query $query