Technitium DNS Server 的 Query Logs (SQL Server) App：将 DNS 查询日志写入 Microsoft SQL Server 的完整实战指南

• 网络
• 后端

本指南围绕 Technitium DNS Server 仓库中的QueryLogsSqlServerApp（Query Logs (SQL Server) DNS App）展开，它是一套将 DNS 服务器收到的所有查询请求与响应，异步持久化到 Microsoft SQL Server 数据库的查询日志记录应用。读完本文，你将掌握该 App 的架构设计（有界队列 + 批量写入 + 定时清理）、全部配置项与完整dnsApp.config示例、自动建库建表与索引的底层实现、数据库字段的数值编码规则，以及基于IDnsQueryLogs接口的多维过滤查询能力，可以直接在真实 SQL Server 环境中落地部署并排查问题。

应用定位与适用场景

QueryLogsSqlServerApp是 Technitium DNS Server 的官方 DNS App 之一，仓库中对应的注册信息位于 Apps/apps2.json（名称 "Query Logs (SQL Server)"）。它的核心功能是：把 DNS 服务器处理的每一次查询请求和对应响应写入 SQL Server 数据库，并且这些日志可以直接通过 DNS Server 的 Web 控制台查询浏览——这正是IDnsQueryLogs接口的用途。

典型的适用场景包括：

• 需要一个集中式、可长期保留的 DNS 查询审计库，供安全分析、故障排查或合规留存；
• 已有 SQL Server 基础设施，希望把 DNS 日志纳入现有数据库运维体系（备份、监控、权限管理）；
• 需要按客户端 IP、协议、响应类型、RCODE、QNAME、QTYPE 等条件灵活检索历史查询记录。

值得说明的是，该 App 与 Technitium DNS Server 内置的查询日志功能相互独立：启用本 App 后，所有 DNS 查询会同时写入 SQL Server，可在 Web 控制台的日志查询界面中浏览这些记录，而不会影响服务器内置日志。

功能概览与扩展点

根据 Apps/QueryLogsSqlServerApp/README.md，该 App 提供三大核心能力：

• 异步日志写入（Async logging）：日志条目先写入一个有界的内存队列，再由后台线程批量落库，写入路径不阻塞 DNS 请求处理；
• 自动清理（Cleanup support）：按保留天数（maxLogDays）或最大记录数（maxLogRecords）定期清理过期数据；
• 持久化存储（Retained schema）：通过databaseName+ SQL Server 连接字符串组合，实现数据库与表结构的自动初始化。

在扩展点层面，它同时实现了三个接口（定义于 DnsServerCore.ApplicationCommon 目录）：

在 Apps/QueryLogsSqlServerApp/App.cs 中，类声明为public sealed class App : IDnsApplication, IDnsQueryLogger, IDnsQueryLogs，正是这三个接口的组合实现，因此它既是日志写入方，也是日志查询数据源。

快速部署：先决条件与安装

在 Web 控制台安装并启用该 App 之前，需要先满足以下前提条件（说明同样见于 Apps/apps2.json 的官方描述）：

1. 在 SQL Server 中创建专用数据库用户；
2. 为该用户启用dbcreator服务器角色（Server Role）。原因是 App 首次启用时会自动执行CREATE DATABASE、CREATE TABLE以及索引创建语句，普通db_owner权限不足以完成建库操作；
3. 确认网络可达：从运行 Technitium DNS Server 的主机能够访问 SQL Server 实例（示例连接串使用tcp:192.168.10.101,1433，即 1433 默认端口）；
4. 连接串建议带上TrustServerCertificate=true（自签名/测试证书场景）并按需配置Encrypt。

之后在 DNS Server 的 Apps 页面安装Query Logs (SQL Server)App，编辑其配置，把enableLogging设为true并保存。App 会自动完成建库、建表和索引初始化，随后立即开始记录查询日志——无需手工执行任何 SQL 脚本。

配置详解与完整示例

配置存放在 Apps/QueryLogsSqlServerApp/dnsApp.config，仓库自带如下示例：

{ "enableLogging": false, "maxQueueSize": 1000000, "maxLogDays": 0, "maxLogRecords": 0, "databaseName": "DnsQueryLogs", "connectionString": "Data Source=tcp:192.168.10.101,1433; User ID=username; Password=password; TrustServerCertificate=true;" }

