Database Schema Design - Tips for improving ability to archive? -

I am preparing a table in the database that will store log entries from the application. There are some things that I think about this design more than usual.

• Though these log entries will be used by the system to make decisions on the runtime so that they are relatively fast reach.
• They also have the problem that many of them are going to be (12.5 million pairs per month is my guess).
• I do not need more than the last 30 days for more than 45 days for the decision-making process.
• I support them all for more than 45 days & amp; Legal issues, at least 2 years.
• The table design is fairly easy, most common types (no blobs or nothing), where possible to use the database database, put in default data, mostly a foreign key.
• If there is a difference then the database will be Microsoft SQL Server 2005.

I was thinking that they were written in a live table / database and then using an ETL solution, the "old" entries in the archive table / database - which is big and slow hardware Is on.

My question is, do you know any tips, tricks or suggestions for database / table design to ensure that it probably works? If you think this is a bad idea, please tell me, and what do you think is a better idea.

Some databases offer "partition" (for example, Oracle). A partition is like a scene that collects several tables in one with the same definition. You can define the criteria that sorts new tables in different tables (for example, month or week-by-year% 6).

From the user's point of view, it is just a table from the database POV, it has many independent tables, so that you can complete the entire table command (like trout, drop, remove from table, load / dump in an efficient manner , Etc.) can run.

If you do not have a split, you get a similar effect with the scenes. In this situation, you can assemble several tables in one scene and redefine the scene, say, once in a month with the "Free" Balance table with the old data. Now, you can efficiently store this table, clean it and see it again when bigger work is done. It will be of great help in improving performance.

[Support] Support for (Enterprise Edition) partition from SQL Server 2005

Thank you for