OK, so I think what you are saying is that you have a very large table that you need to report against, but you only need a relatively small amount of the data for your production environment.

If your challenge is supporting reports against this large data set, you might want to consider directly going against the production table while using the WITH (NOLOCK) statement to avoid locks that might tie things up. You can also use the OPTION (MAXDOP 1) statement for your report queries to limit the number of processors that are used for processing and thus leave the other processors available to handle production requests.

Another approach is as you suggested. You can set up a monthly routine that will copy groups of rows (10,000-400,00 at a time would be my recommendation) from production into your reporting instance and then in the same transaction, delete those rows from production. That should work just fine for you too.

I have used partitioning extensively, and it works absolutely great. It can improve performance to an amazing degree by flattening the indexes and leveraging multiple processors.

You can also use partitioning for an easier and more efficient solution for archiving data from production into a reporting instance. To do this you would create two separate tables, one for production and one for reporting. Partition each table by date (probably one month per partition). Have each table have identical partition file groups and indexes. Initially the production table would contain all of the data and the reporting table would be empty. You can then move a month at a time by using this command:

ALTER TABLE BigTableInProduction

SWITCH PARTITION <partition number of month to be moved> TO BigTableInReporting PARTITION <partition number of month to be moved>

Regardless of how much data is in the table, the statement should just take a second or two to run -- seriously, it goes that fast. The downside is that managing the partitions does require some additional DBA effort.

Let me know if you have any more questions.