Infosys - Using SQL Server FILESTREAM to store BLOBs - Tutorial

Infosys - Using SQL Server FILESTREAM to store BLOBs - Tutorial

-

English
8 Pages
Read
Download
Downloading requires you to have access to the YouScribe library
Learn all about the services we offer

Description

Tutorial: Using SQL Server FILESTREAM to store BLOBsBalasubramanian ShanmugamIntroductionIn 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 tierFormor einformation,Contact askus@infosys.comOct2008Overview of FILESTREAM“FILESTREAM”isanewfeatur eintr oducedinSQLSer ver2008tostor eunstructur edbinar yfilesinthedatabase.It’ snotadatatypelikeV ARBINAR Y(MAX),butanattribute/pr opertyimposedupontheV ARBINAR Ycolumntotellthatthedatahastobestor eddir ectlyonthefilesystem.Theyr emainasintegralpartofthesystemandmaintainatransactionalconsistency .Followingar eafewpointsonFILESTREAM:1. Binar ydatastor ...

Subjects

Informations

Published by
Reads 27
Language English
Report a problem

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 askus@infosys.com
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“FileManagement”database,achangeisobser vedinthealr eadycr eatedfolder
structur e:
Figure 5: Encircled folder created for FILESTREAM data
Datawillbestor edinthetableasbelow:
Figure 6: Representation of FILESTREAM data in SQL Server table
MSDN Reference
DesigningandImplementingFILESTREAMStorage
http://msdn.micr osoft.com/en-us/librar y/bb895234(SQL.100).aspx
Summary
Asmentionedearlier ,thoughthepr eviousversionsofSQLSer versupportBLOBdatatypetostor elar gesizedata,the
appr oachisbit-complexandlessflexible.FILESTREAMpr ovidesaneasywayofhandlingBLOBdatawithdatabase
transactionalconsistencyandthusimpr ovestheperformance.Thesizeofthefileislimitedbythediskspacethatmakesthis
featur emor euseful.
Download source code
forthecodeofthistutorialcontact V ir endra_W adekar@infosys.com.
Infosys–WhitePaper| 7Infosys among the world’s top 50 most respected companies
Reputation Institute’s Global Reputation Pulse 2009 ranked Infosys among the
world’s top 50 most respected companies.
About Infosys
Infosys provides its clients with business and technology consulting services that
deliver measurable business value to help you build tomorrow’s enterprise. Through a
well-integrated end-to-end range of consulting, technology, engineering and
outsourcing, Infosys clients derive the business value they’ve always been requiring
from enterprise transformations. Our extensive research teams, including the
award-winning SET Labs, ensure Infosys solutions are always cutting-edge and
relevant. Our high investment in training – over 10,000 graduates a year pass
through our Mysore campus, the world’s largest corporate university – ensures our
people stay best-in-class. Infosys (NASDAQ:INFY) also believes in giving back to the
communities with scholarships, libraries, schools, and many other elds through the
Infosys Foundation.
Global presence
The Americas Europe India Asia Pacic
Atlanta Amsterdam Bangalore Brisbane
Bellevue Brno Bhubaneswar Dubai
Belo Horizonte Brussels Chandigarh Hangzhou
Bentonville Copenhagen Chennai Hong Kong
Boston Dublin Gurgaon Kuala Lumpur
Calgary Frankfurt Hyderabad Manila
Charlotte Geneva Jaipur Melbourne
Chicago Helsinki Mangalore Moscow
Detroit Łódz´ Mumbai Perth
Fremont London Mysore Shanghai
Hartford Madrid New Delhi Sharjah
Houston Milano Pune Singapore
Los Angeles Milton Keynes Thiruvananthapuram Sydney
Minneapolis Oslo Tokyo
Monterrey Paris Wellington
New York Prague
AfricaPhiladelphia Stockholm
ReduitPhoenix Stuttgart
Plano Swindon
Reston Toulouse
Toronto Walldorf
Zurich
For more information, contact askus@infosys.com www.infosys.com
© 2011 Infosys Technologies Limited, Bangalore, India. Infosys believes the information in this publication is accurate as of its publication date; such
information is subject to change without notice. Infosys acknowledges the proprietary rights of the trademarks and product names of other companies
mentioned in this document.
Did you
know?