SQL Server Replication Alerts and Health Check using Azure Durable Functions (2)

SQL Server Replication Health Check using Azure Durable Functions

1. Design

The design proposes the use of Azure Durable functions, which have an internal implementation that allows them to keep track of the function's execution history.

The Azure Durable function app contains the following functions and performs the described operations:

1. Orchestration function: 
   This function contains Activity Functions 1 and 2.

2. Activity Function 1: 
   This function implements the code that updates a record in the main  
   database.

3. Activity Function 2: 
   This function checks if the previously modified record has been updated 
   in the database's replica.

4. The health check API performs the following actions:

    1. Checks if the last executed orchestration instance is too old and 
       needs to be re-started.

    2. Re-starts the orchestration.

    3. Monitors the changes to the replica database using Activity 
       Function 2.

    4. When the changes have been applied for a pre-determined time  
       interval or the time interval expires, it returns the status of the 
       replication process.

    5. If the last orchestration is not too old, it returns the status of 
       the last replication process.        

## Sample Code

    To simulate the update of a database replication, the code uses a      
    global counter. The first activity resets the counter, the second 
    activity increments the counter, and the health check function checks   
    if the counter value is greater than 0.

    The API that checks the status of the replication process is named  
    "StatusCheck" and performs the following actions:

    Step 1 
    - Retrieves the last completed orchestration instances, ordered  in  
      descending order based on the "lastUpdatedTime".        

Step 2 - Retrieve the most recent completed instance, sorted by 
        'lastUpdatedTime' in descending order.
Step 3 - Obtain the status of the most recent completed instance.
Step 4 - Compute the time elapsed since the most recent completed instance 
         was updated.
Step 5 - Verify if any instances are currently running.
Step 6 - Proceed if no instances are found.
Step 7 - Initiate the orchestration.        

Step 6.1 - Instances have completed and no instances are currently 
           running.
Step 7.1 - The most recent completed instance is more than 1 minute old.
Step 7.2 - Restart the orchestration.
Step 8   - Instances from the past are still running.
Step 8.1 - A completed instance has been processed within the last minute.
Step 9   - This indicates that a use case has not been implemented.        

How to use the time interval:

This variable it is used in Step 7.1 (see figure above) to check if the last completed orchestration is older than this interval.

Activity 2 is called once a minute. If the timeIntervalInMinutes = 5 then this activity will be called 5 times.

Execution's results:

1. Start the project in Debug mode

2. Call the StatusCheck function for the first time:

3. Call the StatusCheck function for the second time:

4. Call the UpdateCounter function:

5. Call the StatusCheck function for the third time:

6. Call the StatusCheck function for the forth time, after longer than 1 minute:

7. Call the StatusCheck function for the fifth time, after the orchestration has been completed.

Notice that we have not called the UpdateCounter function therefore the timeout will expire and the replication's status is set to "Not Healthy":

8. Call the StatusCheck function for the sixth time, after longer than 1 minute: you will see the same screenshot as in 6).

References:

Timers with Azure Durable Functions