how to divide a filetable to several filetable on several disk in sql server?

Question

I have a dms database that use filestream. I want to distribute data to several disk based on year. How can I divide this database to several disk in sql server?

I used Partitioning technique but I consider it dosen't support filetable. Now I create 3 filestreams group and 3 filegroups. when I insert data to these 3 filetables, show error on constraint of filetable, path_locator, file_type and cached_file_size. How to partitioning Manual filetable?

---

I have a document management system for my company to manage company documents(~2TB). Considering this scenario, what would be the best strategy for us regarding SQL Maintenance (backup, restore , etc.) and best use of storage space? How can we reduce the volume of database on raid storage? Challenges we are facing: Full Backup will take too much time. DB Restore will take very long time if any disaster happened. Database and Backup file size are too big and we have problem for storing them on a Raid Storage.

I want partitioned or divide my filetable. and store my blob filestreams on several filegroup (several directory on several disk). how to do it and how to move recoreds of main filetable to my new filetables based on creation_time and store filestreams to new directories. This scenario make backup & restore process faster by backup & restore based on filegroup

Answer 1

This "I want partitioned or divide my filetable. and store my blob filestreams on several filegroup (several directory on several disk)."

Will not help with your challenges.

Challenges we are facing: Full Backup will take too much time. DB Restore will take very long time if any disaster happened. Database and Backup file size are too big and we have problem for storing them on a Raid Storage.

Backups and restores are of the whole database. Partitioning is irrelevant.

Answer 2

There is no need to store your backup files in RAID volumns. Storing recently backup files, such as last 5 days, in a stand alone and fast hard disk, such as an SSD, this may speed up backup and restore, and save expensive primary storage space. After this, make a copy of newest backup to a removable mass storage, which allows you to store backups as much as possible, and it will be an offline backup copy.

You can backup your database using the Files and Filesgroup backup feature. This may provide faster backup and recovery performance. Please check out the document from microsoft and series of articles written by Rajendra Gupta.