This is for Rebuilding Index of Azure Database, I have tried to use the out of box one and could not get it work. 

So I referenced most of it and made it very simple to run.

The concept is to run the first query and get all the table names.

Then run rebuild index on all the tables. 

This is my first Azure Automation RunBook, so I may add more functions into it. I.E. put a threshold to it to only run index rebuild on them, however I am very exited to share this with you first, it is more about get it work than get it work perfectly.

 

1. Create an Azure Automation Account under Azure Automation, if you have no access to create Azure Automation account, you will need to be the Azure Active Directory administrator first, Ask your Azure subscription administrator to grant you Azure Active Directory administrator access. Do not create Azure Automation Account without Azure Run As account created, you are wasting your time.

2. Once Azure automation account and Run As account is created, go to Credentials to create an credential for your Azure Sql Server access, make sure you can use the same credential to run the queries you need under SQL Management Studio. I.E. SqlCredentials.

3. Now create your RunBook as following:

<# 
.SYNOPSIS 
    Outputs the number of records in the specified SQL Server database table. 
 
.DESCRIPTION 
    This runbook demonstrates how to communicate with a SQL Server. Specifically, this runbook 
    outputs the number of records in the specified SQL Server database table. 
 
    In order for this runbook to work, the SQL Server must be accessible from the runbook worker 
    running this runbook. Make sure the SQL Server allows incoming connections from Azure services 
    by selecting 'Allow Windows Azure Services' on the SQL Server configuration page in Azure. 
 
    This runbook also requires an Automation Credential asset be created before the runbook is 
    run, which stores the username and password of an account with access to the SQL Server. 
    That credential should be referenced for the SqlCredential parameter of this runbook. 
 
.PARAMETER SqlServer 
    String name of the SQL Server to connect to 
 
.PARAMETER SqlServerPort 
    Integer port to connect to the SQL Server. Default is 1433 
 
.PARAMETER Database 
    String name of the SQL Server database to connect to 
 
.PARAMETER SqlCredentialAsseet 
    Credential asset name containing a username and password with access to the SQL Server   
 
.EXAMPLE 
    Use-SqlCommandSample -SqlServer "somesqlserver.databases.windows.net" -SqlServerPort 1433 -Database "SomeDatabaseName" -Table "SomeTableName" -SqlCredentialAsset sqluserCredentialAsset 
 
.NOTES 
    AUTHOR: System Center Automation Team 
    LASTEDIT: Dec 15, 2016  
Modified by Lei: 27/03/2017 #> param( [parameter(Mandatory=$False)] [string] $SqlServer="<yourAzureSQLServerNameHere>", [parameter(Mandatory=$False)] [int] $SqlServerPort = 1433, [parameter(Mandatory=$False)] [string] $Database="<YourAzureDatabaseNameHere>", [parameter(Mandatory=$False)] [string] $SqlCredentialAsset="SqlCredential" #noting this is the credential name that you saved under credentials. ) $SqlCredential = Get-AutomationPSCredential -Name $SqlCredentialAsset if ($SqlCredential -eq $null) { throw "Could not retrieve '$SqlCredentialAsset' credential asset. Check that you created this first in the Automation service." } # Get the username and password from the SQL Credential $SqlUsername = $SqlCredential.UserName $SqlPass = $SqlCredential.GetNetworkCredential().Password # Define the connection to the SQL Database $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$SqlServer,$SqlServerPort;Database=$Database;User ID=$SqlUsername;Password=$SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;") # Open the SQL connection $Conn.Open() # Define the SQL command to run. In this case we are getting the number of rows in the table $sqlScripts = @" SELECT '['+s.name +'].[' + t.name + ']' AS TableName, t.OBJECT_ID FROM sys.tables t join sys.schemas s on t.schema_id = s.schema_id "@ $Cmd=new-object system.Data.SqlClient.SqlCommand($sqlScripts, $Conn) $Cmd.CommandTimeout=120 # Execute the SQL command $Ds=New-Object system.Data.DataSet $Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd) write-output "we are good now!" [void]$Da.fill($Ds) # # Output the count #$Ds.Tables.Column1 foreach ($Row in $Ds.Tables[0].Rows) { $tableName = $Row[0] write-output $tableName $sqlScripts ="ALTER INDEX ALL ON $tableName REBUILD With (Online=ON);" $Cmd=new-object system.Data.SqlClient.SqlCommand($sqlScripts, $Conn) $Cmd.CommandTimeout=1500 Try { write-output ("Building table $TableName online") $Ds2=New-Object system.Data.DataSet $Da2=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd) [void]$Da2.fill($Ds2) } Catch { if (($_.Exception -match "offline") -and ($Using:RebuildOffline) ) { Write-output ("Building table $TableName offline") $SQLCommandString = @" EXEC('ALTER INDEX ALL ON $TableName REBUILD') "@ # Define the SQL command to run. $Cmd=new-object system.Data.SqlClient.SqlCommand($SQLCommandString, $Conn) # Set the Timeout to be less than 30 minutes since the job will get queued if > 30 # Setting to 25 minutes to be safe. $Cmd.CommandTimeout=1500 # Execute the SQL command $Ds2=New-Object system.Data.DataSet $Da2=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd) [void]$Da2.fill($Ds2) } Else { # Will catch the exception here so other tables can be processed. Write-output "Table $TableName could not be indexed. Investigate indexing each index instead of the complete table $_" } } } # Close the SQL connection $Conn.Close() write-output "Completed and disconnected."

 

 

Reference 1: https://azure.microsoft.com/en-au/blog/azure-automation-your-sql-agent-in-the-cloud/

2. http://geeks.ms/davidjrh/2015/10/08/rebuilding-sql-database-indexes-using-azure-automation/

I have also quoted the orignial author of this. 

 

About Lei

I am an IT specialist with over 10 year experience - years on Automation, on-Premise or Azure.

I am happy to develop however never want be a full time developer. Only do what I have to do. If it has to be PowerShell,HTML, PHP, CSS, C#, VBS or JS, front end or backend, so be it, doesn't matter!

Spent years with Windows, SCCM, SharePoint, SQL and Exchange servers. For last several years, I have been actively working under On Premise > Azure environment.

THERE IS NO WAY BACK!!!

Current Certificates:
    Microsoft® Certified-
  • -Enterprise Administrator
  • -Database Administrator
  • -SharePoint Administrator
  • -Administering and Deploying SCCM 2012
Red Hat Certified Technician
ITIL V3 Foundation - Practitioner

Working on Azure Certificates now and hopefully they can stop upgrading their questions one day! GIVE ME A BREAK!

Contact Lei

Name *
Email *
Comments *

Traffic since 10/08/2016

Today45
Yesterday83
This week669
This month1965
Total416325

Visitor Info

  • IP: 54.163.210.170
  • Browser: Unknown
  • Browser Version:
  • Operating System: Unknown

Who Is Online

2
Online

2017-12-17

Login