SQL Server

Aquí encontraras todos nuestros post relacionados con SQL Server desde cero hasta un nivel avanzado. Desde infraestructura hasta modelado de datos.

17
Feb
2025

Gestión de almacenamiento en SQL Server

¿Alguna vez te has preguntado cómo hace SQL Server para gestionar los datos en disco? Y no sólo SQL Server, la administración eficiente del almacenamiento es fundamental para garantizar un rendimiento óptimo y una gestión eficaz de los datos en cualquier sistema gestor de bases de datos. El motor de base de datos tiene que utilizar estructuras bien definidas, como páginas y extensiones, para organizar y controlar el espacio en disco. Para nosotros, como DBAs o usuarios avanzados, comprender en profundidad estas estructuras y sus mecanismos asociados es esencial para diseñar, implementar y mantener bases de datos de alto rendimiento.

Páginas, la unidad atómica de almacenamiento en SQL Server 

Una página es la unidad básica de almacenamiento en SQL Server, en estas páginas se guardan nuestros datos y tienen un tamaño fijo de 8 KB, siempre tienen el mismo tamaño.

Cada página comienza con un encabezado de 96 bytes que contiene información crucial, como el número de página, el tipo de página, la cantidad de espacio libre y el ID de la unidad de asignación del objeto propietario. Las filas de datos se almacenan secuencialmente después del encabezado, y al final de la página se encuentra una tabla de desplazamiento de filas que facilita el acceso rápido a cada fila.

Tipos de Páginas en función su almacenamiento

SQL Server utiliza diversos tipos de páginas para almacenar diferentes tipos de información.

Aunque el usuario medio podría pensar que todo lo que hay son páginas de datos lo cierto es que este es solo un tipo de página. En concreto, son las páginas que almacenan las filas de datos de las tablas pero, excluyendo los datos de tipos como text, ntext, image, nvarchar(max), varchar(max), varbinary(max) y xml cuando la opción «text in row» está activada. Esos datos veremos que van a otro tipo especial de página.

En concreto a páginas de texto o imagen que son las encargadas de almacenar datos de objetos grandes (LOB), incluyendo text, ntext, image, nvarchar(max), varchar(max), varbinary(max) y xml.

Para cerrar con las páginas que almacenan datos tenemos que nombrar las páginas de índice que contienen esas entradas de índices que facilitan el acceso rápido a los datos.

Pero esto no es todo, no sólo hay páginas con nuestros datos, SQL Server necesita una serie de metadatos para poder gestionar todo esto y los almacena en las páginas de asignación. En este tipo de páginas incluimos las páginas GAM (Global Allocation Map), SGAM (Shared Global Allocation Map), IAM (Index Allocation Map) y PFS (Page Free Space), que gestionan la asignación y el seguimiento del espacio en la base de datos.

Extensiones, como realmente SQL Server gestiona el almacenamiento

Una extensión (lo encontrarás también como extent) es un conjunto de ocho páginas físicamente contiguas, totalizando 64 KB. Las extensiones son la unidad básica mediante la cual SQL Server gestiona el espacio de almacenamiento. 

Permitidme hacer un parón en este punto para interiorizar esto, vuelve a leer el párrafo anterior y grábatelo a fuego así, la próxima vez que tengas que preparar un nuevo disco para almacenar datos de SQL Server entenderás por qué es una buena práctica formatear los discos con un tamaño de asignación de bloques de 64 KB.

Ahora sé, volvemos con los extent y, nuevamente, nos encontramos con que existen varios tipos. En este caso, no dependen del tipo de los datos que estamos almacenando sino de si son o no dedicados para un objeto. 

De esta manera, podemos encontrar extensiones uniformes donde las ocho páginas son propiedad de un único objeto o extensiones mixtas donde las ocho páginas pueden ser compartidas por hasta ocho objetos diferentes, permitiendo una asignación eficiente para objetos pequeños. Es importante destacar que aunque el extent puede ser mixto, las páginas siempre son dedicadas al 100% para el mismo objeto.

Almacenamiento GAM
Almacenamiento SGAM

A partir de SQL Server 2016, veremos que el comportamiento predeterminado es asignar extensiones uniformes para la mayoría de las operaciones. En principio para optimizar el rendimiento pero bueno, eso es un tema discutible. 

Gestión del almacenamiento

Como hemos comentado antes, SQL Server emplea varias estructuras internas (páginas de asignación) para gestionar la asignación y el uso del almacenamiento. Aquí va un pequeño resumen aunque ya os adelanto que a este tema le vamos a dedicar un artículo completo porque tiene mucho de lo que hablar.

Páginas GAM: mapa global de asignación de almacenamiento

Las páginas GAM (Global Allocation Map) registran qué extents han sido asignadas en el archivo de datos. Cada bit en una página GAM representa una extensión: un bit en 1 indica que la extensión está libre, mientras que un bit en 0 señala que está asignada. Cada página GAM puede rastrear aproximadamente 4 GB de datos, cubriendo 64,000 extents.

Páginas SGAM: mapa global de almacenamiento compartido

Las páginas SGAM (Shared Global Allocation Map) indican qué extents mixtos tienen al menos una página disponible para su uso. Un bit en 1 significa que la extensión mixta tiene espacio libre, mientras que un bit en 0 indica que todas sus páginas están ocupadas o que no es una extensión mixta. Al igual que las páginas GAM, cada página SGAM cubre cerca de 4 GB de datos.

Páginas PFS: almacenamiento disponible

Las páginas PFS (Page Free Space) rastrean el estado de cada página individual en términos de asignación y espacio libre. Cada bit en una página PFS representa una página de datos y señala si está libre, parcialmente llena o completamente ocupada. Cada página PFS puede rastrear 8,088 páginas, lo que equivale aproximadamente a 64 MB de datos.

Páginas IAM: Mapa de asignación de índices

