优雅替换只读 SQL Server 数据库的最佳实践
我有一个只读数据库，其中缓存了用于在网站上显示页面的信息。 有些进程运行来生成数据库，并且这些进程运行在不同的服务器上。 当我需要更新实时数据库时，我会将该数据库恢复到实时服务器，并使用新的名称和文件。 然后我删除实时数据库并将其重命名为原始名称。 目前，我使用一个存储过程来执行此操作，该过程通过“sp_who”和“kill”杀死所有连接，然后删除数据库命令，然后“sp_renamedb”。 现在，当我终止所有连接时，它会在当前访问这些页面的人和爬虫的网站上抛出错误，是否有更好的方法来执行此过程？
I have a read-only database that has cached information which is used to display pages on the site. There are processes that run to generate the database, and those run on a different server. When I need to update the live database, I restore this database to the live server, to a new name and file. Then I drop the live database and rename this one to the original name. Currently I do this with a stored procedure, that kills all connections via 'sp_who' and 'kill', then the drop database command, then 'sp_renamedb'. Now when I kill all connections it will throw an error on the site for people and crawlers currently accessing those pages, is there a better methodology for performing this procedure?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您可能想要做的是使用以下命令使实时数据库脱机：
您可以在此处阅读更多信息< /a>，但它说：
一旦数据库脱机，您应该能够安全地执行恢复/重命名操作，并在完成后将其恢复联机。 您可能需要进行一些尝试才能了解数据库处于脱机状态时允许执行哪些操作。
如果您在离线状态下进行恢复/重命名时遇到困难，您需要在单用户模式下将其恢复在线。
在这种情况下，连接池或其他长时间运行的连接可能会导致问题。 您可能需要设置一个脚本，以便在发出 ALTER DATABASE SET OFFLINE 命令后等待一段时间（例如 15 分钟），如果数据库尚未脱机，则可以使用WITH NO_WAIT 重新发出该命令强制其离线的选项。
如果这对您来说还不够强大，您还可以正常关闭 SQL Server...这也会等到服务器中的所有工作完成后再停止。
Probably what you want to do is take the live database offline with the command:
You can read more here, but it says:
Once the database is offline, you should be able to do your restore/rename operations safely and bring it back online when done. You might have to do some playing around to see what is permitted while the database is in the offline state.
If you have trouble doing the restore/rename while it's offline, you'll want to bring it back online in single user mode.
Connection pooling or other long-running connections may cause problems in this scenario. You may want to set up a script to wait for a period of time after the ALTER DATABASE SET OFFLINE command has been issued (say 15 minutes), and if the database is still not offline yet, you can reissue the command with the WITH NO_WAIT option to force it offline.
If that isn't high-powered enough for you, you can also gracefully shutdown SQL Server...that will also wait until all work in the server is done before stopping.
设置事务复制，将数据生成服务器作为发布者，将实时服务器作为订阅者。 客户端连接不会注意到任何事情。
Set up transactional replication with your data generation server as the publisher and your live server as a subscriber. The client connections won't notice a thing.
Mike Sharek +1 - 我喜欢你的解决方案，它非常整洁，可以防止用户突然断开连接（而且非常粗鲁）。
如果您不想等待用户注销几个小时，则还有另一种选择。 您可以通过您在站点中构建的 AJAX 机制向连接的客户端发送警报，该机制基本上处于“打开”状态，并在超时并重新连接之前等待来自服务器的通知一段时间，因此本质上 XmlHttpObject 始终在等待响应。 收到响应后，它会将其显示在屏幕上，然后打开另一个连接。 然后，您将拥有一个管理员界面，可以将消息推送到等待的 AJAX 组件。
这将允许您以类似于 Microsoft Outlook Mobile Access 工作方式的方式近乎实时地发送消息，从而允许用户在 10 分钟内收到网站将关闭进行维护的通知。
我不确定是否有任何预构建的组件可以做到这一点，但我确信实现起来不会太困难。
它并不能立即为您提供帮助，但值得您在网站的未来版本中考虑。
+1 for Mike Sharek - I like your solution, it's very tidy and prevents users being disconnected abruptly (and very rudely).
Another alternative, if you don't want to sit around waiting for users to log off for hours. You could send an alert to connected clients through an AJAX mechanism you build into the site that basically sits "open" and waits for a notification from the server for a period before it times out and reconnects so essentially the XmlHttpObject is always waiting for a response. At the point of receiving the response, it displays it onscreen and then opens another connection. You then have an administrator interface that pushes out messages to the waiting AJAX component.
This would allow you to send a message in approximately realtime in a similar manner to the way Microsoft's Outlook Mobile Access works allowing users to be notified that in 10 minutes, the site is going down for maintenance.
I'm not sure if there's any prebuilt components out there that do this, but I'm sure it couldn't be too difficult to implement.
It doesn't help you out in the immediate sense, but it's worth thinking about for future releases of your site.
如果您想为用户做一些真正无缝的事情。 我会做这样的事情。
当您复制数据库的新版本时，请为其指定一个新名称，可以用日期时间命名，具体取决于您更新它的频率。 一旦出现，告诉您的 Web 应用程序开始使用新数据库。 这可以通过更改 web.config 中的连接字符串或将其设置为配置数据库来完成。
一旦经过一定时间，根据站点的使用特征删除旧数据库。
If you want to do something really seamless for your users. I would do something like this.
When you copy the new version of your database over give it a new name, maybe named with the datetime, depending on how often you update it. Once it is there tell your web application to start using the new database. This could be done by changing the connection string in the web.config, or having it a configuration database.
Once a certain amount of time has passed, based on the usage characteristics of your site remove the old database.