One of the ways we archive data is by setting an archive cutoff point. So say we only need the last 6 months of data... Every month we will run a script that takes anything older then 6 months and move it to a separate table (or database). Obviously this solution isnt perfect for every situation. But depending on how much data you have for each row, a million rows usually isnt all that much. So far example we may have a table called "data" and a second table called "data_archive". This will keep the data table and indexes small and manageable.

You should of course still backup your data since the archive is really just a reference.