Insert data in Azure SQL database on a schedule fails using Azure Functions Python V2 bindings due to cold start database

[LouisDeconinck]

I have an Azure Function that scrapes a website every day on a daily time trigger, all results are stored in a dictionary. At the end I want to store all of those rows (~10.000) in the Azure SQL database. Usually when I manually run the function after having manually connected to the database everything goes perfectly, but when the scraping happens due to the time trigger I always get this error: Database 'X' on server 'x.database.windows.net' is not currently available. Please retry the connection later. If the problem persists, contact customer support, and provide them the session tracing ID of '{11B1C15C-BA7C-4BF9-929E-33AE4F11FC72}'.

This sometimes also happens when using the Query editor.

I believe this is caused due to some kind of cold start of Azure SQL database? Now, this would not be a problem if I could implement a proper retry logic, unfortunately I have not succeeded. I have a feeling that r.set(rows_sql) is launched as a background task, and that the try statement does not check if it was successful, which it never is due to the cold start. Is there any way how I can deal with this scenario with the Azure SQL binding Python V2 for Azure functions?

• Azure Functions SQL Extension or Extension Bundle Version:

  "extensionBundle": {
    "id": "Microsoft.Azure.Functions.ExtensionBundle",
    "version": "[4.*, 5.0.0)"
  }

• Is this a deployed or local function: deployed
• What type of Database are you using? (Run SELECT @@VERSION as Version, SERVERPROPERTY('EngineEdition') as EngineEdition on your database): Microsoft SQL Azure (RTM) - 12.0.2000.8 Apr 3 2024 14:04:26 Copyright (C) 2022 Microsoft Corporation, engine edition 5
• List any custom settings for your function app. This could be a custom time-out defined specifically for your database server or optional configuration that can be customized for the app defined here:

@app.function_name("AddRow")
@app.schedule(schedule="0 0 7 * * *", arg_name="myTimer", run_on_startup=False, use_monitor=True)
@app.sql_output(arg_name="r",
                command_text="[dbo].[skool_groups]",
                connection_string_setting="AzureWebJobsSqlConnectionString")
def addrow(myTimer: func.TimerRequest, r: func.Out[func.SqlRowList]) -> None:
[...]
        # Convert the list of dictionaries into a SqlRowList
        rows_sql = func.SqlRowList(map(lambda r: func.SqlRow.from_dict(r), rows))

        max_retries = 10
        retries = 0

        while retries < max_retries:
            try:
                r.set(rows_sql)
                
                total_time = time.time() - start_time

                logging.info(f"End of scraping. Scraped {len(rows)} groups in {total_time}.")
                break

            except Exception as e:
                logging.error(f"An error occurred: {str(e)}")
                retries += 1
                if retries < max_retries:
                    logging.info(f"Retrying after 30 seconds... (retry {retries}/{max_retries})")
                    time.sleep(30)
                else:
                    logging.error(f"An error occurred: {str(e)}")

Steps to Reproduce:

1. Use a daily time trigger to insert data in an Azure SQL database.
2. Database connection fails the first time (probably due to cold start).

[lucyzhang929]

@LouisDeconinck SQL output bindings does not have built in support for retries (https://github.com/Azure/azure-functions-sql-extension/blob/main/docs/BindingsOverview.md#retry-support-for-output-bindings).

Since you are using a timer trigger, would something like this retry example help? https://learn.microsoft.com/en-us/azure/azure-functions/functions-bindings-error-pages?tabs=fixed-delay%2Cisolated-process%2Cnode-v4%2Cpython-v2&pivots=programming-language-python#retry-examples