I use elastic pools. They are a fabulous way of saving money when running many Azure SQL Databases, that is assuming you understand the resource utilization patterns of the databases involved.

I recently created couple of elastic jobs for my pool and went through a successful setup (so I thought) and proceeded to execute the query. The query being elastic in nature basically runs across all my databases from a single point. This is where it got puzzling, if you look at the below screen shot you can clearly see that the status is RUNNING, drilling into it, it says RETRYING. This obviously is not right.

Drilling in:

The Shard map refresh, script split and expand tasks all worked well.

The issue I had was I had no idea what was going on. I would like error logging built into this feature. To truly understand what was going on I had to connect to the controller database and find out myself.

What is this controller database? It is just a SQL database (has its own server too) that holds all the job metadata and you can connect to it assuming you have the correct firewall settings enabled.

It looks like this – very ugly.

I then issued the below query against the controller database looking for the message column.

 SELECT *
FROM [__ElasticDatabaseJob].[JobTaskExecution]
ORDER BY StartTime

 

Well, looks like I didn’t do a correct setup – it was complaining about permissions.

System.AggregateException: One or more errors occurred. —> System.Data.SqlClient.SqlException: Login failed for user ‘ArunSirpal’.

Number: 18456

State: 1

Class: 14

LineNumber: 65536

ClientConnectionId: 0ef4567b-04c9-4ad3-aac9-de23cb5efa4dHResult: 0x80131904at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

Now I understand where I went wrong but I don’t think the setup process helped, or should I say it can be improved. During setup of the job we have to enter a username and password to connect to the databases to run the code. Such as:

I promise you, there is no login called Jackie Chan yet I get a green tick, this process ASSUMES that the username and password exists and is correct. So double check those credentials!