How to delay process using Azure functions?

Question

I need to delay the execution of the data insertion in the DB2. I need to wait for 10 min for the following DB deployment reason

Registration Process

• Step 1: I made an entry to the table of DB1.
• Step 2: Start the process to create DB2- Database creation DB2
• Step 3: After 10 min need to make entry in created DB2.

How it can be achieved using the Azure functions?

Unlovely

Answer 1

One way would be to send a message to a Service Bus queue with ScheduledEnqueueTimeUtc property set to 10 minutes from now. (docs)

Then have an Azure Function which is triggered by that Service Bus queue. (docs)

Thekla

Answer 2

Because I need to wait for 10 min for the following DB deployment reason : Not able to open Azure SQL Server DB immidiately after the creation

As the global configuration options host.json states about functionTimeout as follows:

functionTimeout Value indicating the timeout duration for all functions.

• In Dynamic SKUs, the valid range is from 1 second to 10 minutes and the default value is 5 minutes.
• In Paid SKUs there is no limit and the default value is null (indicating no timeout).

Per my understanding, if your registration process need to be done under CreateTenant, I assumed that you could check the creation status of your database after Step2, then when the database is online, you could do Step3. I wrote a sample for this scenario, you could refer to it:

run.csx

#r "System.Configuration"
#r "System.Data"

using System.Net;

using System.Configuration;
using System.Data.SqlClient;
using System.Threading.Tasks;

public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)
{
    // Get request body
    dynamic data = await req.Content.ReadAsAsync<object>();
    string dataBase_Name = data?.dataBase_Name;
    string source_dataBase_Name = data?.source_dataBase_Name;

    log.Info("Begin create the database for the tenant...");

    //Create the database
    var str = ConfigurationManager.ConnectionStrings["sqldb_connection"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(str))
    {
        conn.Open();
        var copyDbSql = $"CREATE DATABASE [{dataBase_Name}] as COPY OF [{source_dataBase_Name}] (SERVICE_OBJECTIVE='S0')";
        try
        {
            using (SqlCommand cmd = new SqlCommand(copyDbSql, conn))
            {   
                //30s by default, https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx
                //cmd.CommandTimeout=0; 
                cmd.ExecuteNonQuery(); //30s timeout if the server is not responding, you could change it, but I would leave it for default.
                log.Info("create database statement executed...");
            }
        }
        catch (Exception e)
        {
            //exception for timeout and so on
            //you need to make sure the database creation has been accepted, you could execute the following sql statement:
            //SELECT * FROM sys.dm_database_copies where partner_database=N'{your-database-name}'
            log.Info(e.Message);
        }
    }

    log.Info("check the creation processing...");
    //If the database creation is accepted, then check the creation status of your database
    bool status = false;
    using (SqlConnection conn = new SqlConnection(str))
    {
        conn.Open();
        var text = "Select count(*) from master.sys.databases where name=N'" + dataBase_Name + "' and state_desc='ONLINE'";
        using (SqlCommand cmd = new SqlCommand(text, conn))
        {
            do
            {
                var count = await cmd.ExecuteScalarAsync();
                if (count != null && Convert.ToInt32(count) > 0)
                    status = true;
                if (status)
                    log.Info($"Database [{dataBase_Name}] is online!!!");
                else
                {
                    log.Info($"Database [{dataBase_Name}] is creating...");
                    Task.Delay(TimeSpan.FromSeconds(30)).Wait(); //sleep for 30s
                }
            } while (!status);
        }
    }

    if (status)
    {
        //Database is online, do other operations
    }
    return req.CreateResponse(HttpStatusCode.OK, "");
}

Result:

Also, as Mikhail suggested, you could send a queue message after you has executed the database creation, then use QueueTrigger to pick up the message and check the status of your database and insert entries after the database is online to decouple your registration process.

Here are some useful tutorials, you could refer to them:

Use Azure Functions to connect to an Azure SQL Database

sys.dm_database_copies (Azure SQL Database)

Aristocracy

Answer 3

I think now there are many solutions for that after Azure Function V2 and also Durable Functions extension:

1- You can use Durable Functions (link) so you can orchestrate the whole workflow and then put all the code you want to delay in separate function and then delay it using the normal Task.Delay

2- You can use Azure Logic Apps which you can configure it and schedule it to run based on time (link).

3- Also you can use Scheduled Service Bus Queue (link).

4- You can think out of the box by making the delay it self in the DB side using SQL job but its depend on your business and architecture.

Piecework