SqlFileStream roadmap

[msmolka]

Hello
I'd like to ask are there any plans to implement SqlFileStream. At this moment this is the only part that makes me to stop migrate to core base.

[saurabh500]

We need to investigate this. Right now its not on the roadmap and if there are any Windows specific dependencies, we may not bring it in Core.

[dnfield]

I've actually been interested in this for a while and have taken some time over the last few days.

Unfortunately, FILESTREAM uses several NTFS and NT specific system calls to manage access to the file. Also, unfortunately, as of this writing the latest MSSQL CTPs for Linux don't support FILESTREAM, and there's little clarity as to whether that's on the roadmap or where it might be (strangely, you can restore a database that supports FILESTREAM but FileTable doesn't seem to be supported).

There are two problems here: it's not clear that replacing the NT specific APIs would respect transactional integrity (or even work at all), and it's not clear that they could ever work from a non-Windows environment anyway. Because of these facts, I don't see SqlFileStream being supported any time in the near future for core.

There is some precedent for Windows Only type of low level, for example in System.Net.Socket.IOControl. SqlFileStream could perhaps take a similar path. Alternatively, it might be possible to build a specific SqlFileStream NuGet package, but have it only be supported/runnable on Windows. I'm not sure how valuable this would be though - if you're going to P/Invoke in a Windows only way to begin with, why not just P/Invoke to a .NET 4.6.x dll?

[evil-shrike]

It's a very pity that SqlFileStream isn't supported on .NET Core.

@dnfield Hi. Are you sure that FILESTREAM isn't supported in MSSQL on Linux?
It's mentioned here: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-whats-new

Improvements from Service Pack 1 in this CTP1.1 release:
Database cloning for CLR, Filestream/Filetable, In-memory and Query Store objects.

[ErikEJ]

@evil-shrike That only refers to "database cloning" - not engine support for filestream

[dnfield]

I forgot to mention this in my original comment, but part of the issue is that if you look at the reference source for the existing class, it makes some ioctl calls. These kind of things are tough to make portable - may be possible to abstract them, but it doesn't make sense to even try that without support in SQL Server for Linux. If/when that happens, it'd make sense to implement this in core. Otherwise, you can always get the blob through the engine (not that great but works as a fallback) or use some other form of transacted file storage...

[dnfield]

Related: microsoft/mssql-docker#47

Documentation here: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes clearly states that SqlFileStream not supported on Linux MSSQL.

[keeratsingh]

@dnfield @msmolka @evil-shrike
So here are my findings so far on SqlFileStream on .Net Core.
For SqlFileStream to work we need to acquire and supply two pieces of information:

• Path to the destination file, This can be acquired using PathName method, bear in mind this is not a UNC path to the physical file system on the server.
• Transaction Context Token, The path above is just a bogus path that has meaning only in the context of this transaction which SQL Server can use to map to the real file in the file system. Secondly, we need a token that identifies the NTFS file system transaction that SQL Server initiated behind the scenes, which we obtain with the GET_FILESTREAM_TRANSACTION_CONTEXT function.

Scenario 1: Windows Client, Windows SQLServer
This transaction context can be supplied via the Extended Attribute Buffer (eaBuffer) argument in case of NTCreateFile, which is a Windows-specific method to acquire the handle to the file, therefore it should be feasible to get SqlFileStream working in .Net Core for Scenario 1.

Scenario 2: Linux Client, Windows SQLServer
I am still investigating feasibility of scenario 2, as @dnfield already mentioned there are a lot of Windows specific system calls with the SqlFileStream implementation on Windows, also I am yet to determine, if indeed there exist equivalent Linux APIs, and if there would be a way to supply the Transaction Context Token via the API. Also, SqlFileStream would require the user to setup Integrated Security on the client.

Scenario 3: Windows Client, Linux Server
Scenario 4: Linux Client, Linux Server
As @dnfield already pointed out SqlServer on Linux doesn't support FileStream yet, so the scenarios 3 and 4 are infeasible until FileStream is supported by SqlServer on Linux.

To summarize, so far I have only been able to determine feasibility of scenario 1, I will continue working on determining feasibility for scenario 2.
I wanted feedback from the community as to what are the typical use cases for SqlFileStream, are you planning to use scenarios 2,3 and 4 extensively ?

[msmolka]

@keeratsingh currently my only usage is with Scenario 1. And the only stop to move from full framework to .NET Core is missing this part. The Scenario 2 would be nice to have to check other than IIS possibilities for web server but it is not necessary to move out from Full Framework.

[keithnicholson]

