未来MVC3站点的SQL Server数据库如何演进并避免数据丢失
我正准备使用 C#、实体框架和 SQL Server 开发 MVC 3 网站。
这个网站是为关键工作而建立的，数据丢失是绝对不允许的！据我所知，我没有发展数据库的经验，但我知道这个项目应该能够在使用增量开发方法的同时发展。我可以知道是否有任何可遵循的指南以及如何在不出现任何错误的情况下改进它？在数据库初始设计或任何方面。只是，0 数据丢失是最高优先级的要求。
我需要这 2 个问题的答案，希望一些经验可以在这个问题上指导我
- 如何更新数据库包括表、列而不影响同一个表中的其他数据
- 如何更新远程数据库（例如 C# 窗口应用程序和数据库不在我身边） ）
对于问题 1，数据库位于我的网络服务器上，但问题 2 数据库位于用户端。
I'm getting ready to develop a MVC 3 website with C#, Entity Framework and SQL Server.
This website is built for critical jobs and data lost is something absolutely not allowed ! In my knowledge I had no experience of evolving database, but I know this project should be able to evolve while using incremental development methodology. May I know is there any guideline to follow and how do I evolve it without any single error? In term of database initial design or anything. Just, 0 Data Lost is highest priority requirement.
I need answer for this 2 question and hope some experience could guide me in this issue
- How to update database include table, column without affect other data in the same table
- How to update remote database (for example C# window apps and database is not with me)
For the 1. question the database is located at my web server but question 2 the database is staying with user end.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
答案是：您有责任按照满足您的要求的方式设计升级流程。没有自动魔法可以为你做到这一点。
该过程通常涉及创建升级 SQL 脚本，该脚本将修改数据库结构，如果需要，它还可以将数据移动到临时表，同时更改主表的结构，以便数据不会丢失。您还可以在某些特殊表中维护数据库版本，并在运行更新之前检查它，以便确保更新脚本在预期的旧版本上运行。
RedGate SQL Compare 和 Visual Studio 数据库工具（仅限高级版和旗舰版）等工具能够采用旧数据库、新数据库并为您创建差异脚本，以便将旧数据库模式升级到新数据库模式。这适用于大多数情况，但您必须始终在测试环境中非常仔细地测试结果。如果可能的话，最好对生产数据库的备份进行测试。
如果出现问题，如何避免数据丢失？在进行任何更改之前，只有一种非常简单的方法备份数据库，并在出现问题时恢复旧数据库。备份甚至可以使用 SQL 编写脚本。如果没有成功的备份，切勿触摸您的生产数据库。
如何升级客户端数据库？您将使用相同的过程，但将其全部包装在某个安装包 (.msi) 中，例如使用 WiX 创建的安装包。
The answer is: it is your duty to design upgrade process in the way your requirements are met. There is no auto magic which will do this for you.
The process usually involves creation of upgrade SQL script which will modify database structure and if needed it can also move data to temporary tables while structure of main tables are changed so that data are not lost. You can also maintain database version in some special table and check it before you run the update so that you ensure that update script is run on expected old version.
There are tools like RedGate SQL Compare and Visual Studio Database tools (only Premium and Ultimate version) which are able to take old database, a new database and create difference script for you so that old database schema can be upgraded to a newer one. This works for most scenarios but you must always very carefully test result in your testing environment. It is best to test in on backup of your production database if possible.
How to avoid data loss if anything goes wrong? There is only one very simple way BACKUP THE DATABASE before you do any changes and restore the old database if anything goes wrong. Backup can be even scripted with SQL. Without successful backup never touch your production database.
How to upgrade client side database? You will use the same process but you will wrap it all in some installation package (.msi) for example created with WiX.