Azure Automation and Sitecore EventQueue

As you may or may not know, SQL Azure (previously known as Azure SQL) is a trimmed down feature set of a full installation of SQL Server.  Two of the features that you will miss out on are Full Text Search and the ability to run SQL Agent jobs.  Nonetheless, SQL Azure is a phenomenal solution for a Platform-as-a-Service (PaaS) SQL.

Sitecore maintains it’s own task scheduling solutions.  However, even if you wanted to run a SQL job instead, you wouldn’t have the ability if you were running SQL Azure.  Recently, we found that we were having performance problems and found that clearing the event queue was our solution.  While a default installation of Sitecore has it’s own agent to cleanup the event queue (Sitecore.Tasks.CleanupEventQueue), we found that this was not sufficient due to the number of events that our particular implementation created.

2016-12-05_1355

 

This default agent runs every 4 hours and leaves events in the event queue that are less than 1 day old.  For our application, 1 day is a very long time and we needed the ability to clear out events at a shorter interval and leave only events from the last hour.

Enter Azure Automation.

We needed the following script to run hourly:

[code language=”SQL”]DELETE TOP (500)
FROM eventqueue
WHERE
created < dateadd(hh, -1, getdate()) OR
instancedata like ‘%virtualindexcrawler%'[/code]

Note* the above code loops through a delete operation 500 rows at a time, instead of running a truncate.

Azure Automation allows you to create a Runbook which allows you to execute Powershell scripts in the cloud.  Here is the scripts we came up with:

[code language=”Powershell”]

workflow ProdClearEventQueue
{
inlinescript
{

function EQOpenConnection() {
# Create connection to Core DB
$CoreDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
$CoreDatabaseConnection.ConnectionString = "Data Source=tcp:SERVER;Initial Catalog=DATABASE;Integrated Security=False;User ID=USER;Password=PASSWORD;Encrypt=True;"
$CoreDatabaseConnection.Open();

return $CoreDatabaseConnection
}

$CoreConnection = EQOpenConnection

function EQSelect($connection) {
# Select the number of remaining records in the event queue
$selectCommand = New-Object System.Data.SqlClient.SqlCommand
$selectCommand.Connection = $connection
$selectCommand.CommandTimeout = 540
$selectCommand.CommandText =
"
SELECT COUNT (1)
FROM eventqueue
WHERE
created < dateadd(hh, -1, getdate()) OR
instancedata like ‘%virtualindexcrawler%’
"
$selectResult = $selectCommand.ExecuteReader()

while($selectResult.Read()) {
$numRecords = $selectResult.GetValue($1)
}

$selectResult.Close()

return $numRecords
}

# put the number of event queue records into a variable for later use
$selectCount = EQSelect -connection $CoreConnection

function EQDelete($connection) {
# delete 500 rows of event queue rows, leaving those from the last hour
$deleteCommand = New-Object System.Data.SqlClient.SqlCommand
$deleteCommand.Connection = $connection
$deleteCommand.CommandTimeout = 540
$deleteCommand.CommandText =
"
DELETE TOP (500)
FROM eventqueue
WHERE
created < dateadd(hh, -1, getdate()) OR
instancedata like ‘%virtualindexcrawler%’
"

$deleteResult = $deleteCommand.ExecuteScalar()

}

# execute the deletion in a loop
do
{

EQDelete -connection $CoreConnection
$selectCount = EQSelect -connection $CoreConnection
# output the number of rows
Write-Output $selectCount

} while ($selectCount -gt 0)
# Close connection
$CoreConnection.Close()

}

}

[/code]

1. To get this to run in Azure, you first need to create an Azure Automation account within your Azure portal.

Create Azure Automation Dialog
2. Once you have done that, you then need to create a Runbook.

runbooks_settings

Create A Runbook part 1

Create a Runbook part 2
Create a Runbook

3. After that, paste the Powershell script previously mentioned.

Edit Runbook
Edit Runbook

 

4. After you Save and then Publish, click on the Schedules.

Click Schedules
Click Schedules

5. Add a Schedule.

6. Link a schedule to your runbook.

7. Create a new schedule.

Create a Schedule
Create a Schedule
New Schedule
New Schedule

After you have added a Schedule for your Runbook, you can look at the status of the runs in the Jobs section.

Jobs
Jobs

You can then click on a job and look at the output.

Output of a Job
Output of a Job

If you wanted to get fancy – and I recommend you do, you can store your credentials and connection string in an Azure Automation Connection asset.