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.
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’.
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!
Pingback: Azure SQL Database Elastic Job False Successes – Curated SQL
Pingback: Dew Drop - August 23, 2018 (#2788) - Morning Dew
the azure portal interface has been having issues in the latest months, with errors displaying authentication feedback when creating new jobs.
Anyway this system is now deprecated and will retired in November, with a new version called Elastic Database Jobs released (actually still in preview).
I would say that the new one is actually a BETTER and more flexible solution, at least for out needs, (for example, having DBs spread across 5 different elastic pools, we had to manually create 5 distinct jobs, while with the new system you create custom target_groups that can point to multiple e.pools or databases) with the only drawback that you can’t manage your jobs from the azure portal, having to use powershell or direct T-SQL in the backend db to create and verify jobs.