Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
I was really interested in the FILESTREAM technology when it came out. Unfortunately, the access from only Win32 APIs made this a pain for many people. I get why this is needed, but without having some tooling to make it easy to add/edit/delete blobs.
In SQL Server 2012, we got FileTable, which simplified development and allowed everyone to easily get data in and out of SQL Server.
This post looks at a few differences between the technologies.
FILESTREAM is essentially a folder surfaced as a column in SQL Server. You can create a filegroup to hold FILESTREAM files, and these are paths. The path is managed by SQL Server, and when you designate a column as containing FILESTREAM data, a new folder is created.
None of that matters, as you should NEVER go to these folders, but this is also how some other things in SQL Server (like In-Memory OLTP) also work.
Access to this data is with a Win32 API, and it’s semi-complex. Not hard, and really you can use the sample code (with some additional error handling) to do the work you need. On the application side, you need to do some stream handling and manipulation, but that’s really not difficult. It can be cumbersome, but it’s easy.
FileTable was an additional layer that gives you SMB, or Windows Explorer, access to the data in the FILESTREAM column. Except, you can’t make a FileTable column, you create a FileTable, which has some restrictions, limitations, and required columns. You can add some things to the table, but really you want your FileTable to be a vertical partition of your regular OLTP data. Separate out the blog stuff from other items in your entity.
Both save your data in database backups, both are more efficient for semi-large blogs moving in and out of SQL Server.
Neither works in the PaaS cloud, though they could. I’d hope that FileTable especially would be added, with some enhancements to smooth the way a FileTable is structured, ubt I think with the focus on database level technologies (contained in a db), this technology is likely never getting any enhancement.