Optional configurations for Sql Trigger are not respected

[ben-ward1]

• Azure Functions SQL Extension or Extension Bundle Version: 3.0.461
• Is this a deployed or local function: both
• What type of Database are you using?
  • deployed:
    • Version: Microsoft SQL Azure (RTM) - 12.0.2000.8 Nov 2 2023 01:40:17 Copyright (C) 2022 Microsoft Corporation
    • Engine: 5
  • local:
    • Version: Microsoft SQL Server 2019 (RTM-GDR) (KB5029377) - 15.0.2104.1 (X64) Aug 16 2023 00:09:21 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 19045: ) (Hypervisor)
    • Engine: 3

Steps to Reproduce (local):

1. Create sql trigger function
2. Add these values to local.settings.json

{
  ... other configs
  "Sql_Trigger_BatchSize": 1,
  "Sql_Trigger_PollingIntervalMs": 30000
}

3. Configure trigger with table name, connection string
4. Enable change tracking on db and table
5. Insert multiple rows into target table

Referencing the documentation here, I would expect that with the config values above, only a single record would process every 30 seconds. It actually executes with the defaults. Same behavior is observed in deployed environment with these same values as application settings. Application insight traces read: SqlOptions { "MaxBatchSize": 100, "PollingIntervalMs": 1000, "MaxChangesPerWorker": 1000 }

I believed these configs should be set as app settings based on this comment and associated PR. Let me know if I am mistaken.

[Charles-Gagnon]

The trace logs only show the value configured for the SqlOptions themselves - so that's expected behavior since it doesn't take into account any config based options.

I just tried it myself and it seems to work fine for me - if I change the MaxBatchSize and polling interval in my local.settings.json like this :

{
  "IsEncrypted": false,
  "Values": {
    ...
    "Sql_Trigger_MaxBatchSize": 1,
    "Sql_Trigger_PollingIntervalMs": 5000
  }
}

I only see one value processed every 5 seconds. Can you double check that you have the values set correctly?

That being said - we'd definitely recommend switching to using the host configuration - as the config based settings will be deprecated at some point in the future.

https://learn.microsoft.com/en-us/azure/azure-functions/functions-host-json#extensions

e.g.

  "extensions": {
    "sql": {
      "maxBatchSize": 1,
      "pollingIntervalMs": 5000
    }
  }

[ben-ward1]

Thanks @Charles-Gagnon.

Although my app is still not respecting these values in local.settings.json, the host.json based config works as expected. Not sure how to isolate the issue, but I'll continue with your recommended approach.

Will also review the docs to see if anything needs to be added to communicate how to use host.json for the sql options when I get a chance.