Las páginas IAM (Index Allocation Map) mapean las extensiones utilizadas por un objeto específico, como una tabla o un índice, en una unidad de asignación particular. Estas páginas permiten a SQL Server identificar rápidamente qué páginas y extensiones pertenecen a cada objeto, facilitando operaciones eficientes de lectura y escritura.

Consideraciones Avanzadas

Antes de cerrar este artículo quería remarcar unos conceptos sobre los que he podido pasar de manera tangencial antes. 

De cara a entender la compatibilidad con filas largas, aunque una fila de datos no puede dividirse entre múltiples páginas, SQL Server maneja columnas de longitud variable que pueden exceder los 8 KB moviendo parte de los datos a páginas especiales denominadas ROW_OVERFLOW_DATA. Esto permite almacenar filas que, en conjunto, superan el tamaño de una página estándar.

Otra cosa que hemos comentado pero que quería matizar es que en versiones anteriores a SQL Server 2016, las tablas e índices pequeños comenzaban utilizando extensiones mixtas y, al crecer, se les asignaban extensiones uniformes. Desde SQL Server 2016, el comportamiento predeterminado es asignar extensiones uniformes desde el inicio para la mayoría de las operaciones, mejorando el rendimiento en la gestión del almacenamiento. Sin embargo, ahora mismo, con los discos SSD modernos, las lecturas secuenciales ya no representan tanta ventaja como cuando los discos eran mecánicos y esta supuesta ventaja en rendimiento puede ser un problema de desaprovechamiento del almacenamiento. Tampoco es que importe mucho, los discos duros, a la vez que se hacían más rápidos, también han visto crecer su capacidad total y el coste del almacenamiento se ha reducido mucho.

Conclusión

Una comprensión profunda de las estructuras de almacenamiento en SQL Server, incluyendo páginas, extensiones y mapas de asignación, es esencial para administradores y desarrolladores que buscan optimizar el rendimiento y la eficiencia de sus bases de datos. Al dominar estos conceptos, es posible diseñar estrategias de almacenamiento que maximicen el uso de los recursos y aseguren operaciones de base de datos robustas y escalables.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima! 

Publicado por Roberto Carrancio en Rendimiento, SQL Server, 1 comentario
14
Feb
2025
¿Por qué necesitas usar AUTO_DROP en tus estadísticas?

¿Por qué necesitas usar AUTO_DROP en tus estadísticas?

Hoy vamos a hablar de una de las novedades que nos llegaron con SQL Server 2022 pero, de la que se ha hablado poco y no mucha gente conoce. Estoy hablando de la opción AUTO_DROP de las estadísticas. El objetivo de este artículo es explorar esta característica y entender cómo funciona y por qué es beneficioso para ti. 

El reto de las estadísticas manuales

Hubo un tiempo, no tan lejano, donde crear estadísticas manuales era un dolor de cabeza para cualquiera que trabajase con bases de datos SQL Server. Y principalmente era porque las estadísticas manuales eran vinculantes con el esquema. Es decir, si tu o un usuario o aplicación creabais una estadística manual en una tabla, esta estadística iba a bloquear la modificación de la estructura de la tabla. Esto era un problema a la hora de modificar o borrar columnas ya que la sola existencia de la estadística provocaba un error y requería pasos adicionales. Tenías que borrar la o las estadísticas, modificar la tabla y, después, recrear las estadísticas. Por este motivo, con estadísticas manuales, las modificaciones de tabla se convertían en un proceso manual la mayoría de las veces.

Introducción a la opción AUTO_DROP de las estadísticas

Con SQL Server 2022 llegó la opción AUTO_DROP de las estadísticas dispuesta a facilitarnos la vida un poco solucionando, en parte, el problema que mencionaba antes. En resumidas cuentas, cuando habilitamos esta opción para una estadística, esta se crea en un modo que permite que se borre automáticamente cuando se produzca una modificación de la estructura de la tabla.

Características clave de AUTO_DROP

La principal característica de esta funcionalidad es, como hemos visto, que no bloquea la estructura de la tabla. En lugar de eso, la estadística se borra automáticamente cuando es necesario. De esta manera, el comportamiento de las estadísticas manuales se equipara con el de las estadísticas creadas de manera automática por el motor de SQL Server, que también se borran cuando es necesario. 

Además, es importante destacar, que esta es una de esas pocas novedades que SQL Server activa por defecto para todas las bases de datos por lo que si quieres mantener el comportamiento como hasta ahora deberás desactivarlo manualmente.

Buenas prácticas y consideraciones

Ten en cuenta que las estadísticas creadas automáticamente por el motor de base de datos siempre usan la opción AUTO_DROP y no se les puede deshabilitar, si intentas cambiarlo te va a dar error. Esta opción solo está disponible para las estadísticas creadas manualmente y, en bases de datos con nivel de compatibilidad 160, estará activada de manera predeterminada, esto aplica para todas las bases de datos creadas en este nivel de compatibilidad pero también para las que hayas migrado de versiones anteriores. Ten en cuenta este comportamiento y desactívalo si lo deseas. 

 

¿Cómo usar la opción AUTO_DROP?

A la hora de crear una estadística manual podemos definir si queremos o no habilitar la opción AUTO DROP. Por ejemplo, este comando crea una estadística con AUTO_DROP:

CREATE STATISTICS [NombreEstadística] ON [Esquema].[Tabla]([Columna1], [Columna2]) WITH AUTO_DROP = ON;

Para crear una estadística SIN AUTO_DROP usaremos este:

CREATE STATISTICS [NombreEstadística] ON [Esquema].[Tabla]([Columna1], [Columna2]) WITH AUTO_DROP = OFF;

Si lo que quieres es cambiar la opción AUTO_DROP en una estadística existente puedes hacerlo también. Esta vez con UPDATE STATISTICS. Por ejemplo este sería el script para activar AUTO_DROP en una estadística que no lo tenga.

UPDATE STATISTICS [Esquema].[Tabla] [NombreEstadística] WITH AUTO_DROP = ON;

