Is it a good idea to wrap a data migration into a single transaction scope?

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 i开发者_如何学Gos not a problem but is there a limit on how much modified or new data that can be inside a transaction scope?

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.

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.:

• If the server is in use for other queries, your long-running transaction will probably lock other users out.
• Whereas, If the server is not in use, you don't need a transaction.

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.