Solved

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

Posted on 2013-02-01
8
1,656 Views
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.
http://johonline.wordpress.com/tag/the-network-path-was-not-found/

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?
http://msdn.microsoft.com/en-us/library/dd283098.aspx


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
0
Comment
Question by:Camillia
  • 4
  • 4
8 Comments
 
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.
0
 
LVL 7

Author Comment

by:Camillia
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?
0
 
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:

http://msdn.microsoft.com/en-us/library/cc645923.aspx#best
0
 
LVL 7

Author Comment

by:Camillia
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...
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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.
0
 
LVL 7

Author Comment

by:Camillia
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 http://research.microsoft.com/apps/pubs/default.aspx?id=64525

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.
0
 
LVL 7

Author Comment

by:Camillia
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
0
 
LVL 28

Accepted Solution

by:
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.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 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

19 Experts available now in Live!

Get 1:1 Help Now