Well, from past two days I am working on SQL Server 2008 new feature called File Stream. In the period of SQL Server 2005 when we want to store some files to the database we can have that using varbinary(max) but that approach is not either smart nor popular amongst the developers. So, many developers like me wants to store images on any physical location and keep the file location in the table. But, that have issues too, what if somebody delete the files from physical location ? will file entries in the database also deleted and what if somebody deleted the records using t/sql will the files on the physical location also deleted.
In nutshell, both the previous approaches have issues. So, this File Stream data type can replace the problem we had before. It will save the file to the physical location and store the stream of of that file to the table. In my opinion, that is the smart approach.
So, let us dig down and see how can we configure file stream to the new SQL Server 2008 instance , Database and then Table.
Getting Your SQL Server 2008 Instance Ready for File Stream:
- Goto Start > Programs > Sql Server 2008 > Configuration Tools > SQL Server Configuration Manager
- Now that Configuration Manager is open, right click on the default instance and go to properties.
- On this form, Go to File Stream Tab and Enable the file stream. See the image below
Ok, let me brief you the option we have here.
- Enable FileStream for transact-sql access : This way you can access the file using t/sql
- Enable FileStream for File I/O streaming access : By checking this you can access the files using IO Stream
- All remote clients to have streaming access to file stream data : Here you are allowing remote connections to play around with File Stream Files.
Once you finish with this you need to set the access level by run the following query. Please bear in mind that the following query will not work until you set FileStream stuff from Configuration manager.
1: EXEC sp_configure filestream_access_level, 2 -- 0 : Disable , 1 : Transact Sql Access , 2 : Win IO Access
2: GO
3: RECONFIGURE
4: GO
Or alternatively, you can
- Go to SQL Server Management Studio
- Right Click the database server then properties
- From the properties window select advance and you will see the following screen
- Now simply select the access level you want for your server.
Enable database to have FileStream Data type:
Now, you have done with the sql server instance configuration. Let’s move to Database Configuration. How can we create a database which is FileStream Supported.
For that there are two options.
- Using T-sql
- Using Management Studio
Using T-Sql
For that you need to run the following query
1: CREATE DATABASE Learning_Db
2: ON
3: PRIMARY ( NAME = LearnDb1,
4: FILENAME = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL10.SQLEXPRESS\\MSSQL\\DATA\\Learning_Db .mdf'),
5: FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = LearnDb2,
6: FILENAME = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL10.SQLEXPRESS\\MSSQL\\DATA\\Learning_DbStream')
7: LOG ON ( NAME = LearnDbLog1,
8: FILENAME = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL10.SQLEXPRESS\\MSSQL\\DATA\\Learning_Db.ldf')
9: GO
Ok now, notice that along with the Primary and Log file we have one new file group which we use for FileStream. Remember we have discuss above that FileStream will save a file on a physical location and store the stream in the the database which will later use for accessing that file.
Now, when you go to the location where we create our new database you will see there is a folder (in our case it should be “Learning_DbStream”). This folder contain all the files of your FileStream.
Using Management Studio
While creating a new database window go to file group, you will find the File Stream section at the bottom. See image below
Note : If your database is already created, you can set FileStream stuff by right click your database and then properties and then file group
For using the FileStream, you need to add a File Stream Group here and make it default. Once you add that, Go to the Files and add a new file
- Give Logical name In our case we have used “LearningDb_FileStream” and then Select “FileStream Data” from file type.
- After that, you only need to set the path and that’s it
- Click Ok, and now your database is ready to play with FileStream.
Using FileStream Data type in table:
Now, for using the FileStream data type in table you need to create a Unique Column which is off uniqueidentified datatype and a column that uses varbinary(max) to store the Stream. Here is the SQL for that
1: CREATE TABLE [dbo].[tbl_Files](
2: [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
3: [SystemNumber] [int] NOT NULL,
4: [SystemFile] [varbinary](max) FILESTREAM NULL
5: ) ON [PRIMARY]
6:
7: GO
Just give this a Go and you are all done with FileStream configuration.
