If your database design calls for the immigration of files such as audio, images or other document types, then you’ll want to investigate the SQL Server feature called Filestream.
In the past, immigrating a database with unstructured binary data files meant providing links to their location on the disk drive. This is not an ideal solution since the database cannot manage those files adequately. It can’t create backups or prevent alterations made by external programs. An alternative option brought the binary data directly into a SQL Server table using the varbinary(max) field. This allows the database to manage the file but makes accessing it from external applications difficult while simultaneously bloating the size of the database and hogging resources.
This is where Filestream comes into play. When Filestream is implemented on a database, varbinary(max) columns use the file system for storage while SQL Server still maintains the ability to manage other attributes of the file. It does this by creating a specially designated file group that contains system directories called data containers. These Filestream filegroups are integrated directly into the database engine and allow SQL Server to use all of the backup and recovery models to protect the integrity of the binary files as well as the structured data.
FileTables build upon the Filestream filegroup by supporting the Windows file namespace and providing compatibility with Windows applications. This means that Windows apps can makeuse of the data directly while still benefitting from the managementcomponents provided by SQL Server.
Sources:
https://www.linkedin.com/learning/designing-database-solutions-for-sql-server-2016/store-filestream-and-filetable-data
Comments