Para desactivarlo solo cambia el ON del final por un OFF

Para consultar la configuración AUTO_DROP de nuestras estadísticas podemos hacerlo con la vista sys.stats.

SELECT object_id, [name], auto_drop FROM sys.stats WHERE user_created = 1;

Os comparto también un último script para generar automáticamente estos últimos de cambiar la opción AUTO_DROP para todas las estadísticas de usuario.

SELECT 
  SCHEMA_NAME(t.[schema_id]) AS Esquema,
  t.[name] AS Tabla,
  s.[name] AS NombreEstadistica, 
  CASE WHEN s.auto_drop = 1 THEN 'Activado' ELSE 'DESACTIVADO' END AS [AUTO_DROP],
  'UPDATE STATISTICS '+QUOTENAME(SCHEMA_NAME(t.[schema_id]))+'.'+QUOTENAME(t.[name])+' '+QUOTENAME(s.[name])+' WITH AUTO_DROP = ON;' AS HabilitarAuto_Drop,
  'UPDATE STATISTICS '+QUOTENAME(SCHEMA_NAME(t.[schema_id]))+'.'+QUOTENAME(t.[name])+' '+QUOTENAME(s.[name])+' WITH AUTO_DROP = OFF;' AS DeshabilitarAuto_Drop
FROM sys.stats s
INNER JOIN sys.tables t 
 ON s.object_id = t.object_id
WHERE user_created = 1; 

Conclusión

En conclusión, la opción AUTO_DROP de las estadísticas en SQL Server 2022 representa un avance significativo en la gestión de estadísticas manuales. Su implementación permite reducir la fricción en la modificación del esquema de las tablas, eliminando automáticamente las estadísticas cuando ya no son relevantes. Esto no solo simplifica la administración de la base de datos, sino que también evita errores comunes y la necesidad de procesos manuales adicionales.

Si bien esta funcionalidad está activada por defecto en bases de datos con nivel de compatibilidad 160, es importante conocer su impacto y decidir si se desea mantener o desactivar en cada caso. Al final, la correcta gestión de las estadísticas sigue siendo clave para optimizar el rendimiento de las consultas y garantizar un mantenimiento eficiente de nuestras bases de datos en SQL Server.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima! 

Publicado por Roberto Carrancio en Cloud, SQL Server, 0 comentarios
12
Feb
2025

Azure Data Studio ha muerto. Larga vida a VS Code.

El pasado 6 de febrero de 2025, Azure Data Studio (ADS) dejaba de recibir soporte y actualizaciones. Aunque se mantendrá operativo hasta el 28 de febrero de 2026, Microsoft ha dejado claro que el futuro del desarrollo SQL pasa por Visual Studio Code (VS Code) con la extensión MSSQL.

Este anuncio marca el fin de una era para quienes han utilizado ADS como su herramienta principal de administración y desarrollo SQL. Sin embargo, lejos de ser una mala noticia, este cambio representa una evolución lógica hacia un entorno más moderno, flexible y potente.

Hoy voy a desgranar qué significa el fin de Azure Data Studio, cómo afecta a los desarrolladores y DBAs, y por qué VS Code es el camino a seguir.

¿Por qué Microsoft ha decidido matar Azure Data Studio?

Azure Data Studio nació como una alternativa ligera y multiplataforma a SQL Server Management Studio (SSMS), con una interfaz moderna, soporte para notebooks y conectividad con Azure. Durante años, fue una herramienta fundamental para administradores de bases de datos y desarrolladores SQL, más bien para estos segundos, que buscaban una solución ágil.

Sin embargo, VS Code ha evolucionado hasta el punto de haber alcanzado en funcionalidad a ADS. Consolidar todo en un único entorno de desarrollo reduce la fragmentación y permite concentrar esfuerzos en una sola plataforma.

Motivos clave detrás de la migración a VS Code

Principalmente, como ya he comentado, VS Code ha mejorado tanto que ya hace todo lo que hacía ADS y más.

Con la extensión MSSQL, VS Code soporta conexiones a SQL Server, Azure SQL Database y Fabric. Pero no solo conectar, obviamente permite ejecutar consultas, visualizar esquemas, exportar resultados y administrar bases de datos con una experiencia optimizada.

Además, en VS Code tenemos a nuestra disposición herramientas avanzadas como Table Designer, Query Execution Plans y scripting.

Menos fragmentación, más velocidad de innovación

Por supuesto, mantener dos herramientas similares implica duplicar esfuerzos. Esto es otro de los principales motivos para consolidar todo en VS Code. De esta manera, Microsoft acelera el desarrollo de nuevas funcionalidades y se eliminan inconsistencias entre ADS y VS Code, asegurando que todos los usuarios tengan acceso a las últimas mejoras.

Integración total con el ecosistema DevOps y CI/CD

A día de hoy VS Code es la herramienta más utilizada por desarrolladores de software. Su ecosistema de extensiones permite integrar bases de datos con herramientas de DevOps, CI/CD y control de versiones. Microsoft está apostando por una experiencia SQL que encaje dentro del mundo cloud-native y automatizado.

¿Qué ofrece VS Code con la extensión MSSQL?

Si aún no has probado VS Code como entorno SQL, te sorprenderá lo completa que es su experiencia con la extensión MSSQL.

Como funciones destacadas de VS Code para SQL Server podemos encontrar el soporte para filtrado, ordenación y exportación a JSON, CSV y Excel. También la administración visual de bases de datos gracias a las herramientas Object Explorer y Table Designer que nos permiten gestionar esquemas sin escribir código. En cuanto a las herramientas de optimización del rendimiento SQL tenemos un visualizador de planes de ejecución de consultas. 

Por último, pero importante tenemos el soporte para DevOps y CI/CD especialmente pensado para proyectos de bases de datos SQL para integración con pipelines de despliegue. Esto es totalmente compatible con herramientas de control de versiones y automatización como Git o Azure DevOps.

