We have a table related to documents(about 17m records on one table), and another one for properties of that documents. And team decided to archive old entries. I thought about partitioning with filegroups, which can be placed in other storage drive But it will be still same server/host So, does SQL Server have the ability to partition data by date and send queries for older data to another SQL Server instance, while having same filtering in local server ?

And if that possible, will we have performance impact except network IO ?

1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.