Archive for March, 2011

Binary Waveform Data in SQL Server 2008

Tuesday, March 8th, 2011

As Shahid points out in Consider MySQL ‘Archive’ storage engine to store large amounts of med device structured or waveform data, saving physiologic waveform data from a medical device in a MySQL database for archive purposes is a reasonable alternative to using flat files.

In SQL Server 2008 you can have it both ways.  In addition to saving binary data directly in the database you have the option to have a varbinary column stored as a file stream. From the article How to store and fetch binary data into a file stream column:

File stream data can be used from the .NET Framework using the traditional SqlParameter, but there is also a specialized class called SqlFileStream which can be used with .NET Framework 3.5 SP1 or later. This class provides mechanisms, for example, for seeking a specific position from the data.

There are pros and cons to this approach. The backup and transactional issues, along with the performance considerations, all have to be evaluated against your specific system requirements.  Having the SQL Server engine manage the database relationship to the binary files seems like a big advantage over maintaining flat files yourself.

Read the MSDN article FILESTREAM Storage in SQL Server 2008 for all the gory details.

UPDATE (3/25/11): Who's Got Access to your FileStream Directories?