Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

There is a project for lab. The requirement to save the lab data (raw data and related infomation ) for research for years.

So the data must be saved for years, but the data is so big, every raw data is more than 10 GB. If we store the raw data in SQL Server's FILETABLE and store related information in a standard SQL Server table, after months or years, the database size will be so big that we must move some history data out of the database.

Maybe we could use adding files (on a different hard disk) to the FILESTREAM filegroup for filetable (to store raw data ), but I feel that is not suitable for maintaining the SQL Server. We must keep the raw data and related information's consistency.

We have considered using tape to save the historical raw data. We want to use hard disk to save latest raw data and use tape to save the historical raw data. When we move the history data to tape we record the move in a log table, so we can know where the history data is moved and pick up it as soon as possible.

Is there good advice for:

  1. How to store the raw data and related information?
  2. Is filetable suitable for this scenario?
  3. Is there a better solution for this scenario?
share|improve this question
2  
Any requirement from the file storage? HA/DR? Failover, Mirroring? Backup/Restore? ACID Xact? Why did you consider FT to start with? –  Remus Rusanu Oct 28 '13 at 12:35
    
@Max Vernon One requirement detail:we have a machine(Xradia MicroXCT) to scan the sample(such as small rocks),in the processing we will input some parameters for the MicroXCT's control system.Because of the parameters are important for MicroXCT's result,so we must save the parameters and the MicroXCT's result(raw data) which size is bigger than 10 GB._The raw data will be processed in next days or months or years,so we must keep the raw data and the parameters for long time._ –  xudong125 Oct 29 '13 at 1:53

3 Answers 3

up vote 7 down vote accepted

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.

share|improve this answer
    
I have added some detail of my project's requirement.(under my question's comments) –  xudong125 Oct 29 '13 at 1:57

I would say no. You wrun into tons of Problems and you run into a lot of costs over time (licensing).

I am all pro storing biary data in SQL when it makes sense, but 10g files would be way larger than I am comfortable with.

share|improve this answer
    
In my opinion,file table is just suitable for save some small files. –  xudong125 Oct 28 '13 at 9:25
1  
I am not suer I back that up - it is relative. BUt I would be doubtfull with 512mb files - 10g REALLY is too much. We can argue about 64mb, especially when this is rare or in a document management system. But 10g... no way. –  TomTom Oct 28 '13 at 10:33

No, you should put the raw files into a system optimized for storing big amounts of data: the filesystem. Only the metadata should reside in the database. Make sure only your application (besides the system administrator) can access the files to ensure integrity.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.