Rebuilding Indexes Nightly on Azure SQL Database

There are a number of ‘gotchas’ when working with Azure – things that you’ve done the same way for so many years you take them for granted.

We were recently tasked with setting up nightly maintenance on a SQL Azure Database, something we’ve done the same way for many years using a simple statement.

EXEC sp_MSforeachtable @command1='SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF,FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON)'

We take this statement, schedule it using SQL Server Agent, and we’re done. Two issues:

sp_MSforeachtable does not exist on Azure SQL Database
There is no SQL Server Agent on an Azure SQL Database

There are multiple approaches we could have taken to solve this, but the most straightforward was to create our own custom stored procedure to rebuild the indexes (replacement for sp_MSforeachtable), write a PowerShell Runbook, and schedule it with Azure Automation. Below are the steps to get this done.

Stored Procedure to Rebuild Indexes

The below stored procedure can be added to any database to do a basic rebuild of all indexes in all tables with a FILL_FACTOR of 80.

DECLARE @table TABLE
(
    TABLE_CATALOG VARCHAR(255),
    TABLE_SCHEMA VARCHAR(255),
    TABLE_NAME VARCHAR(255),
    TABLE_TYPE VARCHAR(255)
)


INSERT INTO @table
SELECT * from INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_SCHEMA, TABLE_NAME

SET NOCOUNT ON;  

declare @TABLE_CATALOG VARCHAR(255),
    @TABLE_SCHEMA VARCHAR(255),
    @TABLE_NAME VARCHAR(255),
    @TABLE_TYPE VARCHAR(255)

DECLARE table_cursor CURSOR FOR   
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM @table
ORDER BY TABLE_SCHEMA, TABLE_NAME

DECLARE @script NVARCHAR(1000)

OPEN table_cursor  

FETCH NEXT FROM table_cursor   
INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @TABLE_TYPE

WHILE @@FETCH_STATUS = 0  
BEGIN  
    PRINT @TABLE_SCHEMA + '.' + @TABLE_NAME  

    SET @script = 'ALTER INDEX ALL ON [' + @TABLE_SCHEMA + '].[' + @TABLE_NAME +  '] REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);'
    EXEC sp_executesql @statement=@script


        -- Get the next table.  
    FETCH NEXT FROM table_cursor   
    INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @TABLE_TYPE
END   
CLOSE table_cursor;  
DEALLOCATE table_cursor; 
END
GO

Creating a PowerShell Runbook

Creating a PowerShell Runbook to invoke the stored procedure was quite easy. I started from a template here. The template runs a stored procedure that returns a result, so the major difference was to call “ExecuteNonQuery” on the SqlCommand rather than creating a Data Adapter. The code is below.

NOTE: I could not get this to work with a PSCredential as an input parameter, but if you’re running this in a production scenario, I highly recommend looking at the original article and restoring the PS Credential OR using a service principal and connecting to Azure using Integrated Security.

workflow Rebuild-All-Indexes
{
    param(
        [parameter(Mandatory=$True)]
        [string] $SqlServer,
		
		[parameter(Mandatory=$False)]
        [int] $SqlServerPort = 1433,
        
        [parameter(Mandatory=$True)]
        [string] $Database,
        
        [parameter(Mandatory=$True)]
        [string] $SqlUsername,
		
		[parameter(Mandatory=$True)]
        [string] $SqlPassword
    )

    inlinescript {
        # Define the connection to the SQL Database
        $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$using:SqlServer,$using:SqlServerPort;Database=$using:Database;User ID=$using:SqlUsername;Password=$using:SqlPassword;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")
        
        # Open the SQL connection
        $Conn.Open()

		#Specify the query
		$Query = "EXEC dbo.usp_RebuildAllIndexes"
		
        # Define the SQL command to run. In this case we are getting the number of rows in the table
        $Cmd=new-object system.Data.SqlClient.SqlCommand
		$Cmd.Connection=$Conn
        $Cmd.CommandTimeout=600
		$Cmd.CommandText=$Query
		

        # Execute the SQL command
        $Cmd.ExecuteNonQuery()

        # Close the SQL connection
        $Conn.Close()
    }
}

Schedule the Runbook in Microsoft Azure

This is the easy part and can be done fully through the Azure portal.

Step 1: Create an Azure Automation Account under All Resources -> Add -> Automation. Specify a Name, Subscription, Resource Group, and Location.

Step 2: Open the new Azure Automation Account and select Runbooks -> Add a runbook -> Import an existing run book. Select the PowerShell file that you created in the previous section. Azure will automatically pick up the type as PowerShell Workflow. Click Create.

Step 3: Now that the Runbook is created, you can test it manually before scheduling by clicking on Edit and then Test Pane. Azure will prompt you for the input parameters specified in your script, which are by default the SQL Server, Database, Port, Username, and Password. Key in those variables and click Start. The process may take several minutes based on the size of your database tables and index fragmentation.

Step 4: Once your Azure Runbook is working as designed, you can Publish. Publishing is necessary before you can create a schedule and the option is somewhat buried under the Edit menu. Click on your runbook, then click Edit -> Publish.

Step 5: Go back to your Runbook and click Schedules -> Add a Schedule. Click Link a schedule to your runbook. Click Create a new schedule. Give the schedule a descriptive name, specify the start time, select Recurring then 1 Day in the next section. Click Create. Back under Parameters and run settings, add in the parameters to your runbook, which is again by default the SQL Server, Database, Port, Username, and Password. Click Ok, then Ok again.

That’s it! You now have a nightly job to rebuild all indexes.