Creating a Maintenance Plan for a SQL Azure Database

Question

I need to create a maintenance plan for a number of SQL Azure databases. Unfortunately, my many years of experience as a SQL Server DBA does not include experience with this task (sarcasm intended).

After a quick search, I found some information in an article here. Using the information in this article and not knowing whether or not there was a better option, I decided upon using the second option (Database Maintenance Plans). What follows makes the assumption that I understand the process correctly.

Using a local instance of SQL Server, I created a new maintenance plan. From there, I added a new connection to one of the SQL Azure databases by using the Manage Connections option. From there, I added a task to the designer pane. I used the task for updating statistics but I imagine it could have been any other task. Editing the properties of the task, I selected a single database. That is about as far as I made it. What follows is some of the observations I have made.

• By default in the Update Statistics Task property page, the option Tables and Views is selected for the Object item. If I change this option in my environment (for the SQL Azure database), the dialog is immediately closed (almost as though it has crashed).
• If I re-open the Update Statistics Task property page and then click the View T-SQL button, an error is generated indicated that login failed for the specified user. From there, I re-opened the Manage Connections dialog and checked the properties of the connection to SQL Azure and noticed that the password is blank (almost as though the password is not being retained anywhere).
• If I use a database on the local server this issue does not exist.

Am I proceeding in the correct direction? If so, is there something I am missing? Is there a better or more correct way of accomplishing this?

Answer 1

Instead of creating a maintenance plan on a local SQL Server instance to perform maintenance tasks on an Azure SQL database, consider creating a runbook using Azure Automation. The runbook should run a PowerShell script like the following:

$AzureSQLServerName = "<ServerName>"
$AzureSQLDatabaseName = "<DatabaseName>"

$AzureSQLServerName = $AzureSQLServerName + ".database.windows.net"
$Cred = Get-AutomationPSCredential -Name "SQLLogin"
$SQLOutput = $(Invoke-Sqlcmd -ServerInstance $AzureSQLServerName -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database $AzureSQLDatabaseName -Query "exec [dbo].[AzureSQLMaintenance] @Operation='all' ,@LogToTable=1" -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose) 4>&1

Write-Output $SQLOutput

Please read detailed information here.