SQL Server 2008 架构更改的最佳实践
我正在寻找有关以下内容的信息：
将开发数据库的架构更新到生产数据库的最佳实践是什么，或者更简洁地进行一般数据库架构更改。
生产数据库是两个不同 ASP.NET 网站的后端。
我们的架构更改过程相当稳健，每个“迁移”实际上都是包含架构更改的 .cs 文件。然后，我们将使用 ADO.NET 对数据库应用架构更改。
我的问题更多是关于数据库的连接性。
我应该停止访问数据库的两个网站吗？我想我应该这么做。 我应该将数据库置于单用户模式吗？看起来我应该这样做，但我对此并不完全有信心。
我可能会错过什么？在涉及数据库模式更改之前，有哪些事情让您感到困扰。
I am looking for information concerning the following:
What are the best practices for updating the schema of my dev DB to my production DB, or even more succinctly making DB schema changes in general.
The production database is the back-end for two distinct ASP.NET websites.
Our schema change process is fairly robust with each "migration" actually being a .cs file containing the schema changes. We will then use ADO.NET to apply the schema changes against the db.
My question is more about the connectivity of the database.
Should I stop the two websites that are accessing the db. I assume I should.
Should I put the DB into single user mode. It looks like I should but I am not entirely confident on that.
What could I be missing? What are things that have bitten you in the hand before concerning DB schema changes.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果更新更改了列名称、存储的过程参数等内容，则在进行架构更新之前始终使应用程序离线。
如果更新仅针对不影响数据正常处理的事情，那么您可能能够“热”地进行更新。此类别适用于您添加索引、表等内容的情况。
如果有人在处理架构更新时使用该应用程序，您很可能会发现自己处于数据一致性受损的情况。
如果此更新需要对您的 Web 应用程序文件进行相应的更新，请在执行更新之前使站点脱机。您不知道谁可能正在查看某个页面，并且单击“提交”后却出现错误...
通常此类维护是在非高峰时段完成的。您需要提前通知用户网站何时关闭以及关闭多长时间。
此外，我们还使用 Redgate 的 SQL Compare 等工具来编写数据库更新脚本。这是在实际推送之前从生产数据刷新的临时服务器上进行的，以确保不会出现意外并且可以非常快速地完成。
关于单用户模式，您通常使用此模式将对数据库的访问限制为单个 Management Studio 实例。这不是我们通常在部署过程中所做的事情。
If the updates change things like column names, stored proc parameters, etc then always take the apps offline prior to doing a schema update.
If the updates are only for things that do not impact the normal processing of the data then you might be able to do so "hot". This category is when you are adding things like indexes, tables, etc.
If someone is using the app while a schema update is processing you might very well find yourself in a situation where data consistency is impaired.
If this update requires a corresponding update to your web application files then take the site(s) offline prior to performing the update. You don't know who might be viewing a page and about to click submit only to get an error...
Typically maintenance of this sort is done during your off peak hours. You will want to notify the users ahead of time as to when the site will be down and for how long.
Also, we use tools like Redgate's SQL Compare to script our db updates. This is practiced on a staging server that had been refreshed from production data prior to the actual push in order to ensure that there are no surprises and it can be done very quickly.
Regarding Single User Mode, you typically use this to limit access to a database to a single management studio instance. It's not something we normally do as part of our deployments.
我没有考虑使用 .cs/ado.net 路由来进行架构更改。我们所做的是创建“增量”.SQL 文件 - SQL 来进行更改。这些是针对已知修订的架构执行的以进行更改，并且运行 .SQL 是部署的一部分。
我们还尽我们所能，通过检查架构中特定事物（例如列、索引等的存在）来确保这些操作可以安全地多次运行。这样它们就可以“意外地”执行多次。
当我们部署时，我们会在一天/一周的特定时间，警告用户/客户，关闭网站等。在发布前的最后几天，几乎每天都会在开发和临时服务器上进行“练习”部署，最终部署信心十足是高的。
在 SQL 中保留架构更改，它们看起来更像是它们修改的“主”架构 SQL，因此更容易跟踪。调试也更少！
它们也与其余代码一起在 TFS 中进行管理。
I have not considered the .cs/ado.net route to make schema changes. What we do is create 'delta' .SQL files - SQL to make the changes. These are executed against a schema of known-revision to make the changes, and running the .SQL is part of a deployment.
As best we can, we also try to make these safe to run multiple times by checking schema for specific things like the existence of a column, index, etc. That way they could be executed 'accidentally' multiple times.
When we deploy we have specific times of the day/week, warn users/customers, shut-down websites, etc. With 'practice' deployments going on nearly every day on dev and staging servers the final days before a release, final deployment confidence is high.
Keeping schema changes in SQL they look more like the 'master' schema SQL they modifify and are therefore easier to keep track of. Less to debug too!
They are also managed in TFS along with the rest of the code.
理想的情况，但并不总是可以实现：您进行与 V兼容的架构更改。您网站的版本。然后您释放 V到您的网络服务器。一旦所有 Web 服务器都升级到 V，您就可以执行任何清理（填充缺失值等），然后进行适当的可空性更改。
Ideal situation, but not always achievable: You make schema changes that are compatible with V<old> version of your website. You then release V<new> to your web servers. Once all of your web servers are up to V<new>, you then may perform any cleanups (populating missing values, etc), and then make suitable nullability changes.