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. 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 eventqueueWHEREcreated < dateadd(hh, -1, getdate()) ORinstancedata 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 = EQOpenConnectionfunction 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 eventqueueWHEREcreated < dateadd(hh, -1, getdate()) ORinstancedata 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 $CoreConnectionfunction 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 eventqueueWHEREcreated < dateadd(hh, -1, getdate()) ORinstancedata like '%virtualindexcrawler%'"$deleteResult = $deleteCommand.ExecuteScalar()}# execute the deletion in a loopdo{EQDelete -connection $CoreConnection$selectCount = EQSelect -connection $CoreConnection# output the number of rowsWrite-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.[caption id="attachment_3412" align="alignnone" width="581"] Create Azure Automation Dialog[/caption]

2. Once you have done that, you then need to create a Runbook.

[caption id="attachment_3416" align="alignnone" width="311"] Create a Runbook[/caption]3. After that, paste the Powershell script previously mentioned.[caption id="attachment_3417" align="alignnone" width="780"] Edit Runbook[/caption] 4. After you Save and then Publish, click on the Schedules.[caption id="attachment_3419" align="alignnone" width="583"] Click Schedules[/caption]5. Add a Schedule.6. Link a schedule to your runbook.7. Create a new schedule.[caption id="attachment_3420" align="alignnone" width="780"] Create a Schedule[/caption][caption id="attachment_3422" align="alignnone" width="314"] New Schedule[/caption]After you have added a Schedule for your Runbook, you can look at the status of the runs in the Jobs section.[caption id="attachment_3423" align="alignnone" width="780"] Jobs[/caption]You can then click on a job and look at the output.[caption id="attachment_3424" align="alignnone" width="780"] Output of a Job[/caption]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.