One of the most important new features in SQL Server 2008 is certainly the possibility to use File System to store BLOB data. So if you work with images, binary files and so on and you need to store them in a SQL server database, it is possible to use this feature. It is important for those who are using SQL Server Express edition, because it avoids the problem of the 4GB maximum size for the database giving virtually no limit to the BLOB data growth.
Let’s see how to activate this feature on our server:
- First step is to activate the feature at server protocol level, so, from Programs menu > Microsoft SQL Server 2008 > Configuration Tools – launch the SQL Server Configuration Manager (icon with the red toolbox).
- Select SQL Services in the left treeview
- On the right window, select SQL Server (MSSQLSERVER) for the default instance or the name of the instance to configure.
- Right click on it, and select properties.
- On the property window there is a series of TABS, select FILESTREAM, activate the T-SQL access and if necessary the API access and the remote client access (I haven’t done a full test so I don’t know if this is necessary, so you have to make some tests on this.)
- Second step is in SQL Management Studio, connect to the server with an account with Sysadmin rights, on the server, right click and select properties, select Advanced on the left options list and on the first row of the property grid on the right part of the window, activate the Filestream option in T-SQL mode or Full Access.
- Create a Database able to memorize data on FILESTREAM:
USE [master]
GO
/****** Object: Database [Paperinik] Script Date: 03/04/2010 18:09:07 ******/
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'Paperinik')
BEGIN
CREATE DATABASE [Paperinik] ON PRIMARY
( NAME = N'Paperinik', FILENAME = N'C:\SQL.DIR\Data\Test\Paperinik.mdf' ,
SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [Binari] CONTAINS FILESTREAM DEFAULT
( NAME = N'Paperinik_Bin', FILENAME = N'c:\sql.dir\data\test\PaperinikFS\paperinik_bin' )
LOG ON
( NAME = N'Paperinik_log', FILENAME = N'C:\SQL.DIR\Data\Test\Paperinik_log.ldf' ,
SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
END
this example made with my favourite database :D creates the database with the Paperinik.mdf as master data file and the file Paperinik_log.ldf as a log file. Adding a FILEGROUP named Binari which defines the FILESTREAM zone, telling the server which is the filesystem folder where to put the data. in my case, c:\sql.dir\data\test\PaperinikFS\ pay attention that the Service account needs full control on this folder.
- To proceed with test we need to create a table with a Varbinary(MAX) field with option FILESTREAM
USE [Paperinik]
GO
CREATE TABLE [dbo].[TbFiles](
[IDFile] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[IDFileSerial] [int] NOT NULL,
[DDFile] [nvarchar](255) NULL,
[FileData] [varbinary](max) FILESTREAM NULL,
CONSTRAINT [PK_TbFiles] PRIMARY KEY NONCLUSTERED
(
[IDFile] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [Binari]
GO
USE [Paperinik]
/****** Object: Index [UQ_TbFileSerial] Script Date: 03/04/2010 17:23:33 ******/
CREATE UNIQUE CLUSTERED INDEX [UQ_TbFileSerial] ON [dbo].[TbFiles]
(
[IDFileSerial] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] FILESTREAM_ON [Binari]
GO
In this example, I’ve created a table to memorize some files, even though in the example I put into the table something simpler, I will made some more test and let you know how it works later on this blog.
To be able to contain a FILESTREAM field, it is mandatory that the table has a GUID (uniqueidentifier) ID field, to made it more easy I added an integer unique ID to be able to find things in a more fashionable way.
- Now we need to insert the test data in the table, it is very simple using T-SQL:
INSERT INTO [Paperinik].[dbo].[TbFiles]
([IDFile]
,[IDFileSerial]
,[DDFile]
,[FileData])
VALUES
(NEWID()
,1
,'Nullo'
,null)
GO
INSERT INTO [Paperinik].[dbo].[TbFiles]
([IDFile]
,[IDFileSerial]
,[DDFile]
,[FileData])
VALUES
(NEWID()
,2
,'Vuoto'
,cast('' as varbinary(max)))
GO
INSERT INTO [Paperinik].[dbo].[TbFiles]
([IDFile]
,[IDFileSerial]
,[DDFile]
,[FileData])
VALUES
(NEWID()
,3
,'Stringa'
,cast('Questa stringa è binaria' as varbinary(max)))
GO
INSERT INTO [Paperinik].[dbo].[TbFiles]
([IDFile]
,[IDFileSerial]
,[DDFile]
,[FileData])
VALUES
(NEWID()
,4
,'La quarta'
,cast('Questa riga contiene questa stringa' as varbinary(max)))
GO
I’ve created four rows one with a NULL binary field, one with an empty string and two with a string inside.
If we open our root folder after this operation on c:\sql.dir\data\test\PaperinikFS\ we will find some new folders, with a GUID as name, digging into the content we will find some files with names like nnnnnnnn-nnnnnnnn-nnnn that when opened with notepad will show the data in our varbinary field, the NULL field has no file. I haven’t yet tried to memorize a binary file inside this field, I’ll tell you what happens in a future post.
What comes out in the end from this test? If we need to store unstructured data in a database, this can be a good solution, because SQL Server gives us transactions, logs, managed purge the possibility to activate indexing, I’ve to try Full text indexing on NON Sql Express servers, the possibility to backup these data as a normal file system directory, eliminates the 4GB database limit but pay attention, even though the BLOB data can be accessed from the file system it is not easy to find things inside this file system, it is very simple to find things and check out/check in binary data using the T-SQL or the direct acces APIs. So if you thought to use FILESYSTEM in SQL Server to be able to access data both from the database and directly from file system, this is not the right way.
If you want to build a database to index informations on your non structured data and be able to keep files on the filesystem, and use them directly from where they are stored, well, you have to build something manual, and put just the “Path” info inside your database.