Infosys - Using SQL Server FILESTREAM to store BLOBs - Tutorial

Infosys Technologies Limited

Informations

• Publié par: Ustli
• Nombre de lectures: 27
• Langue: English

Extrait

Tutorial: Using SQL Server FILESTREAM to store
BLOBs
Balasubramanian Shanmugam
Introduction
In the earlier versions of SQL Server, storing unstructured data posed many
challenges in terms of maintaining transactional consistency between the
structured and unstructured data, managing backup and restore, performance
and scalability issues etc. To overcome these problems, SQL Server 2008 has
introduced a feature called FILESTREAM. This document outlines using
illustrations, the benefits of FILESTREAM and shows how to implement this
feature in SQL Server 2008.
This document is intended to help the below mentioned communities:
• Architects – Useful to understand the feature and its capabilities
• DBA - Helps in having a better maintenance plan for databases which has large
data items being stored
• Developers - Helps as a quick start in using this feature by performing
operations on unstructured data in the middle tier
Formor einformation,Contact [e-mail]
Oct2008Overview of FILESTREAM
“FILESTREAM”isanewfeatur eintr oducedinSQLSer ver2008tostor eunstructur edbinar yfilesinthedatabase.It’ snota
datatypelikeV ARBINAR Y(MAX),butanattribute/pr opertyimposedupontheV ARBINAR Ycolumntotellthatthedatahas
tobestor eddir ectlyonthefilesystem.Theyr emainasintegralpartofthesystemandmaintainatransactionalconsistency .
Followingar eafewpointsonFILESTREAM:
1. Binar ydatastor edasindividualfilesoutsidethedatabase
2. Theseindividualfilesar eaccessedthr oughWIN32APIforfileoperations
3. T ransactSQLstatementsapply
4. Limitationof2GBfilesizeonV ARBINAR Y(MAX)columnr emovedforobjectsstor edonfilesystem
5. Canbeusedoncompr essedvolumesaswell
Thefollowingfigur edepictsthedualinterfaceforFILESTREAMaccess.
Figure 1: Data Flow using FILESTREAM
Current support/ implementation of unstructured data
Befor eSQLSer ver2008,ther ewer edif fer entmechanismsusedforstoringunstructur eddata.Considerascenariowher ean
applicationhasar equir ementtostor eMISdata(mostlyinunstructur edformat)likeintheformofquotations,vendordata
etc.Thiselectr onicdatawhichisusuallyintheformoffilesar estor edinacommonfoldertowhichnecessar ypeoplear e
givenaccesspermissions.TheUNCpathofthesefileswillbestor edinthedatabasetableasacolumndata(V ARCHAR(n)),
fortheapplicationtoaccessit.Inthiscase,thepr oblemliesinsecuringthesefiles.Atalaterpointoftime,ifsomemor e
peopleneedaccesstothosefiles,theaccesspermissionlisthavetobechangedtoaccommodatethemwhichisanadditional
over head.Alsoadditionalef fortsar eneededtomaintainthefiles.Forexample,takingthedatabasebackupandr estoringitis
notsuf ficient,thebackupandr estor eoffilesisexplicitlyneeded.
LatertheconceptofBinar yLar geObjects(BLOB)cameintopictur ewhichhelpedpeopleinstoringunstructur eddatato
someextent.Themainadvantageofthisconceptistheintegratedmanagementandtransactionalconsistencywithinthe
database.Securityissuesar etakencar eofinthiscase.Butthelimitationisther estrictedsizelimitwhichisof2GBandheavy
loadonthelogfile.
SQLSer ver2008over cametheabovepr oblemswiththeFILESTREAMfeatur e.Itnotonlypr ovidesintegratedmanagement
andtransactionalconsistency ,butalsopr ovidesstr eamingsupportmakinguseofWIN32APIfunctions(fasterr etrievalof
data).
2| Infosys–WhitePaperPre-requisites
• RunsonSQLSer ver2008orlaterversions
• Eachdatar owshouldcontainauniquer owID
• FILESTREAMdatacontainerscannotbenested
• Filegr oupscr eatedforFILESTREAMmustbeonshar edr esour cesincaseofusingfail-overclustering
Usage scenario
• A veragedatasize>1MB
• Fastr eadaccessr equir ed
• Applicationdevelopedusingthemiddletieroftheapplicationlogic
Pros
• Thefilescr eatedaspartoftheFILESTREAMar emanagedbytheSQLSer veritselfintheirownfilegr oupswhichcan
bebackedupandr estor edalongwithotherSQLSer verdata
• Readingandwritingthesefilesar enowpartofthedatabasetransaction
Cons
• FILESTREAMdatacanonlybestor edonthe“local“ser verdrivesandhencesizeuptoser verdrivespace
• Notsupportedindatabasesnapshot
• Databasemirr oringisnotsupported
• T ranspar entDataEncr yptionisnotsupported
• Cannotbeusedintablevaluedparameters
Tutorial
ThefollowingstepsneedtobeperformedforusingFILESTREAMdatatypeforstoringfiles:
Step 1: Enable FILESTREAM support for the SQL server Instance
OpentheSQLManagementStudioenvir onmentandrunthefollowingcommandtoenableFILESTREAMcapability:
exec[sp_filestr eam_configur e]
@enable_level=3,
@shar e_name=“SQLSer ver2008”
Syntaxof“sp_filestr eam_configur e”:
sp_filestr eam_configur e[[@enable_level=]level]
[[,@shar e_name=]‘shar e_name’];
0-Disabled.Thisisthedefaultvalue.
1-EnabledonlyforT ransactSQLaccess.
2-EnabledforT ransact-SQLandlocalfileaccess.
3-EnabledforT ransact-SQL,localfilesystemaccess,andr emotefilesystemaccess.
Infosys–WhitePaper| 3Afterrunningtheabovecommandinthequer yanalyzer ,ashar ewiththe“@shar e_name”value,willbecr eatedandcanbe
viewedwithNETSHAREutilityasshowninFigur e2.
Figure 2: NET SHARE Utility
Step 2: Creating a database with FILESTREAM capability
RunthefollowingSQLscriptinmanagementstudiotocr eatethesampledatabasewithFILESTREAMenabled:
CREATEDATABASE FileManagement
ON
PRIMARY (
NAME = FileManagement_Primary,
FILENAME = 'D:\temp\data\FileManagement.mdf'),
FILEGROUP FileStreamGroup CONTAINS FILESTREAM(
NAME = FileManagement_FileGroup,
FILENAME = 'D:\temp\data\FileManagement')
LOG ON (NAME = FileManagement_Log,
FILENAME = 'D:\temp\data\FileManagement.ldf')
AftertheexecutionoftheaboveSQLstatement,thefollowingfolderstructur ewillbecr eatedinthelocaldiskdriveasshown
belowinFigur e3:
Figure 3: Change in folder structure
Datafolderwillcontainthe“.mdf”and“.ldf”filesand“Filemanagement”folderwillholdthefilescr eatedforthe
FILESTREAMdata.Aftercr eatingthedatabasewithFILESTREAMcapability ,a“filestr eam.hdr”fileandan$FSLOGfolder
appearsintheD:\temp\data\FileManagement\folder .This“filestr eam.hdr”isaheaderfilefortheFILESTREAMcontainer .
Note: The filestream.hdr is a system file maintained by SQL Server which is not to be removed or tampered.
4| Infosys–WhitePaperStep 3: Creating sample tables to store unstructured data
Thenextstepistocr eatetablestoholdtheunstructur eddata.Thefollowingscriptwillcr eateatablewitha“V ARBINAR Y”
columntoholdtheFILESTREAMdata.
Use FileManagement
CREATETABLE [dbo].[Files]
(
FileId UNIQUEIDENTIFIER NOTNULL ROWGUIDCOL PRIMARY KEY,
[FileContents] varbinary(MAX) FILESTREAM DEFAULT NULL
)
Step 4: Scenario to depict FILESTREAM capability making use of .NET application
Inthisstep,wewillcr eateaFILESTREAMenableddatabaseandinsertsomeunstructur eddata(imagedata)intoatable
containingV ARBINAR YcolumnusingasimpleW indowsFormapplicationin.Net.Thisapplicationisbuiltwiththe
followingsoftwar einstalled:
a. V isualStudio2008R TM
b. SQLSer ver2008FebCTPversion
Performthefollowingsteps:
1. Dotheabove3stepstoconfigur e/cr eateadatabaseforthesampleapplication
2. Cr eateasimpleW indowsFormsapplication
Figure 4: Creating Windows forms Application
3. Drag2buttonsontotheformandontheclickononebutton,thefunctionalityforinsertingtheFILESTREAMdata
willbecarriedoutandontheother ,thedatawillbedisplayedtotheuser
4. FILESTREAMdataisnothingbutbinar ydatasotheDMLstatementscanbewrittenontopofittodothenecessar y
operations.ADO.NETcodewillbewrittentomakeuseoftheFILESTREAMdata
Infosys–WhitePaper| 5SqlCommand cmd = new SqlCommand("select
[FileContents].PathName(),GET_FILESTREAM_TRANSACTION_CONTEX T()f rom
Files", conn, tran);
PathName()-theWIN32APIfunctionwhichhelpsinr etrievingthepathoftheactualdatastor ed
GET_FILESTREAM_TRANSACTION_CONTEXT()–Thisr etur nsatokenwhichr epr esentsthecurr enttransaction
contextofasessionwhichisusedinfetchingtheFILESTREAMdata
5. BelowistheWIN32APIfunctionwhichhelpsinr etur ningavalidFILEhandletostr eamthedatatoafile:
handle = SqlNativeClient.OpenSqlFilestream(
sqlFilePath.Value,
SqlNativeClient.DESIRED_ACCESS_READ,
0,
transactionToken.Value,
(UInt32)transactionToken.Value.Length,
new SqlNativeClient.LARGE_INTEGER_SQL(0));
T omakeuseoftheOpenSqlFilestr eamfunctionwehavetocalltheunmanagedDLL(sqlncli10.dll),
[DllImport("sqlncli10.dll", SetLastError = true, CharSet = CharSet.Unicode)]
public static extern SafeFileHandle OpenSqlFilestream(
string FilestreamPath,
UInt32 DesiredAccess,
UInt32 OpenOptions,
byte[] FilestreamTransactionContext,
UInt32 FilestreamTransactionContextLength,
LARGE_INTEGER_SQL AllocationSize);
6. T ogettheimages,copythefollowingcodesnippettooneofthebuttonclickevent.Thissnippetwillopenafiledialog
andbr owsefortheimagefiles.Andonselectingtheimagefile,convertitintobytearray .
OpenFileDialog f = new OpenFileDialog();
f.ShowDialog();
FileStream fs = new FileStream(f.FileName, FileMode.OpenOrCreate);
Byte[] blob = new Byte[fs.Length];
fs.Read(blob, 0, blob.Length);
fs.Close();
6| Infosys–WhitePaper7. Nowwear edonewithoursampleapplication.Uponrunningtheapplication,aformshouldpopupwithabutton
pr esentinitandonclickingit,theimagedataisinsertedintothedatabase
8. Oninsertingimagedataintothedatabase,wecanseethefollowingfolderstructur e:
Forexample,whenweinsertsomedataintotheV ARBINAR Ycolumnofthetable,wecanseethechangeinthefolder
structur e
insert into Files Values ( newid(), cast('My example' as varbinary(max)))
AfterexecutingtheaboveSQLin“FileM