Use SQL Server database as Windows file system
-
Are there and drivers for Windows that can use a SQL Server database as a file system?
The Sharp Ninja
-
Are there and drivers for Windows that can use a SQL Server database as a file system?
The Sharp Ninja
-
That sounds a lot like this WinFS - Wikipedia[^]
-
Are there and drivers for Windows that can use a SQL Server database as a file system?
The Sharp Ninja
There is no reason why you couldn't store files as "blobs" in an SQL Server database, and use additional tables to add attributes to the "blobs". This would give you some of the WinFS capabilities, at a much lower cost. Another way would be to store only the pathnames (rather than the "blobs") for each file. This would allow access by standard file APIs in addition to the API provided by your database, and may be useful for backward compatibility. One problem that arises here is synchronization - ensuring that new files are added to the database, and that deleted files are removed from the database. Either way, you would have to write your own file access API - Windows provides no support for accessing files via anything but their pathnames (and directories).
Freedom is the freedom to say that two plus two make four. If that is granted, all else follows. -- 6079 Smith W.
-
Close. It's the same FS that is found in [F2FS](https://en.wikipedia.org/wiki/F2FS). :)
Freedom is the freedom to say that two plus two make four. If that is granted, all else follows. -- 6079 Smith W.
-
There is no reason why you couldn't store files as "blobs" in an SQL Server database, and use additional tables to add attributes to the "blobs". This would give you some of the WinFS capabilities, at a much lower cost. Another way would be to store only the pathnames (rather than the "blobs") for each file. This would allow access by standard file APIs in addition to the API provided by your database, and may be useful for backward compatibility. One problem that arises here is synchronization - ensuring that new files are added to the database, and that deleted files are removed from the database. Either way, you would have to write your own file access API - Windows provides no support for accessing files via anything but their pathnames (and directories).
Freedom is the freedom to say that two plus two make four. If that is granted, all else follows. -- 6079 Smith W.
If they are large(ish) blobs you can use [Filestream](https://docs.microsoft.com/en-us/sql/relational-databases/blob/filestream-sql-server?view=sql-server-ver15), which is a kind of a "best of both". Although I only used it once as a prototype, never has a real use-case for it, so not sure what the downsides are.
-
Are there and drivers for Windows that can use a SQL Server database as a file system?
The Sharp Ninja
Roll your own.
-
Are there and drivers for Windows that can use a SQL Server database as a file system?
The Sharp Ninja
I'd suspect it would be a pretty low performance file system - it's going to hammer the bandwidth as soon as files get big, or many users access them - and it is guaranteed to be slower than the equivalent local storage. Storage is cheap at the moment - you can get internal 1TB SSD drive for under £100 - so I'd be tempted to set up a file server instead; relatively low processor requirements (compared to SQL) and full SMB shares from Windows or Linux boxes. Or buy a NAS ready loaded.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
-
Are there and drivers for Windows that can use a SQL Server database as a file system?
The Sharp Ninja
You could store them as standard files but maintain a database that had the file location and information about the file. A Windows Service app could process file system change notifications to keep the database updated.
-
Are there and drivers for Windows that can use a SQL Server database as a file system?
The Sharp Ninja
Yup, in multiple tastes. You could use filestreams and access them like a FileSystem; needs a Windows share name and some configuration. See https://www.sqlshack.com/filestream-in-sql-server/[^] Easy to set up, and nice to have if you have a lot of blobs in your db, like images. Alternative, you could look at the Dokan libraries; my article on it is outdated, but there was an open source interface to SQL Server. Dokan at the time was limited to roughly 50MB/s. Last option might be FUSE, using Sql Server. See FUSE(file system in userspace) for Microsoft-SQL using C#[^]. Enjoy :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
Yup, in multiple tastes. You could use filestreams and access them like a FileSystem; needs a Windows share name and some configuration. See https://www.sqlshack.com/filestream-in-sql-server/[^] Easy to set up, and nice to have if you have a lot of blobs in your db, like images. Alternative, you could look at the Dokan libraries; my article on it is outdated, but there was an open source interface to SQL Server. Dokan at the time was limited to roughly 50MB/s. Last option might be FUSE, using Sql Server. See FUSE(file system in userspace) for Microsoft-SQL using C#[^]. Enjoy :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
This is the response I was looking for! Going to definitely check these out.
