Table partitioning on OLTP
With partitioning you have a couple of additional options.

Switch older partitions to a separate table in the same database.  Query is still not transparent, but advantage of is primarily that a partition switch is a metadata-only operation, only requiring a brief schema lock and not generating a significant amount of log writes.

Partition and leave the older rows in the partitioned table, perhaps setting an 'archived' status column.  The older partitions can be compressed differently, and if there is some column in that differentiates the archived from non-archived rows and that column is universally used as a filter by the OLTP application, then you can create filtered indexes that differentially index the archived and non-archived rows.


And on SQL 2017 you have the simple option to make the table a Temporal Table.  You would continue to DELETE older rows, but SQL Server will copy the deleted rows to the history table, manage the retention of archived rows, and provide AS OF views across the main and history table.
BTW Table and Index partitioning is available in every edition of SQL Server 2016 SP1+.