Serilog写入SQL Server实战：字段映射、批量写入与避坑指南

从系列第一篇写到第五篇我一直没碰数据库 Sinks原因很简单文件 Sinks 够用的时候真没必要把问题搞复杂。但等到日志量上来业务的请求日志、异常堆栈、用户操作记录全混在一起再靠grep去翻文件效率低到让人抓狂。这时候把日志写进数据库就成了很自然的选择。这一篇我把 Serilog 接 SQL Server 的完整过程、字段映射、批量写入参数、以及那几个容易踩的坑一次说清楚。内容基于 .NET 8 Serilog.Sinks.MSSqlServer 实战适合已经把 Serilog 基础用法跑通、准备进一步升级日志存储方案的开发者。1. 为什么我会把日志写进数据库1.1 日志文件方案先撑不住了先说一个很典型的场景。项目早期用户量不大我在appsettings.json里用WriteTo.File按天切割日志文件保留 30 天开发排查问题基本靠记事本打开日志文件CtrlF搜关键字。这个阶段文件方案完全能打。但业务一旦跑起来几个问题会陆续浮现。一是数据量上去以后一天几个 GB 的日志文件任何编辑器打开都卡grep一条关键字等好几秒。二是日志分散在多台服务器上你在本机只能翻到其中一台的日志真要定位跨节点的问题得一台一台去看。三是没法按维度过滤比如想查某个 userId 最近一天内所有报错在文件里做这种事情非常痛苦但换成 SQL 就是一条WHERE查询的事。最开始我还想过用日志中间件把结构化字段塞进日志消息里比如用户 {UserId} 登录成功这种模板文件里看着还行但真要统计今天登录失败多少次就尴尬了。这些场景都指向同一个方向把日志当数据存而不是当文本存。数据库 Sinks 解决的问题就是这个。它把每条日志结构化写入表默认就有消息模板、级别、时间、异常、属性列再加上你自定义的业务字段后续查询、统计、对接可视化系统都省事得多。1.2 主流数据库 Sinks 怎么选Serilog 生态里数据库 Sinks 不少我列一下我自己用过的几个做个对比。Sink 包目标数据库使用体验我的一点看法Serilog.Sinks.MSSqlServerSQL Server / Azure SQL功能最全字段映射和选项丰富微软技术栈首选也是官方维护比较积极的Serilog.Sinks.PostgreSQLPostgreSQL支持 COPY 批量插入性能不错注意列名不要和系统列冲突类型映射要手调Serilog.Sinks.MySQLMySQL / MariaDB配置简单中规中矩小团队够用字段自定义要写 JSON 配置Serilog.Sinks.SQLiteSQLite轻量单机零部署适合嵌入式或本地工具不适合多实例共享选型没有绝对标准我一般看三点团队对哪种数据库最熟、公司是否已经有现成的数据库集群/备份体系、日志数据后续要不要做统计分析。像我这边的项目业务库本身就是 SQL Server运维、备份、权限体系都成熟那MSSqlServer就是最省事的选择。还有人问要不要专门为日志建一套单独的数据库我的建议是尽量分开。日志表写入频繁、增长快和业务库混在一起容易出现相互影响建独立库之后备份策略、分区策略也更好单独控制。2. 动手前的准备连接串、表结构与权限2.1 连接字符串和最小权限先把这几项盯牢接数据库 Sinks 之前我建议先顺手把连接字符串的细节确认好。Serilog.Sinks.MSSqlServer用的连接字符串和普通 ADO.NET 完全一样比如var connectionString Serverlocalhost;DatabaseLogDb;Integrated SecurityTrue;TrustServerCertificateTrue;;这里面有两个细节值得注意。第一是TrustServerCertificate。如果你是连本地开发库或者内网测试库SQL Server 默认会给你一个自签名证书新版驱动连接时如果校验证书就会报证书链相关的错误。开发环境直接设TrustServerCertificateTrue最省事。生产环境如果已经配置了正式证书可以关掉这个选项保持加密校验。第二是账号权限。如果只是往已有的日志表里写数据数据库账号有db_datawriter就够了。但如果你依赖AutoCreateSqlTable让代码自动建表那当前账号必须拥有CREATE TABLE权限通常需要db_owner或者 DDL 管理员。我见过不少同事第一步就卡在这配置没问题、连接串没问题但表就是建不出来打开数据库一看账号权限不够日志全部悄悄丢了。所以我的习惯是往数据库写入的专用账号能不开最高权限就不要开最高权限。独立的日志库 权限收敛后面排障会省很多事。2.2 自动建表很香但生产库别直接依赖它Serilog.Sinks.MSSqlServer有个很实用的配置叫AutoCreateSqlTable设成true之后程序第一次启动会自动创建日志表本地验证流程特别方便。它默认建出来的表结构大概是这样的列名类型说明Idint identity自增主键Messagenvarchar(max)渲染后的日志消息MessageTemplatenvarchar(max)原始消息模板Levelnvarchar(128)日志级别TimeStampdatetime时间戳Exceptionnvarchar(max)异常堆栈Propertiesxml附加属性一眼看过去这套默认结构已经很能打了。但我还是建议生产环境手动建表原因有三个。第一默认表没有任何业务索引。日志量一大按TimeStamp查最近数据、按Level筛选报错全表扫描会让你怀疑数据库性能。手动建表可以把索引事先规划好。第二默认列的长度和类型不一定适合你。比如你希望TimeStamp存datetime2而不是datetime手动建表就能自由控制。第三有些团队会给日志表做分区、压缩或者把历史数据归档到独立文件组这些都不是自动建表能解决的。下面是我常用的手动建表 SQL你可以根据实际情况裁剪CREATE TABLE [dbo].[Logs] ( [Id] INT IDENTITY(1,1) NOT NULL, [Message] NVARCHAR(MAX) NULL, [MessageTemplate] NVARCHAR(MAX) NULL, [Level] NVARCHAR(128) NULL, [TimeStamp] DATETIME2(3) NOT NULL, [Exception] NVARCHAR(MAX) NULL, [Properties] NVARCHAR(MAX) NULL, [UserId] NVARCHAR(64) NULL, [IpAddress] NVARCHAR(64) NULL, [RequestPath] NVARCHAR(512) NULL, CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED ([Id] ASC) ); GO CREATE NONCLUSTERED INDEX [IX_Logs_TimeStamp_Level] ON [dbo].[Logs] ([TimeStamp] DESC, [Level] ASC); GO注意这里我把Properties列设成了NVARCHAR(MAX)因为后面配置里我会把属性类型改成 JSON和默认的 XML 区分布局区分开。3. 核心接入从引入包到正确映射字段3.1 安装包与最小配置先在本地跑通先装包。用dotnet命令或者 Visual Studio 的 NuGet 管理器都行我需要两个包dotnet add package Serilog.AspNetCore dotnet add package Serilog.Sinks.MSSqlServerSerilog.AspNetCore负责把 Serilog 接入 ASP.NET Core 的日志管道Serilog.Sinks.MSSqlServer才是写数据库的核心包。版本方面我用的是当前稳定版 6.x如果你是老项目还在用 3.x配置差异不会太大但 API 细节可能有变化建议先升级。最省事的方式是在appsettings.json里配置。下面这个是最小配置{ Serilog: { MinimumLevel: { Default: Information, Override: { Microsoft.AspNetCore: Warning } }, WriteTo: [ { Name: MSSqlServer, Args: { connectionString: Serverlocalhost;DatabaseLogDb;Integrated SecurityTrue;TrustServerCertificateTrue;, tableName: Logs, autoCreateSqlTable: true } } ] } }这样配完之后在Program.cs里调用一行builder.Host.UseSerilog((context, config) config.ReadFrom.Configuration(context.Configuration));启动程序然后随便打几条日志去数据库看一眼表建出来了记录也进去了。这一切顺畅的话说明环境没问题。但我必须提醒一句appsettings.json配置虽然方便字段映射选项一旦多起来JSON 里的写法会变得很难维护。所以我后面更推荐代码方式配置尤其是要加自定义字段的时候。3.2 字段映射把日志上下文变成可查列默认情况下写进数据库的只有那 7 个基础列。业务侧真正需要的往往是这个日志是哪个用户触发的请求来自哪个 IP对应哪个请求路径这些默认都不会单独成一列而是混在Properties里。要把这些字段变成独立的表列需要配置ColumnOptions.AdditionalColumns。下面是一段完整的配置代码using System.Data; using Serilog; using Serilog.Sinks.MSSqlServer; var columnOptions new ColumnOptions { AdditionalColumns new ListSqlColumn { new SqlColumn { ColumnName UserId, DataType SqlDbType.NVarChar, DataLength 64 }, new SqlColumn { ColumnName IpAddress, DataType SqlDbType.NVarChar, DataLength 64 }, new SqlColumn { ColumnName RequestPath, DataType SqlDbType.NVarChar, DataLength 512 } } }; columnOptions.Properties.PropertyType PropertyType.Json; var connectionString Serverlocalhost;DatabaseLogDb;Integrated SecurityTrue;TrustServerCertificateTrue;; Log.Logger new LoggerConfiguration() .MinimumLevel.Information() .Enrich.FromLogContext() .WriteTo.MSSqlServer( connectionString: connectionString, sinkOptions: new MSSqlServerSinkOptions { TableName Logs, AutoCreateSqlTable true, BatchPostingLimit 1000, BatchPeriod TimeSpan.FromSeconds(10) }, columnOptions: columnOptions) .CreateLogger();这一段里面有三处很关键。第一处是.Enrich.FromLogContext()它是后续所有自定义字段生效的前提。没有这行你LogContext.PushProperty推上去的属性根本不会进入日志事件。第二处是AdditionalColumns我定义了三列列名和 Sql 类型、长度都显式指定。第三处是columnOptions.Properties.PropertyType PropertyType.Json把默认的 XML 格式改成 JSON查询时更直观也避免了一些奇怪字符导致 XML 序列化报错。字段在代码里怎么推常见的有两种方式。手动用LogContext.PushProperty适合在某个业务方法里临时加字段using Serilog.Context; using (LogContext.PushProperty(UserId, userId)) using (LogContext.PushProperty(RequestPath, currentPath)) { Log.Information(用户操作成功); }在 ASP.NET Core 项目里更实用的做法是在请求管道入口处统一附加字段。比如配合UseSerilogRequestLogging重载里的EnrichDiagnosticContextapp.UseSerilogRequestLogging(options { options.EnrichDiagnosticContext (diagnosticContext, httpContext) { diagnosticContext.Set(UserId, httpContext.User?.Identity?.Name); diagnosticContext.Set(IpAddress, httpContext.Connection.RemoteIpAddress?.ToString()); diagnosticContext.Set(RequestPath, httpContext.Request.Path); }; });这样请求级日志会自动带上这三个字段业务日志里加字段仍然用LogContext.PushProperty手动推。两套配合起来查询体验会好很多。3.3 批量写入参数别让日志 sink 拖垮主业务很多刚开始用数据库 Sinks 的人会有一个误区认为日志是异步写入的所以没性能压力。实际上 Serilog 的 Sinks 内部确实有队列缓冲、批量提交机制但如果你把BatchPostingLimit配得特别小或者忘记调整默认周期高频日志场景下依然会出现频繁的小批次写入对数据库造成不小的压力。我项目里的推荐配置是BatchPostingLimit 1000BatchPeriod TimeSpan.FromSeconds(10)。意思是队列攒够 1000 条日志或者最多等 10 秒就批量写一次。你可以根据业务量调整这两个值但有一点要清楚批量越大日志落库的延迟越高批量太小数据库写入次数就越频繁。日志系统追求的是吞吐和检索不是每条日志秒级可见所以稍微攒一批再写是合适的。这里要额外提一下失败处理的机制。Serilog 的 batched sinks 内部有一个生产者消费者模型日志事件先进入内存队列后台线程负责批量发送到数据库。如果数据库暂时不可用写入会重试但这不是无限可靠。如果程序进程崩溃或者队列积压超过容量这部分日志就会丢。所以我的原则一直是数据库 Sink 负责主要存储但保留一个文件 Sink 作为兜底万一数据库出问题文件里还有原始日志可查。配置里同时写多个 Sinks 很直观Log.Logger new LoggerConfiguration() .WriteTo.Console() .WriteTo.File(logs/log-.txt, rollingInterval: RollingInterval.Day) .WriteTo.MSSqlServer(...) .CreateLogger();这样控制台输出、文件存档、数据库存储三份并行互不干扰故障时也有退路。4. 我踩过的坑和排查技巧4.1 日志表建不出来 / 写入没反应这个场景我至少遇到过三轮。现象都一样程序正常启动日志正常输出到控制台数据库里却没表、没记录。先从最简单的排查起连接字符串对不对数据库账号有没有权限。我有个项目用集成认证连接本地数据库一切正常换到服务器上改成 SQL 账号认证结果密码字符串里带了分号连接直接失败。所以连接字符串排在第一顺位。第二步检查权限。如果你的AutoCreateSqlTable是true但账号只有写权限没有建表权限程序不会主动报错日志就是静默丢。我的排查技巧是在开发环境故意用一个高权限账号跑通再换回生产专用账号这样就能快速定位是不是权限问题。第三是检查级别过滤。MinimumLevel配置成Warning的时候你打的Information日志是不会进入 Sinks 的。这种问题最隐蔽因为程序完全正常日志也都打了但被过滤器挡在了上游。所以排查的时候先在MinimumLevel里临时放宽到Verbose或Debug同时控制台 Sink 打开先确认消息有没有到达 Sink 层。4.2 时间、JSON 与特殊字符的麻烦时间字段的坑比较常见。默认TimeStamp存的是 UTC 时间如果你在本地测试时看到数据比系统时间早了 8 个小时不要慌这是 UTC 和本地时间的差异。我的做法是数据库里统一存 UTC展示层按用户时区转换。如果你看到的时间完全不对检查一下是不是 MySQL 或者其他 Sink 的配置弄混了。Properties的格式也值得小心。刚才我特意把PropertyType设成了Json因为默认的Xml在某些情况下会有问题。举个例子日志模板里如果带了特殊的控制字符或者某些框架写的属性值里包含不合法 XML 字符写入时可能触发序列化异常。换成 JSON 之后我基本再没遇到过这类问题。另一个跟字段有关的坑是长度截断。AdditionalColumns里我建议把字符串类型的DataLength留足。像RequestPath看起来 256 够用实际生产环境有人把整个查询字符串拼进去512 也不一定够。长度不够时写入会失败而且错误信息不一定明显。我的经验是宁可设大一点也别卡太紧。最后提一句敏感信息。异常堆栈里经常带上连接字符串、token、堆栈路径日志入了数据库可检索性就强了公司内部能查日志的人也会变多。我建议在入口处加一个日志脱敏步骤或者至少不要把密码、密钥这些直接塞进日志字段这个问题等踩雷再处理就晚了。4.3 性能回归了先怀疑这里有段时间我发现数据库日志表所在的实例 CPU 经常飙高排查了很久最后发现是这两个原因叠在一起。第一个原因是默认的日志表索引不够。业务日志按TimeStamp排序查询日志量上百万行以后没有索引就是全表扫描数据库再强也扛不住。解决方法是手动建表时把IX_Logs_TimeStamp_Level这种组合索引建上查询效率会有数量级的提升。第二个原因是并发连接没有控制。Serilog 的MSSqlServerSink 后台线程数量是受控的但如果你在一个进程里创建了多个Logger实例每个实例都持有独立的连接池和后台线程连接数就会翻倍。我的建议是全程只用一个静态Log.Logger不要每个类里都 new 一个LoggerConfiguration出来。我自己整理过一个常见问题速查表放在这里供你参考现象可能原因解决办法日志表没有自动创建账号权限不足检查CREATE TABLE权限或手动建表日志完全不写入连接串错误 / 级别过滤验证连接字符串临时调低MinimumLevel只有一部分日志写入批量周期未到等待BatchPeriod触发或调大批量参数写入报 XML 错误Properties默认 XML 格式改成PropertyType.Json数据库 CPU 高缺少索引 / 多次创建 Logger 实例手动建索引统一使用静态Log.Logger某字段被截断DataLength过短调大字段长度或改成MAX日志系统看起来只是打日志这么简单但真正跑起来之后存储选型、字段规划、权限控制、批量策略、索引设计每一个点都能直接影响线上排查效率。数据库 Sinks 把日志从文本文件变成了可查询的数据这步迈对了后面做监控、告警、统计报表都会顺很多。我个人在实际操作中的体会是接入数据库 Sinks 之后最大的变化不是日志能查了而是团队协作方式变了。以前排查问题开发找运维要服务器日志、运维打包传文件、开发本地慢慢翻现在直接给运维一条带条件的 SQL运维查完把结果贴回来事情几分钟就结束了。这种效率提升才是日志系统升级最值钱的部分。最后再分享一个小技巧。如果你用的是 SQL Server日志表最好从一开始就加上保留天数清理策略。日志表只增不删时间一长再多的磁盘也不够用。我习惯写一个简单的 SQL Agent 作业每天凌晨删除TimeStamp超过 30 天的记录或者把这部分记录归档到独立的历史表。这样日志主表体积可控查询也一直能保持在一个比较快的水平。