将数据迁移包装到单个事务范围中是个好主意吗？
我正在将数据子集从一个数据库迁移到另一个数据库。
我正在编写一个 .net 应用程序，它将与我们内部的 ORM 进行通信，ORM 会将数据从源数据库拖到目标数据库。
我想知道，是否可行，或者将整个过程放入事务范围中，然后如果没有问题就提交它是否是一个好主意。
我想说我可能会传输大约 1Gig 的数据。
性能不是问题，但是事务范围内可以修改的数据或新数据的数量是否有限制？
I'm doing a data migration at the moment of a subset of data from one database into another.
I'm writing a .net application that is going to communicate with our in house ORM which will drag data from the source database to the target database.
I was wondering, is it feasible, or is it even a good idea to put the entire process into a transaction scope and then if there are no problems to commit it.
I'd say I'd be moving possibly about 1Gig of data across.
Performance is not a problem but is there a limit on how much modified or new data that can be inside a transaction scope?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
除了日志文件的物理大小之外，没有任何限制（请注意，所需的大小将远远大于迁移数据的大小。还要考虑是否出现错误并且回滚事务，这可能需要非常非常长的时间 如果原始数据库相对较小
（< 10 gigs），那么我只会进行备份并在没有事务的情况下运行迁移，
如果有任何问题，只需从备份中恢复即可
。为此，您可以使数据库脱机 - 在实时情况下进行迁移完全是另一回事......）
如果您需要在实时情况下执行此操作，那么在事务中小批量执行它是唯一的方法。
There's no limit other than the physical size of the log file (note the size required will be much more then the size of the migrated data. Also think about if there is an error and you rollback the transaction that may take a very, very long time.
If the original database is relatively small (< 10 gigs) then I would just make a backup and run the migration non-logged without a transaction.
If there are any issues just restore from back-up.
(I am assuming that you can take the database offline for this - doing migrations when live is a whole other ball of wax...)
If you need to do it while live then doing it in small batches within a transaction is the only way to go.
我假设您正在不同服务器之间复制数据。
回答你的问题，没有任何限制。然而，有一些限制因素会影响这是否是一个好主意。第一个是锁定和锁争用。即：
其他建议：
考虑编写代码，使其是增量的、可中断的，即一次执行一点，并从中断的地方继续执行。这将涉及大量的小事务。
考虑将数据加载到目标数据库内的临时表或临时表中，然后在从该源更新时使用存储过程或 SQL 批处理使用事务。将其放入事务中应该不会有太多麻烦，因为在同一服务器上，它应该快得多。
还可以考虑将 SSIS 作为一种选择。实际上，我对 SSIS 一无所知，但它应该擅长这种事情。
I assume you are copying data between different servers.
In answer to your question, there is no limit as such. However there are limiting factors which will affect whether this is a good idea. The primary one is locking and lock contention. I.e.:
Other suggestions:
Consider writing the code so that it is incremental, and interruptable, i.e. does it a bit at a time, and will carry on from wherever it left off. This will involve lots of small transactions.
Consider loading the data into a temporary or staging table within the target database, then use a transaction when updating from that source, using a stored procedure or SQL batch. You should not have too much trouble putting that into a transaction because, being on the same server, it should be much, much quicker.
Also consider SSIS as an option. Actually, I know nothing about SSIS, but it is supposed to be good at this kind of stuff.