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.
This set of instructions worked perfectly, thank you for posting it. For thoroughness, note I had to switch the database to SINGLE_USER mode, then immediately back to MULTI_USER mode, to drop any existing connections, just prior to running ALTER DATABASE SET FILESTREAM.
Thank you for the pointer!
This is the best explanation I’ve found to date! Thanks!
That said, I’ve gotten to the Set Filestream (…, Directory_Name = …) portion of your instruction and when I run the query it takes FOREVER (so far 01:56:00), what could be my problem?
Here is the SQL Statement I’m running:
ALTER DATABASE EngineeringGageLending
SET filestream (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N’CalibrationTable’);
GO
CREATE TABLE CalibrationStore AS FILETABLE
WITH (
Filetable_directory=’CalibrationTable’,
FileTable_Collate_Filename=database_default
);
go
This Page is very Good.
thanks a lot