We have a specialized business software (transportation) running on SQL Server. The software company decided to store all sorts of files (invoices, scans, pdfs, emails, pictures, ...) that have to be retrieved as blobs in the database. Most of the data becomes readonly after e.g. a year or so, so for general performance and manageability reasons I forced them to switch for all new files to filestreams.

But still our databases are filled with 90% ancient data. We will migrate the server to new instances (from sql/win 2008R2 to sql/win 2016). For easier management and (worst-case), faster restore times, I would like to create some kind of primary server, that holds all the data, that is not older than a year, and a secondary server, that holds all the old stuff. The application itself is designed to use many databases. If a database is full, they set it to readonly, and create a new one (manually, through support. no hard limits anywhere). Can I (ab)use this behavior to link the old databases to another (the secondary, archive) SQL server with regular SQL Server capabilities?

Another way could be to strip all the old blobs out and save them into a remote files system. Is this possible? If one could do this, the archive server could be windows server with a data partition and a share...

I hope I was not too confusing :)

  • If all files going forward will be stored as filestream data instead of blobs, why not just export your old blob data to the filesystem and reimport as filestream data?
    – HandyD
    May 7 '19 at 7:38
  • that is the plan. but i would like to store the old data somewhere else. it is hardly accessed, and i do not mind if opening a document from the archive takes 2-3 seconds. only data that it e.g. younger than one year shall be on the main sql server. that keeps everything slim and recovery time, in case of an error, are low too. the server now takes around 5 hours to fully restore (4TB), which is not acceptable for me. redusing the server size is the easiest way to accomplish that without reinventing my backup infrastructure.
    – Guybrush
    May 9 '19 at 8:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.