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
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:
ALTER PARTITION FUNCTION [MY_PF]() MERGE RANGE ('MDOC 20161201')
MERGE RANGEis 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:
Alter database [MY_DB] modify file (name = 'MYDB_FS__201612', filename = 'q:\archive\MYDB_FQ_20161201')
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?