You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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?
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@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 ?
@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.
@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.
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.
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.
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>.
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.
@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.
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.
Activity
saurabh500 commentedon Mar 20, 2017
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 commentedon May 23, 2017
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 commentedon Aug 21, 2017
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
ErikEJ commentedon Aug 22, 2017
@evil-shrike That only refers to "database cloning" - not engine support for filestream
dnfield commentedon Aug 23, 2017
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 commentedon Apr 12, 2018
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 commentedon Apr 24, 2018
@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 commentedon Apr 25, 2018
@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 commentedon Jun 5, 2018
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 commentedon Jun 7, 2018
@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 commentedon Jun 8, 2018
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 commentedon Jun 8, 2018
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 commentedon Jun 8, 2018
GH Issue #15652 SqlFileStream Implementation (#29879)
keeratsingh commentedon Jul 6, 2018
The
Windowsimplementation for SqlFileStream is complete, #29879 ports the SqlFileStream capability on .NET Core only onWindowsfor the reasons explained above.SqlClient throws
SqlFileStream is not supported on this platformexception forNon-WindowsPlatforms.msmolka commentedon Aug 13, 2018
Just wondering is it any way to use this changes right now, or we should wait till .NET Core 2.2?
keeratsingh commentedon Aug 15, 2018
@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.0and 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 commentedon Mar 26, 2019
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 commentedon May 15, 2019
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 commentedon Jul 31, 2019
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.
Address PR #4336 review feedback (pipeline version computation)