Y no olvidemos que es extensible y personalizable, ya fuera de lo que es SQL tiene miles de extensiones disponibles en el marketplace de VS Code con soporte para Python, PowerShell, Bash, Kubernetes y más, todo en el mismo entorno.

Cómo migrar de Azure Data Studio a VS Code

Microsoft ha asegurado que la transición de Azure Data Studio a VS Code será sencilla, ya que casi todas las funcionalidades de ADS ya están disponibles en VS Code con la extensión MSSQL. 

Para la migración lo primero que debemos hacer, obviamente, es descargar e instalar VS Code. Una vez hecho esto deberemos instalar la extensión MSSQL ya sea desde el Marketplace de Extensiones (buscando «SQL Server (mssql)») o con este comando en la barra de búsqueda de comandos de VS Code:

ext install ms-mssql.mssql

Con todo instalado es el momento de configurar las conexiones a bases de datos Usa la opción «Agregar conexión» para configurar tus servidores SQL Server o Azure SQL.

Puedes trasladar manualmente tus conexiones de ADS sin necesidad de migración de datos. 

Por último, si usabas notebooks Jupyter, puedes instalar la extensión de Jupyter en VS Code. 

Y si eres administrador de bases de datos o necesitas funcionalidades avanzadas para SQL Server Agent y Schema Compare, existen alternativas dentro de SQL Projects y herramientas de terceros. Esto no es lo ideal pero, es lo mismo que nos pasaba en ADS y el motivo por el que, al menos de momento, yo sigo prefiriendo SSMS para trabajar.

Preguntas frecuentes sobre la transición

A continuación os respondo algunas de las preguntas que he visto sobre el tema de la desaparición de Azure Data Studio

¿Pierdo funcionalidades si dejo de usar Azure Data Studio?

No realmente. VS Code con MSSQL Extension cubre casi todas las funcionalidades de ADS, y en muchos casos, las mejora. Es cierto que no tiene todas las opciones que nos da SSMS pero es que ADS tampoco las tenía.

¿Mis scripts y consultas seguirán funcionando en VS Code?

Sí. No necesitas modificar nada. Lo que cambia es la herramienta de conexión pero los scripts SQL que ejecutabas en ADS funcionan sin problemas en VS Code porque vas a usar los mismos servidores.

¿Qué pasa si no quiero cambiarme de ADS?

¿Eres un rebelde y te gusta ir contracorriente? No pasa nada, puedes seguir usándo ADS hasta febrero de 2026, pero ya no recibirás actualizaciones ni soporte. El cambio es inevitable amigo.

Conclusión

Azure Data Studio nos ha servido bien, pero VS Code representa el futuro del desarrollo SQL. Con una comunidad activa, actualizaciones constantes y una integración más fluida con herramientas modernas, VS Code con MSSQL Extension es la mejor alternativa para administrar bases de datos SQL. Si aún no has probado VS Code, este es el momento perfecto para hacer la transición y aprovechar todas sus ventajas. Yo prometo hacer pronto un video en mi canal de youtube explicando la interfaz.

Para más información, consulta la documentación oficial.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima! 

Publicado por Roberto Carrancio en Cloud, SQL Server, 0 comentarios
07
Feb
2025

El extraño error de conversión y su solución

Hoy vamos con un artículo distinto, nos vamos a centrar en un caso práctico que me ha pasado hoy en el trabajo y que me ha parecido lo suficientemente curioso como para traerlo aquí y compartirlo con todos vosotros. La idea es que si alguna vez encontráis este error o alguno parecido sepáis como actuar rápido y sin volveros locos.

Disclaimer 1: Todos los datos que veis son una reproducción de lo que ha pasado, no voy a mostrar ni estructuras ni datos reales.

Disclaimer 2: Vaya por delante que estamos hablando, no solo de un sistema totalmente Legacy, sino de un patio de recreo para todas las malas prácticas habidas y por haber en el mundo de los datos, documentadas y por descubrir. Vosotros me entendéis, ¿verdad? No sé como más decir que esto hay que borrarlo, os prometo que he recurrido a todos los métodos posibles antes de incurrir en ilícito penal. Porque hay veces que la tortura tendría que estar aceptada, ya veréis cuando os cuente…

El misterioso error

Como os digo estaba yo en un día tranquilo, de esos que aprovechas para poner la documentación al día, cuando los lloros de un usuario han perturbado mi paz. Un error salvaje había aparecido:

Error Conversion 1

Hasta aquí todo normal, un error de conversión normal y corriente. “Deja de hacer esa conversión. ¡ Qué estás tratando de convertir a fecha y hora un texto que no es una fecha, ANIMAL !” 

Pero no, iba a ser más complicado, no había ninguna conversión aparente. Era un select * de la tabla sin más. 

El valor de la experiencia

Uno que ya es perro viejo ha ido directamente a mirar la definición de la tabla, ya sabes, más sabe el diablo por viejo que por diablo y, aquí, la experiencia es un grado. No es la primera vez que veo una cosa de estas. Y lo que he visto os sorprenderá.

Error Conversion 2

Bingo. Tenemos una columna calculada que saca el dato de un fichero de texto. Seguro que uno de los campos del JSON tiene un valor en la fecha que realmente no es una fecha o, si lo es, no es de un rango válido. Pero, tenemos en la tabla más de 50 millones de registros, no es plan de ponerse a mirar todos los JSON.

Investigación y solución del error

No hay problema que frene al DBA. Localizar el error es sencillo si sabes como. Basta con usar la misma expresión de CONVERT que tenía la columna calculada pero con TRY_CONVERT que, como expliqué en este vídeo, hace lo mismo que CONVERT pero en caso de error devuelve nulo. Esto en un filtro del where y filtrando por resultados nulos nos va a señalar directamente al culpable, como cuando pillan al malo con restos de pólvora en las manos en nuestra serie favorita de policías americanos.

Error Conversion 3

