How to use Azure Automation to maintain SQL indexes and statistics
Posted On July 28, 2020
When you migrate to Azure SQL, you might think that Azure does all SQL maintenance, including the maintenance of your database… But the truth is, you will need to setup some maintenance yourself for your databases. Microsoft doesn’t know what is best for your application or database. With this manual you should be able to setup basic database maintenance on Azure SQL.
STEP 3: Next step is to create a SQL user that will have DB Owner permissions to perform the maintenance. For security purposes we just create a database user. Run the following script, adjust where needed (username/password)
CREATE USER ServiceAccount
WITH PASSWORD = 'SecureStrongPassword'
EXEC sp_addrolemember 'db_owner', 'ServiceAccount';
STEP 4: Now go to the Azure Portal (https://portal.azure.com) and create a new resource, and select Automation
Create a name for your automation account, select a subscription and resource group, and click create.
Once the creation task is completed, go to your resource
STEP 5: Now we will need to import the SQL module for Azure automation. Go to Modules and Browse the gallery
Now search for the SqlServer module from matteot_msft.
Now import the module
STEP 6: Now we will need to add the credentials from step 3. Go to Credentials, and click on Add a credential.
Fill in the credentials from step 3, make sure to create the credential with the name: SqlCredential
STEP 7: Time to implement the script it self. Now go to Runbooks, and Create a runbook
Give your runbook a name, and select PowerShell as runbook type. Click create when done.
A new window will open, copy and paste the script below