MSSQL Server 2008 Standard Edition archive large 800GB table
<p>I have a table which has millions of records being inserted per day since 2011. The data size for that table alone have grown more than 800GB. For me only last 3 months of data is being queried frequently whereas for older data query needs to be done very rarely and it can be done in offline mode.</p>

<p>Problem 1 : The table has a date column and I want to archive all data before 3 months in files so that I can move them on another storage. So what can be the best approach to meet the requirements and I do not want to keep data on backup mssql server.</p>

<p>A. Create SSIS packages to dump the historical data into csv and zip the files by some month or date name. When required the SSIS package can read the files and load into database. This job would be scheduled daily or weekly basis.
B. Write some JAVA or python code to do the same job.
C. Any other alternatives?</p>

<p>Problem 2: Query is executed for only one date at a time. While querying this table is joined with some other meta tables. So should I add partitioning in my table for faster query performance?</p>