So, if this is not a big request, how are developers choosing to load and stream files/documents/images to SQL Server? Directly into VARBINARY(MAX)?

[evil-shrike]

@keeratsingh Given the fact that MSSQL on Linux doesn't support FileStream yet I'd expect the feature to work in all other cases - "Any Client - Windows Server". Also it'd be nice to get rid of the requirement of using "Integrated Security" (it was always too tedious).
But it's just speculating, I can't say that we really depend on the feature.

[keithnicholson]

Okay, I'm just now noticing the PR by @keeratsingh prior to my comment from 3 days ago. My apologies. This is exciting to see work being done on making this work on Windows (Scenario 1) . How can I participate in this effort? Will there be a way to add this as an extension without needing to do a full Core upgrade? I will do a clone, but this will be my first foray into System.Data and I suspect it will take a little to come up to speed.

[dnfield]

I've worked around this by creating services that can consume the file and any metadata and then use .NET Framework to actually use the SQL FileStream data. These can then be run either over local IPC or over TCP/HTTP/etc.

To be honest, I'm not confident that there's any meaningful roadmap to being able to use this in a cross platform way outside of that kind of solution. The existing implementation relies on some low level Windows (and NTFS) specific calls that need to be coordinated between server and client. This will require input from the SQL Server team to really make happen - it's not something that can happen in .NET Core all by itself because of the coordination magic that happens with the ioctl calls handling specific NTFS properties.

[keithnicholson]

Thanks for your reply. I discovered a pr on github for corefx this morning that is trying to create only the windows scenario with appropriate warnings for cross platform. I’ll post back here if anyone wants to know how it appears to be going. Best wishes, Keith Nicholson

…

________________________________ From: Dan Field <notifications@github.com> Sent: Friday, June 8, 2018 9:09:34 AM To: dotnet/corefx Cc: Keith Nicholson; Comment Subject: Re: [dotnet/corefx] SqlFileStream roadmap (#15652) I've worked around this by creating services that can consume the file and any metadata and then use .NET Framework to actually use the SQL FileStream data. To be honest, I'm not confident that there's any meaningful roadmap to being able to use this in a cross platform way outside of that kind of solution. The existing implementation relies on some low level Windows specific calls that need to be coordinated between server and client. This will require input from the SQL Server team to really make happen - it's not something that can happen in .NET Core all by itself. — You are receiving this because you commented. Reply to this email directly, view it on GitHub<https://github.com/dotnet/corefx/issues/15652#issuecomment-395772349>, or mute the thread<https://github.com/notifications/unsubscribe-auth/ACIBMFndaJD9qIKV4t09izdl2EM7QpB4ks5t6oWegaJpZM4LyUqx>.

[keeratsingh]

The Windows implementation for SqlFileStream is complete, #29879 ports the SqlFileStream capability on .NET Core only on Windows for the reasons explained above.
SqlClient throws SqlFileStream is not supported on this platform exception for Non-Windows Platforms.

Client	Server	Feasible	Comments
Windows	Windows	Yes	Implemented by this PR.
Windows	Linux	No	SQL Server on Linux does not support FileStream.
Linux	Windows	No	Currently there is no way to open a file over SMB and pass the Extended Attributes.
Linux	Linux	No	SQL Server on Linux does not support FileStream.

[msmolka]

Just wondering is it any way to use this changes right now, or we should wait till .NET Core 2.2?

[keeratsingh]

@msmolka These changes will be part of .NET Core 3.0, you can still download the pre-release alpha version of the core-sdk 3.0 and test the changes if you wish to do so, but keep in mind since the version linked above is an alpha version I wouldn't recommend using it in production.

[subbiahkk]

I am trying to Implement SQLFileStream on .Net Core 3.0 Preview version. I am using VS2019 Preview and .net core Preview 3.0. I am running the application in Windows 10 VS2019 iisexpress and getting Access Denied Error while trying to read the stream from the Path using (var fileData = new SqlFileStream(this.transactionContext.path, this.transactionContext.trx, FileAccess.Read))

But the same code is working in Regualar .Net Framework with same Privilleges.

Any idea to resolve this?

[divega]

As recently announced in the .NET Blog, focus on new SqlClient features an improvements is moving to the new Microsoft.Data.SqlClient package. For this reason, we are moving this issue to the new repo at https://github.com/dotnet/SqlClient. We will still use https://github.com/dotnet/corefx to track issues on other providers like System.Data.Odbc and System.Data.OleDB, and general ADO.NET and .NET data access issues.

[David-Engel]

This is done in the latest .NET Core 3.0 (System.Data.SqlClient) and Microsoft.Data.SqlClient previews and will also be in their GA releases.