Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Hello all I referred to this http://stackoverflow.com/a/5613926/842112

In that they mentioned this

if your pictures or document are typically over 1 MB in size, storing them in the filesystem is more efficient (and with SQL Server 2008's FILESTREAM attribute, they're still under transactional control and part of the database)

Now my question is how to use File stream attribute in SQL Server 2008? I referred to http://msdn.microsoft.com/en-us/library/cc949109.aspx but I don't get how to use this. So please suggest me how to use this I want to store images in a database table. Thank you.

share|improve this question

1 Answer 1

up vote 4 down vote accepted

To use FILESTREAM, it has to be configured at the OS and instance level. At the OS level, FILESTREAM is enabled either during the installation of SQL Server 2008 or by running SQL Server Configuration Manager. Once FILESTREAM has been setup, you have to create a FILESTREAM filegroup. You then have to create a data container in the FILESTREAM filegroup. Once all these steps are done, you can create a table in your DB to use FILESTREAM.

(From your link) Here are the steps to configure FILESTREAM at the OS level, if not done during installation:

  1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008, point to Configuration Tools, and then click SQL Server Configuration Manager.
  2. In the list of services, right-click SQL Server Services, and then click Open.
  3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
  4. Right-click the instance and then click Properties.
  5. In the SQL Server Properties dialog box, click the FILESTREAM tab.
  6. Select the Enable FILESTREAM for Transact-SQLAccess check box.
  7. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access.
  8. Enter the name of the Windows share in the Windows Share Name box.
  9. 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.

Next configure the SQL instance for FILESTREAM access. Run the following query on the instance (SSMS):

EXEC sp_configure filestream_access_level, 2; -- Options are 0/1/2
GO
RECONFIGURE;
GO

Now add the FILESTREAM filegroup to your DB:

ALTER DATABASE Production ADD
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM;
GO

ALTER DATABASE Production ADD FILE (
       NAME = FSGroup1File,
       FILENAME = 'F:\Production\FSDATA') -- Location for your data container 
TO FILEGROUP FileStreamGroup1;
GO

You can now use FILESTREAM in your tables.

share|improve this answer
    
While creating table which datatype we have to select varbinary(MAX) or we will get FILESTREAM ? –  M.S.Nayak Jan 27 '12 at 7:06
    
While I right click database table and select design I dint get datatype Filestream? –  M.S.Nayak Jan 27 '12 at 7:28
    
Your table should have a unique identifier and the varbinary(max) column should have a FILESTREAM attribute. eg: CREATE TABLE DocumentStore( Document VARBINARY (MAX) FILESTREAM NULL, DocGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID ()) FILESTREAM_ON FileStreamGroup1; GO –  StanleyJohns Jan 27 '12 at 17:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.