推荐的方法如何修改生产 SQL 数据库的架构？
假设有一个数据库有100多个表，并且添加了一个主要功能，这需要修改20个现有表并添加30个以上。这些更改是由多个开发人员在很长一段时间内（6 个月）对开发数据库完成的。我们假设这些更改不会使任何现有的生产数据无效（例如，添加的列上允许有默认值/空值，不存在无法满足的新关系或约束）。
将架构中的这些更改发布到生产数据库的最简单方法是什么？最好不要长时间关闭数据库。
Say there is a database with 100+ tables and a major feature is added, which requires 20 of existing tables to be modified and 30 more added. The changes were done over a long time (6 months) by multiple developers on the development database. Let's assume the changes do not make any existing production data invalid (e.g. there are default values/nulls allowed on added columns, there are no new relations or constraints that could not be fulfilled).
What is the easiest way to publish these changes in schema to the production database? Preferably, without shutting the database down for an extended amount of time.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
编写执行所需更改的 T-SQL 脚本。在生产数据库的副本上进行测试（从最近的备份恢复以获取副本）。修复测试中不可避免的错误。重复直到脚本完美运行。
然后，当需要进行实际迁移时：锁定数据库，以便只有管理员可以登录。进行备份。运行脚本。验证结果。让数据库重新上线。
最长的部分将是备份，但如果不这样做你就会发疯。您应该知道备份需要多长时间，整个过程不会比这更长，所以这就是您的停机时间需要多长时间。对于大多数企业来说，半夜都适合。
Write a T-SQL script that performs the needed changes. Test it on a copy of your production database (restore from a recent backup to get the copy). Fix the inevitable mistakes that the test will discover. Repeat until script works perfectly.
Then, when it's time for the actual migration: lock the DB so only admins can log in. Take a backup. Run the script. Verify results. Put DB back online.
The longest part will be the backup, but you'd be crazy not to do it. You should know how long backups take, the overall process won't take much longer than that, so that's how long your downtime will need to be. The middle of the night works well for most businesses.
对于如何在不停机的情况下进行“改变”，没有通用的答案。答案实际上取决于具体情况，具体取决于具体的变化。有些更改对停机时间没有影响（例如添加新表），有些更改影响很小（例如向现有表添加列而不更改数据大小，例如不会增加空位图大小的新可为空列）其他更改会严重破坏停机时间（任何会更改数据大小的操作都将强制重建索引并在持续时间内锁定表）。如果没有*大量*停机时间，某些更改是不可能应用的。我知道并行应用更改的情况：创建数据库的副本，设置复制以使其保持最新，然后更改副本并保持同步，最后将操作移至修改后的副本，该副本将成为主数据库。 Michelle Ufford 给出的 PASS 2009 上有一个演示，其中提到了 godaddy 是如何消失的经过这样一个持续几周的改变。
但是，在较小的规模上，您必须通过经过良好测试的脚本应用更改，并衡量对测试评估的影响。
但真正的问题是：这将是您对架构所做的最后更改吗？最后，您是否发现了应用程序的完美架构并且生产数据库永远不会改变？恭喜你，一旦你成功了，你就可以去休息了。但实际上，6 个月后你将面临同样的问题。真正的问题是您的开发过程，与开发人员一起从 SSMS 或从 VS Server Explored 直接进入数据库进行更改。您的开发过程必须有意识地努力采用基于版本控制和 T-SQL 脚本的模式更改策略，如 版本控制和您的数据库。
There is no generic answer on how to make 'changes' without downtime. The answer really depends from case to case, based on exactly what are the changes. Some changes have no impact on down time (eg. adding new tables), some changes have minimal impact (eg. adding columns to existing tables with no data size change, like a new nullable column that doe snot increase the null bitmap size) and other changes will wreck havoc on down time (any operation that will change data size will force and index rebuild and lock the table for the duration). Some changes are impossible to apply without *significant * downtime. I know of cases when the changes were applies in parallel: a copy of the database is created, replication is set up to keep it current, then the copy is changed and kept in sync, finally operations are moved to the modified copy that becomes the master database. There is a presentation at PASS 2009 given by Michelle Ufford that mentions how godaddy gone through such a change that lasted weeks.
But, at a lesser scale, you must apply the changes through a well tested script, and measure the impact on the test evaluation.
But the real question is: is this going to be the last changes you ever make to the schema? Finally, you have discovered the perfect schema for the application and the production database will never change? Congratulation, once you pull this off, you can go to rest. But realistically, you will face the very same problem in 6 months. the real problem is your development process, with developers and making changes from SSMS or from VS Server Explored straight into the database. Your development process must make a conscious effort to adopt a schema change strategy based on versioning and T-SQL scripts, like the one described in Version Control and your Database.
使用工具创建 diff 脚本并在维护时段运行它。我使用 RedGate SQL Compare 来实现此目的，并且对此非常满意。
Use a tool to create a diff script and run it during a maintenance window. I use RedGate SQL Compare for this and have been very happy with it.
我已经成功使用 dbdeploy 几年了。它允许您的开发人员创建小的 SQL 更改增量，然后可以将其应用于您的数据库。这些更改由数据库中的更改日志表进行跟踪，以便知道要应用什么。
http://dbdeploy.com/
I've been using dbdeploy successfully for a couple of years now. It allows your devs to create small sql change deltas which can then be applied against your database. The changes are tracked by a changelog table within database so that it knows what to apply.
http://dbdeploy.com/