各配置项的含义、类型与默认值如下表（源自 README.md 的 Configuration 一节）：

配置解析逻辑位于 App.cs 的InitializeAsync方法：代码通过jsonConfig.GetPropertyValue(...)依次读取各键，并对connectionString做了三项强校验：

• 为空时直接抛出异常，提示必须在connectionString参数中指定有效连接串；
• 禁止包含Initial Catalog——若检测到会抛错并要求改用databaseName参数，避免库名出现两处定义的不一致；
• 若连接串末尾没有分号，会自动补上;，为后续拼接Initial Catalog={databaseName};做准备。

连接串写法建议

连接串负责与 SQL Server 实例建连，但不选择具体数据库。实际运行时，App 会在其内部拼接Initial Catalog={databaseName};得到完整连接串（见 App.cs 与 App.cs）。例如示例配置最终等效于：

Data Source=tcp:192.168.10.101,1433; Initial Catalog=DnsQueryLogs; User ID=username; Password=password; TrustServerCertificate=true;

运行时行为：有界队列 + 批量写入 + 定时清理

README 将运行时行为概括为三步，结合源码可以完整还原其实现细节。

1. 有界 Channel 缓冲查询

每条 DNS 查询在服务器处理完成并返回响应后，通过InsertLogAsync被包装成LogEntry（结构体包含时间戳、请求、客户端端点、协议、响应，见 App.cs 与 App.cs），随后TryWrite进一个System.Threading.Channels的有界通道。

通道通过StartNewChannel创建（App.cs），关键配置为：

BoundedChannelOptions options = new BoundedChannelOptions(maxQueueSize); options.SingleWriter = true; options.SingleReader = true; options.FullMode = BoundedChannelFullMode.DropWrite;

FullMode = DropWrite意味着：当队列积压到maxQueueSize上限时，新日志条目会被直接丢弃而不是阻塞 DNS 处理线程，这保证了日志功能不会反向拖垮查询性能，代价是高并发瞬时峰值下可能丢日志（README 的 Risks 一节明确提示了这一点）。

2. 后台消费者线程批量落库

StartNewChannel同时启动一个名为QueryLogsSqlServer（类名）的后台线程作为消费者，它循环WaitToReadAsync，攒够一批后调用BulkInsertLogsAsync批量插入（App.cs）。

批量写入有几个值得注意的工程细节（App.cs）：

• 批次大小固定为 190 条：常量BULK_INSERT_COUNT = 190的注释说明，SQL Server 单条 SQL 最多支持 2100 个参数，每条日志占 11 个参数（server、timestamp、client_ip、protocol、response_type、response_rtt、rcode、qname、qtype、qclass、answer），190 × 11 = 2090，正好压线；
• 单条多值 INSERT 语句：用StringBuilder拼出INSERT INTO dns_logs (...) VALUES (...),(...)...，每条记录对应一组@server{i}之类的带下标参数；
• 参数类型显式声明：timestamp为SqlDbType.DateTime、protocol/response_type/rcode为TinyInt、response_rtt为Real、qtype为Int、qclass为SmallInt、文本列使用VarChar；
• answer 字段的归一化处理：无应答时若截断（Truncation）写[TRUNCATED]，否则写NULL；应答超过 2 条且发生区域传送（Zone Transfer）时写[ZONE TRANSFER]；正常情况则拼接为"TYPE RDATA, TYPE RDATA, ..."的文本，且截断到 4000 字符以内（与表定义的answer VARCHAR(4000)对齐）；
• 写库失败不重试但延迟：捕获异常后写服务器日志并Task.Delay(BULK_INSERT_ERROR_DELAY)（10 秒），随后循环继续尝试下一批。

3. 定时清理旧记录

构造函数中注册了一个Timer（App.cs），行为受两个配置控制：

• 初始间隔 5 秒（CLEAN_UP_TIMER_INITIAL_INTERVAL = 5000），之后周期 15 分钟（CLEAN_UP_TIMER_PERIODIC_INTERVAL = 15 * 60 * 1000）；
• 仅当maxLogRecords > 0或maxLogDays > 0时才会启动定时器（见 App.cs）；
• 按记录数清理：先SELECT Count(*) FROM dns_logs统计总量，超出部分用DELETE FROM dns_logs WHERE dlid IN (SELECT TOP n dlid FROM dns_logs ORDER BY dlid)分批删除，每批BULK_REMOVE_COUNT = 10000条；
• 按天数清理：用WHERE timestamp < @timestamp（@timestamp = DateTime.UtcNow.AddDays(-maxLogDays)）统计并分批删除，同样按dlid升序取最旧记录。

