如何避免数据丢失与EF Model First数据库模式升级？ [英] How to avoid data loss with EF Model First database schema upgrade?

问题描述

这是一个很长的问题，但如果能够就此提出一些很好的建议，我将非常感激。简而言之，我正在寻找一个很好的MS SQL数据库模式的版本升级方法，也需要将数据从已删除的表移动到新表中。

我认为Stack溢出是这个问题最适合的地方（而不是dba.stackexchange.com），因为它的核心是这个.NET开发人员使用Entity Framework的问题，其数据库部分主要由自动生成的sql脚本组成。 / p>

---

背景

.NET应用程序和SQL数据库正在运行在Azure（Azure中的工作者角色和数据库中的应用程序）。到目前为止，版本升级工作正常，因为所有数据库模式更改都很简单（如添加新列）。然而，从现在开始，我也需要处理在升级期间将数据从一个表移动到另一个表。 （我可以通过创建一个新的数据库来临时解决这个问题，使用旧数据库中的数据生成一个脚本，并手动编辑脚本以使其符合新的架构，但我希望有更好的方法）。

我使用实体框架，我使用Model First。实体和关联在Visual Studio数据模型设计器中定义，这种方法非常适合我的应用程序。

我使用dacpac来升级Azure SQL数据库，而这个方法已经很好，直到现在（但现在我会得到数据丢失，所以现在我必须找到一种方法来移动数据到新的表）。

我希望我可以继续使用实体框架并在设计器中定义实体/关联，但是如果需要，可以从dacpac升级切换到另一种技术。 p>

直到现在升级方法

1. 我添加新的实体（表），关联）和设计器中的属性（列）。


2. 我右键单击，选择从模型生成数据库，这将导致一个.sql脚本删除旧的数据库对象并创建新数据库对象


3. 我创建一个空数据库并运行脚本来创建表/键等。


4. 在SQL Server Management Studio中，右键单击数据库并选择任务 - >提取数据层应用程序...。当向导完成时，我得到了我需要的dacpac（实际上我现在可以删除数据库，因为我只创建它可以获取dacpac文件，因为我不认为我可以在Visual Studio数据模型设计器中生成它） 。


5. 我右键单击Azure SQL数据库，然后选择任务 - >升级数据层应用程序...并按照向导进行操作。
   到现在为止，我从来没有丢失数据，所以这一切都很好！

当前情况

这是一个简单的例子来说明这个问题，但是从现在开始我将会经历几乎相同的情况。看下图中的旧版本和新版本的架构。假设数据库中已经有数据。我需要ImageFile中的数据结束于ImageFileOriginal或ImageFileProcessed，具体取决于IsOriginal布尔/位值。使用升级数据层应用程序我将收到数据丢失的警报。你会推荐什么方法来处理这个问题？如前所述，如果需要，可以从dacpac升级切换到另一种技术。

我已经阅读过Visual Studio数据库项目，Fluent Migrator，Red Gate和Entity Designer数据库生成Power Pack（不支持Visual Studio 2012），但是我没有找到一个很好的方法为了这。我承认我没有花一整天的时间挖掘每种技术，但我确实花了一些时间尝试找到一个好的方法。

解决方案

迁移数据库模式（创建/删除表/列）以及数据的最佳方式是使用可用于Visual Studio 2010和Visual Studio 2012的SSDT - Sql Server数据工具。

以下是一些非常有用的链接：

http://msdn.microsoft.com/data/tools
http://blogs.msdn.com/b/ssdt
http://msdn.microsoft.com/en-us/data/hh297027

This is a long question, but I would be very very thankful if I can get some good advice on this. In short, I’m looking for a good approach for version upgrade of MS SQL database schema that also demands data being moved from deleted tables into new tables.

I think Stack Overflow is the most appropriate place for this question (not dba.stackexchange.com) because at its core, this is an issue for .NET developers using Entity Framework, and the database parts of this consists mostly of auto-generated sql scripts.

---

Background

A .NET application and SQL database is running in Azure (The application in worker roles and the database in Azure SQL). Until now, version upgrades have worked fine, because all database schema changes have been simple (like adding a new column). However, from now on I also need to deal with moving data from one table to another during upgrades. (I’m able to fix this temporarily by creating a new database, generate a script with data from the old database and manually edit the script to make it fit the new schema, but I hope there is a better approach).

I use Entity Framework and I use Model First. Entities and associations are defined in Visual Studio Data Model Designer, and this approach is very appropriate for my application.

I use a dacpac to upgrade the Azure SQL database, and this approach has worked well until now (but now I will get data loss, so now I must find a way to move data to new tables).

I hope I can continue to use entity framework and defining entities/associations in the designer, but it’s fine to switch away from dacpac upgrade to another technology if needed.

Upgrade approach until now

1. I add new entities (tables), associations (relations) and properties (columns) in the designer.
2. I right-click, pick "Generate Database from Model…" and this results in a .sql script that drops old database objects and creates the new database objects.
3. I create an empty database and run the script to create the tables/keys etc.
4. In SQL Server Management Studio, I right-click the database and pick "Tasks -> Extract Data-tier Application…". When the wizard completes I get the dacpac I need (Actually I can now delete the database, since I only created it to be able to get the dacpac file, since I don’t think I can generate it in Visual Studio Data Model Designer).
5. I right-click the Azure SQL database and pick "Tasks -> Upgrade Data-tier Application…" and follow the wizard. Until now I have never had data loss, so this has worked fine!

Current situation

This is a simplified example to illustrate the issue, but I will get into almost identical situations quite often from now on it seems. Look at the old and the new version of the schema in the figure below. Assume there is already data in the database. I need the data in ImageFile to end up in ImageFileOriginal or ImageFileProcessed depending on the IsOriginal boolean/bit value. Using "Upgrade Data-tier Application" I will get alerted of data loss. What approach would you recommend to deal with this? As I said earlier, it’s fine to switch away from dacpac upgrade to another technology if needed.

I have read about Visual Studio Database Projects, Fluent Migrator, Red Gate and Entity Designer Database Generation Power Pack (It doesn't support Visual Studio 2012), but I didn’t find a good way for this. I admit I haven’t spent a whole day digging into each technology, but I certainly spent some time to try finding a good approach.

解决方案

The best way to migrate database schema (create / delete tables / columns) and also data, is using the SSDT - Sql Server Data Tools, available for Visual Studio 2010 and Visual Studio 2012.

Here are some very useful links:

http://msdn.microsoft.com/data/tools http://blogs.msdn.com/b/ssdt http://msdn.microsoft.com/en-us/data/hh297027

这篇关于如何避免数据丢失与EF Model First数据库模式升级？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！