19.03.2009

SQL Server 2008 FileStream

Storing binary large objects in database is not a good idea because

  1. Takes memory in SQL Server Buffer
  2. BLOB data takes place in database file so it makes transaction log
  3. No more then 2GB data can be stored as  varbinary(max)
  4. Updating BLOBs can cause fragmentation

 

FileStream

  1. Has own transaction system so never causes to database own transaction file to grow abnormaly
  2. Single File create/delete operation executed for filestream so fragmentation is not as much problem as varbinary(max)
  3. In books online it writes that upto 1MB is optimal for stroing binary large objects but up to teched 2008 Bob Beauchemin it is optimal up to 256K

FILESTREAMWhitepaper1.GIF

                                                                                    [1] Image 1

Demo

  1. Create a table that has a column with varbinary(max)
use BlogDb
go
if exists(select * from  sys.tables where name = 'tbl_user_documents')
  drop table tbl_user_documents
go
create table tbl_user_documents
(
  DocumentId  int not null identity(1,1) primary key  ,
  Title    varchar(255) not null          ,
  Document  varbinary(max) not null          ,
  [Type]    varchar(5)    not null  
)




2. Insert Documents



using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
namespace DocumentUploader
{
  class Program
  {
    static void Main(string[] args)
    {
      string[] files = Directory.GetFiles(@"c:\Files");
      foreach(string file in files)
      {
        byte[] bytFile = GetFileBytes(file);
        InsertDocument(Path.GetFileName(file), bytFile);
      }
      
      Console.WriteLine("Finished");
      Console.ReadLine();
      
    }
    static void InsertDocument(string title,byte[] bytDoc)
    {
      using(SqlConnection con = new SqlConnection("data source=localhost;initial catalog=BlogDb;integrated security=sspi"))
      using (SqlCommand cmd = new SqlCommand("insert into tbl_user_documents(Title,Document,[Type]) values (@title,@doc,@type)",con))
      {
        cmd.Parameters.Add(new SqlParameter("@doc", bytDoc));
        cmd.Parameters.Add(new SqlParameter("@title", Path.GetFileNameWithoutExtension(title)));
        cmd.Parameters.Add(new SqlParameter("@type", Path.GetExtension(title)));
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
      }
    }
    static byte[] GetFileBytes(string filePath)
    {
      byte[] bytFile = null;
      using(FileStream fs = new FileStream(filePath,FileMode.Open))
      {
        bytFile = new byte[fs.Length];
        fs.Read(bytFile, 0, bytFile.Length);
        fs.Close();
      }
      return bytFile;
    }
  }
}


4. Configure Database Server for FileStream



image



5. Configure Database for FileStream



 



--add filegroup for FileStream
alter database BlogDb
  Add FileGroup FileStreamFileGroup1 contains FileStream
go
--Add FileGroup To Database
alter database BlogDB
  add file
  (
    name = 'UserDocuments'  ,
    filename = 'C:\FileStream\UserDocuments' 
  ) to filegroup FileStreamFileGroup1
go


 



6. To get benefit of using filestream add rowguid column, and filestream typed data column



ALTER DATABASE BlogDb Set Read_Committed_snapshot off
--add filegroup for FileStream
alter database BlogDb
  Add FileGroup FileStreamFileGroup1 contains FileStream
go
--Add FileGroup To Database
alter database BlogDB
  add file
  (
    name = 'UserDocuments'  ,
    filename = 'C:\FileStream\UserDocuments' 
  ) to filegroup FileStreamFileGroup1
go
  
alter table tbl_user_documents set(filestream_on ='default')
go
alter table tbl_user_documents
  add 
  
    [Document2] varbinary(max) filestream  null  ,
    FileId  uniqueidentifier  not null rowguidcol unique default (newid())
go  



7. Update Document2 to Document ,Drop Document and rename Document2 to Document



 



update tbl_user_documents
  set Document2 = Document
go
alter table tbl_user_documents
  drop column  Document
sp_rename  @objname = N'BlogDB.dbo.tbl_user_documents.Document2',
      @newname = N'Document'  ,
      @objtype = N'COLUMN'
      


8. Create Fulltext Index on only Document Column



9. Now we’ve been started to use fulltext index on FileStream



select * from tbl_user_documents t
  inner join containstable(tbl_user_documents,Document,'"*Microsoft*"') c
    on c.[Key] = t.DocumentId
      order by c.[Rank]


image



References

[1] http://msdn.microsoft.com/en-us/library/cc949109.aspx


[2] Teched – 2008 Bob Beauchemin – SQL Server 2008 FileStream




Hiç yorum yok: