Scheduling job on SQL Azure

  • Article

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.