Dot Net Thoughts

SQL Server 2008 comes with lots of new features compared to the previous versions of SQL Server. One of the new feature is FileStream, which allows storage of and efficient access to BLOB data using a combination of SQL Server 2008 and the NTFS file system.

You can get more details about this in MSDN : FILESTREAM Storage in SQL Server 2008

Enable Filestream in SQL Server

By default the Filestream feature will be disabled. You can enable the filestream using SQL Server Configuration Manager under SQL Server 2008 > Configuration Tools. In this you will get all the SQL Server services. Select the Properties of the instance and select the Tab “FileStream”, from that you can enable the FileStream, you can also specifiy the instance name also.

How to enable FileStream

You can also do it via T-SQL statement also

EXEC sys.sp_configure N'filestream access level', N'2'
RECONFIGURE

After doing this SQL Server will create a shared folder in your machine(or in Server) with the instance name specified. (Or it will create the Windows Share name we are specifying in the textbox) Only SQL Server can access the contents.

Network

You can check this via command prompt, using “Net Share”, you will get an output like this.

Net share command output

Using Filestream in the Database.

For using Filestream in your database you have to add file group in New Database screen.

Enable filestream for new Database

Or you can do this via TSQL like this

CREATE DATABASE FileStreamDemo
ON PRIMARY
   (NAME = FileStreamDemo,
      FILENAME = N'D:\DB\FileStreamDemo_data.mdf'),
FILEGROUP FileStreamFileGroup CONTAINS FILESTREAM
   (NAME = FileStreamDemo,
      FILENAME = N'D:\DB\FileStreamDemo')
LOG ON
   (NAME = 'FileStreamDemo_log',
      FILENAME = N'D:\DB\FileStreamDemo_log.ldf');
go

After doing this, SQL Server will create Folder in “D” drive, with name FileStreamDemo under DB directory. This FileStreamDemo folder will contains two files

1. filestream.hdr – This is the FILESTREAM metadata for the data container.
2. The directory $FSLOG. This is the FILESTREAM equivalent of a database’s transaction log.

Creating a Table with FILESTREAM Data
You can create a Table for consuming FileStream like this.

CREATE TABLE SQLFileSystem
(
FileId UNIQUEIDENTIFIER  ROWGUIDCOL UNIQUE DEFAULT NEWID() PRIMARY KEY,
FileName VARCHAR(255),
FileContents VARBINARY(MAX) FILESTREAM  NULL default (0x)
)

Thats it, you have created SQL Server Database and Table with Filestream.

Today, Aneesh (the guy who blogged about SQL Encryption few months back in dot net thoughts), told me about the Multiple Active Result Sets (MARS), a new option available in SQL Server 2005, which helps developers to maintain Multiple active Statements on a connection. When using SQL Server default result sets, the application had to process or cancel all result sets from one batch before it could execute any other batch on that connection. SQL Server 2005 introduced a new connection attribute that allows applications to have more than one pending request per connection, and in particular, to have more than one active default result set per connection.

The MARS feature is disabled by default. It can be enabled by adding the “MultipleActiveResultSets=True” keyword pair to your connection string, as below:

string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI;MultipleActiveResultSets=True";

Without MARS

string sql1 = "SELECT * FROM gotMenus";
string sql2 = "SELECT * FROM gotUsers";

using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;  Initial Catalog=GotCms;Integrated Security=SSPI;"))
{
conn.Open();
using (SqlCommand cmd1 = new SqlCommand(sql1, conn))
{
SqlDataReader dr1 = cmd1.ExecuteReader();
}

using (SqlCommand cmd2 = new SqlCommand(sql2, conn))
{
SqlDataReader dr2 = cmd2.ExecuteReader();
}
}

The above code will throw an invalid operation exception(There is already an open DataReader associated with this Command which must be closed first),unless you close the dr1 using dr1.Close();

Or you can modify the connection string like the below.

With MARS

string sql1 = "SELECT * FROM gotMenus";
string sql2 = "SELECT * FROM gotUsers";

using(SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;  Initial Catalog=GotCms;Integrated Security=SSPI;MultipleActiveResultSets=True"))
{
conn.Open();
using (SqlCommand cmd1 = new SqlCommand(sql1, conn))
{
SqlDataReader dr1 = cmd1.ExecuteReader();
}

using (SqlCommand cmd2 = new SqlCommand(sql2, conn))
{
SqlDataReader dr2 = cmd2.ExecuteReader();
}
}

It will work fine without any issue.

You can get more detailed information about MARS from these links

http://technet.microsoft.com/en-us/library/ms345109
(SQL.90).aspx

http://msdn.microsoft.com/en-us/library/ms131686.aspx

Tip : The server connection attribute, used in SQL Server connection string is optional. Only Database name and Security attributes required as mandatory for establishing a connection to SQL Server.