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

  1. Prasant Nanda says:

    Thanks Dinesh for sharing the link.

  2. srini says:

    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.

  3. Prasant Nanda says:

    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.

  4. Rodrigo says:

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

  5. prithiv vasudevan says:

    I get the same execution error , how do i set the new user which will run the scheduler ?

    The EXECUTE permission was denied on the object

  6. Zubair says:

    Thank you very much for the great article…

    I am unable to see the SCRIPT tab…

  7. Deepak says:

    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?