Archivos y Grupos de Archivos en SQL Server

Toda base de datos en SQL Server, en su nivel más fundamental, se compone de archivos. Estos archivos son los contenedores físicos donde reside toda la información, desde los datos de tus tablas hasta el registro de cada transacción realizada. Comprender la función y la estructura de estos archivos, así como la forma en que se organizan en grupos, es crucial para una administración de bases de datos eficaz y para optimizar el rendimiento.

En esencia, una base de datos SQL Server requiere, como mínimo, dos tipos de archivos del sistema operativo: un archivo de datos y un archivo de registro. El archivo de datos es el que almacena la información real: las tablas, los índices, los procedimientos almacenados, las vistas y otros objetos de la base de datos. El archivo de registro, por otro lado, contiene el registro de transacciones, que es vital para la recuperación de la base de datos y para garantizar la integridad de los datos.

Tipos de Archivos de Base de Datos

Las bases de datos de SQL Server utilizan tres tipos principales de archivos:

Por ejemplo, una base de datos simple podría consistir en un archivo .mdf y un archivo .ldf. Una base de datos más compleja podría tener un archivo .mdf, varios archivos .ndf distribuidos en diferentes unidades y uno o más archivos .ldf. Es una práctica recomendada separar los archivos de datos y los archivos de registro en discos físicos distintos para minimizar la contención de E/S y mejorar el rendimiento, aunque por defecto SQL Server los coloca en la misma unidad y ruta.

Nombres Lógicos y Físicos de los Archivos

Cada archivo en SQL Server tiene dos nombres:

• Nombre Lógico (logical_file_name): Este es el nombre interno que se utiliza para referirse al archivo dentro de las sentencias Transact-SQL. Debe ser único dentro de la base de datos y cumplir las reglas de identificadores de SQL Server.
• Nombre Físico (os_file_name): Este es el nombre real del archivo en el sistema operativo, incluyendo la ruta completa del directorio. Debe seguir las reglas de nombres de archivo del sistema operativo.

Cuando interactúas con los archivos de la base de datos mediante comandos como CREATE DATABASE o ALTER DATABASE, especificas ambos nombres.

Consideraciones del Sistema de Archivos

Los archivos de datos y registro de SQL Server pueden residir en sistemas de archivos FAT o NTFS. Sin embargo, en sistemas Windows, Microsoft recomienda encarecidamente utilizar NTFS debido a sus características de seguridad y robustez. Es importante destacar que los grupos de archivos de datos de lectura/escritura y los archivos de registro no son compatibles con sistemas de archivos NTFS comprimidos. Solo las bases de datos o grupos de archivos secundarios de solo lectura pueden colocarse en un sistema de archivos comprimido. Para ahorrar espacio en bases de datos de lectura/escritura, se recomienda utilizar la compresión de datos de SQL Server en lugar de la compresión del sistema de archivos.

Estructura Interna de los Archivos de Datos

Los archivos de datos de SQL Server están organizados en páginas, que son la unidad fundamental de almacenamiento. Cada página tiene un tamaño fijo de 8 KB. Las páginas dentro de un archivo se numeran secuencialmente, comenzando desde cero. Para identificar de forma única una página dentro de una base de datos, se necesita tanto el ID del archivo como el número de página.

La primera página de cada archivo es una página de encabezado que contiene metadatos sobre los atributos del archivo. Varias otras páginas al inicio del archivo también contienen información del sistema, como mapas de asignación. Tanto el archivo de datos primario como el primer archivo de registro contienen una página de arranque de la base de datos con información clave sobre la base de datos misma.

Gestión del Tamaño de los Archivos

Los archivos de SQL Server tienen la capacidad de crecer automáticamente desde su tamaño inicial especificado. Al definir un archivo, puedes establecer un incremento de crecimiento (FILEGROWTH). Cada vez que el archivo se llena, aumenta su tamaño por este incremento. Si hay varios archivos en un grupo de archivos, no crecerán automáticamente hasta que todos los archivos dentro de ese grupo estén llenos.

También es posible especificar un tamaño máximo (MAXSIZE) para un archivo. Si no se especifica un tamaño máximo, el archivo puede seguir creciendo hasta consumir todo el espacio disponible en el disco. La función de crecimiento automático es muy útil para reducir la carga administrativa de monitorear constantemente el espacio libre, pero un crecimiento automático frecuente con incrementos pequeños puede causar fragmentación del disco y afectar el rendimiento. Se recomienda establecer un incremento de crecimiento razonable o pre-dimensionar los archivos a un tamaño estimado adecuado.

Archivos y Grupos de Archivos

Aunque una base de datos simple puede funcionar con un solo archivo de datos primario y un archivo de registro, SQL Server permite organizar los archivos de datos (primarios y secundarios) en Grupos de Archivos (Filegroups). Los grupos de archivos son unidades lógicas que agrupan archivos de datos con fines de administración, asignación de datos y mejora del rendimiento.

El Grupo de Archivos Primario (PRIMARY) es el grupo por defecto que contiene el archivo de datos primario y cualquier archivo secundario que no se haya asignado explícitamente a otro grupo de archivos. Todas las tablas del sistema de SQL Server residen en el grupo de archivos primario.

Además del grupo primario, puedes crear Grupos de Archivos Definidos por el Usuario. Estos grupos permiten agrupar archivos de datos y luego especificar que ciertos objetos (como tablas o índices) se creen en un grupo de archivos particular. Esto es especialmente útil para distribuir la carga de E/S en múltiples discos. Por ejemplo, podrías crear un grupo de archivos en una unidad SSD de alta velocidad para tablas muy consultadas, mientras que otras tablas menos críticas residen en archivos en unidades de disco tradicionales, todas dentro del mismo grupo de archivos definido por el usuario.

