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

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
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Ryan McCauleyConnect With a Mentor Data and Analytics ManagerCommented:
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.
Ryan McCauleyData and Analytics ManagerCommented:
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.
CamilliaAuthor Commented:
>>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?
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Ryan McCauleyData and Analytics ManagerCommented:
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:
CamilliaAuthor Commented:
>>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...
Ryan McCauleyData and Analytics ManagerCommented:
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.
CamilliaAuthor Commented:
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.
CamilliaAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.