SQL Server 2008 Creating FILESTREAM Enabled Database

In this post I am going to explain how you can create a database with the new FILESTREAM feature enabled. If you want to know, how to enable FILESTREAM in instance level, please refer to my previous post "SQL Server 2008 FILESTREAM Feature".

Lets start by creating a database name TestFileStream using the below scripts.

CREATE DATABASE [TestFileStream] ON PRIMARY
( NAME = N'TestFileStream', FILENAME = N'C:\DB\TestFileStream.mdf' ,
SIZE = 25MB , MAXSIZE = UNLIMITED, FILEGROWTH = 12% )
LOG ON
( NAME = N'TestFileStream_log', FILENAME = N'C:\DB\TestFileStream_log.ldf' ,
SIZE = 25MB , MAXSIZE = UNLIMITED , FILEGROWTH = 12%)
GO

Now the database is ready. Lets go ahead and add the filegroups.

ALTER DATABASE [TestFileStream]
ADD FILEGROUP [TestFileStreamGroup] CONTAINS FILESTREAM
GO

ALTER DATABASE [TestFileStream]
ADD FILE (NAME = N'TestFileStream_FSData', FILENAME = N'D:\DB\TestFileStream')
TO FILEGROUP TestFileStreamGroup
GO

One important fact is the usage of

CONTAINS FILESTREAM

clause. Atleast for one filegroup we must specify this clause. Open the properties window of the newly created database, and look into the Files section. There you can see that for the file “TestFileStream_FSData”, the file type is “File Stream Data”. Now open the folder “C:\DB”. There will be folder named “TestFileStreamData”. All the FILESTREAM related data gets stored in TestFileStreamData folder which is also known as FILESTREAM Data Container. Inside this folder you can see the following files

Among this, the file “filestream.hdr” is the most important one. As the name suggests it hold the file stream information.

Let go ahead and create a table. Before creating keep a note of the following points.

• Must have a column of type VARBINARY(MAX) along with the FILESTREAM attribute.


• Table must have a UNIQUEIDENTIFIER column along with the ROWGUIDCOL attribute.

Try this query

