Does Aware IM Store BLOB in MsSQL20008 FileStream?

BenHayat

SQL2008 has a feature (once setup) that will allow for large BLOB to be stored as a FileStream rather than directly inside of Database Record. Here is a small summary:

Summary: This white paper describes the FILESTREAM feature of SQL Server 2008, which allows storage of and efficient access to BLOB data using a combination of SQL Server 2008 and the NTFS file system. It covers choices for BLOB storage, configuring Windows and SQL Server for using FILESTREAM data, considerations for combining FILESTREAM with other features, and implementation details such as partitioning and performance.

I would like to know if this feature is supported in Aware IM for storing images and documents?

Thanks!

aware_support

No, it's not, but I suppose you could manually change the corresponding table column to support it (not sure if this will work, but you can try)

BenHayat

aware_support wrote
No, it's not, but I suppose you could manually change the corresponding table column to support it (not sure if this will work, but you can try)

Can you elaborate what you mean by "change the corresponding table column to support it"?
I believe this feature needs to be handled by the running software, i.e. Aware IM, since you'll be storing the name of the file (that contains the Image or PDF) inside of the table and then when you need the image, the software needs to get that and stream it over to client.

Here is an overview to look at:
http://technet.microsoft.com/en-us/library/bb933993.aspx

One of the main reasons for introducing this feature in SQL2008, was to keep large BLOBs fields out of the database record, so when you're going through the normal readings, these blobs don't have to fetched to slow down the reading and would use less memory. So, there is a huge advantage to that. Would you look into this so this feature can be supported in future release(s)?

Currently, how do you find fetching the performance when a table is holding the BLOBS stored directly in the table rather than outside as a separate file?

Thanks!

tford

Ben,

Not sure if this sample BSV has any value with what you are trying to accomplish:

https://forum.awareim.com/viewtopic.php?p=13269#13269

BenHayat

Got a bit more info.

Basically, the SQL2008 engine must be setup (through the SQL Studio) to enable the FILESTREAM.

Then, A FileStream group needs to be created.

I guess these two steps can be done by us.

However, the third part is when Aware IM is creating an SQL script to create a BLOB field, there are two things needed. a) The Column type must be varbinary(max) and also needs the attribute of FILESTREAM.

Here is a sample of SQL script when the table is being created.
CREATE TABLE Archive.dbo.Records
(
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
[SerialNumber] INTEGER UNIQUE,
[Chart] VARBINARY(MAX) FILESTREAM NULL
)
GO

BenHayat

tford wrote
Ben,

Not sure if this sample BSV has any value with what you are trying to accomplish:

https://forum.awareim.com/viewtopic.php?p=13269#13269

Hi Tom;

What I'm discussing with the support team, is the "Internal" mechanism how a Large Binary file is either stored within the actual table (as is now with Aware IM and most databases) or SQL2008 allows to be stored as a FILESTREAM.

Those developing apps in AWARE IM, shouldn't be concern about this subject for their apps, since this is a low level process, however, taking advantage of this feature, it can boost the performance and memory usage of the application (in this case Aware IM) to a higher degree.

Based on the above post, this feature can be added into Aware IM, and if someone is using SQL2008 (and higher), then a small modification to SQL script can take advantage of it.

aware_support

Check if ALTER TABLE supports this syntax. If it does you can manually change the table after Aware IM creates it by manually running an SQL query that changes the column type to FILESTREAM. Aware IM will NOT create the column with this setting automatically.

BenHayat

aware_support wrote
Check if ALTER TABLE supports this syntax. If it does you can manually change the table after Aware IM creates it by manually running an SQL query that changes the column type to FILESTREAM. Aware IM will NOT create the column with this setting automatically.

This is a good idea, but raises a problem.

Let's say I develop an app and in Items table I'm storing the image and drawing of the item. Then after I publish it the first time, I do have a chance to run your ALTER_TABLE solution in order to set the required attributes.
Now sometime has passed and lots of data and images are stored. Remember, in FileStream, the image is stored on disk as a file and it's URI is stored in database table.

Then after sometime when lots of data has been entered, we decide to publish a new version. But in the new version, we have added more fields to Item table. So, what's going to happen:
When we publish a new version, with new Item table schema, your system is going to run ALTER_TABLE that is created by your system WITHOUT the usage of FILESTREAM attribute. This will cause the original item table that is holding the URI to change (ultimately will delete the URI and replaces it with regular BLOB). At this point, SQL engine, will also delete the actual file from the disk when the URI field is deleted. (It's part of it's system). So at the first stage of running your ALTER_TABLE, will cause all the images to be deleted. Even, if I go again and run ALTER_TABLE with the FILESTREAM attribute turned on, it will only be good for the next set of images to be created as filestream, but the first ALTER_TABLE run by your system, will delete them all.

Anytime a tool (Like Aware IM) is maintaining database, is not a good idea, for developers to interject other commands, because the integrity will be broken. So unless this feature in built in, I wouldn't risk trying to keep up with maintaining it outside of the tool.

Does it make sense?
Thanks!

aware_support

What you are saying is true. However, your scenario will only cause a problem if you change this particular attribute in your configuration. Only then will Aware IM issue an ALTER TABLE that will lose your manual setting. Any other changes - to other objects and other attributes, will not cause SQL Server to lose the manual setting.

Like I said, this feature is currently not supported by Aware IM and what I suggested is a workaround that may work in certain cases, but has problems in others. Most of the time it should be fine, though.