DataChangeTrackingFunctionApp

Azure Functions app (.NET 8, isolated worker) that tracks SQL table changes, filters allowed columns, posts changes to an HTTP endpoint, and uses Durable Functions for retries, notifications, and entity state.

Features

• SQL Change Tracking via a C# Azure Function SqlTrigger.
• Durable Functions:
  • Orchestrations for retry/backoff (DataPostOrchestration, NotifyOrchestrator).
  • Durable Entities for state: RetryCount, LastError, AllowedColumns.
• Configurable allowed columns (per table) that can be set in the app config, and an additional allowed columns that can be set by the downstream target via an Api call.
• Cleanup job to purge old orchestration history and entity state.
• Event-driven, no polling required: This app uses the Azure SQL trigger in an event-driven manner. When SQL Change Tracking/CDC commits changes, the SqlTrigger delivers them to the function automatically. You do not need to write custom polling loops; the extension handles lease management, batching, and delivery.

Serverless app that can be used for data integration

This app runs serverlessly on Azure Functions, scaling automatically based on load with minimal operational overhead. It is well-suited for integration scenarios where database changes must be propagated to downstream systems in near real time. Common patterns include:

• Posting change events to HTTP APIs or webhooks
• Enqueuing messages to queues/topics
• Triggering orchestrations for retries and notifications

Prerequisites

• .NET 8 SDK
• Azure Functions Core Tools (for local run)
• SQL Server with Change Tracking enabled and the demo tables created (see sql.txt).

Configuration

Create local.settings.json (excluded from deployment) with required settings:

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "UseDevelopmentStorage=true",
        "FUNCTIONS_WORKER_RUNTIME": "dotnet-isolated",
        "WEBSITE_SITE_NAME": "DataChangeTrackingFunctionApp",
        "SqlConnectionString": "<your-connection-string>",
        "Sql_Trigger_MaxBatchSize": 1000,
        "Sql_Trigger_PollingIntervalMs": 5000,
        "Sql_Trigger_MaxChangesPerWorker": 10000,
        "HttpPostBaseUrl": "https://httpbin.org/",
        "DurableFunctionRetryIntervalMinutes": 15,
        "AllowedColumns_dbo.TrackingDemo": "Id,Name,LastUpdate",
        "NotifyOnRetryCount": 10,
        "KeepInstanceCompletedHistoryDays": 7,
        "KeepInstanceFailedHistoryDays": 30
  }
}

Notes:

• For client-driven columns, use AllowedColumns entity via the SaveClientAllowedColumns function.
• Ensure lease table names passed to SqlTrigger are valid (no dots in lease names). Example: "lease_dbo_TrackingDemo" if required by your environment.

Getting Started

1. Create demo tables in SQL: see DataChangeTrackingFunctionApp/sql.txt.
2. Enable SQL Change Tracking or CDC for the database/tables.
3. Restore/build:
   • dotnet restore
   • dotnet build
4. Run locally:
   • func start or from IDE.
5. Trigger changes: insert/update/delete rows in tracked tables.
6. Observe functions:
   • ChangeTrackingFunction picks up changes and posts JSON to an http endpoint.
   • Failures record LastError entity and start DataPostOrchestration.
   • NotifyOrchestrator runs when RetryCount hits NotifyOnRetryCount.
   • CleanupFunction purges old history and entity state per schedule.

HTTP Endpoints

• SaveClientAllowedColumns: set allowed columns per table.
• GetClientAllowedColumns: read allowed columns for a table.
• /post: sample endpoint to receive posted change payloads (configure HttpPostBaseUrl).

Tuning

• SQL trigger polling/lease via host.json (extension settings).
• Orchestration intervals via DurableFunctionRetryIntervalMinutes.
• Retry policies in NotifyOrchestrator.

Deployment

Deploy to Azure Functions (Consumption or Premium). Ensure app settings include:

• WEBSITE_SITE_NAME (set by Azure automatically on deploy).
• All values from local.settings.json moved into Azure Configuration.

License

MIT