Is anyone using SQL Server FILESTREAM for BLOB storage?

5519
8
12-10-2013 05:42 AM
StevenMenefee1
New Contributor II
We are wanting to allow one of our web applications the ability to upload attachments so we have enabled attachments on a feature class in our ArcSDE geodatabase.  However, we're trying to understand how we want to store the resultant BLOB data on the back end in SQL Server.

One of our DBA's has done a little research on using FILESTREAM in SQL Server to help manage the BLOB data.  I was wondering if anyone here has had any experience with using FILESTREAM.  Any help would be appreciated, thank you!
0 Kudos
8 Replies
DaleKubenka
New Contributor
We are currently researching the same topic.  Have you received any feedback on the topic?
0 Kudos
WilliamCraft
MVP Regular Contributor
I've not worked with FILESTREAM in the past, and I doubt that you'll find many others on this forum who have.  Hopefully someone will reply with their experiences.  Some quick research tells me that the FILESTREAM storage attribute in SQL server is binary (BLOB) data stored as a VARBINARY(MAX) column.  According to Esri documentation (I'm going with 10.2.x since you didn't mention your software version), that data type is 'seen' by the Esri software as a BLOB and is supported.  The documentation is here.  Specifying the FILESTREAM attribute on a VARBINARY(MAX) column causes SQL Server to store the data on the local file system instead of in the database.  Microsoft's documentation also states that a ROWGUID field with a unique non-clustered index is required to augment the attachment field.  So, while I can't say I have direct experience with this, at first glance it should be possible to achieve what you're trying to do.
0 Kudos
JoshWhite
Regular Contributor III
I'm not familiar with FILESTREAM but I would love to hear more about it.  Right now I am using the feature in ArcMap called the Attachment Manager. It works but this is a slow way to do it.  I have multiple attachments each for over a thousand features and it is becoming a tedious process.  If I could even drag and drop files as attachments that would greatly speed things up.  As it is now I have to have a windows explorer window open to view the image previews so that I'll know what to select in the Attachment manager.  So there is a wasted extra step in there.
Josh White, AICP
Principal Planner

City of Arkansas City
0 Kudos
DaveFrame
New Contributor II

We've been testing FILESTREAM with ERSI Attachments. You might be OK using it as long as you don't need to replicate or move the data. Essentially, enable attachments on a feature class. Go to you SQL database and enable and configure FILESTREAM. In SQL, go to the ##_ATTACH table that was created from enabling attachments. Generate the SQL code to create the table. Delete the ##_ATTACH table. Modify the SQL code to add a rowguidcol at the beginning and set the DATA field to fe the FILESTREAM field. My query statement is below. For some reason I had to add an extra [varbinary] at the end for the statement. I just deleted the field after the table was created. At this point I was able to add attachments in desktop and you could go to the folder on the server where you specified as the FILESTREAM folder and see some files were created. I haven't figured out how to move it yet because XML export fails on a table with FILESTREAM enabled.

CREATE TABLE [GIS].[FACILITYSITEPOINT__ATTACH](

[RowNumber][uniqueidentifier] rowguidcol not null unique,           (ADDED this field)

[ATTACHMENTID] [int] NOT NULL,

[REL_OBJECTID] [int] NOT NULL,

[CONTENT_TYPE] [nvarchar](150) NOT NULL,

[ATT_NAME] [nvarchar](250) NOT NULL,

[DATA_SIZE] [int] NOT NULL,

[DATA] [varbinary](max) FileStream NULL,            (Set this field as FIELSTREAM)

[FileStream] [varbinary](max) NULL                  (Added and deleted this field)

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


ScottFierro2
Occasional Contributor III

To follow up what @Dave Frame said we are testing it now ourselves. We recently used AGO to build a collection using the Collector app. We puled the data down from AGO to push into our enterprise running on SQL 2012. Our next steps will be to use the below link to try and shift the images collected into the filestream.

Adding FILESTREAM to an existing table in a database in SQL 2008 R2

Still unsure how this change will carry through to usage within ArcMap, or even more so how it will work when we try to publish this data set for use in a service but will try to keep people updated as we push it forward.

0 Kudos
ScottFierro2
Occasional Contributor III

Having a discussion with ESRI reps tomorrow about getting them to leverage Filestream capabilities. Part of what they brought to our attention was the new site they are using for "Enhancement Requests". I did a few searches there and did not find this over there so I have started up things there. Here is the link

https://c.na9.visual.force.com/apex/ideaView?id=087E0000000CxTH&returnUrl=%2Fapex%2FideaList%3Fc%3D0...

0 Kudos
DaveFrame
New Contributor II

ESRI has opend up a Bug/Enhancement request for this topic.

Bug ENH-000087562: Allow data storage to utilize SQL Server FILESTREAM capability with ArcSDE geodatabases.

Anyone interested in this functionality can contact ESRI support and ask to be added to Bug ENH-000087562. The more users that want this functionality, the faster it will be done.

ScottFierro2
Occasional Contributor III

Dave Frame​ thanks for the info and I will be sure to bring that up to them when I speak with them today. Great to see this is getting attention and being worked on.

0 Kudos