Brent Ozar answers a question:
At least once a month, I get this question from a client:
We have big data, and we want to save money. We want to move the older data down to some kind of archives that will cost us less.
I ask exactly one followup question:
Are you willing to modify the app that queries the data, or do you want the users to still be able to query the old data in their existing screens and reports?
Brent recommends trying a linked server approach. I’d also toss PolyBase in to the mix, though it’s going to have similar pros and cons to linked servers in this specific scenario. I have an old post on cold storage with PolyBase but the mechanisms haven’t changed much. I do have some sample scripts in my PolyBase talk’s GitHub repo, though fair warning that these are going away soon as I rebuild the talk for 2025.