What you describe sounds a lot like SQL Server Stretch Database. Stretch DB only works when the remote server is Azure, though. Also, the pricing on it is fairly high--so much such that I don't personally consider it a viable option, especially since archive-level storage of old data should also help with cost savings, not cost a premium.
Assuming Stretch DB won't work for you, there is no built in feature to do this for you. You'll have to build your own.
You could build an ETL pipeline to move data from your primary server to your archive server. You could also move data to the archive server via backup & restore.
I've personally built solutions that put busy logging data into monthly/yearly databases on the Primary server, then moving the old logs to the archive/reporting server via backup restore for long term retention. With that solution, I use a synonym or a view in the main database to redirect reads/writes of active data to the other database, and update that when the "active" database changes. This allows application code to remain consistent, even when the actual table is changing behind the scenes.
When there's a requirement that data be moved to a cooler archive server, the partitioning/sharding into a separate database is very attractive, because databases can be moved around so easily from one server to another. Database-level backup/restore is much more efficient (and transactionally consistent, if that matters) than reading data from a table & moving it via a pipeline.
