Transferring files from a legacy project to an existing one as varbinary
Our team is currently transferring all functionality (+ some changes) from small and very old project A (almost code freeze) to project B (actively developed). As part of the data migration, there are about 10GB of files to be transferred.
Currently, project B stores files as varbinary in the database (not OK, will be migrated in a couple of months to an internal storage that provides an API). The files migration boils down to migrating physical files of project A to varbinary in project B.
The projects reside on different web servers which access different SQL Server instances. What I know for sure now is that a linked server is configured between the two SQL Server instances.
Project A and B will run side by side for a little while, so I need to support differential migration of data (real-time not required though).
Project A's files reside on the webserver and developers are not allowed to access any production server, except through the application's APIs.
Installing a new application on the production requires a lot of headaches (all apps are installed through an old-school pipeline), so the solution should involve using the existing applications.
What I have in mind for the migration:
- add an endpoint in application A that syncs the files in project A's database
- add an endpoint in application B that sync the files in its database using the existing linked server
An alternative that should be easier to maintain would be for application B to be able to read files directly from webserver A, but I am not sure that the security rules allow it.
Any ideas about how to improve this process? Or possible pitfalls? The only thing that concerns me is the transfer of large data over the linked server.