Como veis un 0 en la fecha era el causante de este expediente X, en mi caso real (no la demo que veis en las imágenes) no era uno sino 258 registros pero vamos, la solución es la misma. UPDATE de esas fechas y a funcionar.

Error Conversion 4

 

Prevención de errores

Una vez arreglado el problema es momento de analizar las causas raíz y ver cómo evitar esto en un futuro. En este caso podríamos resumirlo en hacer las cosas bien pero oye, cuando a uno le están pagando por ello, hay que currarse las respuestas un poco más.

Veamos pues qué pasa:

  1. Usar datos semiestructurados en la base de datos no es una buena idea por rendimiento. Pero es que tampoco tiene validaciones, como hemos podido comprobar. Con una columna de fecha para introducir el dato este error no habría pasado. Directamente este registro incorrecto no se habría escrito en la base de datos.
  2. SQL Server no está preparado para trabajar con JSON, por eso lo del tipo de datos nvarchar(max) en la columna. Mientras que para XML si tenemos un tipo de datos específico para JSON no será hasta SQL Server 2025 que lo veamos (podemos probarlo en preview en Azure SQL Databases y Azure Managed Instance). Este futuro tipo de datos JSON nos permitirá añadir estos controles de los que hablábamos en el punto anterior.
  3. Usar una función CONVERT en una columna calculada es una mala práctica pues, en caso de fallo de los datos, nos devuelve error. Para estos casos, siempre que sea posible es mejor usar TRY_CONVERT. Realmente aquí hay discrepancias de opiniones, y dependerá de vuestro caso. Depende a que deis prioridad, si a tener el resto de datos sin error y el registro incorrecto como nulo o si por el contrario preferís que salte el error para detectarlo y corregirlo.

Conclusión

Los errores de conversión como este pueden ser una pesadilla, pero la realidad es que suelen ser más culpa de un diseño regulero que de un usuario despistado. Aquí la clave es sencilla: si metemos datos como si fueran churros, que no nos sorprenda si luego nos encontramos un «churro» en los resultados. Por otro lado, usar TRY_CONVERT en lugar de CONVERT nos habría ahorrado el susto, pero el problema de fondo sigue siendo el mismo: SQL Server y JSON no son precisamente mejores amigos. 

Aquí estamos, esperando que el tipo de datos JSON nativo llegue en SQL Server 2025. Hasta entonces, toca ser cuidadosos, validar lo que metemos en la base de datos y asumir que, si confiamos ciegamente en los datos, tarde o temprano nos van a dar un disgusto. 

Así que ya sabéis: menos improvisación, más validación y, sobre todo, menos sustos en producción.

Publicado por Roberto Carrancio en Cloud, SQL Server, 1 comentario
05
Feb
2025

Integración de FILESTREAM y FileTable con Always On Availability Groups

La combinación de FILESTREAM y FileTable con Always On Availability Groups en SQL Server permite gestionar datos no estructurados con integridad transaccional (FILESTREAM y FileTable) mientras se garantiza alta disponibilidad y recuperación ante desastres (Always On). Sin embargo, esta integración no está exenta de retos, lo que requiere un enfoque y mantenimiento planificados y precisos. En este artículo, quiero hacer una introducción a cómo configurar estas tecnologías y resolver los problemas más comunes que surgen en su implementación. No pretendo generar una guía paso a paso, simplemente introducir los conceptos clave que debéis tener en cuenta.

Introducción a FILESTREAM, FileTable y Always On Availability Groups

FILESTREAM es una funcionalidad de SQL Server que habilita el almacenamiento de datos binarios grandes (como imágenes, documentos o vídeos) directamente en el sistema de archivos. Esto permite a las aplicaciones interactuar con los datos mediante APIs de sistema de archivos estándar, mientras que SQL Server mantiene la consistencia transaccional.

FileTable extiende FILESTREAM al ofrecer una estructura predefinida que facilita la gestión de datos no estructurados. Proporciona una forma más sencilla de organizar los archivos almacenados, permitiendo su acceso directo a través de rutas de sistema de archivos además de la base de datos.

Por su parte, Always On Availability Groups ofrece un modelo avanzado de alta disponibilidad y recuperación ante desastres a nivel de base de datos. Su capacidad para replicar datos entre réplicas en distintas ubicaciones lo hace ideal para garantizar continuidad en el servicio.

Cuando se combinan FILESTREAM y FileTable con Always On, surgen retos relacionados con la sincronización de datos, el acceso mediante Nombres de Red Virtual (VNN) y la compatibilidad funcional tras un failover.

Requisitos previos y consideraciones iniciales

Para garantizar una integración exitosa, es fundamental cumplir con ciertos requisitos previos:

Habilitar de FILESTREAM en todas las instancias del grupo

Debemos asegurarnos de que FILESTREAM esté habilitado en cada instancia del grupo de disponibilidad. Esto incluye habilitar el acceso a través de Transact-SQL y APIs de sistema de archivos.

Configuración del clúster y actualizaciones

Si utilizamos Windows Server 2012 o versiones más recientes, deberemos asegurarnos también de aplicar cualquier hotfix recomendado para el acceso adecuado a recursos compartidos mediante VNN.

Validar la infraestructura de Always On

El clúster de conmutación por error debe estar configurado y operativo. 

Además, las bases de datos que contendrán FILESTREAM o FileTable deben cumplir con los requisitos para formar parte de un Availability Group.

Configuración de FILESTREAM y FileTable con Always On Availability Groups

Una vez que FILESTREAM está habilitado en todas las instancias, podremos añadir bases de datos que utilicen FILESTREAM a un grupo de disponibilidad. Para hacerlo, no tenemos que hacer nada fuera de lo normal. Simplemente crear un grupo de disponibilidad desde SSMS o mediante el asistente de Always On y asegurarnos de que las bases de datos estén en modo «FULL Recovery Model» y se haya realizado un backup full reciente. De esta manera podremos incluir las bases de datos con FILESTREAM al configurar el grupo de disponibilidad.

Configuración del acceso mediante Nombres de Red Virtual (VNN)

