June 19, 2024 at 9:49 pm
though I'd recommend at least considering storing files w/ operating system and just storing paths in the database
That’s what filestream does, stores the files directly on a dedicated ntfs folder structure and not in the database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 19, 2024 at 10:02 pm
Perry,
Sorry but you said "developer will work". Now I'm totally confused again... I have read things... nothing says ANYTHING about how to migrate this data. It tells me how to set it up, how to insert to it, how the storage works... it doesn't say anything about how to move it from one platform to another. So, now you're saying that I cannot use the sql developer db migration tool to convert the data to Oracle? If there is some magically documentation on how to migrate filestream to Oracle.... then please, I'm more than happy to read it.
June 19, 2024 at 10:06 pm
I dunno why I can't wrap my head around this... but I'm so confused how creating a brand new table that ISN'T filestream... still keeps the files intact. Where do they go if they aren't in filestream?
just have good read up and practice on your own system
blobs can typically be stored in the database but this bloats the db size and import\export is through .net streams. Workable if files are small
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 20, 2024 at 7:19 am
The customer does not want to spend money on code changes really.... so I have to keep everything basically the same but get rid of filestream and move to either RDS SQL Server or Oracle.
I suspect RDS storage is going to cost a lot more than S3 storage. If the files are large, they might be very happy to spend money on code changes once they look at their AWS bill.
Also, if they do not want code changes why are you converting to Oracle? This is looking like an Entity Framework Code First project.
June 20, 2024 at 11:51 am
Ken,
Not sure why it won't let me quote anyone... how are you guys putting the quotes in the responses?
Moving to Oracle actually won't require much code change as long as we keep all the object names the same.
I'm just getting frustrated because I don't understand why I can't get a straight answer on how to migrate off this filestream. Nothing I am reading is giving me any clues on how you get stuff out...aside from how the API's call the files. I suppose I could sit in the front end and download each file by itself... but then I lose the unique identifier association from the database. And it would be incredibly tedious.
I am also still confused on the other response which said I should create a new table with a varbinary field and import the data into that table.
June 20, 2024 at 1:31 pm
Yes, agreed.
But the OP is planning a move to an RDBMS that doesn't have a filestream option to my knowledge.
Since the OP is planning a move to RDS (which just sunk in!), local filesystem isn't an option. It has to be either cloud storage or stored in a table (in which case Steve's method is the best: https://www.sqlservercentral.com/forums/topic/removing-filestream-how#post-4429828).
The OP mentions not wanting to spend money on changing code, but code is almost certainly going to change if they move to Oracle.
June 20, 2024 at 4:58 pm
Thanks Ratbak, yes that is basically it in a nutshell. The developers have analyzed the code and moving to oracle doesn't impose much of a challenge supposedly.
I did use Steve's method today and I created a new regular table with varbinary(max) and imported the data into that table and am now trying to get the application to use the new table and see if it works...but they are using some sort of strange entity framework module and NOW that is causing problems. Which blows their former theory out of the water about no code changes....
June 20, 2024 at 5:38 pm
We've given you straight answers, but I don't think you understand Filestream, so they don't make sense. You have to invest a bit to understand how this works to learn what migration might make more sense to you.
June 20, 2024 at 5:57 pm
I understand it is storing links to the files and they are stored on the filesystem as part of the filestream filegroup. I understand that the APIs and UI interface is what reads the files... but I'm not sure what else I need to understand in order to know what the method is for migrating away from using it.
I wanted to get more detailed information about how the process works by me creating a brand new table with varbinary and then doing the insert into that table from the filestream table... I'm assuming that converts it from being stored in the file system and now they are actually stored in the table? I also have a grasp on the process to remove filestream after I magically figure out how to migrate the data to a "normal" binary table.
June 20, 2024 at 6:03 pm
that's incorrect. It's not links to files. It's streamed a file into a hidden/inaccessable place in the file system that can be read back from t-sql and a win32 api. These aren't files per se that you can access.
June 20, 2024 at 6:08 pm
Ok thank you for the correction.
So, can I accomplish what I want to do by simply creating the new table with the varbinary(max) field set without the filestream and inserting the data from the filestream table to that table?
You can do that to remove filestream. The data then lives in the db.
If you migrate to a new architecture where you want links and files, you need to stream out the binary data into a filename and capture/record the path.
June 20, 2024 at 6:27 pm
Thank you.
June 20, 2024 at 7:24 pm
We've given you straight answers, but I don't think you understand Filestream, so they don't make sense. You have to invest a bit to understand how this works to learn what migration might make more sense to you.
This is what i've been saying all along
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 20, 2024 at 7:26 pm
that's incorrect. It's not links to files. It's streamed a file into a hidden/inaccessable place in the file system that can be read back from t-sql and a win32 api. These aren't files per se that you can access.
They are links in the form of rowset and column guids, these guids are based upon the LSN of the transaction that created the file(s).
Paul Randal details in this post
https://www.sqlskills.com/blogs/paul/filestream-directory-structure-where-do-the-guids-come-from/
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply