When working with Azure relational databases you will find out that it’s very cost effective compared to Azure DW, but also sometimes extremely limited. Luckily there are quite a few roads to the truth, and the limitations can therefore often be defeated – even though it’s painful to rewrite, in other SQL Server versions, ‘working’ SQL-code. Microsoft has announced that they from now on will update their cloud versions of SQL Server before they update the on prem versions, which in my opinion is good! But we’re not quite there yet.

One of the components missing in Azure SQL is the “SQL Server Agent”, which is crucial when you want to keep your data up to date. So, you stand in front of two options. Either you go with the Azure DW ($1/h for a 250GB database) or you find a way to use Azure SQL (which is somewhere around $0,02/h for a standard 250GB database).

One approach to SQL Server Agent in Azure is to use something called Azure Mobile Services, which mainly is used for creating mobile apps. Below is a step to step guide how to use Azure Mobile Services as an agent server in Azure. When only using one job, no extra charge will be added in Azure, therefore I included all my procedures in a single procedure called LoadAPETL%:

  1. Create mobile services app in Azure, name it LoadAPETL, use javascript and point it to the database you want to use and login with username and password
  2. Click scheduler and create a new schedule, define the interval.
  3. Click the tab “Script” within the job, and enter the following javascript code:
    function LoadAPETL() { console.log("Executing ETL for .."); mssql.query('EXEC [].[]' ,{
    success: function(results){
    console.log("The job finished successfully."); },
    error: function(err) { console.log("the error is: " + err);
    } });
    }
  4. Click save or enable the job directly.
  5. Go to the database you chose in step 1. Open SQL Server Management Studio (ssms) and navigate to the security tab within the database. Collapse Users. Find the user name that corresponds to the name you created for the mobile services app. It should end with ‘%Login_LoadAPETL<subscriptionname>User’
  6. Open a new query in ssms, write the following command:
    GRANT EXECUTE, SELECT,INSERT,UPDATE ON SCHEMA::[<schemaname>] TO <user name>
    In this case, permissions is added for a whole schema to the new mobile user. This can be modified so that is suits your needs.
  7. Go back to Azure and hit “run once” on the scheduler for mobile services. Check under the log tab if the job succeeded.