Always On utiliza un VNN para virtualizar el acceso al grupo de disponibilidad. Para acceder a los datos FILESTREAM o FileTable en este contexto.

Tendremos que validar que el recurso compartido de FILESTREAM se haya creado automáticamente para el VNN. Este recurso compartido tendrá la forma típica de un directorio de red pero con el nombre del grupo de disponibilidad en vez de el de uno de los nodos:

\\<NombreVirtualDelGrupo>\mssqlserver

Las aplicaciones que interactúan con FILESTREAM mediante APIs de sistema de archivos deben usar este recurso compartido en lugar del nombre del servidor físico.

Sincronización y replicación de datos

Aunque Always On replica los datos relacionales entre réplicas, los datos almacenados en el sistema de archivos a través de FILESTREAM no se replican automáticamente. Para garantizar consistencia podremos usar herramientas como Distributed File System Replication (DFS-R), que permite sincronizar las carpetas de FILESTREAM entre las réplicas.

Problemas comunes y tips para superarlos

La sincronización de datos no estructurados con FILESTREAM entre nodos puede ser compleja. Como ya hemos mencionado, DFS-R u otras soluciones de replicación similares nos ayudarán a mantener consistencia entre las réplicas. Es imprescindible asegurarse de que las carpetas FILESTREAM estén correctamente sincronizadas en todas las réplicas antes de habilitar un grupo de disponibilidad.

Ten en cuenta que, tras un failover, los datos de FILESTREAM son accesibles tanto en la nueva réplica primaria como en las réplicas secundarias legibles. Sin embargo, una limitación que debemos conocer es que los datos de FileTable solo son accesibles en la réplica primaria. 

Otra cosa importante, que ya habrás adivinado en puntos anteriores es que las aplicaciones que dependen de rutas de sistema de archivos deben actualizarse para usar las rutas basadas en VNN, evitando dependencias del servidor físico. De este modo, en caso de failover seguirán siendo accesibles

Buenas prácticas para FILESTREAM y FileTable en Always On

Es aconsejable usar DFS-R para sincronizar los datos FILESTREAM entre réplicas y para ello deberemos configurar correctamente las políticas de la aplicación.

Tampoco debemos olvidar adaptar nuestras herramientas para monitorizar el estado de las réplicas para tener bajo control también los recursos compartidos de FILESTREAM.

Y ahora dos cosas que nunca me cansaré de decir. Primero, realizad pruebas periódicas para garantizar que los failovers funcionan y, en este caso, que no interrumpen el acceso a los datos FILESTREAM o FileTable. Y segundo y muy importante, mantened siempre una documentación clara sobre la configuración y validad regularmente los cambios en la infraestructura.

Conclusión

Integrar FILESTREAM y FileTable con Always On Availability Groups es una solución potente para gestionar datos no estructurados con alta disponibilidad. Sin embargo, requiere una configuración cuidadosa y una planificación meticulosa para superar los desafíos inherentes.

Con una configuración adecuada y el uso de herramientas complementarias como DFS-R, las empresas pueden disfrutar de las ventajas de estas tecnologías mientras minimizan los riesgos y los problemas operativos. Para obtener más detalles sobre FILESTREAM, recomendamos consultar nuestro artículo previo sobre FILESTREAM en SQL Server y la documentación oficial de Microsoft.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima! 

Publicado por Roberto Carrancio en Alta Disponibilidad, SQL Server, 0 comentarios
29
Ene
2025

FileTable en Filestream

La característica FileTable en SQL Server es una extensión que tenemos a nuestro alcance para dar superpoderes a la funcionalidad FILESTREAM. Está diseñada para simplificar el almacenamiento, la administración y el acceso a datos no estructurados en nuestras bases de datos relacionales. Suena bien, ¿verdad? En este artículo, veremos en detalle cómo funciona, sus beneficios, casos de uso y consideraciones para implementarla.

Introducción a FileTable

En resumidas cuentas FileTable combina las capacidades de FILESTREAM con una estructura de tabla especial que permite gestionar datos no estructurados, como documentos o imágenes, directamente desde el sistema de archivos. Lo interesante de esta funcionalidad es que los archivos y carpetas almacenados en una FileTable son accesibles desde aplicaciones tradicionales que usan operaciones de entrada/salida (E/S) en el sistema de archivos, mientras que también se pueden consultar y administrar desde SQL Server.

Esta dualidad ofrece lo mejor de ambos mundos: el rendimiento y las características del sistema de archivos junto con la robustez de SQL Server para consultas y transacciones.

Cómo funciona FileTable

Lo principal que debemos saber es que FileTable está estrechamente relacionado con FILESTREAM. Es más, FileTable se basa en FILESTREAM que, como ya vimos, es una funcionalidad que habilita el almacenamiento de datos binarios en el sistema de archivos en lugar de dentro de las páginas de datos de la base de datos. Cuando habilitamos FileTable el comportamiento cambia y mientras que FILESTREAM por sí solo requiere que las aplicaciones interactúen directamente con SQL Server para leer y escribir los datos binarios, FileTable da un paso más al exponer los datos a través del sistema de archivos, como si fueran carpetas y archivos normales.

Estructura de FileTable

Cuando se crea una FileTable, SQL Server configura una tabla especial que incluye las siguientes columnas adicionales, entre otras:

  • stream_id: un identificador único para cada archivo o carpeta.
  • file_stream: una columna tipo VARBINARY(MAX) que almacena los datos binarios del archivo.
  • name: el nombre del archivo o carpeta.
  • file_type: la extensión del archivo.
  • path_locator: una jerarquía que representa la ubicación del archivo o carpeta dentro de la estructura de directorios.
  • is_directory: indica si el registro representa un archivo o una carpeta.

Estas columnas permiten que FileTable se integre perfectamente tanto en el sistema de archivos como en las operaciones SQL.

Carpetas raíz y Namespace