Use TestFileStream
GO
CREATE TABLE [FileStreamTable]
(
[ID] [INT] IDENTITY(1,1) NOT NULL,
[Data] VARBINARY(MAX) FILESTREAM NULL,
[DataGUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
[DateTime] DATETIME DEFAULT GETDATE()
)
ON [PRIMARY]
FILESTREAM_ON TestFileStreamGroup
GO

Lets insert some data

Use TestFileStream
GO
INSERT INTO [FileStreamTable] (Data)
SELECT * FROM
OPENROWSET(BULK N'C:\DSCN5021_large.jpg' ,SINGLE_BLOB) AS Document
GO

This will create a folder under “C:\DB\TestFileStream”, if you can travel inside the subfolder and one file will be there. Open it in any image viewer and you can see the image you have inserted.

To retrieve the data, use the following query

USE TestFileStream
GO
SELECT ID
, CAST([Data] AS VARCHAR) as [FileStreamData]
, DataGUID
, [DateTime]
FROM [FileStreamTable]
GO

For updating, use the following query

USE TestFileStream
GO
UPDATE [FileStreamTable]
SET [Data] = (SELECT *
FROM OPENROWSET(
BULK 'C:\DSCN5022_large.JPG',
SINGLE_BLOB) AS Document)
WHERE ID = 1
GO

For deletion, use the following query

USE TestFileStream
GO
DELETE [FileStreamTable]
WHERE ID = 1
GO

On updating/deleting, the table will be updated/deleted immediately. But the FileStream container data will be removed once the Garbage Collector Process runs.

That’s all for this post. In my next post I am planning to explain optimizing FileStream objects.

SQL Server 2008 FILESTREAM Feature

One of the new feature addition to the SQL Server 2008 is FILESTREAM. This enables the storage of BLOBs in File Sytem instead of database file.

We can enable FILESTREAM feature as follows:

• Open SQL Server Configuration Manager
  (Start->All Programs->Microsoft SQL Server 2008->Configuration Tools->SQL Server Configuration Manager)
• From SQL Configuration Manager select "SQL Server Services as shown in below figure.

• Now from the right pane right click SQL Server and select properties. A new window will be bought up as shown below.

• Now go to the FILESTREAM tab, and Select the option “Enable FILESTREAM for Transact-SQL access.

• If you want to enable reading/writing FILESTREAM date from windows, then select the option “Enable FILESTREAM for file I/O streaming access” and provide the window share name.
• If you want to enable remote client access for FILESTREAM data, then enable the 3rd option and hit “Apply”.

Another way to enable the option is through T-SQL query. Open SQL Server Management Studio. Open new query window and execute the following query.

USE master
Go
EXEC sp_configure 'show advanced options'
GO
--0 means FILESTREAM is disabled
--1 means FILESTREAM is enabled for this instance
--2 means FILESTREAM is enabled with window streaming
EXEC sp_configure filestream_access_level, 1
GO
RECONFIGURE WITH OVERRIDE
GO

Another way is using SQL Server Management Studio. Open SQL Server Management Studio. From the object explorer, select the server and right click.

Click on the properties menu item

There from the Advanced property, you can see FileStream Access Level. Set the level you want and click OK.

Another way to enable this feature is during the installation of SQL Server 2008. If you have referred to my post related to SQL Server 2008 installation over here. You can find how to do that.

Now we are done with enabling FILESTREAM on our database server.

On my next post I will describe, how to create a FILESTREAM database and tables fields.

Hope you have nice reading. For more queries and information ping me.

SQL Server 2008 Installation

Start the SQL Server 2008 installation by clicking the following icon

This will bring up the following screen

From right pane, click on “Installation link”. You will get the following screen.

From the left pane, click on the link “New SQL Server stand-alone installation or add features to an existing installation”. The 1st action performed by the installer is checking the setup support rules.

You can get a detailed report, by following the link “View detailed report”. Click OK.

Now we need to specify, which version we are going to install. If you have purchased SQL Server 2008 then enter the product key. Click Next.

Accept the agreement and Click Next.

Hit Install.

For detailed report, follow the link “View detailed Report”. Click Next.

Select the features you want to install and Click Next.

Select the instance name and Path. Click Next.

From the screen we can understand that the minimum disk space we need in C drive is 2040. Click Next.

In this screen we can specify on which account each of the SQL services should run. Click Next.

Specify the Authentication Mode. If you want to add the Current User in the SQL Administrators list, then Click Add Current User. You can enable the FILESTREAM feature by opening the FILESTREAM tab.

Once you are done, Click Next.

Provision the accounts that require have admin privileges. Click Next.

In this screen, we must specify how the Reporting Server must be configures. The available options are “Native Mode”, “Sharepoint Integrated Mode”. If you want to access the data from sharepoint lists, then select Sharpoint integrated mode. Else Native mode will be fine. Click Next.

Click Next.

Click Next.

Click Install.

Installation Completed. Click Next.

SQL Server 2008 Installation Pre-Requisites

In this blog series, I am going to explain in details about how we can install SQL Server 2008 dev edition on a windows XP box.

My current configuration is

• Windows XP SP3
• Visual Studio 2008 (No service pack installed)
• .Net Framework 3.5 SP1

Once I started installing SQL Server 2008 on my dev box, I started complaining that it need the KB(KB942288-v2) to be installed.

This KB installer is a hot fix for Windows installer. I clicked Ok and an installer window comes up, and installed that hot fix. Once this hot fix is installed, we needs to reboot our machine.

Once the system reboots, execute the SQL 2008 installer. This will bring up the following screen.

In this screen if you notice, there is an option called “System Configuration Checker”. Click on that link, which will bring up a utility that can scan and show the components which has passed/failed as shown below.

Now, we are ready to start our SQL Server 2008 installation :-)