Scheduling job on SQL Azure

The SQL Azure doesn't support sql jobs. So we cannot deploy the jobs on Azure. But we can schedule the job (stored procedure execution) on Azure. There are 2 approaches to do this.

1. By scheduler available in Azure mobile service.

We can execute sql procedures on Azure based on our need (schedule configuration).

1. Create a mobile service.
2. Create a scheduler. Mention the database to be used for this.
3. Configure the scheduler for the frequency.
4. Click on tab Script. The script can be written in java script or .net. The script should have the code to run a proc.

 I used below code to run dbo.ExecuteDataRequest procedure Azure database.

 function Execute_Process_Request() {
      console.log("Executing ExecuteDataRequest...");
      mssql.query('Exec dbo.ExecuteDataRequest',{
         success: function(results){
         console.log("Finished the Process Request job.");
         },
          error: function(err) {
            console.log("error is: " + err);
         }
     });
 }

 

 

Find the new user added which will run the scheduler (Under Login folder of Security of Azure db server). Need to grant execute permission to the new user on the database where we need to execute the job.

 Grant Execute to [new user available under security of the database]

If you want to give permission only to execute few objects, we can do so by specifying the objects like Grant Execute on [object name] To [new user] 

2. By creating an sql job on on-premise database server.

We can create a normal sql job on any database server which resides on on-premise. We can execute the procedure with the help of sqlcmd utility.

 

We can execute the job at remote database server with above sqlcmd utility. If we are using windows identity we can create credential and proxy to execute the job at remote server without providing the user credential in command.

Comments

• Anonymous
  July 16, 2014
  One more way - azure.microsoft.com/.../azure-automation-your-sql-agent-in-the-cloud

• Anonymous
  July 20, 2014
  Thanks Dinesh for sharing the link.

• Anonymous
  January 05, 2015
  Hi Prasant, Thanks for the great article, I am having a requirement, where I need to also pass parameters to the SP call, how can I do that.

• Anonymous
  March 17, 2015
  Hi Srini, Thanks for visiting. It depends on the requirement and existing design. But I can suggest to have a config sql table and let the sproc (used in sql job), read the config table and proceed further. You can call it as a wrapper proc call other proc.

• Anonymous
  June 10, 2015
  My problem is to find the new user added which will run the scheduler. I've tryed to search for security area on database, but there is only a auditing and security area. When I try to start the scheduler, I receive an error of authorization :(

• Anonymous
  June 26, 2015
  The comment has been removed

• Anonymous
  July 13, 2015
  Thank you very much for the great article... I am unable to see the SCRIPT tab...

• Anonymous
  February 09, 2016
  Can I use scheduler to find the PRIMARY node in my "always" ON SQL Server setup? Because in always ON there are same sql agent jobs will be running in multiple nodes. I want to disable the one which is not PRIMARY, How can we do it?