Sql-server – Guidance on strategy for serving files remotely using SQL Server

filegroupsfilestreamms accesssql server

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?

Best Answer

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.