I am trying to achieve the same thing as you. I need to archive data from a Production database into an archive Database. The db still needs to be online. I need to purge the production database regularly so that i can keep the db clean and achieve maximum performance.
I need to also bear in mind that some of our customers use Sql Express which does not support replication, scheduled tasks etc. I tried Replication, but after cleaning up the production database, it filter the deletes to the archive database. I considered partitioning but you need Sql Enterprise for that.
I am moving towards a parameterized stored procedure. I need to get the rollback and the necessary checks in place to avoid duplication and i need to ensure the data integrity.
How do you go about archiving your data and would you have any other ideas?