数据库 Schema 与字段数值编码

自动建库建表

ApplyConfig中的建库逻辑（App.cs）会在enableLogging为 true 时执行：

1. 先用不指定库的连接串连接 SQL Server 实例；
2. IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = '{databaseName}') CREATE DATABASE "{databaseName}"自动建库；
3. 切到该库后，IF NOT EXISTS(...) CREATE TABLE dns_logs (...)自动建表；
4. 随后通过一系列IF NOT EXISTS判断，幂等地创建 10 个索引：index_server、index_timestamp、index_client_ip、index_protocol、index_response_type、index_rcode、index_qname、index_qtype、index_qclass、index_timestamp_client_ip、index_timestamp_qname、index_client_qname、index_query、index_all（其中index_all覆盖 server、timestamp、client_ip、protocol、response_type、rcode、qname、qtype、qclass 全列）。

dns_logs表结构如下（与 App.cs 一致）：

Schema 脚本还内置了兼容性处理：例如若已存在index_qtype/index_query/index_all索引但qtype列类型不是INT，会先DROP INDEX再ALTER COLUMN qtype INT，以应对早期版本遗留的表结构。

Protocol 字段（传输协议）

README 给出了完整的数值映射表，写入时paramProtocol.Value = (byte)log.Protocol（App.cs）：

Response Type 字段（响应类型）

响应类型枚举DnsServerResponseType定义于 IDnsQueryLogger.cs，与 README 的数值表完全对应：

写入逻辑位于 App.cs：若log.Response.Tag为空则视为Recursive，否则按Tag强转枚举，最终以byte落库。

response_rtt 与 RCODE

response_rtt（往返时延）仅在响应类型为Recursive且响应元数据中存在RoundTripTime时写入，否则写入DBNull（App.cs）。rcode为(byte)log.Response.RCODE，即标准 DNS 响应码（NOERROR=0、NXDOMAIN=3、SERVFAIL=2 等）。

日志查询：接口能力与过滤维度

IDnsQueryLogs.QueryLogsAsync（定义见 IDnsQueryLogs.cs）由 DNS Server 的 Query Logs HTTP API 在 Web 控制台展示日志时调用。实现位于 App.cs，其能力包括：

• 分页：pageNumber（0 自动修正为 1，-1表示最后一页）+entriesPerPage，配合ORDER BY dlid与OFFSET ... FETCH NEXT ... ROWS ONLY实现；
• 排序：descendingOrder决定按dlid倒序还是正序，同时通过rowNumber计算每行在总数据集中的真实行号；
• 多维过滤：start/end（时间范围）、clientIpAddress、protocol、responseType、rcode、qname、qtype、qclass均可选，过滤条件动态拼进WHERE子句；
• 通配符支持：当qname含*时转为 SQLLIKE查询（*替换为%），否则做精确匹配（App.cs）；
• 固定隔离条件：查询始终附带server = '{_dnsServer.ServerDomain}'，确保多服务器写入同一数据库时，每个服务器只看到自己的日志；
• 返回结构：封装为DnsLogPage（含PageNumber、TotalPages、TotalEntries、Entries），条目为DnsLogEntry（含行号、时间戳、客户端 IP、协议、响应类型、RTT、RCODE、问题记录、应答文本），两者均定义于 IDnsQueryLogs.cs。

查询时qname会先ToLowerInvariant()归一化，与写入路径中query.Name.ToLowerInvariant()（App.cs）保持一致，避免大小写不一致导致过滤失效。

容错与初始化重试

InitializeAsync对数据库连接做了专门的容错设计（App.cs）：

• 首次启动（_isStartupInit = true）时，初始化被放到线程池异步执行，最多重试 20 次、每次间隔 30 秒（MAX_RETRIES = 20、RETRY_DELAY = 30000）；
• 仅当捕获 SQL 错误号258（SqlException.Number == 258，即登录超时/无法连接）时才进入重试循环，并在服务器日志中输出“请检查 App 配置与数据库服务器在线状态”的提示；其他 SQL 错误或普通异常则记录日志后直接结束初始化；
• **通过 API 更新配置（非首次）**时则同步执行ApplyConfig()，失败会立即暴露给调用方。

