FileTables y FileStream en SQL Server

FileTables y FileStream

Como usar tablas de Archivos en SQL Server

Características

  • La característica FileTable brinda compatibilidad con el espacio de nombres de archivos de Windows y la compatibilidad con las aplicaciones de Windows con los datos de archivos  almacenados en SQL Server.
  • FileTable permite que una aplicación integre sus componentes de almacenamiento y administración de datos, y proporciona servicios integrados de SQL Server, que incluyen búsqueda de texto completo y búsqueda semántica, sobre datos no estructurados y metadatos.
  • Con el uso de FileTables se puede almacenar archivos y documentos en tablas especiales en SQL Server llamadas FileTables, pero acceda a ellos desde las aplicaciones de Windows como si estuvieran almacenados en el sistema de archivos, sin realizar ningún cambio en las aplicaciones de sus clientes.
  • La característica FileTable se basa en la tecnología FILESTREAM de SQL Server.




Como usar FileStream en SQL Server

  • FILESTREAM permite que las aplicaciones basadas en SQL Server almacenen datos no estructurados,  como documentos e imágenes, en el sistema de archivos.
  • Las aplicaciones pueden aprovechar las valiosas API de transmisión y el rendimiento del sistema de archivos y, al mismo tiempo, mantener la coherencia transaccional entre los datos no estructurados y los datos estructurados correspondientes.
  • FILESTREAM integra el Motor de base de datos del servidor SQL con un sistema de archivos NTFS o ReFS al almacenar datos de objetos binarios grandes (BLOB) varbinary (max) como archivos en el sistema de archivos.
  • Las instrucciones de Transact-SQL pueden insertar, actualizar, consultar, buscar y hacer una copia de seguridad de los datos de FILESTREAM.
  • Las interfaces del sistema de archivos Win32 proporcionan acceso continuo a los datos FILESTREAM utiliza la memoria caché del sistema NT para almacenar datos de archivos en caché.  Esto ayuda a reducir cualquier efecto que los datos de FILESTREAM puedan tener en el  rendimiento del motor de base de datos.
  • El grupo de búferes de SQL Server no se usa; por lo tanto, esta memoria está disponible para el procesamiento de consultas.
  • FILESTREAM no se habilita automáticamente cuando instala o actualiza SQL Server.  Debe habilitar FILESTREAM utilizando el Administrador de configuración de SQL Server y SQL Server Management Studio.
  • Para usar FILESTREAM, debe crear o modificar una base de datos para contener un tipo especial de grupos de archivos.
  • A continuación, cree o modifique una tabla para que contenga una columna varbinary (max) con el atributo FILESTREAM.
  • Después de completar estas tareas, puede usar Transact-SQL y Win32 para administrar los datos de FILESTREAM.

Como configurar y utilizar FileStream en SQL Server

1.  Como habilitar los servicios de FileStream

En este paso debemos abrir el Administrador de configuración de SQL Server.


En el servicio del motor de base de datos, pulsar doble clic, se abre la ventana de propiedades. Activar la ficha FileStream. La imagen muestra la ventana de configuración.


Activar las casillas de verificación
Habilitar FILESTREAM para el acceso de Transact-SQL
Habilitar FILESTREAM para el acceso de E/S de archivo
Escribir el nombre del recurso compartido de Windos, que por defecto es el nombre de la instancia.
Permitir que los clientes remotos tengan acceso a los datos de FILESTREAM.

2. Como configurar el servidor para FileStream

En el servidor pulsar botón derecho e ir a propiedades, en la opción Avanzados, configurar el
FILESTREAM para el acceso total.


También se puede ejecutar el siguiente procedimiento

EXEC sys.sp_configure N’filestream access level’, N’2′
GO
RECONFIGURE WITH OVERRIDE
GO

3. Como configurar la base de datos para FileStream

Abrir la ventana de propiedades de la base de datos, luego en la página opciones configurar el
nivel de acceso a Full. Las otras opciones son: Off y ReadOnly. En la figura se muestra las opciones seleccionadas para la base de datos Northwind.

También se puede configurar utilizando el siguiente código.

USE master
GO
ALTER DATABASE Northwind
SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL )
WITH NO_WAIT
GO

4. Como configurar el grupo de archivos para FileStream

En la base de datos se necesita un grupo de archivos que tenga la opción de FileStream habilitada,  usando la base de datos Northwind, insertar un grupo y luego un archivo al grupo.
Agregar un grupo de archivos para FileStream

