Background:
Microsoft Azure does a great job of hosting applications and making it easy to develop and deploy new code to an ~ always available platform hosted in a region and data center of your choice. Available in SQL2017 and Azure SQLDB are features such as automatic index tuning and plan regression where over time Azure gains insight into your data query patterns and can optimize for best query performance automatically.
What if you want to manage re-indexing on your own? Here I will discuss an approach I took to schedule and run a re-index maintenance on Azure.
Setup and Code:
These steps assume you are familiar with the azure portal and you have some databases created for which you want to run your maintenance on.
1. You will need to setup a SQL account Credential in the portal and in each database you want to re-index. This is the account which connects from the Azure Portal and will do the work. This account should have at minimum execute procedure and create table rights in each database.
2. Create an Azure Automation Account and associate it to your sql logical server and databases to be re-indexed, this will allow you to import the runbook and schedule it.
3. I like to create the Powershell script and then modify the connection string to a local instance for testing. An example script using ADO.Net is below:
Note: To test this, you can step through it , as you won’t need the credential on a local instance. Here I set the Credential to my local Sql Account Logon credentials.
workflow Start-ReIndex {
Param (
[parameter(Mandatory=$True)]
[string] $SqlServerServer = 'MyLocalInstance\dev',
[parameter(Mandatory=$True)]
[string] $Database = 'WideWorldTestDB',
[parameter(Mandatory=$False)]
[int] $SqlServerPort = 12345,
[parameter(Mandatory=$False)]
[string] $MaintenanceType = 'all'
)
# Get the stored username and password from the Automation credential
$SqlCredential = Get-AutomationPSCredential -Name $SQLCredentialName
if ($SqlCredential -eq $null)
{
throw "Could not retrieve '$SQLCredentialName' credential asset.
Check that you created this first in the Automation service."
}
$SqlUsername = 'SQLTestAccount'
$SqlPass = 'test123$'
Inlinescript {
Try
{
# Define the connection to the SQL Database
$Conn = New-Object System.Data.SqlClient.SqlConnection
("Server=tcp:$SqlServerServer;Database=$Database;User ID=$SqlUsername;
Password=$SqlPass;Connection Timeout=30;")
# Open the SQL connection
$Conn.Open();
# SQL command to start stored procedure
$SQLCommandString = @"
exec dbo.maintenanceProc @Operation='$MaintenanceType';
"@
# Set command timeout and associate with connection
$Cmd=new-object system.Data.SqlClient.SqlCommand($SQLCommandString, $Conn)
$Cmd.CommandTimeout=120
# Execute the SQL command
$Cmd.ExecuteNonQuery();
$Conn.Close();
}
Catch
{
$ErrorMessage = $_.Exception.Message
# Check if connection still open if so clean up
if ($Conn.State -eq 'Open')
{
$Conn.Close();
}
}
}
}
Now that you have a working script, you can also test the procedure (see link below) on SQL2017+ to see how you like it and make sure you understand all the options before you implement this on an azure connection.
Note: By default this procedure will log to a table in the database where the procedure is run, so be sure to look for dbo.AzureSQLMaintenanceLog table and check the output, an example is shown below.
4. Download and publish SQLMaintenance Procedure from the link below to your local instance and when ready to your Azure SQL Database.
5. Modify the PowerShell by updating the lines for the SQL Connection and the Credential Username and Password.
workflow Start-ReIndex {
Param (
[parameter(Mandatory=$True)]
[string] $SqlServerServer = "MySqlServerName.database.windows.net",
[parameter(Mandatory=$True)]
[string] $Database = "DBToReIndex",
[parameter(Mandatory=$False)]
[int] $SqlServerPort = 1433,
[parameter(Mandatory=$False)]
[string] $MaintenanceType = "all"
)
# Get the stored username and password from the Automation credential
$SqlCredential = Get-AutomationPSCredential -Name $SQLCredentialName
if ($SqlCredential -eq $null)
{
throw "Could not retrieve '$SQLCredentialName' credential asset.
Check that you created this first in the Automation service."
}
$SqlUsername = $SqlCredential.UserName
$SqlPass = $SqlCredential.GetNetworkCredential().Password
Inlinescript {
Try
{
# 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:SqlPass;
Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")
# Open the SQL connection
$Conn.Open();
# SQL command to start stored procedure
$SQLCommandString = @"
exec dbo.maintenanceProc Operation='$MaintenanceType';
"@
# Set command timeout and associate with connection
$Cmd=new-object system.Data.SqlClient.SqlCommand($SQLCommandString, $Conn)
$Cmd.CommandTimeout=120
# Execute the SQL command
$Cmd.ExecuteNonQuery();
$Conn.Close();
}
Catch
{
$ErrorMessage = $_.Exception.Message
# Check if connection still open if so clean up
if ($Conn.State -eq 'Open')
{
$Conn.Close();
}
}
}
}
6. Upload the PowerShell script to the Azure Automation Account and add a schedule.
Note: Azure has a fair-play timeout which it uses to ensure that no one process hogs resources for an extended period of time, at time of writing, that was 3 hours. If you have a database which is large or has many indexes, you may want to run this on an Azure Hybrid Worker which will get you around the timeout issue. The other option would be to up your service level for the database while this is running ( could be more cost )
Output from the above maintenance procedure:
Resolution:
Once this is up and running, you have a fully functional re-indexing solution for Azure or SQL Server 2017+ instances. The nice things about this method are:
- No extra software to install, like Management Studio for access to SQLCMD
- Flexible, since you can add the procedure to a new database and schedule a new runbook instance on the fly.
- Azure if run as a service ( PaaS) allows you to scale up and scale down your service tier on the fly, this allows you to speed up the re-indexing work if you need to fit it to a shorter time window.
Information on Terms:
1. PaaS – Platform as a Service – this is also known as AzureSQL DB, it is Microsoft’s Managed Database cloud offering.
2. SQLCMD – SQL Command Line – tool installed as art of SQL Management Studio to connect to a SQL Server instance from a windows prompt.
3. Credential – User account assigned in the Azure Portal which has access to Azure Resources and can execute code on and perform actions against those resources.
4. Azure Automation Account – container for creating and scheduling jobs to run against Azure resources. The automation account acts on resources by securely passing the credential information (username and password) to the resource as authentication.
Subject Links:
https://docs.microsoft.com/en-us/azure/automation/automation-create-standalone-account