Azure SQL 依赖项(基于 EF Core 3.1.7)未出现在 App Insights 的应用程序地图中 [英] Azure SQL dependency (based on EF Core 3.1.7) is not appearing in Application Map of App Insights

问题描述

我们有基于 .net Core 3.1 的 Azure 函数.我们使用最新版本的 EntityFrameworkCore.

它连接到 Azure SQL 以存储/检索/查询数据.我们有时可以在应用洞察实时流中看到 Azure SQL 的日志，例如打开连接、关闭连接(有时可能是因为启用了采样)

但是，我们在应用洞察的应用程序地图中没有看到 Azure SQL 依赖项.甚至，查看 traces 表，我也没有看到任何与 Azure SQL 相关的内容.

是否需要启用某些东西才能让 Azure SQL 显示为依赖项?我在几篇 m​​sdn 文章中读到，当您使用 Microsoft.Data.SqlClient 包时，它会自动检测到 SQL(我看到 EF 核心已经在内部安装了该包).

如果上述问题得到解答和解决，还有一个后续问题 - 有没有办法，我可以检查连接是否已释放/关闭，或者连接何时为应用洞察中的给定函数调用打开/关闭?

根据下面的评论，添加更多信息，

我们使用启动文件中的以下语句将 DbContext 添加到服务中.

builder.Services.AddDbContextPool(options =>{options.UseSqlServer("connectionstring"), builder =>{builder.EnableRetryOnFailure(3, TimeSpan.FromSeconds(2), null);});});

OurDbContext 类具有以下构造函数，

public OurDbContext(DbContextOptions options):基础(选项){}

然后我们在不同的存储库中注入 OurDbContext 类，这些存储库使用此上下文与 SQL 对话.类似如下:

公共类回购:IRepo{公共回购(OurDbContext ourDbContext){}公共异步任务 AddAsync(实体实体){ourDbContext.AddAsync(entity);ourDbContext.SaveChangesAsync()}}

我们在Function类中注入这些repos并调用上面的方法如

await _repo.AddAsync()

我们使用以下 EFCore 包

我们在host.json文件中有以下内容.

<代码>{版本":2.0"，记录":{应用洞察":{samplingExcludedTypes":请求"，采样设置":{isEnabled":真}}}}

注意:我尝试了下面的链接只是为了检查 sql 依赖项是否显示在应用程序洞察中，尽管它不使用我正在使用的 EFCore/最新版本的 Azure 函数的配置.我唯一添加的是本地设置中的 APPINSIGHTS_INSTRUMENTATIONKEY.

来自 stackify 的日志.

解决方案

更新您在 host.json 中的日志部分，如下所示，以允许信息级别的日志(注意我在您上面发布的现有配置中添加了 logLevel).默认情况下，如果您未指定，则为警告.如注 此处所述，依赖关系以信息级别记录.另请注意excludedTypes(不是samplingExcludedTypes)应该在samplingSettings 内，按照文档.

<代码>{版本":2.0"，记录":{应用洞察":{采样设置":{isEnabled":真，excludedTypes":Dependency;Request"}},日志级别":{默认":信息"}}}

同样对于 Azure 函数，你不应该在 Startup 中添加 microsoft.applicationinsights.aspnetcore nuget 和 builder.Services.AddApplicationInsightsTelemetry();.那是针对asp.net 核心应用程序的.函数不应该有 https://docs.microsoft.com/en-us/azure/azure-functions/functions-dotnet-dependency-injection#logging-services

We have Azure functions based on .net Core 3.1. We use latest version of EntityFrameworkCore.

It connects to Azure SQL to store/retrieve/query data. We are able to see logs for Azure SQL such as Opening connection, closing connection in Live Stream of App insights sometimes (sometimes may be because of Sampling being enabled)

But, we don't see Azure SQL dependency in Application map of App insights. Even, looking at the traces table, I don't see anything related to Azure SQL.

Is there something we need to enable for Azure SQL to show as dependency? I read in few msdn articles, that it is automatically detected for SQL when you use Microsoft.Data.SqlClient package (and I see EF core has that package installed already internally).

Also a follow up question if above is answered and resolved - is there a way, I can check if connection is disposed/closed or when was the connection opened/closed for given function invocation in App insights?

As per below comment, adding more info,

We add DbContext to the services using following statement in the startup file.

builder.Services.AddDbContextPool<OurDbContext>(options =>
{
    options.UseSqlServer("connectionstring"), builder =>
    {
       builder.EnableRetryOnFailure(3, TimeSpan.FromSeconds(2), null);
    });
});

OurDbContext class has following constructor,

public OurDbContext(DbContextOptions<OurDbContext> options)
    : base(options)
{
}

And then we inject OurDbContext class in different repositories which uses this context to talk to SQL. Similar to below:

public class Repo : IRepo
{
  public Repo(OurDbContext ourDbContext)
  {

  }
  
  public async Task AddAsync(Entity entity)
  {
    ourDbContext.AddAsync(entity);
    ourDbContext.SaveChangesAsync()
  }
}

We inject these repos in Function classes and call above methods such as

await _repo.AddAsync()

We use below EFCore packages

we have below in host.json file.

{
    "version": "2.0",
    "logging": {
        "applicationInsights": {
            "samplingExcludedTypes": "Request",
            "samplingSettings": {
                "isEnabled": true
            }
        }
    }
}

Note: I tried below link just to check if sql dependency is showing up in App insights, though it does not use the configuration of EFCore/latest version of Azure functions which I am using. Only thing I have added is APPINSIGHTS_INSTRUMENTATIONKEY in my local settings.

https://dev.to/azure/using-entity-framework-with-azure-functions-50aa GitHub Source code: https://github.com/jeffhollan/functions-csharp-entityframeworkcore

With above, I was able to see SQL dependency in my app insights. But, when I modified above to the version of Azure functions, .net core, EFCore, I am using for my current project, SQL dependencies stopped appearing in App insights. Though, adding below logging level shows debug logs in Console.

"Logging": {
    "LogLevel": {
      "Default": "Debug",
    }
}

Screenshot as per below comment for KrishnenduGhosh-MSFT.

Logs from stackify.

解决方案

Update your logging section in host.json as below to allow Information level log (note I added logLevel in the existing config you posted above). By default it's Warning if you do not specify. As mentioned in the Note here, dependencies are logged with Information level. Also note excludedTypes (not samplingExcludedTypes) should be inside samplingSettings as per documentation.

{
  "version": "2.0",
  "logging": {
    "applicationInsights": {
            "samplingSettings": {
                "isEnabled": true,
                "excludedTypes": "Dependency;Request"
            }
        },
    "logLevel": {"default": "Information"}
  }
}

Also for Azure function, you should not add microsoft.applicationinsights.aspnetcore nuget and builder.Services.AddApplicationInsightsTelemetry(); in Startup. That's for asp.net core application. Function should not have that https://docs.microsoft.com/en-us/azure/azure-functions/functions-dotnet-dependency-injection#logging-services

这篇关于Azure SQL 依赖项(基于 EF Core 3.1.7)未出现在 App Insights 的应用程序地图中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！