I’ve been using Azure SQL Database for quite some while and have set up it in many various ways to run Ola’s Index Optimize and Statistics Updates on them. All of these have seemed way too complicated probably because I was setting them up once, not again for several more months or a year. Well with my new job, I have over 20 subscriptions with various Azure SQL Servers in them so it was time to streamline at least knowing what I was doing. No matter what I googled on the Internet I never did find one source that walked me step by step on each thing I needed to know to set this up. So hopefully this will cover everything.
Note: This can be used for any PowerShell process trying to run against Azure SQL Databases, it is set up to loop through all Azure SQL Servers in and a subscription and run the T-SQL in the PowerShell against each non-master database.
First, let’s cover everything you need to do outside of the Azure SQL Database first to make this work. The main goal of this post is to be able to run a PowerShell runbook via an Automation Account against all your databases in your current Azure subscription (and it’s a lot easier than it sounds).
Our first step is to log into the Azure Portal, most of this will be done using the GUI there. First, search for “Automation Accounts” in the top part of the portal. Click on it when it comes into the dropdown list and then click the blue “Create automation account” button at the bottom of the screen. You will be presented with this screen. Select the subscription your Azure SQL Database is in and give your account a name. After which you to click on the blue “Review + Create” button at the bottom left. Then hit the blue “Create” button assuming you don’t get any errors.
Now that we have an account created we need to give it permission to objects in the subscriptions in order for it to be able to run our PowerShell. So, navigate to the Automation Account you created and go to the “Identity” tab so we can assign some “Azure role assignments“.
At this point now that Azure has access to the database let’s do the stuff in SQL. Install Ola‘s scripts in each user database or whatever else you may decide to use this for. Create a user for the automation account to use inside each user database.
CREATE USER dbamaint FROM EXTERNAL PROVIDER; ALTER ROLE db_owner ADD MEMBER dbamaint
Under “Azure role assignments” hit “Add role assignment“.
After this select your Scope, which in our case is “SQL”, select your subscription, your resource (Azure SQL Server), and give it Role. I’ve gone with Owner, there may be a lesser one, which I know there probably is because all I’m using this for is to be able to access the Azure SQL Servers and find out what Azure SQL Database is there. So I’ll come back and edit this once I figure out what the less restrictive role is. Then click the blue “Save” button.
If you go back to your Automation Account’s Identity tab and select the subscription you placed the account in you will see this now for your automation account.
All the Azure PowerShell modules we need are already imported into Runbooks but we will need the SqlServer PowerShell module and you most certainly can use dbatools instead if you prefer. Navigate to your “Modules” tab on the left-hand side in the Automation Account.
At the top hit the “Browse Gallery” button and type in SqlServer.
Select the module and hit the blue “Select” button on the next screen. Select what version of PowerShell you want to use and hit the blue “Import” button.
Now we are ready to create our runbook. Navigate to the “Runbooks” tab under the automation account. Personally, I deleted the two samples that were there because well clutter. Hit “Create a new runbook” at the top. Fill in the screen with your details.
Now you can place the code below in your runbook, and edit the $Query variable to be what you want it to run exactly. Setup one runbook for Statistics Updates and one for IndexOptimize and we will cover how to schedule them to run. Of particular importance to note in this script is $errorActionPreference, without this the script will not error out and will not get alerts when we set them up. The process follows as-is:
- Obtain the automation accounts context
- Get the current tenant
- Get the current subscriptions in this tenant
- Based on what subscriptions the account has access to it will loop through them at this point
- Access each Azure SQL Server
- Get a list of the databases that aren’t master
- Loop through each db
- Retrieve a token for each database
- Run the command provided
$errorActionPreference = "Stop"
Import-Module SqlServer
$Query = @"
EXECUTE dbo.IndexOptimize @Databases = 'ALL_DATABASES', @LogToTable = 'Y'
"@
$context = (Connect-AzAccount -Identity).Context
$Tenant = Get-AzTenant
$Subscription = Get-AzSubscription -TenantID $Tenant.TenantId
ForEach ($sub in $Subscription) {
$AzSqlServer = Get-AzSqlServer
if($AzSqlServer) {
Foreach ($SQLServer in $AzSqlServer) {
$SQLDatabase = Get-AzSqlDatabase -ServerName $SQLServer.ServerName -ResourceGroupName $SQLServer.ResourceGroupName | Where-Object { $_.DatabaseName -notin "master" }
Foreach ($Database in $SQLDatabase) {
$Token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
Invoke-Sqlcmd -ServerInstance $SQLServer.FullyQualifiedDomainName -AccessToken $Token -Database $Database.DatabaseName -Query $Query -ConnectionTimeout 60 -Verbose
}
}
}
}After you have put this into the screen, hit “Save” and use the “Test Pane” to test it. Once you are satisfied it works go back and hit “Publish“. You may want to test this by giving it the @TimeLimit parameter for the proc and maybe only do statistic updates as part of your testing. I have a whole GitHub Repository with my customize setup for Ola’s scripts you can check out.
Now that you have tested it, it’s time to schedule it and set up Alerts for if it fails. Inside the runbook window click “Link to Schedule” and follow the screens to create a schedule for the runbook.
I’ve even found articles on Microsoft’s site on how to set them up. The Azure Runbooks on you can no longer use the way it is written since it won’t retrieve an encrypted password for you in PowerShell and well we don’t want to have passwords in plain sight.
- Automating Azure SQL DB index and statistics maintenance using Azure Data Factory – Single Database
- Automating Azure SQL DB index and statistics maintenance using Azure Automation
- SQL DB maintenance activity using Automation accounts for Azure SQL DBs(Different scenarios)
The follow-up blog post to this one will be PowerShell to auto-deploy this to all my subscriptions. Here is a link to setup up alerts if the runbook fails, seemed pointless for me to rehash this since I was easily able to find this.
The post Using Azure Automation and Runbooks to Run Azure SQL Database Maintenance Tasks first appeared on Tracy Boggiano's Blog.