SQL Server FILESTREAM – Skladištenje nestuktuiranih podataka

U ranijim verzijama SQL Server-a skladištenje nestruktuiranih podataka postavljalo je mnoge izazove u smislu održavanja konzistencije između struktuiranih i nestruktuiranih podataka, upravljanja backup/restore procedurom, problemima sa perfomansama, skalabilnošću itd…

Pre SQL Servera 2008 postojali su različiti mehanizmi za skladištenje nestruktuiranih podataka. Ti podaci su obično u formi fajlova skladišteni u zajedničkom folderu za koje su određenim korisnicima dodeljivana prava pristupa. UNC putanja ovih fajlova je obično skladištena u bazi kao kolona  u tabeli (varchar(n)) da bi aplikativna logika mogla da pristupa konkretnim fajlovima. Problem je bio u bezbednosti fajlova, administriranju pristupa njima i njihovom održavanju.

Kasniji koncept Binary Large Objects (BLOB) je pomogao u skladištenju nestruktuiranih podataka do izvesnog stepena. Glavna prednost ovog koncepta je integrisani management i transakciona koenzistencija unutar baze podataka. Problemi sa bezbednošću (prethodnog rešenja sa fajlovima) su u ovom slučaju rešeni.  Problemi su ipak postojali a to su ograničenje od 2Gb i preopterećenje log fajla.

Da bi se prevazišli ovi problemi SQL Server 2008 je uveo poboljšanje koje je nazvano FILESTREAM.

To nije tip podataka kao što je VARBINARY (MAX) već atribut /property postavljen preko VARBINARY kolone da bi se pokazalo da podatak treba da se skladišti direktno na fajl sistem. Takvi podaci ostaju integralni deo sistema i održavaju transakcionu konzistenciju. Neke od osobina FILESTREAM-a su:

  1. Binarni podaci se skladište kao indivdualni fajlovi van baze podataka,
  2. Tim individualnim fajlovima može da se pristupa kroz WIN32 API za operacije sa fajlovima,
  3. Primenjivi su T-SQL izrazi,
  4. Ograničenje od 2GB za veličinu fajla za VARBINARY(MAX) kolone su uklonjene za objekte skladištene u fajl sistemu kroz FILESTREAM,
  5. FILESTREAM može biti korišćen i na komprimovanim folderima, diskovima, volumes-ima.

Na slici 1 je dat Data Flow korišćenja FILESTREM-a.

Slika 1. Data Flow korišćenja FILESTREAM-a

Da bi se koristio FILESTREAM potrebno ga je enable-ovati. FILESTREAM se enable-uje na dva nivoa. Prvi nivo je na nivou SQL Servera. Ovaj nivo je bilo moguće enabl-ovati prilikom instalacije SQL Server-a. Ako to tad nije urađeno, enabl-ovanje se može uraditi kroz SQL Server Confguration Manager čekiranjem ček  boksova na FILESTREAM kartici Properties-a SQL Server-a (slika 2).

Slika 2. Enabl-ovanje FILESTREAM-a na nivou SQL Servera korišćenjem SQL Server Configuration Manager-a

Drugi nivo enabl-ovanja FILESTREAM-a je na nivou SQL Server Instance. To enabl-ovanje se radi izvršavanjem T-SQL skrpta koji je dat u  skrptu 1.

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

Skript 1. Enabl-ovanje FILESTREAM-a na nivou Server Instance

U tabeli 1 su date moguće  vrednosti za filestream_access_level.

Value Description
0 Disables FILESTREAM support for this instance.
1 Enables FILESTREAM for Transact-SQL access.
2 Enables FILESTREAM for Transact-SQL and Win32 streaming access.

Tabela 1. Moguće vrednosti za filestream_access_level

Sledeći korak je kreiranje baze u kojoj je moguće kreiranje tabela u kojima će se koristiti FILESTREAM. Primer T-SQL skripta za kreiranje baze u kojoj će biti moguće korišćenje FILESTREAM-a je dat u skriptu 2.