Cada FileTable tiene una carpeta raíz en el sistema de archivos. Los archivos y carpetas dentro de esta raíz se gestionan automáticamente en sincronización con los registros correspondientes en la base de datos. Esto significa que cualquier cambio realizado en el sistema de archivos, como mover, renombrar o eliminar un archivo, se refleja automáticamente en SQL Server.

Casos de uso de FileTable

Esta característica, como veis, eleva la funcionalidad de FILESTREAM y lo hace ideal para aplicaciones de gestión documental, por ejemplo. En concreto, en sistemas de gestión documental donde es necesario almacenar archivos, como PDFs, imágenes o documentos de Word, FileTable nos habilita acceder a los ficheros tanto desde aplicaciones que utilizan SQL Server como desde exploradores de archivos estándar.

También podemos recurrir a esta característica para una migración de aplicaciones legacy. Es decir, aplicaciones heredadas que gestionan archivos directamente en las carpetas del sistema. En estos casos FileTable permite una transición gradual hacia una solución basada en bases de datos sin necesidad de reescribir el acceso a los archivos.

Podría seguir, por ejemplo es muy interesante  para archivos adjuntos en aplicaciones web o móviles. En estas aplicaciones, los archivos adjuntos cargados por los usuarios pueden almacenarse en una FileTable, ofreciendo una gestión más sencilla de los datos no estructurados con capacidades avanzadas de consulta y seguridad.

Configuración de FileTable en SQL Server

Para utilizar FileTable, es necesario habilitar y configurar varias opciones tanto a nivel de instancia como de base de datos. Veamos los pasos principales.

Antes de crear una FileTable, es necesario habilitar FILESTREAM en el servidor. Esto puede hacerse desde SQL Server Configuration Manager. Después debemos configurar la base de datos para habilitar FILESTREAM. No me detengo más en estos pasos porque ya los vimos cuando hablamos de FILESTREAM. Una vez configurado FILESTREAM, podemos crear una FileTable usando un comando SQL. Os pongo un ejemplo:

CREATE TABLE MiFileTable AS FileTable
WITH (
    FileTable_Directory = ‘Documentos’,
    FileTable_Collate_Filename = DATABASE_DEFAULT
);
GO

En este caso, FileTable_Directory define la carpeta raíz donde se almacenarán los archivos.

Beneficios de FileTable

Como vengo mencionando, el principal beneficio de FileTable es el acceso híbrido a los ficheros. FileTable nos permite trabajar con los archivos desde aplicaciones que usan el sistema de archivos o mediante consultas SQL. Derivado de esto, podemos resaltar también la capacidad de tener soporte transaccional. Me refiero a que los cambios en los archivos se pueden incluir en transacciones SQL, garantizando consistencia.

Al ser parte de SQL Server, los archivos se pueden indexar y buscar utilizando capacidades de texto completo lo que también es un gran punto a su favor. 

Por último siempre me gusta destacar el tema de la seguridad integrada. FileTable aprovecha la autenticación y autorización de SQL Server para controlar el acceso a los datos y no solo los permisos sobre la estructura de carpetas.

Limitaciones y consideraciones

Aunque la característica FileTable ofrece muchas ventajas, no está exenta de limitaciones. Por ejemplo, no admite replicación, las FileTables no son compatibles con las tecnologías de replicación tradicionales de SQL Server. Además tiene una gran dependencia del sistema de archivos y ya sabemos que las operaciones intensivas en archivos pueden verse limitadas por el rendimiento del hardware subyacente. 

Otra de las cosas que debemos vigilar muy de cerca es el uso de espacio en disco. Dado que los datos se almacenan en el sistema de archivos, es necesario dimensionar rigurosamente el almacenamiento para evitar problemas de espacio.

Conclusión

FileTable es una funcionalidad poderosa y única de SQL Server que simplifica la gestión de datos no estructurados, al permitir un acceso integrado tanto desde el sistema de archivos como desde consultas SQL. Es especialmente útil en escenarios donde las aplicaciones deben trabajar con archivos directamente, pero también se requiere la capacidad de consulta y gestión avanzada que ofrece SQL Server.

Como siempre, antes de implementar FileTable en un entorno de producción, es fundamental evaluar cuidadosamente los requisitos de la aplicación, las capacidades del sistema y las posibles limitaciones. Si se utiliza correctamente, puede transformar la forma en que gestionamos los datos no estructurados en nuestras bases de datos.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima! 

Publicado por Roberto Carrancio en SQL Server, 0 comentarios
27
Ene
2025

Reduce el tiempo de tus BACKUPS a la mitad o más

La semana pasada publiqué un post sobre las configuraciones avanzadas de backups BLOCKSIZE, MAXTRANSFERSIZE y BUFFERCOUNT y cómo impactan en el rendimiento de nuestras copias de seguridad. A raíz de este artículo, muchos comentasteis una optimización mucho más sencilla y que mejora sustancialmente los tiempos de backup, separar las copias en varios ficheros. Y es cierto, incluso cuando no estás escribiendo estos múltiples ficheros en discos separados puedes ganar tiempo con esta configuración. Pero, ¿por qué? Veámoslo en profundidad. Además veremos otro truco que nos puede ahorrar gran cantidad de tiempo en estos procesos.

¿Por qué optimizar los backups?

A menudo no pensamos en que la optimización de rendimiento sea algo que afecte a las copias de seguridad y sin embargo es un campo donde podemos ganar mucho. 

A ver, un momento, si trabajas con una base de datos pequeña o mediana (hasta 300Gb aprox) y tu empresa solo tiene necesidad de acceso a la base de datos en horas de oficina esto no es un problema, no te compliques más. Seguramente tengas tiempo durante la noche y los fines de semana para hacer todas las tareas de mantenimiento completas sin mucho más miramiento. Pero, a medida que los datos crecen o crecen la demanda de los datos acercándose al tan temido 24×7 te va a tocar hacer malabares con los tiempos de los planes de mantenimiento y ajustarlo todo de manera que impacte lo menos posible en el resto de actividad. 

