数据库客户端:如何在 SQL Server 重启后等待数据库

问题描述

服务依赖不足以保证数据库客户端在系统重启后会发现他们的 SQL Server 启动并运行.他们可能能够创建到 master 数据库的连接，但特定的数据库可能仍在打开过程中(在恢复模式下)，并且在最初的一段时间内将拒绝连接.

Service dependency is not enough to guarantee that database clients will find their SQL Server up and running after a system reboot. They may be able to create a connection to the master database, but a specific database may still be in the process of opening (in the recovery mode) and connections to it will be refused for some initial period of time.

延迟的确切持续时间变化很大，取决于系统上的数据库数量、恢复模式、LDF 文件大小或重启前的流量等因素.

The exact duration of the delay is highly variable and depends on factors such as number of databases on the system, recovery mode, LDF file size, or traffic prior to the reboot.

处理对客户端的依赖的众所周知的方法是休眠和重试.然而，这种方法似乎并不完全干净或可靠，尤其是当客户在技术和所有权方面存在差异并且这种竞争条件很少出现时.

The well known approach to dealing with the dependency on the client is sleeping and retrying. However, this approach does not seem exactly clean or reliable, especially when the clients are diverse in terms of technology and ownership and when this race condition appears only very rarely.

是否有更好的方法来同步客户端与服务器启动，最好是通过将 SQL Server 服务保持在正在启动"状态直到所有数据库都被打开或标记为可疑?

Is there any better way to synchronize the clients with the server start-up, ideally through keeping the SQL Server service in the "Starting" state until all databases have been either opened or marked suspect?

推荐答案

创建一个 Windows 服务，该服务尝试访问数据库，除非成功建立可靠的连接，否则该服务不会报告自己已启动.基本上该服务会返回一个错误，服务管理器会尝试根据服务设置运行它.

Create a windows service that tries to access the database which does not report itself as having started unless successful in establishing a reliable connection. Basically that service would return an error and the service manager would try to run it according to the service settings.

现在，您可以依赖其他服务中的新服务来保证数据库响应.

And now you can depend on that new service in your other services that would guarantee that the database is responsive.

相关问题

• 在 C# 中，确定数据库是否已启动并正在运行的最佳方法是什么?

更新

此外，要了解数据库是否已启动并正在运行，更简洁的方法是检查以下值:

Also, for a much cleaner way to know if the database is up and running is to check the following value:

SELECT state_desc
FROM sys.databases
WHERE name = 'YourDatabase'

如果除了ONLINE"之外什么都不说，那么数据库还没有准备好.(如果它存在的话)http://msdn.microsoft.com/en-us/library/ms190442.aspx

If it says anything but 'ONLINE', then the database is not yet ready. (if it exists at all) http://msdn.microsoft.com/en-us/library/ms190442.aspx

这篇关于数据库客户端:如何在 SQL Server 重启后等待数据库激活?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！