Filestream is not a good way of uploading/storing files??

Posted on 2013-02-01
Last Modified: 2013-02-05
I'm using SQL Server 2008's Filestream functionality to store files. Our DB server is separate from our web server in production...
1. From time to time, we get this error : network path is not found.
2. This error does not happen in staging since we have our web and database on the same server.
3. I've put debug steps and I asked our hosting server if there's a network intermittent connectivity issue and they said there isn't.

4. Per this link, port 445 needs to be open and our hosting server said it's not open.

5. This is from our hosting server
After taking a look at your servers, there does not appear to be any networking related issues. We do see that your database server only has 3GB free on it. Once windows gets below 10% free, it can start to slow down while accessing the disk. Depending on timeouts, it could be affecting this procedure. The server started with 30 GB of storage. Windows and MsSQL will typically use 10-20 GB depending on options.

6. Should I ask the hosting server to do this?

Any ideas on what might be causing this? Should I have port 445 opened? I researched this a lot and I think Filestream is a good way to go. Not sure why we're getting the error from time to time. Is filestream good for same server? "FILESTREAM data can be stored only on local disk volumes"

The error is this:
at System.Data.SqlTypes.SqlFileStream.OpenSqlFileStream(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize)     at System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize)     at System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access)     at UserControl_NonMemberFax.SaveLetterDatabase(Byte[] dataLetter, String fax, String patientId, Nullable`1 nonMemberProviderId)     at UserControl_NonMemberFax.btnSubmit_onClick(Object sender, EventArgs e)========The network path was not found
Question by:Camillia
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38846196
I've only ever stored Filestream data on disks mounted locally to the server (on a SAN, but mounted to an NTFS folder), but I don't see why you couldn't use a remote server. Port 445 is used for Windows SMB (a sharing protocol), so it's what's used to access files on a remote server. However, the thing about firewall rules is that they're all or nothing - if you're having periodic issues connecting, a firewall rule isn't to blame, as it either allows access or it doesn't. Since it generally works for you and you're having dropped connects from time to time, I'd look more at intermittent drive issues, as your hosting company is suggesting.

That said, are you accessing the files outside of SQL Server? Why not use in-line VARBINARY(MAX) fields directly in the table? I know there are some cases where FileStream is a better choice, but it's mostly because of accessibility to other processes, and I've always preferred an inline field where it's feasible.

Author Comment

ID: 38846670
>>Why not use in-line VARBINARY(MAX) fields directly in the table?
How can I do this? not sure what you mean by it. Do you have an example?

>>I'd look more at intermittent drive issues, as your hosting company is suggesting.
You mean adding more GB to the drive?
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38846786
You can try adding more space, though i'm not sure that will address intermittent timeouts. If it's a cheap option though, I'd advice that you try it.

To store the files in the table rather than in Filestream, just define the column as VARBINARY(MAX) without defining it as FILESTREAM as well. That way, SQL Server will store small files directly in the table, and larger binary streams elsewhere in the database.

Also, there are some best practices here - you may not have control of all of these since you're environment is hosted, but you may be able to improve performance if you do have some control:
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.


Author Comment

ID: 38846901
>>To store the files in the table rather than in Filestream
Is it ok to store the files in the table, rather than the filestream? I remember in sql 2000, it was adviced against that because the files would get corrupt (i think that was the reason)

Also, I read this somewhere "FILESTREAM data can be stored only on local disk volumes"
I'm thinking the issue we're seeing is because we have the database server and web server on separately...

But, if it's ok to save the file in the database table, that's what i will do then...
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38847518
In SQL 2000, there was no VARBINARY(MAX) - there was the IMAGE type (and TEXT instead of VARCHAR(MAX) ), and there were a number of concerns about using it - performance among them, but not the only by any means. I've used both of the new VAR types in a number of cases and they've always been great - as long as the row data is less than 8KB (a single page), it will store the large object data with the regular row data, and it will move it to large object storage (elsewhere in the data file) if it's larger than that. This allows quick access to smaller values in the field, while still allowing for storage of objects up to 2GB in size (as if you'd do that ;)

Filestream requires storage that's local to the SQL Server (your database server), not local to the server accessing it (your web server). In this case, I think you're configured correctly - if you decide that Filestream is the way to go, you can't place it on storage that's not local, so the fact that you currently have it configured means that it's set up correctly.

Author Comment

ID: 38847544
Thanks for the explanation of the Filestream. I'm not a DBA and dont have one...I kept thinking maybe I have it incorrectly....

I'm reading this article

So, if the files are less than 8KB (i think the article has it as 256K), it's ok to save them in the database? I have debug steps all over that ASP.Net page and in the stored proc...hoping I'd catch it.

Author Comment

ID: 38847572
So, you've stored files as VARBINARY(MAX) and they were fine? what's the limit that I can store a file in a column/table?

I might have to change my code to remove the fielstream and just use a table
LVL 28

Accepted Solution

Ryan McCauley earned 500 total points
ID: 38849547
The way our storage was set up, it actually ended up being faster for me to store the files in the tables directly than it was using Filestream - I'm not privy to how to storage is set up, but after deploying Filestream and experiencing some slowness, I did some side-by-side benchmarking and found table storage to be a bit faster, so I switched things and I'm much happier. Filestream may be much better in some cases, but this was on a SQL 2008 server (it may have come a ways since then) and a few years ago, so results may vary.

As far as VARBINARY(MAX), I believe the limit is around 2GB, whether you're using Filestream or storing it directly in the table itself. You can store any length you want in the table - I was just suggesting 8KB because that means SQL can store it in the same page as the data on disk.

But file integrity wasn't one of my problems - the file storage was completely solid and there was no corruption of any kind.

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question