A document upload forces a choice about transactions and recovery. Storing files in the database or on disk changes both. SQL Server can hold bytes in varbinary(max), manage FILESTREAM data in the file system, or store metadata while an application manages files. Each choice changes backups, permissions, and failure recovery.
Clarify the Access Pattern Before Storing Files in the Database
Start with file size, count, read frequency, update frequency, and how clients retrieve data. Small documents frequently read with row metadata can be convenient in the database. Large media files streamed to many users can favor a different path. Legal retention, versioning, and search needs also shape the design.
I ask whether the application needs a file and its metadata to commit or roll back together. That transactional requirement is a genuine strength of storing files in the database. If the application only needs a durable link to an object store, keeping every byte in the main data files can add operational weight without providing useful consistency.
Consider varbinary(max)
varbinary(max) stores binary values under SQL Server management, with the exact row and off-row layout depending on size and table settings. It simplifies transactional consistency and follows database backup and security controls. It can also enlarge data files, backups, restore time, and buffer or I/O pressure when queries pull the payload unnecessarily.
Use explicit columns in queries so ordinary list pages do not fetch file bytes. A separate payload table keyed by document ID can keep common metadata access narrow. I test backup and restore time as part of this option, because storage capacity alone does not describe recovery cost.
Understand FILESTREAM
FILESTREAM stores varbinary(max) values in a SQL Server-managed filegroup on the Windows file system while retaining database transaction integration. It supports streaming access and can handle large objects. It requires setup, specific table structure, backup planning, and feature compatibility checks. FILESTREAM files should be managed through SQL Server, not modified as ordinary application files.
This catalog query identifies FILESTREAM filegroups and files in the current database. It is read-only and helps confirm whether the feature is configured. The decision to enable it belongs in a tested architecture plan.
SELECT fg.name AS filegroup_name,
fg.type_desc, df.name AS logical_name,
df.physical_name
FROM sys.filegroups AS fg
LEFT JOIN sys.database_files AS df
ON df.data_space_id = fg.data_space_id
WHERE fg.type = 'FD';Know What FileTable Adds
FileTable builds on FILESTREAM to expose files through a Windows file-system-style interface while retaining SQL Server metadata and transactional integration. It is useful when an application or tool needs that interface, but it adds configuration and security considerations. Do not choose it simply because the name sounds familiar.
Check feature support, directory namespace, permissions, and backup procedures. I prefer a proof of concept that exercises the actual client access method. A clean SQL query to the metadata does not prove Windows file access behaves as the application expects.
Use External Storage Deliberately
Keeping bytes outside SQL Server can reduce database size and let a file or object service handle delivery and scaling. The database stores a key, path, hash, size, and state. The application must then coordinate consistency: a database commit can succeed while the file upload fails, or a file can remain after its metadata is deleted.
Design idempotent upload and cleanup workflows. Use immutable object identifiers rather than trusting a mutable path alone. I store a checksum and verify it when content integrity matters. Backups must cover both the database and the file store at compatible points in time. External storage simplifies one layer while creating coordination work in another.
Compare Recovery Behavior for Storing Files in the Database
For varbinary data, a database backup includes the bytes. FILESTREAM participates in SQL Server backup and recovery according to its filegroup configuration. External files require a separate backup or replication plan and a tested way to rejoin them with metadata after restore. The right choice depends on recovery point and restore-time objectives.
I run a restore test with sample files for each candidate architecture. Can a user open an older document after a point-in-time database restore? What happens to files uploaded after that recovery point? Those questions are more useful than comparing storage cost per gigabyte alone.
Keep Permissions Coherent When Storing Files in the Database
Database permissions can govern varbinary access. FILESTREAM and FileTable have additional Windows access paths and configuration to review. External storage needs its own identity, authorization, and signed delivery design. Do not expose raw internal paths to clients as a substitute for access control.
Audit who can read, upload, replace, and delete files. The database row and byte store should agree on ownership and retention. I include malware scanning at an appropriate ingestion point without scanning active SQL Server database files in a way that disrupts the engine. Security is part of the workflow, not an afterthought.
Test the Real File Sizes
Benchmark upload, download, backup, restore, and metadata queries with the actual distribution of file sizes. A design that excels with 20 KB PDFs can behave differently with multi-gigabyte videos. Measure concurrent access and cache behavior. Include network costs between application, SQL Server, and file storage.
The query below finds large payloads in a varbinary table and shows the size distribution to examine. Adjust the table and column names. It is a starting point for capacity planning, not a recommendation to scan a huge production table during peak time.
SELECT TOP (20) DocumentID,
DATALENGTH(ContentBytes) AS content_bytes
FROM dbo.Documents
WHERE ContentBytes IS NOT NULL
ORDER BY DATALENGTH(ContentBytes) DESC;Choose With Operations in Mind
List transaction needs, client access pattern, file size, backup and restore time, security ownership, and cost for each option. Then test the two strongest candidates. Avoid choosing based on a single rule such as always store files on disk or always keep them with rows. SQL Server offers several paths because applications have different needs.
Storing files in the database can be the simplest way to preserve consistency for moderate payloads. External storage can be the right scaling boundary for large delivery workloads. FILESTREAM and FileTable bridge requirements in specific cases. The winning choice is the one the team can operate and restore correctly.
Related reading on this blog: Slow Filestream Data Cleanup. What Should We Do? and Images and media file management in SQL Server and MySQL: Coding Media Management Tools is Never Easy.
Storing files in the database is not a yes-or-no rule, it is a recovery and access design choice.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
