Aaron Bertrand shares some thoughts:
Something I’ve been thinking about lately is how much work I force onto SQL Server simply because data gets old. An order processing system may need to keep data for three years for warranty reasons or seven years for compliance reasons, but that doesn’t mean that data has to live all those years in your hottest, most active table and database. It also doesn’t mean you should spend a lot of effort moving it, row by row, to an archive table, never mind a completely different database. Only to delete it later. If you’re constantly fighting archiving processes that interfere with everyday workload, this conversation might be for you.
Click through for the article. One additional thing I could recommend looking into is using something like PolyBase for that archival. I have a demonstration of the process and it essentially uses the same technique SQL Server professionals used prior to partitioning in 2005. That particular scenario would work best when the archival data is stuff you very rarely query, but can allow you to move data off of relatively expensive fast storage onto relatively less expensive and slower storage.