这意味着在服务器启动早期、SQL Server 尚未就绪的情况下，App 会保持重试而不是崩溃退出；但若数据库长期不可达，日志写入（BulkInsertLogsAsync）会持续报错并每 10 秒延迟重试，表现为日志缺失，需要运维介入。

风险与运维注意事项

README 的 Risks / operational notes 一节明确提出三条，结合源码可进一步确认：

1. 队列溢出会丢日志：DropWrite策略下，超过maxQueueSize的新日志被静默丢弃。高并发场景应根据查询速率合理调大maxQueueSize，并监控内存占用（队列上限即内存上限的近似值）；
2. 数据库连接问题会中断日志：写库失败不重试同一批数据，而是延迟 10 秒后继续；连接长期不可用意味着这段时间的查询日志全部缺失；
3. 高流量部署需关注写延迟：批量写入的吞吐取决于 SQL Server 的写入性能，建议为dns_logs所在的磁盘/文件组做性能规划，并关注BulkInsertLogsAsync的耗时与response_rtt数值的合理性。

此外，Dispose时（App.cs）会先关闭日志开关、释放清理定时器、TryComplete()队列并等待消费者线程退出，保证进程/App 卸载时队列中已积压的条目尽量被消费完成。

故障排查清单

README 的 Troubleshooting 一节给出方向，结合实现可形成可执行的排查路径：

1. 确认数据库可达与凭据有效：在 DNS 服务器所在主机用sqlcmd或 SSMS 测试示例连接串（去掉Initial Catalog后能否建连）；注意 SQL 错误 258 表示登录超时，需检查网络、端口 1433 与防火墙；
2. 核对连接串与databaseName：确认connectionString中没有Initial Catalog（否则 App 会直接拒绝加载配置）；确认数据库用户具备dbcreator服务器角色，否则首次建库会失败；
3. 查看服务器日志中的 SQL 客户端错误：BulkInsertLogsAsync、清理定时器与初始化路径都会通过_dnsServer.WriteLog(ex)输出异常，错误号 258、207（无效列名）、262（无权限）等都能直接定位问题；
4. 验证数据落库：在 SQL Server 中执行SELECT TOP 100 * FROM DnsQueryLogs.dbo.dns_logs ORDER BY dlid DESC;，确认协议/响应类型字段数值与上文编码表一致，server列等于当前服务器域名；
5. 若日志缺失但 App 状态正常：优先检查是否触发DropWrite（队列积压）或写库延迟重试（网络抖动），必要时临时调大maxQueueSize并观察服务器日志。

从源码结构可以获得的延伸视角

从仓库目录结构看，Query Logs 系列 App 遵循同一套接口与设计模式：除 SQL Server 版本外，仓库还包含 QueryLogsSqliteApp、QueryLogsMySqlApp、QueryLogsPostgreSqlApp 等对应不同数据库的实现，它们都实现了IDnsApplication/IDnsQueryLogger/IDnsQueryLogs，并采用“有界队列 + 批量插入 + 定时清理”的相似架构（例如 SQLite 版同样定义BULK_INSERT_COUNT、BULK_INSERT_ERROR_DELAY与清理定时器）。这种统一抽象意味着：无论后端是何种数据库，DNS Server 的日志写入与 Web 控制台查询体验保持一致，为在多数据库环境间迁移日志存储提供了便利。

相关仓库资源

• App 源码与实现：Apps/QueryLogsSqlServerApp/App.cs
• 配置文件示例：Apps/QueryLogsSqlServerApp/dnsApp.config
• App 说明文档：Apps/QueryLogsSqlServerApp/README.md
• 项目文件（依赖Microsoft.Data.SqlClient7.1.0，目标框架 net10.0）：Apps/QueryLogsSqlServerApp/QueryLogsSqlServerApp.csproj
• 应用注册信息（含官方安装说明）：Apps/apps2.json
• 接口定义：IDnsApplication.cs、IDnsQueryLogger.cs、IDnsQueryLogs.cs

• 网络
• 后端