In my previous blog post I wrote about what FILESTREAM and FileTables are. In this post I explain how to set FILESTREAM and FileTables up.
To enable and change FILESTREAM settings
- On the Startmenu, select All Programs, select the SQL Server folder, select Configuration Tools, and then click SQL Server Configuration Manager.
- In the list of services, right-click SQL Server Services, and then click Open.
- In the SQL Server Configuration Managersnap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
- Right-click the instance, and then click Properties.
- In the SQL Server Propertiesdialog box, click the FILESTREAM
- Select the Enable FILESTREAM for Transact-SQL access check box.
- If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name
- If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.
- Click Apply.
- In SQL Server Management Studio, click New Queryto display the Query Editor.
- In Query Editor, enter the following T-SQL code:
EXEC sp_configure filestream_access_level, 2 RECONFIGURE
- Click Execute.
- Restart the SQL Server service.
Next we need to add a File Group to the DB with FILESTREAM and then add a File into the File Group.
USE MASTER GO ALTER DATABASE ImageFilesDB ADD FILEGROUP ImageFG1 CONTAINS FILESTREAM; GO ALTER DATABASE ImageFilesDB ADD FILE ( NAME = ImageStorage, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ImageStorageFG1.ndf' ) TO FILEGROUP ImageFG1; GO
Once you have created the File Group and add a file you will need to set the FILESTREAM File Group as the default in the database properties. (Right-click the database, select Properties, select Filegroups in the left hand pane, check the default checkbox in the FILESTREAM section)
Next you need to check whether non-transaction access is enabled on the database.
SELECT DB_NAME(database_id), non_transacted_access, non_transacted_access_desc FROM sys.database_filestream_options; GO
If it is not enabled on the database we need to enable it
ALTER DATABASE ImageFilesDB SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'ImageTable') Once non-transactional access is enabled on the database we can create a FileTable CREATE TABLE ImageStore AS FILETABLE WITH ( FileTable_Directory = 'ImageTable', FileTable_Collate_Filename = database_default ); GO
Now that everything is set up the last thing to do is to add files to our FileTable. There are two methods of doing this, either through T-SQL code or by exploring the FileTable from SQL Management Studio.
T-SQL method:
DECLARE @img AS VARBINARY(MAX) SELECT @img = CAST(BulkColumn AS VARBINARY(MAX)) FROM OPENROWSET(BULK 'C:\temp\testimage.jpg', SINGLE_BLOB ) AS x INSERT INTO dbo.ImageStore(name, file_stream) SELECT 'testimage.jpg', @img SELECT * FROM dbo.ImageStore
Management Studio Method:
- Expand the FileTables node under Tables in your database
- Right-Click the FileTable you created
- Select Explore FileTable Directory
A shared network location will open up where you can paste images/files into.