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
  • 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:
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.


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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Complex SQL script 1 41
MS SQL Inner Join - Multiple Join Parameters 2 31
SQL server 2008 and after encryption method 32 43
SQL Server 2012 r2 - Sum totals 2 21
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

815 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now