Usando FileStream y FileTables en SQL Server

FileTables y FileStream en SQL Server

Tablas de Archivos en SQL Server

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. En otras palabras, 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.

FileStream en SQL Server

Características de FileStream de SLQ 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.

Pasos para configurar y utilizar FileStream

1. 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.

Administrador de configuración de SQL Server

En la ventana de propiedades seleccionar la ficha FileStream

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. Configurar el servidor para FileStream

En la ventana del explorador de onjetos de Microsoft SQL Server Management Studio, pulsar botón derecho en la instancia a configurar 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. 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

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. Agregar un 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.

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. 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. Comprobar si la base de datos Northwind permite y tiene activada la opción FileStream

Para visualizar las bases de datos que soportan FileStream y el directorio.
select DB_Name(database_id) As ‘Base de datos’, directory_name As ‘Carpeta FileStream’ from sys.database_filestream_options
go

Creando 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

Insertar registros en una tabla FileStream

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

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

Carpetas y archivos del FileTable