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:

DELETE TOP (500)
FROM eventqueue
WHERE
created < dateadd(hh, -1, getdate()) OR
instancedata like '%virtualindexcrawler%'

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:


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()

}

}

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: