timeout connection option

[majidaldo]

On Azure SQL, you can have a db 'sleep'. It can take a few minutes to resume. In the meantime, connection attempts usually fail.

To solve this, I suggest a 'timeout' option, such that (an overall) connection attempt is considered to have failed if it exceeds this timeout. (in addition to 'n_tries' and 'time_bw_tries').

[sdebruyn]

This might seem an easy one, but it isn't. The issue here is that from the side of the adapter, there is no way of knowing if the (serverless) database is in paused mode or not. The only way you can determine the paused state of the database is by looking at the Azure Portal.

When you try to connect to a paused database, you just get an error saying that your credentials are invalid. So if we get this error in the adapter, we cannot determine if you actually provided invalid credentials, or if the database is being resumed and we should try again.

There's even a second possible issue here: at the moment the Azure documentation doesn't state how the firewall behaves with paused databases. During some simple tests, I was able to resume a completely private database (so no public networking allowed) from a public network connection. The expected behaviour here is undefined in the documentation as of today.

To realise this in a trusted and stable way, Azure needs to return a specific error code for databases which are currently resuming and might become available after 1 to 3 minutes.

[sdebruyn]

We could use the 2 timeout options in pyodbc (there is 1 for the connection and 1 for the queries).