Vale, en estos casos lo mejor sería una aplicación externa de backup por snapshot para reducir el tiempo a casi 0 pero no seamos tan extremos, veamos qué podemos hacer con herramientas nativas.

Separar los backups en varios ficheros

Incluso cuando escribimos los distintos ficheros de copias de seguridad en el mismo disco duro físico o por la misma interfaz de red suele ser más rápido separar las copias de seguridad en varios ficheros. Esto pasa porque SQL Server tiene “cuellos de botella internos” (limitaciones) en la escritura a ficheros de copias de seguridad y al generar varios vamos a poder salvar en gran medida esas limitaciones. 

Es cierto que a cambio vamos a tener que complicar un poco tanto el script de recuperación de la copia de seguridad como el de la restauración pero, ¿de verdad eso es importante? ¿Sigues escribiendo a mano los scripts de backup y restore en 2025? Amigo para eso existen soluciones como los script de Ola Hallengren o el maravilloso sp_DatabaseRestore de Brent Ozar.

Demostración práctica BACKUPS

Veamos cómo impacta en los tiempos el hecho de dividir las copias de seguridad. Para la prueba estoy usando la base de datos StackOverflow2013 de demo de 52 Gb que tiene 4 ficheros de datos y uno de log. Sobre el hardware de mi máquina de pruebas es una máquina con 8 cores (16 vCores), 32Gb de RAM y un disco SSD M2.Tanto los ficheros de datos como el de log y el backup están en la misma unidad, no es lo ideal pero es lo que tengo para esto.

En un primer intento he hecho un backup full sencillo, a un solo fichero y ha tardado 1:52 minutos, la misma prueba de backup pero con 2 ficheros ha tardado 0:49 minutos y sin embargo, en cuanto he puesto 4 ficheros la prueba se ha ido a 3:42 minutos. ¿Por qué estos resultados? En teoría os había dicho que SQL Server limita la cantidad de datos que escribe a un único fichero por lo que podríamos entender que a más ficheros menos tiempo y sin embargo aunque con 2 ficheros hemos bajado los tiempos con 4 se han disparado.

Esto es porque también tenemos que tener en cuenta las limitaciones de velocidad del disco, en mi caso donde además todos los ficheros de datos y log están en la misma unidad esto cobra más sentido. Durante la prueba con un fichero el uso de disco ha rondado el 30%, durante la segunda prueba entre el 65 y 70% y, en la prueba con 4 ficheros el consumo ha sido del 100% del disco. Por tanto, con 4 ficheros mi hardware ha sobrepasado su límite generando tiempos de espera por cuellos de botella en la E/S de disco.

Demostración práctica RESTORE

Ahora os comparto los resultados que he tenido en la restauración de estas copias que acabo de hacer. Para esta prueba todos los backups siguen en la misma unidad que los datos y los logs y la base de datos existente va a ser sobrescrita, es decir no hay que generar de nuevo los ficheros. ¿Qué pasará?

Aquí los tiempos se disparan, la restauración de la copia con un solo fichero ha tardado 7:18 minutos. La restauración de la copia con dos ficheros, por su parte, ha demorado 11:07. Por último la copia de 4 ficheros ha tomado 10:19 minutos para restaurarse.

Cabe destacar que todas las pruebas de restauración han tenido el uso de disco al 100% en todo momento por lo que no puedo dar por 100% fiables los datos al haber encontrado tán pronto el límite del hardware. Ya os había dicho que la configuración de todo en el mismo disco no es una buena idea.

Bonus extra: Verificar los backups

Otra de las cosas que seguro que estás haciendo es verificar los backups a la hora de hacerlos. Verificar los backups no es que sea una buena práctica es que es imprescindible si queremos estar seguros y cumplir con la normativa vigente para muchas empresas. Como se suele decir, un backup sin probar no es un backup, es como el gato de Schrödinger (claro que he tenido que buscar en google como se escribe). 

Sin embargo, que tengamos que comprobar nuestras copias de seguridad no significa que debamos hacerlo al momento de hacer la copia, ni siquiera significa que debamos hacerlo en el mismo servidor. 

Si nuestros backups están en una unidad de red vamos a poder probarlas de manera independiente y en una máquina distinta al servidor de producción (por ejemplo el servidor de DR o el de pruebas) vamos a poder ganar un 30% o más del total de tiempo de la tarea de copias de seguridad. Incluso, podemos hacer uso del procedimiento sp_DatabaseRestore que he mencionado antes y hacer un CheckDB a la base de datos en este proceso separado de verificación. ¿Te das cuenta de lo que te estoy diciendo, verdad? Más seguridad y mejor rendimiento sin apenas complicarte.

Conclusión

Optimizar los procesos de backups no es solo cuestión de ahorrar tiempo, sino también de garantizar que nuestro entorno sea resiliente, eficiente y cumpla con los estándares de seguridad. A través de ajustes relativamente simples, como dividir los backups en múltiples ficheros o separar la verificación en un proceso independiente, podemos obtener grandes beneficios sin necesidad de recurrir a herramientas externas costosas.

Sin embargo, como hemos visto en las pruebas, no existe una configuración única que funcione para todos los escenarios. Cada entorno tiene sus propias limitaciones, ya sea por el hardware, la arquitectura de los datos o los requisitos operativos. Por eso, es fundamental medir, analizar y ajustar las configuraciones basándonos en pruebas reales. La clave está en encontrar el equilibrio entre el rendimiento y la fiabilidad, adaptando las estrategias a las características de nuestros sistemas.

En resumen, la optimización no siempre implica complejidad, y pequeños cambios pueden marcar una gran diferencia. Ahora te toca a ti: ¿qué ajustes has probado en tus backups? ¿Qué resultados has obtenido? Como siempre, la mejor forma de aprender es compartiendo experiencias.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

Publicado por Roberto Carrancio en Rendimiento, SQL Server, 0 comentarios