使用Write-SqlTableData导入数据中途抛出ConnectionToServerFailed错误问题排查

根据你描述的场景——开发环境稳定、生产环境随机在数据导入中途抛出Failed to connect to server错误，且错误触发时机和插入行数都不固定，结合无多线程、无网络交互、版本一致的前提，大概率和连接池管理、数据库资源瓶颈或模块底层连接处理有关，下面是具体的排查和解决方向：

1. 显式复用数据库连接，避免连接池耗尽 #

默认情况下，Write-SqlTableData每次调用都会创建新的数据库连接（依赖.NET连接池自动管理），但生产环境可能存在其他进程占用连接、连接池回收不及时的情况，导致中途无法获取新连接。

解决方法是手动创建一个持久化的连接，在三次数据写入中复用它，减少连接创建/销毁的开销：

# 构建连接字符串（根据你的认证方式调整，这里用Windows集成认证）
$connectionString = "Server=.;Database=MyDB;Integrated Security=True;Max Pool Size=100;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$sqlConnection.Open()

try {
    # 第一组数据写入
    $userDataRows | Write-SqlTableData -Connection $sqlConnection -SchemaName 'Staging' -TableName 'User'
    # 第二组数据写入（替换成你的实际数据和表名）
    $secondDataRows | Write-SqlTableData -Connection $sqlConnection -SchemaName 'Staging' -TableName 'SecondTable'
    # 第三组数据写入
    $thirdDataRows | Write-SqlTableData -Connection $sqlConnection -SchemaName 'Staging' -TableName 'ThirdTable'
}
finally {
    # 确保连接被正确关闭和释放
    $sqlConnection.Close()
    $sqlConnection.Dispose()
}

注意：如果生产环境连接数紧张，可以在连接字符串中调整Max Pool Size参数（默认是100），但不要设置过大，避免给数据库造成压力。

2. 检查SQL Server的连接限制和资源状态 #

生产环境可能有更多业务负载，导致数据库连接数耗尽或资源不足：

• 查看SQL Server的最大连接数限制：执行SELECT @@MAX_CONNECTIONS，确认是否接近当前连接数（用SELECT COUNT(*) FROM sys.dm_exec_connections查看实时连接数）。
• 检查SQL Server错误日志（SSMS中：服务器 → 管理 → SQL Server日志），查找连接失败的具体细节（比如是否有“Login failed”“Insufficient resources”等提示）。
• 监控生产环境的CPU、内存使用情况，确认数据导入时是否有资源峰值导致数据库无法响应连接请求。

3. 强制使用TCP/IP协议连接本地实例 #

你用'.'作为服务器实例，默认会优先使用共享内存或命名管道协议，生产环境可能存在这些协议的稳定性问题。尝试改用TCP/IP连接，强制走网络协议栈：

# 替换原来的-ServerInstance '.' 为 'localhost,1433'（1433是默认SQL端口，根据你的实际端口调整）
$userDataRows | Write-SqlTableData -ServerInstance 'localhost,1433' -Database 'MyDB' -SchemaName 'Staging' -TableName 'User'

4. 分批写入数据，降低单次负载 #

虽然开发环境能一次性处理18000行，但生产环境可能对大批次写入的兼容性较差。尝试拆分数据为小批次写入，中间加短暂延迟：

$batchSize = 1000
$batches = $userDataRows | Group-Object -Property { [math]::Floor($_.Index / $batchSize) }
foreach ($batch in $batches) {
    $batch.Group | Write-SqlTableData -ServerInstance '.' -Database 'MyDB' -SchemaName 'Staging' -TableName 'User'
    Start-Sleep -Milliseconds 500 # 短暂延迟，给数据库缓冲时间
}

5. 升级或重新安装SqlServer模块 #

即使版本号相同，生产环境的模块可能存在损坏或潜在的bug。尝试卸载并重新安装最新版本的模块：

Uninstall-Module -Name SqlServer -Force
Install-Module -Name SqlServer -Scope CurrentUser -Force

内容的提问来源于stack exchange，提问作者Leon Bouquiet