How to create a table to store Filestream data
If a table needs to store FILESTREAM data then the table should have a ROWGUIDCOL column defined. This is used by the storage engine to keep track of instances in the NTFS file system. The column which will be used to store the Filestream data should have varbinary (max) data type and FILESTREAM attribute. In SQL Server 2008 varbinary (max) data type can store more than 2 GB of data. The TSQL Query below can be used to create the FileStreamTable table:
USE FileStreamDB
Go
CREATE TABLE dbo.FileStreamTable
(
FS_ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
FSData VARBINARY(MAX) FILESTREAM
);
How to insert data into FileStreamTable
Execute the below TSQL to insert the Filestream data into the FileStreamTable table in the FileStreamDB database.
USE FileStreamDB
Go
INSERT INTO FileStreamTable
VALUES(newid(), CAST (‘Inserting data into FileStreamTable……..’ As VARBINARY(MAX)))
Go
Once the data is successfully inserted you can see a new folder getting created under D:FileStreamFileStreamData folder.
Advantages of FILESTREAM
- BLOB data can now be stored in the NTFS system and it’s under the control of SQL Server 2008
- SQL Server will maintain the transactional consistency for all the BLOB data
- There are no limitations for the size of the file which can be stored in FILESTREAM . As long as you have space on the disk drive that much bigger FILESTREAM files can be stored
- When the FILESTREAM enabled database is backed up, all the BLOB files in the specified FileStreamData folder will also be backed up which will help the DBA to manage the BLOB easily
- Data can be easily accessed through TSQL and the NTFS streaming APIs with great streaming performance
Disadvantages of Filestream
- Database mirroring cannot be configured on database which has FILESTREAM enabled on it
- Database snapshots don’t snapshot FILESTREAM data which will be there in the FILESTREAM database
Conclusion
The FILESTREAM feature of SQL Server 2008 helps organizations to manage their BLOB data more efficiently as they can store the data in the NTFS file system and take advantage of the benefits of SQL Server’s transactional consistency. Database Administrators can now easily take the backup of FILESTREAM data in the organization by just taking the backup of the FILESTREAM database rather than taking a backup of each BLOB file independently. This allows them to save lot of time and more over there will no data loss by not backing up some files.
]]>