Archivo de la categoría: SQL Server

Imágenes y SQL Server; BLOB, FILESTREAM…

En muchas ocasiones nos puede interesar almacenar en nuestra BD imágenes en formato binario. Esto es relativamente sencillo cuándo hacemos una aplicación en entorno web o entorno escritorio; tan solo debemos obtener la imagen, convertirla a binario y guardar la transacción en BD.

Existen varias formas de tratar las imágenes en una aplicación y cada una de ellas tiene sus ventajas e inconvenientes,

– Almacenar las imágenes en BD al completo, es decir, en un campo  varbinary(max).

– Almacenar las imágenes en disco y BD de forma hibrida mediante FILESTREAM.

– Almacenar la ruta relativa de las imágenes en disco y devolver la URL absoluta mediante la aplicación.

Con este abanico de posibilidades podemos escoger cual es la mejor forma de tener almacenada esta información.

Guardar las imágenes en base de datos

Para almacenar los datos en una tabla debemos crear una propiedad en esa tabla de tipo varbinary(max). Es altamente recomendable almacenar los datos en una tabla independiente a la de consulta habitual pues estaremos llenando las tablas de datos con BLOBs y deberemos dividir la información para no tener problemas de rendimiento. Es decir, si tenemos la tabla “Employee” y queremos guardar la foto del empleado, es preciso tener una tabla que sea “EmployeePhoto” para almacenar la imagen mediante una relación 1 a 1.

En esta tabla EmployeePhoto almacenaremos nuestra imagen para cada Empleado dentro del campo binario Photo. Si lo hacemos desde una aplicación tan solo debemos usar cualquier control de subida de ficheros y llenar ese campo con el resultado binario que nos facilita.

Si tenemos que llenar ese campo desde una migración de BD, deberemos usar una carga automatizada mediante un conjunto de sentencias SQL.

INSERT INTO EmployeePhoto (IdEmployee, Photo) SELECT 1, BulkColumn FROM OPENROWSET(Bulk 'C:\Photo\DSC_000873.jpg', SINGLE_BLOB) AS BLOB

El OPENROWSET obtiene los datos en binario por nosotros y podemos guardar las imágenes en BD desde disco físico.

Comentar, que este sistema tiene una limitación de 2G, que su acceso a lectura es lento en comparación a los demás, pero que para desarrollar y según en que tipo de aplicaciones es extremadamente útil y sencillo. Desde mi experiencia en estos sistemas, recomiendo usar este sistema cuándo tengamos imágenes de 50kb a 250kb. Debemos tener en cuenta, que estos datos, consumen memoria al ejecutar una consulta.

Guardar las imágenes mediante FILESTREAM

Microsoft SQL Server 2008 y R2 ofrecen un sistema de almacenaje híbrido entre la BD y el sistema de ficheros local. Este se llama FILESTREAM y por defecto no viene activado en nuestros servidores. FILESTREAM intgra el engine de SQL con el sistema de archivos NTFS almacenando BLOB como archivos en el sistema de archivos. Además hace uso extensivo del sistema de caché que tiene el sistema NT para almacenar en memoria caché de archivos. A diferencia del anterior sistema, este no usa el buffer del engine y en consecuencia deja memoria libre para el procesamiento de consultas.  Es conveniente usar este sistema cuándo tenemos necesidad de un acceso de lectura rápido, si son ficheros mayores de 1MB, capas intermedias de la aplicación que transfieren y procesan datos de BD.

Lo primero que vamos a tener que hacer, es configurar el FILESTREAM en nuestra instancia de SQL Server.

Para ello debemos ir a Inicio > Todos los programas >Microsoft SQL Server 2008 R2 > Herramientas de configuración >Administración de configuración de SQL Server y se nos abrirá la siguiente ventana:

Nos situamos encima de nuestra instancia de SQL Server (En este caso MSSQLSERVER2008R) y le hacemos clic con el botón derecho del ratón para acceder a la ventana de propiedades. Una vez dentro de esta tendremos una ventana con varias pestañas; nos interesa la de FILESTREAM.

 