CREATE DATABASE Test_FILESTREAM
ON
PRIMARY(
NAME=Test_FILESTREAM_Prmary,
FILENAME='d:\sqlbaze\mssql\data\FILESTREAM\Test_FILESTREAM.mdf'),
FILEGROUP FileStreamGroup CONTAINS FILESTREAM(
NAME=Test_FILESTREAM_FM,
FILENAME='d:\sqlbaze\mssql\data\FILESTREAM\Test_FILESTREAM_FM')
LOG ON (
NAME=Test_FILESTREAM_Log,
FILENAME='d:\sqlbaze\mssql\data\FILESTREAM\Test_FILESTREAM.log')

Skript 2. Primer kreiranja baze u kojoj će biti moguće koristiti FILESTREAM

Izvršavanjem skripta 2 na fajl sistemu će biti kreirani folder $FSLOG i fajl filestream.hdr (slika 3). Fajl filestream.hdr je heder fajl za FILESTREAM kontejner.

Slika 3. Novokreirani folder za FILESTREAM i header fajl

Sledeći korak je kreiranje tabele sa  kolonom VARBINARY(MAX) sa FILESTREAM atributom (skript 3).

 

USE Test_FILESTREAM

CREATE TABLE [dbo].[Fajlovi]
(
ID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL PRIMARY KEY,
Fajl VARBINARY(MAX) FILESTREAM DEFAULT NULL
)

Skript 3. Kreiranje tabele sa VARBINARY(MAX) FILESTREAM kolonom

 

Još je ostalo da se insertuje zapis u novokreiranu tabelu (skript  4).

 

INSERT INTO Fajlovi
VALUES (newid(), cast('Moj test FIESTREAM-a' as varbinary(max)))

Skript 4. Insertovanje zapisa u tabelu sa VARBINARY(MAX) FILESTREAM kolonom

Insertovanjem zapisa skriptom 4 kreiraće se i novi folder na fajl sistemu (slika 4).

Slika 4. Insertovanjem zapisa u tabelu kreira se i novi folder na fajl sistemu

Zapisi iz tabele sa VARBINARY(MAX) FILESTREAM kolonom mogu se dobiti izvršavanjem skripta 5.

select *
from [Test_FILESTREAM].[dbo].[Fajlovi]

Skript 5. Pregledavanje zapisa iz tabele sa VARBINARY(MAX) FILESTREAM kolonom

Rezultat izvršavanja skripta 5 dat je na slici 5.

Slika 5. Zapis iz tabele sa VARBINARY(MAX) FILESTREAM kolonom

Svaka ćelija u FILESTREAM koloni je putanja datoteke na fajl sistemu koja je povezana sa njom. Da bi se pročitala putanja, potrebno je koristiti svojstvo PathName varbinary(max) kolone u T-SQL izrazu. U skript-u 6 dat je primer kako se čita putanja datoteke varbinary(max) kolone.

DECLARE @filePath varchar(max)

SELECT @filePath = Fajl.PathName()
FROM dbo.Fajlovi
WHERE ID = 'F9A149D0-F5F0-4FC5-9F59-1D27E4D10C1D'

PRINT @filepath

Skript 6. Primer čitanja putanje FILESTREAM datoteke na fajl sistemu

Za  rad sa FILESTREAM podacima može se koristiti T-SQL ali je za to ipak prirodnije okruženje MS Visual Studio. Korišćenje FILESTREAM funkcionalnosti, u aplikativnoj logici, se omogućava implementacijom klase System.Data.SqlTypes.SqlFileStream.

Da bi se zadržala konzistentnost podataka svaka SQL FILESTREAM operacija mora biti u okviru transakcije. MARS (Multiple Active Result Sets) konekcije  imaju posebna pravila za batch transakcije, što T-SQL BEGIN TRANSACTION izjava krši. Da bi se izbegao ovaj problem, klijent aplikacija treba da koristi odgovarajući API za transaction mnagement tj. klasu System.Data.SqlClient. SqlTransaction.

