New Features - SQL 2008: Filestream

When developing systems, notably ones involving document mis.. uhm management, developers are confronted with the question of where they should store their files.

A simplistic (easy manageable / codeable etc) approach is to simply store/stream the files from a database - unfortunately this approach comes with a performance penalty especially when working with large files.

What is considered large exactly? Well, according to research done by Microsoft - "objects smaller than 256K are best stored in a database while objects larger than 1M are best stored in the filesystem. Between 256K and 1M, the read:write ratio and rate of object overwrite or replacement are important factors" from http://research.microsoft.com/apps/pubs/default.aspx?id=64525

The alternative (like seen in the preceding quote) is to store files on the filesystem, which unfortunately introduces a number of manageability issues like security, integrity etc.

In SQL 2008 however, Microsoft attempts to give us the best of both worlds in the form of the Filestream feature, in which files(blobs) are stored/streamed via the NTFS file system - all managed by SQL.

Lets have a look at how to use/enable this functionality.

By default this feature is disabled, in order to enabled it do the following:

• Go to Start menu
  • Navigate to All Programs
  • Navigate to Microsoft SQL Server 2008
  • Navigate to Configuration Tools
  • Click on SQL Server Configuration Manager
• Within SQL Server Configuration Manager
  • Click on SQL Server Services
  • Right click on the SQL Server instance you wish to enable filestreaming
  • Select properties
• Within Properties
  • Click on the Filestream tab
  • Tick Enable FILESTREAM for Transact-SQL access
    
    
    
    
    • Enable FILESTREAM for file I/O streaming access tick shares the filestream within Windows
    • Allow remote clients to have streaming access to FILESTREAM data tick allows remote users to access the share
  • Click on Ok

 
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
 

 
CREATE DATABASE FSDemo
ON 
PRIMARY
(
	NAME = FSDemo,
	FILENAME = 'C:\demo\FSDemo.mdf'
),
FILEGROUP FSDemoGroup CONTAINS FILESTREAM
(
	NAME = FSDemoGroup1, 
	FILENAME = 'C:\demo\FSDemo1'
)
LOG ON 
(
	NAME = FSDemoLog, 
	FILENAME = 'C:\demo\FSDemo.ldf'
)
 

 
CREATE TABLE [dbo].[documents](
	[documentID] [uniqueidentifier] PRIMARY KEY ROWGUIDCOL NOT NULL DEFAULT NEWID(),
	[name] [varchar](255) NULL,
	[DATA] [varbinary](max) FILESTREAM  NULL
)
 

C# Design Pattern : Singleton

The singleton pattern basically involves a class that only allows a single instance of itself to be instantiated.



The first snippet gives a basic (but bad) idea on how to create a singleton.

 
public sealed class Singleton
{
    static Singleton _instance = null;
 
    private Singleton()
    {
        Console.WriteLine("Instance Created");
    }
 
    public static Singleton Instance
    {
        get
        {
            if (_instance == null)
            {
                _instance = new Singleton();
            }
            return _instance;
        }
    }
}
 

• Seal this class in order to prevent derived classes to break the pattern.
• Create a private constructor in order to prevent instantiation of the class.
• Return an instance of the class via static property.

 
class Program
{
    static void Main()
    {
        for (int i = 0; i < 10; i++)
        {
            Thread thread = new Thread(new ThreadStart(ThreadMain));
            thread.Start();
        }
    }
 
    static void ThreadMain()
    {
        Thread.Sleep(500);
        try
        {
            Singleton s = Singleton.Instance;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}
 

 
public sealed class Singleton
{
    private static volatile Singleton instance;
    private static object locker = new Object();
 
    private Singleton() 
    {
        Console.WriteLine("Instance Created");
    }
 
    public static Singleton Instance
    {
        get
        {
            if (instance == null)
            {
                lock (locker)
                {
                    if (instance == null)
                        instance = new Singleton();
                }
            }
            return instance;
        }
    }
}
 

• Use volatile in order to ensure that assignment to the variable completes before we access it.

 
public sealed class Singleton
{
    public static readonly Singleton Instance = new Singleton();
 
    static Singleton() { }
 
    private Singleton()
    {
        Console.WriteLine("Instance Created");
    }
}
 

• Set the static instance variable to readonly in order to prevent any changes to be made to the instance as soon as its created.
• Add a static constructor with regards to lazy initialization e.g. beforefieldinit flag. (the static constructor initializes as soon as we access a static member - which in this case postpone the creation of the singleton instance)

 
public sealed class Singleton
{
    private static readonly Lazy<Singleton> _instance = new Lazy<Singleton>(() => new Singleton());
    private Singleton() { Console.WriteLine("Instance Created"); }
 
    public static Singleton Instance
    {
        get
        {
            return _instance.Value;
        }
    }
}