SQL Server FileStream Advantages and disadvantages

LOB Advantages:

1, ensure the transaction consistency of large objects.

2, backup and restore including Big data object, it can be point-in-time recovery.

3, all data can use a storage and query environment.

LOB Insufficient:

1, large objects in the cache accounted for a very large buffer.

2. Updating large objects results in a large amount of data fragmentation.

3. The database file may become very large.

FileStream Advantages:

1. Large objects are stored in the file system, and only 48 bytes of pointers are saved in the database.

2. Large object data is consistent with the structure data in the transaction.

3. Large objects can be accessed through APIs, T-SQL, and NTFS streams to improve performance.

4. Large objects are limited only by the NTFS volume size, not by the database LOB object 2GB.

FileStream Insufficient:

1. Database mirroring cannot be used for databases that contain file stream data.

2. Database snapshots cannot include file stream filegroups, so file stream data is not available. A select request to it will raise an error.

3. File stream can not be encrypted by SQL Server natively.

SQL Server FileStream Advantages and disadvantages