Index maintenance is a critical task for optimizing database performance, ensuring queries run efficiently, and keeping Azure SQL Database in top shape. Traditionally, scheduling index maintenance required storing credentials in scripts, posing security risks. However, in this blog, we’ll walk through a secure way to automate index maintenance using Azure Automation Account, Managed Identity, and PowerShell, ensuring no explicit credentials are stored. 🔒
Check out the video in you tube for a hand on experience.
✅ Why Automate Index Maintenance?
- Enhances query performance by reducing fragmentation.
- Ensures index optimization without manual intervention.
- Uses Managed Identity for secure authentication, avoiding stored credentials.
- Fully automated scheduling with Azure Automation Account.
🏗️ Prerequisites
Before we begin, ensure you have the following:
- An Azure SQL Database instance.
- An Azure Automation Account.
- The Ola Hallengren IndexOptimize stored procedure installed on your database.
- PowerShell Az module installed in the Automation Account.
🎯 Step 1: Create a Managed Identity for the Automation Account
To allow your Automation Account to securely authenticate against Azure SQL Database, assign it a System-Assigned Managed Identity:
- Navigate to Azure Portal → Automation Accounts.
- Select your Automation Account.
- Under Identity, enable System Assigned Managed Identity.
- Copy the Object ID; we’ll need it in the next step.
🔑 Step 2: Grant Database Access to the Managed Identity
Now, we need to create a login and database user in Azure SQL Database for the Managed Identity.
Run the following T-SQL script in your Azure SQL Database:
CREATE USER jbdbreindex FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER jbdbreindex;This grants db_owner permissions to the Managed Identity, allowing it to execute maintenance tasks.
🖥️ Step 3: Create an Azure Automation Runbook
Next, let’s create a PowerShell Runbook that connects to Azure SQL Database using the Managed Identity and executes the IndexOptimize stored procedure.
- In Azure Portal, navigate to Automation Accounts → Runbooks.
- Click Create a Runbook.
- Provide a name (e.g.,
IndexMaintenanceRunbook), select PowerShell as the Runbook type. - Paste the following PowerShell script:
Write-Output "Index Maintenance started"
# Instantiate the connection to the SQL Database
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
# Connect using Managed Identity
Connect-AzAccount -Identity
# Get a Token
$token = (Get-AzAccessToken -ResourceUrl https://database.windows.net ).Token
# Initialize Connection String
$sqlConnection.ConnectionString = "Data Source=jbsserver.database.windows.net;Initial Catalog=jbdb;Connect Timeout=60"
# Set the Token for authentication
$sqlConnection.AccessToken = $token
# Open the connection
$sqlConnection.Open()
Write-Output "Azure SQL database connection opened"
# Define the SQL Command
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandTimeout = 0
$sqlCommand.Connection = $sqlConnection
Write-Output "Issuing command to run stored procedure"
# Execute IndexOptimize stored procedure
$sqlCommand.CommandText= "EXECUTE dbo.IndexOptimize
@Databases = 'jbdb',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 10,
@FragmentationLevel2 = 30,
@LogToTable='Y'"
$result = $sqlCommand.ExecuteNonQuery()
Write-Output "Stored procedure execution completed"
# Close connection
$sqlConnection.Close()
Write-Output "Run completed"- Click Save and Publish the Runbook.
⏰ Step 4: Schedule the Runbook
Now, let’s schedule the Runbook to run automatically.
- Go to Automation Account → Runbooks.
- Select your Runbook (
IndexMaintenanceRunbook). - Click Schedules → Add a Schedule.
- Set the recurrence (e.g., Daily at Midnight).
- Link the schedule to the Runbook.
📊 Step 5: Monitor Runbook Execution
To verify successful execution:
- Go to Automation Account → Runbooks → Job History.
- Check if the Runbook ran successfully.
- Use
SELECT * FROM dbo.CommandLogin SQL to view maintenance logs.
🎉 Conclusion
Congratulations! 🎊 You have successfully scheduled Index Maintenance on Azure SQL Database securely using Azure Automation, PowerShell, and Managed Identity. This approach ensures:
- 🔒 No explicit credentials are stored in scripts.
- 📈 Automated performance tuning for SQL Database.
- 🛠️ Seamless execution with minimal manual intervention.
