0

Thanks for the help. "I" am the IT department for a small business and my ignorance may keep me from asking the right questions so please bear with me.

Currently, we are using MS Access front and back - transitioning to SQL Server Express. For now, we'll be keeping MS Access as the front-end, keeping in mind that eventually, the data will be consumed in various front-ends such as tablets and cell-phones. We have all kinds of documents "attached" or associated with various entities such as Projects, Invoices, Suppliers, Employees, etc. Currently, I simply store the documents on a share and refer to them by building a path using a simple naming convention based upon the entity name and key for each record. The files are simply opened on demand. This has worked great in a LAN environment. Now that we will be accessing the data via SQL Server remotely, I have looked at FILESTREAMs and FILETABLEs and my head is spinning a bit. I'm not sure if that's the way to go. Also, FTP and/or VPN. I'm not sure if a VPN will work for us in the future because I don't know about setting that up for mobile devices or if that's even a viable option. I have the same concerns using FTP. We are a small company and I'm attempting to keep expenses low and manage this myself if possible. Let me know if you need more details. What are your thoughts?

Improve this question
4
  • Based on most of what you described, the short of it is that your best bet would be File Tables. However, since you're talking about SQL server express edition, you may have no choice but to go with an external file share and only store the URL in the database... Simply because express edition databases are very limited in size.
    – Eitan Blumin
    Jul 17, 2020 at 16:40
  • @Eitan. From what I have gathered, using File Tables does not count against the max size limit in SQL Server Express. See link below. If I go ahead and learn how to use them, will it serve files over a remote connection? I have not been able to find any info or examples of actually querying the file data itself. social.msdn.microsoft.com/Forums/sqlserver/en-US/…
    – Tom Schreiner
    Jul 17, 2020 at 18:12
  • File Tables is essentially a File Share managed by SQL Server. The data is stored in the filesystem on the SQL Server, and accessible both through TSQL and through regular file share access.
    – David Browne - Microsoft
    Jul 19, 2020 at 17:28
  • @David Thanks for the response. I seem to be on my way using ADO.Stream and simply pulling from the file_stream field in my file table. Is there any reason that I should not pursue this avenue?
    – Tom Schreiner
    Jul 21, 2020 at 12:22

2 Answers 2

Reset to default
1

Putting files inside your database tables is almost always a bad idea. Your database server is not a file server.

Store the files on a file server of some sort, and then store the location of the files along with other metadata you might need in the database. Which you're already doing - so keep doing that, you're just going to change where/how you store the files (maybe).

And please, for the love of all you find precious, do not use FTP. It's broken and insecure. Use SFTP, Azure blob storage, or other secure, sustainable, scalable file storage.

Improve this answer
3
  • I suppose that my biggest hurdle is not where the files will be stored, but maintaining the ability for a user to open and (maybe) save them on demand as they are now doing and to also upload files. As of now, we attach mainly PDF files such as an invoice or statement. These may later be referred to/opened and that is what I am trying to achieve from a remote location. I even thought about looking into OneDrive or of the sort to accomplish this. I was just hopeful that I could simply query the self-same back-end for the files themselves. Make sense?
    – Tom Schreiner
    Jul 17, 2020 at 18:22
  • Presenting a means for the user to open and add files to the system would be the task of your user-facing application, not the database itself. If you store URIs or UNC share paths, it'd probably be as simple as creating a hyperlink to click (for opening). Uploading is a bit more work but there are frameworks and libraries that can make it a lot easier.
    – alroc
    Jul 17, 2020 at 18:42
  • @ alroc Thanks for the response. I seem to be on my way using ADO.Stream and simply pulling from the file_stream field in my file table. Is there any reason that I should not pursue this avenue? You seem to be against using the database as a file server though that is not its main purpose. For what it's worth, I created a separate database with the sole purpose of serving up the files. Are there any specific concerns that you have? Thanks!
    – Tom Schreiner
    Jul 21, 2020 at 12:22
0

Thanks for the input. I ended up going with a combination of a file table and ADO stream. For now, the user will only have read-only access to documents from remote locations. The solution simply reads the file_stream field into an ADO stream object, saves the file to the user's temp folder, and shells it out. It seems to work fine. I do appreciate your time. :)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.