Search code examples
azureazure-sql-databasejobsazure-sql-server

How do Jobs in Azure Sql Server Databse?

I'm trying to create jobs in Azure Sql Database but I don't know how to do that. Is It possible do them inside de Sql Server Management Studio?

Solution

  • You need to use Azure Automation to schedule the execution of a stored procedure. For instance, You can use Azure Automation to schedule index maintenance tasks.

    Below are steps :

    1. Provision an Automation Account if you don’t have any, by going to https://portal.azure.com and select New > Management > Automation Account

    enter image description here

    1. After creating the Automation Account, open the details and now click on Runbooks > Browse Gallery

    enter image description here

    Type on the search box the word “indexes” and the runbook “Indexes tables in an Azure database if they have a high fragmentation” appears:

    enter image description here

    1. Note that the author of the runbook is the SC Automation Product Team at Microsoft. Click on Import:

    enter image description here

    1. After importing the runbook, now let’s add the database credentials to the assets. Click on Assets > Credentials and then on “Add a credential…” button.

    enter image description here

    1. Set a Credential name (that will be used later on the runbook), the database user name and password:

    enter image description here

    1. Now click again on Runbooks and then select the “Update-SQLIndexRunbook” from the list, and click on the “Edit…” button. You will be able to see the PowerShell script that will be executed:

    enter image description here

    1. If you want to test the script, just click on the “Test Pane” button, and the test window opens. Introduce the required parameters and click on Start to execute the index rebuild. If any error occurs, the error is logged on the results window. Note that depending on the database and the other parameters, this can take a long time to complete:

    enter image description here

    1. Now go back to the editor, and click on the “Publish” button enable the runbook. If we click on “Start”, a window appears asking for the parameters. But as we want to schedule this task, we will click on the “Schedule” button instead:

    enter image description here

    1. Click on the Schedule link to create a new Schedule for the runbook. I have specified once a week, but that will depend on your workload and how your indexes increase their fragmentation over time. You will need to tweak the schedule based on your needs and by executing the initial queries between executions:

    enter image description here

    1. Now introduce the parameters and run settings:

    enter image description here

    NOTE: you can play with having different schedules with different settings, i.e. having a specific schedule for a specific table.

    With that, you have finished. Remember to change the Logging settings as desired:

    enter image description here