DFM with mssql backend shows no instances ("This list is empty")

[rncwnd79]

Hi,

I have the following setup:

• one durablefunctionsmonitor.mssql:6.5 docker container running as a standalone container in Kubernetes
• two durable functions, using the same mssql database with different connection strings (users), also running in Kubernetes
• the mssql server is not running in Kubernetes (if this matters)

Problem:

• the two durable functions write instances to the mssql db, I can see then in the database
• the DFM correctly identifies the task hubs
• but no instances are shown

I did not change the default configuration regarding db schema, multitenancy and allowed task hubs. So in my understanding the DFM should identify the task hubs directly from the tables (which it indeed does) and not from the connection string.
Therefore it shoule show all instances from all task hubs, but it doesn't.

Do I miss something?

Thanks for any help.

[scale-tone]

@rncwnd79 , can you post host.json file from at least one of your Functions (omitting any secrets) ? And also .CSPROJ file (assuming your Functions are in .NET)?

[rncwnd79]

@scale-tone sure:

host.json

{
  "version": "2.0",
  "extensions": {
    "durableTask": {
      "maxConcurrentActivityFunctions": 10,
      "maxConcurrentOrchestratorFunctions": 10,
      "storageProvider": {
        "type": "mssql",
        "connectionStringName": "SQLDB_Connection",
        "createDatabaseIfNotExists": true
      }
    }
  },
  "logging": {
    "applicationInsights": {
      "samplingSettings": {
        "isEnabled": true,
        "excludedTypes": "Request"
      },
      "enableLiveMetricsFilters": true
    }
  }
}

jsproj

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>net8.0</TargetFramework>
    <AzureFunctionsVersion>v4</AzureFunctionsVersion>
    <RootNamespace>My.Function</RootNamespace>
	<OutputType>Exe</OutputType>
	<ImplicitUsings>enable</ImplicitUsings>
	<Nullable>enable</Nullable>
    <DockerFastModeProjectMountDirectory>/home/site/wwwroot</DockerFastModeProjectMountDirectory>
    <DockerDefaultTargetOS>Linux</DockerDefaultTargetOS>
    <DockerfileContext>.</DockerfileContext>
    <GenerateDocumentationFile>True</GenerateDocumentationFile>
  </PropertyGroup>
  <PropertyGroup Condition="'$(Configuration)|$(Platform)'=='Debug|AnyCPU'">
    <NoWarn>1701;1702;1591</NoWarn>
  </PropertyGroup>
  <PropertyGroup Condition="'$(Configuration)|$(Platform)'=='Release|AnyCPU'">
    <NoWarn>1701;1702;1591</NoWarn>
  </PropertyGroup>
  <ItemGroup>
    <Content Include="local.settings.json" />
  </ItemGroup>
	<ItemGroup>
		<FrameworkReference Include="Microsoft.AspNetCore.App" />
		<PackageReference Include="AspNetCore.HealthChecks.Rabbitmq" Version="8.0.1" />
		<PackageReference Include="Azure.Identity" Version="1.12.0" />
		<PackageReference Include="Microsoft.Azure.Functions.Worker" Version="1.22.0" />
		<PackageReference Include="Microsoft.Azure.Functions.Worker.Extensions.DurableTask" Version="1.1.4" />
		<PackageReference Include="Microsoft.Azure.Functions.Worker.Extensions.OpenApi" Version="1.5.1" />
		<PackageReference Include="Microsoft.Azure.Functions.Worker.Extensions.RabbitMQ" Version="2.0.3" />
		<PackageReference Include="Microsoft.Azure.Functions.Worker.Sdk" Version="1.17.2" />
		<PackageReference Include="Microsoft.Azure.Functions.Worker.Extensions.Http.AspNetCore" Version="1.3.2" />
		<PackageReference Include="Microsoft.Azure.Functions.Worker.ApplicationInsights" Version="1.2.0" />
		<PackageReference Include="Microsoft.Azure.Functions.Worker.Extensions.DurableTask.SqlServer" Version="1.3.0" />
		<PackageReference Include="Microsoft.ApplicationInsights.WorkerService" Version="2.22.0" />
		<PackageReference Include="Microsoft.DurableTask.SqlServer.AzureFunctions" Version="1.3.0" />
		<PackageReference Include="Microsoft.Extensions.Diagnostics.HealthChecks" Version="8.0.6" />
		<PackageReference Include="Microsoft.IdentityModel.JsonWebTokens" Version="7.6.2" />
		<PackageReference Include="Microsoft.VisualStudio.Azure.Containers.Tools.Targets" Version="1.21.0" />
		<PackageReference Include="Microsoft.Extensions.DependencyInjection" Version="8.0.0" />
		<PackageReference Include="Microsoft.Extensions.Hosting" Version="8.0.0" />
		<PackageReference Include="GraphQL.Client" Version="6.1.0" />
		<PackageReference Include="GraphQL.Client.Serializer.Newtonsoft" Version="6.1.0" />
		<PackageReference Include="System.IdentityModel.Tokens.Jwt" Version="7.6.2" />
		<PackageReference Include="System.Net.Http" Version="4.3.4" />
		<PackageReference Include="System.Security.Cryptography.Pkcs" Version="8.0.0" />
		<PackageReference Include="System.ServiceModel.Federation" Version="8.0.0" />
		<PackageReference Include="System.Text.RegularExpressions" Version="4.3.1" />
	</ItemGroup>
  <ItemGroup>
      <None Update="host.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
    <None Update="local.settings.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
      <CopyToPublishDirectory>Never</CopyToPublishDirectory>
    </None>
  </ItemGroup>