USE master
GO
ALTER DATABASE Northwind ADD FILEGROUP GrupoTablasArchivos CONTAINS FILESTREAM
GO

Agregar un archivo al grupo de archivos GrupoTablasArchivos, el archivo se creará en la  carpeta ArchivosStream en la unidad C: (Ver Crear Bases de datos)

xp_create_subdir ‘C:\ArchivosStream’
go
Alter database Northwind
add file (name=’ArchivosStreamData’, filename = ‘C:\ArchivosStream\ArchivosStreamData.ndf’)
to filegroup [GrupoTablasArchivos]
go

En la carpeta donde se creo el archivo aparece un archivo con nombre FileStream.hdr que contiene los
metadatos que relacionan los registros insertados.

5. Como especificar el directorio de FileStream

Este directorio permitirá compartir los archivos.

ALTER DATABASE  Northwind  set Filestream
        ( DIRECTORY_NAME = N’ArchivosStream’ ) WITH NO_WAIT
go

6. Como listar las bases de datos con FileStream

Para comprobar si la base de datos Northwind permite y tiene activada la opción FileStream se visualizan las bases de datos que soportan FileStream y el directorio con la siguiente consulta.

select DB_Name(database_id) As ‘Base de datos’, directory_name As ‘Carpeta FileStream’
from sys.database_filestream_options
go

El recurso compartido donde se guardarán los archivos se puede visualizar en la siguiente imagen.

Como crear una tabla con FileStream

Para poder utilizar una tabla y utilizar las opciones que provee FileStream de debe crear una con un identificador UniqueIdentifier y el campo con la propiedad FileStream debe ser de tipo varbinary(max)

Create Table dbo.TablaFileStream
         (Codigo UniqueIdentifier RowGuidCol Not Null Unique,
        Descripcion nvarchar(100),
        Foto varbinary(max) filestream null)
on [Primary]
go

Al insertar registros se especifican las imágenes o documentos usando OpenRowSet
(Ver Insertar imágenes desde SQL Server Management Studio)

use Northwind
go
insert into TablaFileStream (Codigo, Descripcion, Foto)
select NEWID(), ‘Filtro PiWater Nikken’, *
from OpenRowset(Bulk ‘C:\Fotos\FiltroPiWater.jpg’, Single_Blob) As Foto
go

Ver los registros
Select * from TablaFileStream
go

Restricciones en el uso de FileStream

  • No se puede usar Database Mirroring en las bases de datos configuradas para soporte FILESTREAM.
  • Las tablas temporales (en memoria) no pueden tener columnas con FILESTREAM habilitado.
  • FILESTREAM sólo permite almacenar datos en volúmenes de disco locales.
  • Las columnas computadas que hacen referencia a columnas FILESTREAM no se pueden indexar.
  • Las columnas FILESTREAM no soportan Transparent Data Encription (TDE)
  • FILESTREAM no se puede habilitar en SQL Server de 32 bits que se ejecutan sobre sistemas operativos Windows de 64 bits.
  • No se permite la creación de estadísticas en columnas con FILESTREAM habilitado.
  • El único nivel de aislación (Isolation Level) soportado es el modo READ COMMITED cuando los datos de FILESTREAM son accedidos a través de la API Win32



Como crear la tabla tipo FileTable

Las tablas tipo FileTable permiten el manejo de archivos compartidos. Estos archivos se guardan en el recurso compartido de la instancia donde se especificó el uso de FileStream.
En la imagen siguiente podemos ver el recurso compartido.

use Northwind
go
Create table Northwind.dbo.MisDocumentos AS FileTable
with (FileTable_Directory = ‘ArchivosStream’,
FileTable_Collate_fileName = database_default )
go

Se puede agregar archivos desde el Explorador de Windows en el recurso compartido de la
instancia de SQL Server

Agregar carpetas al FileTable

insert into MisDocumentos (name, is_directory)
values (‘Fotografias’,1)
go
insert into MisDocumentos (name, is_directory)
values (‘Videos’,1)
go

Ver los datos del FileTable
select * from [dbo].[MisDocumentos]
go

Estas opciones de manejo de archivos son muy útiles para compartir archivos guardados en una tabla dentro de la base de datos con FileTables o guardados en un recurso compartido y administrados desde el sistema operativo Windows.