Da bi se omogućio transakcioni pristup fajl sistemu sa FILESTREAM podacima, potrebno je koristiti T-SQL funkciju GET_FILESTREAM_TRASACTION_CONTEXT() da  bi se obezbedio token koji reprezentuje trenutnu transakciju u okviru konkretne sesije (C# kod 1).

SqlConnection sqlConnection = new SqlConnection(

"Integrated Security=true;server=(local)");
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;

SqlTransaction transaction = sqlConnection.BeginTransaction("mainTranaction");
sqlCommand.Transaction = transaction;

sqlCommand.CommandText =
"SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";

Object obj = sqlCommand.ExecuteScalar();

byte[] txContext = (byte[])obj;

C# kod 1. Korišćenje funkcije GET_FILESTREAM_TRASACTION_CONTEXT()

Na ovaj način transakcija je pokrenuta ali još uvek nije commit-ovana ili rollback-ovana. U okviru jedne transakcije se može raditi više operacija sa FILESTREAM podacima. Primer koda za upis podataka korišćenjem Win32 API-ja je dat u C# kod 2 u kome se na početku kreira objekat sqlFileStream na osnovu klase System.Data.SqlTypes.SqlFileStream.

SqlFileStream sqlFileStream = new SqlFileStream(filePath, txContext, FileAccess.ReadWrite);

byte[] buffer = new byte[512];

int numBytes = 0;

//Upis stringa, "FILESTREAM test podaci" u FILESTREAM.
string someData = "FILESTREAM test podaci";
Encoding unicode = Encoding.GetEncoding(0);

sqlFileStream.Write(unicode.GetBytes(someData.ToCharArray()),0,someData.Length);

C# kod 2. Primer koda za upis podataka u FILESTREAM

Primer koda za čitanje FILESTREAM podataka, korišćenjem Win32 API-ja, je dat u C# kod 3. Koristi se isti objekat sqlFileStream koji je kreiran na početku primera C# kod 1.

sqlFileStream.Seek(0L, SeekOrigin.Begin);
numBytes = sqlFileStream.Read(buffer, 0, buffer.Length);
string readData = unicode.GetString(buffer);

if (numBytes != 0)
Console.WriteLine(readData);

C# kod 3. Primer koda za čitanje FILESTREAM podataka

Po završetku transakcija upisa u FILESTREAM (C# kod 1) i čitanja podataka iz FILESTREAM-a (C# kod 2) potrebno je zatvoriti objekat sqlFileStream (koji je kreiran na početku primera C# kod 1 na osnovu klase System.Data.SqlTypes.SqlFileStream) i commit-ovati transakciju (C# kod 4). Objekat sqlCommand je kreiran na početku primera C# kod 1.

sqlFileStream.Close();
sqlCommand.Transaction.Commit();

C# kod 4. Zatvaranje objekta sqlFileStream i commit-ovanje transakcije

Grafikon uporednih vrednosti perfomansi učitavanja podatka, kad se za skladištenje nestruktiranih podataka koriste VARBINARY(MAX) i FILESTREAM a za pristup podacima se koriste T-SQL ili Win32 je dat na slici 6.

Slika 6. Perfomanse učitavanja BLOB-ova različitih veličina

Treba još napomenuti da se FILESTREAM koristi i u sklopu drugih SQL Server tehnologija, kao što su FileTable i In Memory OLTP.

Na kraju:

Kao i svaka druga tehnologija i FILESTREAM ima svoje prednosti i nedostatke u korišćenju.

Prednosti:

  • Fajlovima kreiranim kao deo FILESTREAM-a se upravlja od strane samog SQL Servera u sopstvenim fajl grupama od kojih može biti urađen backup i restore zajedno sa drugim SQL Server podacima,
  • Čitanje i pisanje ovih fajlova je deo database transaction-a,
  • Moguće je skladištiti veoma velike BLOB objekte,

Nedostaci:

  • FILESTREAM podaci mogu biti skladišteni samo na lokalnim disk volumen-ima,
  • Nije podržan u database snapshot-u,
  • Nije podržan na database miroring-u,
  • Nije podržan Transparent Data Encryption,
  • Ne može da se koristi kod table valued parametara,

Leave a Reply

%d bloggers like this: