FILETABLE
data is built upon the FILESTREAM
feature of SQL Server, and uses the Windows Server filesystem to store VARBINARY(MAX)
data in discrete NTFS files on the filesystem of the server itself. This data is not stored in the database, therefore the database file size will not include these 10GB+ files. Database backups will, by default, include the data. You can prevent SQL Server backups of the filestream data by using partial backups to exclude the filestream filegroups. Filestream data is not cached using SQL Server buffers, the operating systems file cache is used instead.
Microsoft recommends AGAINST using FILESTREAM
to store files that are on average less than 1MB since this negates the efficiency gained by using the Win32 file APIs to modify the data involved. The maximum file size for a FILESTREAM
file is only limited by the operating system maximum file size, which for current versions of NTFS is for all practical purposes unlimited (16 Exabytes). There are, by design, no limitations placed on the size of a FILESTREAM
object by SQL Server.
If the meta data you intend to store in SQL Server must be transactionally consistent with the object data stored in the FILESTREAM
you should use FILETABLE/FILESTREAM
. If the object data stored in these 10GB+ files is not logically related to meta data stored in SQL Server, I would expect using FILESTREAM
might be counterproductive.
In short, it depends on your exact use-case. Add more details to your question about why you want to use a FILETABLE
.
FILESTREAM docs at Microsoft: http://technet.microsoft.com/en-us/library/gg471497.aspx
[Edit]
In light of your requirements to store binary X-Ray data along with machine parameters used to capture the x-ray data, it sounds like the precise use-case for a FILESTREAM
. However, instead of archiving to TAPE, I'd consider using DataCore SANSymphony-V Software SAN with multiple storage tiers. SANSymphony-V allows you to easily and reliably expand your SAN with inexpensive commodity Windows-based servers using 15k RPM Enterprise drives (with an SSD tier, possibly) for the main database along with a tier configured with standard 7200 RPM SATA drives for storage of the x-ray data. SANSymphony-V is SQL Server certified and would provide a relatively inexpensive way of maintaining access to both the new data and the older data without any complicated data-management system. If Disaster Recovery and High Availability is a concern, the entire SANSymphony-V SAN can be mirrored, either on-site, or off-site at distances of up to around 100km.
Just for clarity, I am not in any way associated with DataCore - I simply love their design.
Both page and row compression do not compress BLOBs.
Because of their size, large-value data types are sometimes stored separately from the normal row data on special purpose pages. Data compression is not available for the data that is stored separately.
If you want to compress BLOBs you need to store them as VARBINARY(MAX)
and apply your stream compression algorithm of choice. For example GZipStream
. There are many examples how to do this, just search for GZipStream and SQLCLR.
Best Answer
SQL Server supports piecemeal restores. From that link:
So you should be OK with the FULL recovery model.
I'd suggest you partition your tables. The log tables especially should be amenable to partitioning by write date. Direct the older partions to a filegroup on the slower, cheaper storage. Then you can restore the important (i.e. recent) portions first and the ancient logs later.