Situation:
There is an application for a local network (developed in Delphi, it will be completed in Delphi). The application utilizes MS SQL Server.
A server (Windows) and mobile workstations (Windows laptops) are present. When the laptop is offline, the application should function offline (meaning that SQL Server is installed on each mobile workstation). When the laptop is online, the application operates with the server and its database.
Several thousand images (and potentially videos, 3D models, etc.) are part of the application, amounting to several gigabytes.

Question:
Where should the images, videos, etc. be stored? In a database or as separate files?
If separate files, what is the most effective method to synchronize upon connecting a laptop to the network?

I have extensively researched this seemingly straightforward topic, but I haven't been able to reach an educated decision for our specific case.

In your case, storing the images, videos, and other files separately from the database would likely be the most effective approach. Storing such large files directly in the database can lead to increased database size, slower performance, and potential data management issues.

By keeping the files as separate files, you can store them on the local file system of each laptop when offline and on the server's file system when online. This allows for more efficient handling of large files while also enabling synchronization upon connecting a laptop to the network.

To synchronize the files upon connecting a laptop to the network, you can consider implementing a file synchronization mechanism. There are various approaches to achieve this, including:

1. Offline File Sync: You can utilize a file synchronization tool or library that supports offline synchronization. These tools can detect changes made to files locally while offline and automatically synchronize them with the server's file system when the laptop reconnects to the network.

2. Differential Synchronization: Another approach is to implement a differential synchronization algorithm, which compares the local and server file systems to identify differences and synchronizes only the modified or new files. This can help minimize the amount of data transferred during synchronization.

3. Version Control System: Alternatively, you can leverage version control systems like Git to manage the synchronization of files. With Git, each laptop would have a local repository where changes are committed while offline. When online, the laptop can push the changes to the central repository hosted on the server, ensuring synchronization across all devices.


 considerations to help you make an informed decision:

1. File Size and Storage Capacity: Take into account the size of the files you are dealing with and the storage capacity of the laptops and the server. If the files are relatively small and each laptop has enough storage space available, storing them locally on each laptop might be more convenient. However, if the files are large and storage space is limited on the laptops, storing them on a central server might be a better option.

2. File Naming and Organization: Decide on a file naming convention and a folder structure that makes it easy to identify and locate specific files. This will help with synchronization efforts and general file management.

3. Synchronization Frequency: Determine how often you need to synchronize the files between the laptops and the server. Depending on the frequency, you might choose different synchronization mechanisms. For example, if the synchronization needs to occur frequently, using a real-time synchronization approach would be ideal.

4. Network Bandwidth: Consider the network bandwidth available when synchronizing files. Large files can put a strain on limited network bandwidth, potentially causing delays or interruptions in the synchronization process. You may need to optimize file transfer protocols or consider incremental sync strategies to minimize bandwidth usage.

5. Data Integrity and Security: Ensure the integrity and security of your files during synchronization. Implement mechanisms, such as checksums or digital signatures, to verify the integrity of transferred files and encryption for secure transmission.

few more considerations:

1. Backup and Recovery: Determine how you will handle backup and recovery of the files. Consider implementing regular backups of the files on both the laptops and the server to protect against data loss.

2. File Access and Permissions: Define the access levels and permissions for different users or user groups. Ensure that only authorized users have access to specific files or folders to maintain data security and confidentiality.

3. Conflict Resolution: Consider how conflicts between different versions of the same file will be resolved during synchronization. Implement conflict resolution strategies, such as versioning or timestamp comparison, to ensure that conflicting changes are handled appropriately.

4. Offline Data Caching: If the application needs to function offline, consider implementing a caching mechanism to store frequently accessed files locally on the laptop. This can improve performance and reduce the need for frequent synchronization when offline.

5. Scalability: Evaluate the scalability of your chosen synchronization solution. Consider how well it will handle increasing file sizes, numbers of files, and concurrent users accessing and synchronizing files.

6. Testing and Monitoring: Thoroughly test the synchronization process to ensure its reliability and effectiveness. Implement monitoring and logging mechanisms to track synchronization activities and identify any issues or errors.

The file system and the database are essentially the same. Therefore, it is advisable to store all the images securely in the blob fields of the database.

This approach offers several advantages. Firstly, it simplifies the backup process since all the data is stored in one location, the database. This means that backing up the database will automatically include the images, ensuring their safety.

Secondly, by storing the images in the database, data integrity can be easily monitored. The database management system can implement mechanisms for verifying the consistency of the images, ensuring that they are not corrupted or lost.

Lastly, the speed of accessing the data from the database is comparable to that of the file system. Modern database management systems are optimized for efficient retrieval, allowing for quick and seamless access to the images when needed.

Overall, using the blob fields of the database to store images not only provides convenience but also safeguards data integrity and maintains comparable performance to the file system.

To be completely open and honest, I have a vast number of files (images) saved in the table within the bytearray field. The invention of the database and its structure was not mine, and initially, I perceived it as a flaw. However, I now realize that it is incredibly convenient, especially when the data for the other fields are indexed. Even on an aging server with minimal usage, everything runs smoothly.

P.S. Interestingly, as the number of files to store increases, reading speed from the database surpasses that of the file system. This raises the question: where should files be stored? If the files are not within the table, on the same partition (disk, raid?), then first a link to the file is read from the database, followed by reading the file itself. With an increasing number of files, this process will undoubtedly slow down.