连接 SQL Server 数据库后出现间歇性连接错误
我有一个无法解决的问题。我的开发团队有一套针对单元测试数据库运行的 C# 单元测试。在每次测试之前，数据库需要恢复到其基线起点。实现此目的最快的方法是：
- 分离数据库。
- 使用基线备份副本覆盖 .mdf 和 .ldf 文件。
- 附上数据库。
我通过从 C# 调用 Transact SQL 命令来执行这些操作。
我遇到的问题是，大约 50% 的情况下，打开与单元测试数据库的连接会失败 - 异常有不同类型 - 但它们似乎都表明数据库不存在。如果我在 Attach 语句之后输入“睡眠”命令，则每次都可以成功打开数据库。我对此的解释是，必须有某种针对数据库运行的 SQL Server 后台进程来最终使数据库联机。因此，在执行 sp_attach_db 后，数据库实际上要经过几毫秒后才真正可供使用。
当然，我的解决方案可能是在每次数据库附加后执行“睡眠”语句，但此类测试有 800 次，因此分离/恢复/附加过程尽可能快至关重要。
有人有这个问题的经验吗？有谁知道为什么数据库不能立即接受连接？有谁知道我如何检测数据库何时准备好接受连接。
提前致谢。
I have a problem that I am unable to resolve. My development team have a suite of C# unit tests that run against a unit test database. Before each test, the database needs to be restored to its base-lined start-point. The fastest way to achieve this is to:
- Detach the database.
- Overwrite the .mdf and .ldf files with base-lined backup copies.
- Attach the database.
I perform these actions by invoking Transact SQL commands from C#.
The problem I have is that approximately 50% of the time, opening a connection to the unit test database will fail - the exceptions are of different types - but they all seem to suggest that the database does not exist. If I put in a 'sleep' command after the Attach statement then the database can be opened successfully every time. My interpretation of this is that there must be some sort of SQL Server background process that runs against the database to finalise bringing the database on-line. So, directly after executing sp_attach_db, the database is not actually ready for use until after a few milliseconds.
Of course, my solution could be to execute a 'sleep' statement after every database attach but there are 800 of these tests so it is vital that the detach/restore/attach process is as fast as possible.
Does anyone have experience of this problem? Does anyone know why the database is not immediately available to accept a connection? Does anyone know how I can detect when the database is ready to accept a connection.
Thanks in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
尝试关闭连接上的池化。当池化为ON时，当您在分离数据库时强行关闭连接时，池中的连接就会被破坏，因此当连接来自连接池时，第一批将失败。
只需添加到您的连接字符串
Pooling=noPS：如果您的测试不是那么复杂，那么在事务内运行测试会更快，并在完成清除数据库时将其回滚
Try to switch off the Pooling on your connections. When Pooling is ON, your connection in the pool is broken when you forcibly close it while detaching the DB, and thus when the connection comes from connections pool - the very first batch will fail.
Simply add to your connection strings
Pooling=noPS: If your tests not so much complex, it can be much faster to run the test inside a transaction and simply roll it back when it finishes to clear the DB