FILESTREAM de um jeito prático e objetivo

Antes de começar a falar do FILESTREAM propriamente dito, quero falar um pouco sobre a importância de se participar das “Comunidades Virtuais“, segundo o Wikipédia, resumidamente este termo define-se pelo estabelecimento de relações num espaço virtual por indivíduos com interesses em comum. Nestes espaços virtuais, o conhecimento é compartilhado e você sempre encontra pessoas que abraçam sua causa e vão com você até a solução final, é onde literalmente aplica-se a famosa frase: “No man is an island” (John Donne). Então participe ativamente contribuindo e questionando sempre nas comunidades virtuais, independente do assunto relacionado,

Sem mais delongas, e partindo para o que realmente interessa, recentemente fui questionado sobre a melhor maneira de se armazenar arquivos de tipos diversos (.doc, .pdf, .xls, .jpg, etc etc) dentro do SQL SERVER, para que pudesse haver gerenciamento de segurança e backup dos arquivos sendo administrados pelo SGDB. Para que não ficassem dúvidas sobre as melhores práticas de implantação de mais esta extraordiniária funcionalidade do SQL SERVER, recorri à comunidade para discutir alguns pontos e sanar algumas dúvidas. Prontamente atendido pelo amigo de profissão “Marcos Freccia (Blog  –  Twitter)”, dentre vários outros.

Definição: Segundo a página do Technet, o FILESTREAM integra o SQL SERVER com o sistema de arquivos NTFS armazenando objetos do tipo (BLOB) com o tipo varbinary(max) utilizando-se da do sistema de arquivos NTFS para este armazenamento. É possível através de T-SQL realizar a inserção, exclusão, atualização e consulta e backup destes dados.

Neste post, faremos uma implementação Básica e funcional do FileStream para que sejam armazenados os arquivos e atendidos os requisitos supracitados.

Na prática:

Antes de tudo é necessário habilitar o FILESTREAM na instância em que será utilizado o recurso. para isto, abra uma nova Query, e execute o seguinte comando:

–Altera para exibição das configurações avançadas, e aplica estas alterações…
exec sp_configure ‘show advanced options’,1
go
reconfigure

–Altera o nível de acesso do filestream, de 0 para 2, para que este possa ser usado…
exec sp_configure ‘filestream access level’,2
go
reconfigure

–Retorna a configuração avançadas ao seu estado default, e aplica estas alterações…
exec sp_configure ‘show advanced options’,0
go
reconfigure

O segundo passo, é habilitar o FILESTREAM no serviço do SQL SERVER. Para isto acesse:

Iniciar > Todos os Programas > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager

Habilite as opções de utilização do FILESTREAM e dê um nome ao compartilhamento (Windows Share Name), ou mantenha o que for sugerido (recomendado).

Após as configurações realizadas no nível de instância, é necessário configurar a base de dados que será utilizada para o FILESTREAM. É possível também utilizar uma base já pré-existente na instância e até mesmo tabelas, bastando para isso, realizar algumas alterações tanto na base, como na tabela.

• A base que será utilizada para o FILESTREAM, OBRIGATORIAMENTE deve ter um Filegroup específico para este fim.
• A tabela que será utilizada parao FILESTREAM, deverá ter uma estrutura mínima semelhante á definida na tabela de exemplo apresentada logo abaixo.

Para criação do banco de dados, será executado o script abaixo, que fará a criação desta base já com os devidos filegroups e definiões necerrárias para o FILESTREAM.

USE [master]
GO
CREATE DATABASE [FILESTREAMDBNOVO]
        ON PRIMARY (
NAME = N’FILESTREAMDB’
,FILENAME = N’D:MSSQL2008FILESTREAMFILESTREAMDBNOVO.mdf’)
,FILEGROUP [FILESTREAMGROUP] CONTAINS FILESTREAM  DEFAULT (
NAME = N’FS’
,FILENAME = N’D:MSSQL2008FILESTREAMDBNOVO_2.FS’)
      LOG ON (
NAME = N’FILESTREAMDB_log’
,FILENAME = N’D:MSSQL2008FILESTREAMFILESTREAMDBNOVO_1.LDF’)
GO

