随着时间的推移，如何将数据库部分迁移到新系统？
我们正在进行一个多年项目，我们正在构建一个新系统和一个新数据库，以最终取代旧系统和数据库。 当我们改变新系统和旧系统时，用户正在使用它们。
我们不断遇到的问题是，当一个系统中的对象依赖于另一个系统中的对象时。 我们一直在使用视图，但遇到了其中一种技术（实体框架）的限制，正在考虑其他选项。
我们现在正在考虑的另一个选项是复制。 我的老板对由此带来的额外维护并不感兴趣。 那么，还有哪些其他选项可以将相关数据放入需要它的数据库中呢？
更新：
我们使用的技术是 SQL Server 2008 和实体框架。 两个数据库都位于同一个 SQL Server 实例中，因此不需要链接服务器。
我们面临的实体框架的限制是我们似乎无法创建基于表的实体和基于视图的实体之间的关系。 据我所知，数据库中视图和表之间不存在任何关系，因此 edmx 图无法推断它。 我似乎无法在不出现错误的情况下手动创建关系。 它认为视图中的所有列都是键。
如果我保持这种方式，视图中的每一列都会出现如下错误：
关联结束关键属性 [...] 是 未映射。
如果我尝试将非键列上的“Entity Key”属性更改为 false，则会收到此错误：
所有关键属性 EntitySet [...] 必须映射到所有 表的关键属性 [...] 视图名称。
更新 #2
我还应该提到实体框架的主要限制是它一次仅支持一个数据库。 因此，我们需要旧数据出现在新数据库中，以便实体框架能够看到它。 我们只需要在新系统中读取旧系统数据即可。
We are in the process of a multi-year project where we're building a new system and a new database to eventually replace the old system and database. The users are using the new and old systems as we're changing them.
The problem we keep running into is when an object in one system is dependent on an object in the other system. We've been using views, but have run into a limitation with one of the technologies (Entity Framework) and are considering other options.
The other option we're looking at right now is replication. My boss isn't excited about the extra maintenance that would cause. So, what other options are there for getting dependent data into the database that needs it?
Update:
The technologies we're using are SQL Server 2008 and Entity Framework. Both databases are within the same sql server instance so linked servers shouldn't be necessary.
The limitation we're facing with Entity Framework is we can't seem to create the relationships between the table-based-entities and the view-based-entities. No relationship can exist in the database between a view and a table, as far as I know, so the edmx diagram can't infer it. And I cannot seem to create the relationship manually without getting errors. It thinks all columns in the view are keys.
If I leave it that way I get an error like this for each column in the view:
Association End key property [...] is
not mapped.
If I try to change the "Entity Key" property to false on the columns that are not the key I get this error:
All the key properties of the
EntitySet [...] must be mapped to all
the key properties [...] of table
viewName.
According to this forum post it sounds like a limitation of the Entity Framework.
Update #2
I should also mention the main limitation of the Entity Framework is that it only supports one database at a time. So we need the old data to appear to be in the new database for the Entity Framework to see it. We only need read access of the old system data in the new system.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您可以使用链接服务器查询将数据保留在原来的位置，但从其他数据库连接到它。
取决于每个数据库中数据的最新程度 如果一个数据源可以保持只读，您可以：
您可以作为 SQL 代理任务定期运行
将数据获取到另一个数据库
可以接近实时。
如果数据需要在两个数据库中读写，那么您可以使用：
更新订阅、
随着列表的深入，维护解决方案所涉及的工作量也会增加。 如果链接服务器查询适合您想要实现的目标，那么使用链接服务器查询效果最好。
编辑：如果它们是同一台服务器，那么按照另一个用户的建议，您应该能够使用 servername.databasename.schema.tablename 访问该表看起来这是一个实体框架问题& 不是数据库问题。
You can use linked server queries to leave the data where it is, but connect to it from the other db.
Depending on how up-to-date the data in each db needs to be & if one data source can remain read-only you can:
that you can run periodically as a SQL Agent Task
to get the data to the other db
can be near-realtime.
If data needs to be read-write in both database then you can use:
update subscriptions
As you go down the list the amount of work involved in maintaining the solution increases. Using linked server queries will work best if its the right fit for what you're trying to achieve.
EDIT: If they're the same server then as suggested by another user you should be able to access the table with servername.databasename.schema.tablename Looks like it's an entity-framework issues & not a db issue.
我不了解 EntityToSql，但我知道在 LinqToSql 中，如果您在表中添加前缀，则可以连接到一个 .dbml 中的多个数据库/服务器：
我已经能够单击 .dbml 中的表并将其复制并粘贴到备用项目的 .dbml 为名称添加前缀并设置关系，并且它可以工作...就像我说的那样，这是在 LinqToSql 中，尽管还没有尝试使用 EntityToSql。 在你完成所有复制等工作之前，我会先试一下。
I don't know about EntityToSql but I know in LinqToSql you can connect to multiple databases/servers in one .dbml if you prefix the tables with:
I have been able to click on a table in the .dbml and copy and paste it into the .dbml of the alternate project prefix the name and set up the relationships and it works... like I said this was in LinqToSql, though have not tried it with EntityToSql. I would give it shot before you go though all the work of replication and such.
感谢您的回复。 我们将尝试向旧数据库表添加触发器，以在新数据库的新表中插入/更新/删除记录。 这样我们就可以继续使用实体框架并进行我们需要的任何数据转换。
一旦 UI 功能转移到新系统的特定功能，我们将从旧数据库中删除该表，并向旧数据库添加一个具有相同名称的视图，该视图指向新数据库表以实现向后兼容性。
我意识到在执行此操作之前需要发生的一件事是，我们必须在所有代码和 sql 中搜索 @@Identity 并将其替换为scope_identity()，这样触发器就不会弄乱旧系统中的 Id。
Thanks for the responses. We're going to try adding triggers to the old database tables to insert/update/delete records in the new tables of the new database. This way we can continue to use Entity Framework and also do any data transformations we need.
Once the UI functions move over to the new system for a particular feature, we'll remove the table from the old database and add a view to the old database with the same name that points to the new database table for backwards compatibility.
One thing that I realized needs to happen before we can do this is we have to search all our code and sql for @@Identity and replace it with scope_identity() so the triggers don't mess up the Ids in the old system.
如果 Linq-to-Entities 无法跨数据库，那么复制或模拟它的东西是唯一可行的。
出于性能目的，您可能需要合并复制或带有排队（非立即）更新的事务性更新。
If Linq-to-Entities cannot cross DB's then Replication or something that emulates it is the only thing that will work.
For performance purposes you probably want either Merge replication or Transactional with queued (not immediate) updating.