Debemos habilitar el FILESTREAM con todas sus opciones y posteriormente ejecutar el siguiente SCRIPT SQL en la consola de administración de SQL Server (SQL Server Management),

EXEC sp_configure filestream_access_level, 2
GO 
RECONFIGURE
GO

Finalmente, deberemos reeinciar el servidor SQL Server (botón derecho encima del servidor en SQL Server Management Studio y Reiniciar).

Una buena observación para saber si todo ha funcionado cómo debía, es ejecutar el comando NET SHARE en la consola CMD de nuestro servidor y allí veremos que ahora tenemos una nueva carpeta compartida. ¡Genial!

Ahora, al crear una nueva BD en nuestra consola de SQL Server deberemos configurar el Grupo de archivos.

Nos vamos a crear una nueva BD llamada BLOB dónde tendremos que crear un grupo de archivos llamado FileStream. La mejor forma de hacerlo después de crear nuestra base de datos es mediante otro fantástico y sencillo script de SQL.

ALTER DATABASE BLOB
ADD FILEGROUP FileStreamGroup CONTAINS FILESTREAM
GO
ALTER DATABASE BLOB
ADD FILE 
(
NAME = 'FileStreamFile'
,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER2008R\MSSQL\DATA\BLOB'
)
TO FILEGROUP FileStreamGroup
GO

Esta configuración nos permite tener tablas en nuestra BD que almacenen datos en el sistema de ficheros de nuestro servidor. Para ello crearemos nuestra tabla con un identificador único (unique identifier) que le servirá a SQL para identificar de forma única el fichero almacenado. Este último punto es muy importante, ya que si no es así, no funcionará.

CREATE TABLE dbo.EmployeePhoto
(
IdEmployee int Primary Key IDENTITY (1, 1),
IdEmployeePhoto uniqueidentifier NOT NULL UNIQUE ROWGUIDCOL Default newid(),
FileData varbinary(MAX) FileStream NULL
)

Ahora, en esta tabla, podemos guardar normalmente desde nuestras aplicaciones o bien desde SQL.

 

 

 

Cómo vemos, ha creado una carpeta en DATA llamada BLOB que gestionará los datos almacenados mediante FileStream. Vamos a introducir una imagen dentro de esa tabla ejecutando el siguiente script de inserción

Insert Into EmployeePhoto([FileData])
Values((SELECT BulkColumn FROM OPENROWSET(Bulk 'C:\SomBits.png', SINGLE_BLOB) AS BLOB))

Ya tenemos el fichero en nuestro sistema híbrido dónde SQL nos hará de enlace con el sistema de ficheros NTFS. Para recuperar el fichero debemos usar una sencilla consulta.

SELECT FileData.PathName() FROM EmployeePhoto

Y este nos devolverá la ruta completa de nuestra imagen en disco, que en nuestro caso, será  \\PORTATIL\MSSQLSERVER2008R\v1\BLOB\dbo\EmployeePhoto\FileData\FBBA3388-9E5A-4AF3-9437-7ED02A2F2E3F fijaros que el unique identifier del final es justo el que almacena en la tabla. Si nos vamos al sistema de ficheros físico veremos el fichero, y en este caso, si copiamos y pegamos el mismo cambiando su extensión por “.png” veremos que nuestra imagen está almacenada correctamente en nuestro sistema físico a través de nuestro gestor de datos.

Almacenar la ruta relativa de las imágenes en disco

Nuestra última alternativa, y por ese motivo la guardaba al final, es la típica de almacenar las rutas en tablas y luego acceder a esa ruta relativa de forma absoluta desde la aplicación. Justo la guardaba para el final para comentar que, teniendo la alternativa anterior, no es recomendable usar esta vía debido a que juntaremos demasiados inconvenientes en una única solución.

En resumen, usar FileStream es la mejor alternativa para ficheros grandes o para grandes cantidades de ficheros y almacenar ficheros en binario en nuestras tablas, es  otra alternativa en el uso de almacenaje de fichero de poco peso y en dónde los sistemas FileStream, tales cómo servidores hospedados, puede estar no disponible.

Happy save 😉