archiving partitioned filestream table

by Frederik Vanderhaegen   Last Updated January 12, 2018 08:06 AM

I've a database on SQL Server 2016 SP1, in the db there is only one table with a Filestream column and a few columns of type varchar. The table is partitioned and every month a new partition is added by scripting.
Each filestream partition has its own filegroup, while the other partitions are in the primary filegroup.
New data is inserted in the new partition + old data is not be updated. The partition function is created with Right Range.
I want to hold one year of data (12 partitions) on SSD and archive the rest to slower storage. This will be an automatic process (SQL Job/Scheduled task).

I created two scenario's:

  • I merge the oldest partiton on SSD so it is moved to the archive:
    ALTER PARTITION FUNCTION [MY_PF]() MERGE RANGE ('MDOC 20161201')
  • MERGE RANGE is an offline operation => the table is locked due to a SCH-M lock and no one can access the data. So I thought off another way to accomplish the archiving. There are a few steps in this scenario:
    1. Alter database [MY_DB] modify file (name = 'MYDB_FS__201612', filename = 'q:\archive\MYDB_FQ_20161201')
    2. Take db offline
    3. Copy dir using Robocopy
    4. Bring db online

The second method is twice as fast as the first one. But the disadvantage is that after a few years the archive disk will contain a lof of folders/partitions containing the filestream data + database needs to be offline. As with the first method there is only 1 folder/partitions (or I can split that big partition by year).

I hope my explanation is clear enough because it was hard to put all this in writing.
What is the best method to accomplish the archiving or am I missing something?



Related Questions





Buffer Pool in SQL Server FileStream

Updated September 20, 2017 05:06 AM