Entity framework move/archive data from one database to another

Question

I have the following scenario:

I have a production database which is highly transactional. In order to keep queries efficient I would like to archive data from some of the tables to another database with exactly the same schema.

The relationships between tables are not very complex but any dependent objects would have to go with the archived data in order to uphold foreign key constraints.

Is there a simple way to do this using Entity Framework? I have tried to create two different contexts and add to one and delete from the other, but this is a bit of a tedious route.

If Entity Framework is not the best tool for this what is?

Under what conditions would you want to archive data? Data that's too old gets archived at some threshold? Data that gets deleted gets archived? etc. — Ocelot20
– Ocelot20, Commented Jul 30, 2013 at 21:54
You could build something in SSIS using a whole bunch of business logic, but the proper way to do this is (most likely) with partitioning. It has a host of pros and cons to weigh, however. Consult a DBA and maybe a BI developer. — tommy_o
– tommy_o, Commented Jul 30, 2013 at 22:21
@Ocelot20 I have some complex queries on the transactional data using EF. There are millions of rows and the queries are becoming slow. Even with proper indexing. I would like to archive data older than X months to a separate location to try speed things up. — TheGwa
– TheGwa, Commented Jul 31, 2013 at 7:45

D.R. · Accepted Answer · 2013-07-30 21:55:34Z

1

There is no simple way in EF5 to do this.

If your database is MSSQL you can make use of partitioning for archive tables (see http://blogs.msdn.com/b/felixmar/archive/2011/02/14/partitioning-amp-archiving-tables-in-sql-server-part-1-the-basics.aspx for more information).

answered Jul 30, 2013 at 21:55

D.R.

21.5k25 gold badges124 silver badges220 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TheGwa Over a year ago

I am not sure that EF queries are optimised to not look for data in other partitions. There is a cost for querying across partitions.

Collectives™ on Stack Overflow

Entity framework move/archive data from one database to another

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related