Creating File Stream on Existing Table

Creating File Stream on Existing Table

FILESTREAM is a feature of SQL Server. It makes easier for storing large unstructured data files such as documents, PDFs, and images in a structured database.
There are three steps needed to enable FILESTREAM on the database. First we need to turn on the FILESTREAM feature, second we need to turn on the feature on the database, and then last let the database know where to store the data on the file system.

1. Enabling FILESTREAM on an existing instance
a. Create a file share for FILESTREAM.
b. Turning on FILESTREAM
i. Go to SQL-Server configuration tools >> SQL Server Configuration Manager.

Capture

ii. In the list of services, right-click SQL Server Services.
iii. Right click on the SQL Server Service (instance name) and click Properties
iv. In the SQL Server under property dialog box, click file stream and go through the options.
1. Select the ‘Enable FILESTREAM’ for Transact-SQL access check box – This turns FILESTREAM on.
2. If you want to read and write FILESTREAM data from Windows, select “Enable FILESTREAM for file I/O streaming access.”
3. If remote clients must access the FILESTREAM data that is stored on this share, select the “Allow remote clients to have streaming access to FILESTREAM data.”
4. Click Apply.

Capture
v. The feature is enabled, but we now need to set the appropriate access levels and type in the following and click Execute when done:
1. EXEC sp_configure filestream_access_level, 2
2. RECONFIGURE
vi. Finally, restart the SQL Server service to enable the changes.

2. Enabling FILESTREAM on an existing database
a. Add the File Group for FILESTREAM to the database and tell the database that this file group contains FILESTREAM objects
i. ALTER DATABASE DBNAME
ii. ADD FILEGROUP FILESTREAMGroupName
iii. CONTAINS FILESTREAM
iv. GO
b. Tell the database which directory to use.
i. ALTER DATABASE DBNAME
ii. ADD FILE (NAME=’FILESTREAMName’, FILENAME=’System Drive:\FolderName’)
iii. TO FILEGROUP FILESTREAMGroupName
iv. GO

3. Enabling FILESTREAM on an existing table
a. Create a ROWGUID column
i. Use DBNAME
ii. Alter table TableName
iii. Add columname uniqueidentifier not null ROWGUIDCOL unique default newid()
iv. GO
b. Turn on Filestream
i. USE DBNAME
ii. ALTER Table tablename
iii. SET (filestream_on=FILESTREAMGroupName)
iv. GO
c. Add FILESTREAM column to the table
i. USE DBNAME
ii. ALTER Table tablename
iii. Add columname varbinary(max) FILESTREAM null
iv. GO
d. Move data into the new column
i. UPDATE tablename
ii. SET new_columnname=old_columnname
iii. GO
e. Drop the old column
i. ALTER Table tablename
ii. DROP column old_columnname
iii. GO
f. Rename the new FILESTREAM column to the old column name
i. Use DBNAME
ii. EXEC sp_rename ‘tablename.newcolumncreated’, ‘oldcolmncreated’, ‘COLUMN’.
iii. GO
Now you can validate by uploading the unstructured data into the database.

Tags:
  • Creating File Stream,
  • File Stream on Existing Table,
  • Leave a Reply