Figuring out archiving or partition strategy?

We are using SQL Server 2008.

Our requirement is thi开发者_JAVA百科s -

We have about 4 tables related to transactions, which record orders placed by consumers, and their status of delivery.Once,the order is completed it has affect on inventory and finance. Now,these tables will become huge.So,we want to archive every 6-8 months.But,after archiving the inventory and financial calculations should not be affected.So,is partition a right option for us? how will it affect the queries? How should we approach this issue?

It depends.

The two main approaches I can think of is either table partitioning (separating the table into different filegroups) or physical movement of data to somewhere else (ie. reporting server).

Imo, the easiest to script would be the data movement as a nightly batch, which is currently what is used where I work. Nightly we bcp data past the threshold out, bcp in the reporting server and verify both tables for correctness.

Ultimately you have to decided what is better. Will you need to constant query archived data? Archived data will even be queried? Perhaps it is cheaper to move to another server with slower hardware than to keep not-so-heavily-queried data in the faster hardware. Up to you.