Scheduled Tasks with Sql Azure?

Question

I wonder if there's a way to use scheduled tasks with SQL Azure? Every help is appreciated.

The point is, that I want to run a simple, single line statement every day and would like to prevent setting up a worker role.

Answer 1

The overcome the issue of multiple roles executing the same task, you can check for role instance id and make sure that only the first instance will execute the task.

using Microsoft.WindowsAzure.ServiceRuntime;

        String g = RoleEnvironment.CurrentRoleInstance.Id;
        if (!g.EndsWith("0"))
        {
            return;
        }

Answer 2

In addition to David's answer, if you have a lot of scheduled tasks to do then it might be worth looking at:

• lokad.cloud - which has good handling of periodic tasks - http://lokadcloud.codeplex.com/
• quartz.net - which is a good all-round scheduling solution - http://quartznet.sourceforge.net/

(You could use quartz.net within the thread that David mentioned, but lokad.cloud would require a slightly bigger architectural change)

Answer 3

I hope it is acceptable to talk about your own company. We have a web based service that allows you to do this. You can click this link to see more details on how to schedule execution of SQL Azure queries.

Answer 4

There's no SQL Agent equivalent for SQL Azure today. You'd have to call your single-line statement from a background task. However, if you have a Web Role already, you can easily spawn a thread to handle this in your web role without having to create a Worker Role. I blogged about the concept here. To spawn a thread, you can either do it in the OnStart() event handler (where the Role instance is not yet added to the load balancer), or in the Run() method (where the Role instance has been added to the load balancer). Usually it's a good idea to do setup in the OnStart().

One caveat that might not be obvious, whether you execute this call in its own worker role or in a background thread of an existing Web Role: If you scale your Role to, say, two instances, you need to ensure that the daily call only occurs from one of the instances (otherwise you could end up with either duplicates, or a possibly-costly operation being performed multiple times). There are a few techniques you can use to avoid this, such as a table row-lock or an Azure Storage blob lease. With the former, you can use that row to store the timestamp of the last time the operation was executed. If you acquire the lock, you can check to see if the operation occurred within a set time window (maybe an hour?) to decide whether one of the other instances already executed it. If you fail to acquire the lock, you can assume another instance has the lock and is executing the command. There are other techniques - this is just one idea.