SQL Server también tiene grupos de archivos especiales para funcionalidades específicas, como el grupo de archivos para datos optimizados para memoria (Memory Optimized Data Filegroup) y el grupo de archivos FILESTREAM.

El Grupo de Archivos por Defecto

Exactamente un grupo de archivos en cada base de datos se designa como el grupo de archivos por defecto. Cuando creas un objeto (como una tabla) sin especificar explícitamente en qué grupo de archivos debe residir, SQL Server lo asigna al grupo de archivos por defecto. Inicialmente, el grupo PRIMARY es el grupo por defecto, pero puedes cambiarlo usando la sentencia ALTER DATABASE. Sin embargo, los objetos y tablas del sistema siempre permanecerán en el grupo PRIMARY, independientemente de cuál sea el grupo por defecto.

Estrategia de Llenado de Archivos y Grupos de Archivos

Dentro de un grupo de archivos, SQL Server utiliza una estrategia de Llenado Proporcional (Proportional Fill). Esto significa que cuando se escriben datos en un grupo de archivos, el Motor de Base de Datos de SQL Server distribuye la escritura entre todos los archivos del grupo de forma proporcional al espacio libre que tiene cada archivo. En lugar de llenar un archivo completamente antes de pasar al siguiente, si un archivo tiene el doble de espacio libre que otro en el mismo grupo, SQL Server escribirá aproximadamente el doble de datos en ese archivo. Esta estrategia ayuda a asegurar que los archivos se llenen de manera uniforme y permite aprovechar el paralelismo de E/S si los archivos están ubicados en discos distintos, logrando un efecto similar al striping.

Reglas Clave sobre Archivos y Grupos de Archivos

• Un archivo o grupo de archivos pertenece exclusivamente a una única base de datos.
• Un archivo solo puede ser miembro de un único grupo de archivos.
• Los archivos de registro de transacciones nunca forman parte de ningún grupo de archivos de datos.

Recomendaciones y Mejores Prácticas

Para optimizar el rendimiento, la administración y la recuperación de tu base de datos SQL Server, considera las siguientes recomendaciones:

• Para la mayoría de las bases de datos pequeñas o medianas, un único archivo de datos y un archivo de registro pueden ser suficientes.
• Si utilizas múltiples archivos de datos (.ndf), créalos en un grupo de archivos secundario y considera hacer de este grupo el predeterminado para que el archivo primario contenga principalmente los objetos del sistema.
• Distribuye los archivos de datos y los grupos de archivos en diferentes discos físicos o LUNs (Logical Unit Numbers) para maximizar el rendimiento mediante la E/S paralela.
• Coloca objetos que compiten intensamente por el espacio o que son altamente accedidos en diferentes grupos de archivos para reducir la contención.
• Separa tablas y sus índices no clúster en diferentes grupos de archivos para mejorar el rendimiento de las consultas que involucran esas tablas y sus índices.
• ¡Crucial! Siempre coloca los archivos de registro de transacciones (.ldf) en discos físicos separados de los archivos de datos (.mdf, .ndf). La naturaleza de las operaciones de E/S en archivos de datos (aleatoria) y archivos de registro (secuencial) es muy diferente, y separarlos evita que se interfieran mutuamente.
• Cuando necesites expandir volúmenes de disco donde residen los archivos de base de datos, se recomienda detener los servicios de SQL Server y realizar una copia de seguridad completa antes de usar herramientas del sistema operativo.

Preguntas Frecuentes

¿Por qué usar grupos de archivos si puedo tener todos los datos en el archivo primario?
Los grupos de archivos permiten una mejor organización y administración de los datos. Lo más importante es que te permiten distribuir la carga de E/S en múltiples discos físicos, lo que puede mejorar significativamente el rendimiento, especialmente en bases de datos grandes y con mucha actividad.

¿Puede un archivo de datos estar en más de un grupo de archivos?
No, cada archivo de datos pertenece a un único grupo de archivos.

¿Puedo poner el archivo de registro en un grupo de archivos?
No, los archivos de registro de transacciones nunca forman parte de ningún grupo de archivos de datos.

¿Qué pasa si un archivo o un grupo de archivos se llena y el crecimiento automático está deshabilitado o alcanzó el tamaño máximo?
Si un archivo o grupo de archivos se llena y no puede crecer más, las operaciones que intenten escribir datos en esa ubicación fallarán con un error de espacio insuficiente. Esto puede paralizar la base de datos o afectar gravemente su funcionalidad.

¿Dónde se almacenan las tablas del sistema como sys.objects?
Las tablas del sistema siempre se almacenan en el grupo de archivos PRIMARY.

Conclusión

Entender la arquitectura de archivos y grupos de archivos es fundamental para cualquier administrador o desarrollador que trabaje con SQL Server. La correcta configuración y distribución de estos componentes puede tener un impacto directo y significativo en el rendimiento, la disponibilidad y la capacidad de recuperación de la base de datos. Al planificar cuidadosamente la ubicación y el tamaño de tus archivos y utilizar grupos de archivos para organizar tus datos, puedes sentar las bases para una base de datos robusta y de alto rendimiento.

Si quieres conocer otros artículos parecidos a Archivos y Grupos de Archivos en SQL Server puedes visitar la categoría Bases de datos.