SQL Server Index Maintenance using Elastic Jobs

Hi Mates,

We are back this time & are going to explore how to perform index maintenance using Elastic Jobs.

In the previous post we worked on Azure Automation & schedule our jobs. Unlike this Elastic jobs doesn't give you the flexibility to schedule the jobs if we can configure them using GUI.

Why use it? Well we can easily do schema changes, credential management, data updates and database maintenance across the group. To leverage the true power of elastic jobs you should go down the PowerShell Route because the portal has reduced functionality.

1) Go to Azure portal & create SQL Elastic Database pool. In my case I defined it as elastic Pool test.

2) As you can see I opted for Basic Tier as I am making use of Free trial & little worried about my          credits.

3)once you create the Elastic pool click on create job as per the below image.

4)There are 4 key components to an elastic job – it requires Service bus, SQL Server, SQL database and access to a storage account which all reside within a resource group. My understanding is that it needs the SQL database because it uses this as a controller database

5) Kindly pass your SQL Azure credentials which are required for setting up jobs.
6)once this finishes we are fine to proceed with Automation of our jobs.By Default it creates its own resource group & could see below resources getting created automatically.
7)Now click on create job & pass the job credentials.
8)Now enter the job name & the script that you would love to run across all the Elastic databases.
9) once you pass the script then we are fine however we have only 2 options either to run or save it & we don't have option to schedule the job unlike azure automation of what we have seen in other post

Pros: 1) Easy to install as we are performing the installation through portal
         2) Schedule jobs mostly one time which needs to run on all Elastic Databases

Cons:  1)We are unable to schedule the jobs
           2) Most of the scripts needs to be executed on all the databases. If a script needs to be executed  on only one database then I could see the status of job as retrying forever
           3) We can't delete the job.

To overcome all the limitations we need to make use of power shell which I will come up in future post. As always Thanks for Reading.