SQL Server 2008架构更改的最佳实践 [英] Best practice for SQL Server 2008 schema change

问题描述

我正在寻找有关以下方面的信息：

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.

生产数据库是两个不同的ASP.NET网站的后端。

The production database is the back-end for two distinct ASP.NET websites.

我们的模式更改过程非常健壮，每个迁移实际上都是包含模式更改的.cs文件。然后，我们将使用ADO.NET对数据库应用模式更改。

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.

推荐答案

如果更新更改了列名之类的内容，存储的proc参数等，然后始终使应用程序脱机，然后再进行模式更新。

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.

如果此更新需要对您的Web应用程序文件进行相应的更新，则在执行更新之前，请使网站离线。您不知道谁可能正在查看页面，并且只单击提交即可得到错误...

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.

此外，我们使用Redgate的SQL Compare之类的工具来编写数据库更新脚本。这是在一个临时服务器上实践的，该临时服务器在实际推送之前已从生产数据刷新，以确保没有意外，并且可以非常快地完成。

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.

关于单用户模式，通常使用它来限制对单个Management Studio实例的数据库访问。这不是我们通常在部署过程中要做的事情。

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.

这篇关于SQL Server 2008架构更改的最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！