Após a criação da base de dados, será feito a criação de duas tabelas, que estão relacionadas para exemplificar o uso desta funcionalidade do SQL SERVER. A primeira tabela (FSDESC) contém todos os dados de identificação do tipo de arquivo que for inserido. A segunda tabela (FSDATA), contém os dados referentes aos arquivos que foram armazenados. Abaixo, os scripts de criação destas tabelas.

USE [FILESTREAMDBNOVO]
GO
CREATE TABLE FSDESC (
IDFSDESC INT IDENTITY NOT NULL,
DESCRICAO VARCHAR(50) NOT NULL
)
GO
ALTER TABLE FSDESC ADD CONSTRAINT PK_FSDESC PRIMARY KEY (IDFSDESC)
GO

USE [FILESTREAMDBNOVO]
GO
CREATE TABLE FSDATA (
IDFSDATA INT IDENTITY NOT NULL,
IDDESC INT NOT NULL,
IDGUID UNIQUEIDENTIFIER ROWGUIDCOL UNIQUE NOT NULL,
FSDATA VARBINARY(MAX) FILESTREAM NULL
)
ALTER TABLE FSDATA ADD CONSTRAINT PK_IDFSDATA PRIMARY KEY (IDFSDATA)
GO
ALTER TABLE FSDATA ADD CONSTRAINT FK_DESC_DATA FOREIGN KEY (IDDESC) REFERENCES FSDESC(IDFSDESC)

Feito a criação da base de dados e das respectivas tabelas, será realizado a inserção de dados nas tabelas, sendo primeiramente alimentada a tabela FSDESC com descrições de 3 tipos diferentes de arquivos, apenas para exemplificar:

INSERT INTO FSDESC (DESCRICAO)
VALUES(‘IMAGEM .JPG’),(‘E-BOOK .PDF’),(‘OFFICE .DOC’)
GO

Após a inserção destes dados, será feito a inserção na tabela dos dados do FILESTREAM, vale ressaltar, que os dados não estarão fisicamente armazenados dentro do SQL SERVER, mas em um diretório NTFS específico do FILESTREAM, que foi previamente definido.

INSERT INTO FSDATA (IDDESC,IDGUID,FSDATA)
SELECT 1,NEWID(),*
FROM OPENROWSET (BULK N’C:TEMPDesert.jpg’,SINGLE_BLOB) AS Document
GO
INSERT INTO FSDATA (IDDESC,IDGUID,FSDATA)
SELECT 3,NEWID(),*
FROM OPENROWSET (BULK N’C:TEMPdocumentoWord.doc’,SINGLE_BLOB) AS Document
GO
INSERT INTO FSDATA (IDDESC,IDGUID,FSDATA)
SELECT 2,NEWID(),*
FROM OPENROWSET (BULK N’C:TEMPMS_Learning_Transcript.pdf’,SINGLE_BLOB) AS Document

Tendo sido inseridos os registros, é possível fazer um select unindo as duas tabelas, para exibição dos registrosque estão armazenados já utilizando o FILESTREAM.

–Select para exibir os arquivos inseridos…
SELECT A.DESCRICAO, B.IDFSDATA,B.IDGUID, B.FSDATA
FROM FSDATA B
INNER JOIN FSDESC A
ON A.IDFSDESC = B.IDDESC

Para validação de que os arquivos não ficam fisicamente armazenados dentro da Engine do SQL SERVER, podemos observar o tamanho da tabela supracitada.

E compará-la ao espaço ocupado pelos arquivos fisicamente em seus locais de origem.

Uma das principais vantagens em se utilizar o FILESTREAM, é a capacidade de gerenciamento sobre o arquivo. Backup, segurança de acesso e muito mais pode ser gerenciado internamente por dentro do SQL SERVER.

Outras fontes:

• http://marcosfreccia.wordpress.com/2011/08/12/filestream/
• http://technet.microsoft.com/en-us/library/bb933993.aspx

Abraços e até a próxima…