</Project>

[scale-tone]

Tried with your package versions. Cannot reproduce.
So far have two suggestions:

1. This entry in your project file:

     <ItemGroup>
       <Content Include="local.settings.json" />
     </ItemGroup>
   

   looks unusual. If I understand the mechanics correctly, this will result in your local.settings.json file being packaged and deployed into your Function's container. And settings in there would override what you provide as your container's parameters. Are you sure it is intended and your settings do not interfere?

2. Can you try connecting to that DB with VsCode ext? Does that work? If yes, then I would blame some MSSQL connectivity from DfMon's container into your DB, and we'll investigate further.

[rncwnd79]

Thanks for looking into this.

1. local.settings.json is not checked in (included in .gitignore and .dockerignore) and we build using pipelines.
2. I can connect using the vscode extension, however the list is also empty there.

However, when I use the same connection string for the functions and the dfm, I can see the instances.
Is this a bug, or am I missing something?

[scale-tone]

However, when I use the same connection string for the functions and the dfm, I can see the instances.

Well, the connection string is supposed to be the same. Are you saying that your connection strings are different? In what way?

The logic of inferring Task Hub names is here, btw..

[rncwnd79]

Regarding inferring the Task Hub names: I saw that, and this works no matter which connection string is used for the DFM, so I thought retrieving instances from different task hubs should be independent of the connection string user.
As we use the (default) shared schema multi-tenancy mode (https://microsoft.github.io/durabletask-mssql/#/multitenancy?id=shared-schema-mode) and all users have access to the db.

[scale-tone]

Right, so the user name defines the default DB schema name, so when using a different user name you probably get into a different DB schema, and that would explain it.
You could also try to explicitly define the schema name via host.json or via 'AzureFunctionsJobHost__extensions__durableTask__storageProvider__schemaName' config setting.

[rncwnd79]

Thanks for all your effort. I will try that later.

[rncwnd79]

Right, so the user name defines the default DB schema name, so when using a different user name you probably get into a different DB schema, and that would explain it. You could also try to explicitly define the schema name via host.json or via 'AzureFunctionsJobHost__extensions__durableTask__storageProvider__schemaName' config setting.

I tested this and it makes no difference.

• one db
• one db schema ("dt")
• all sql server users have this schema configured as their default
• additionally configured AzureFunctionsJobHost__extensions__durableTask__storageProvider__schemaName to that schema ("dt")
• redeployed functions and dfm
• in dfm I can see two task hubs
• switch to different task hub doesn't change the list of displayed instances, I always see the same instances from the task hub of the dfm connection string user (which is the user of the function which instances are shown), no matter which task hub is selected.

So we will have to use the same db user for all functions + dfm. Not ideal but if nothing else works...

[rncwnd79]

One last comment ;)

I think the cause of this is, that DFM doesn't implement it's own version of the sql function CurrentTaskHub which should return the selected task hub of the ui and not the username / appname.
(Yeah, it's not that easy, as this sql function is used in all stored procedures... and dfm still uses those).