Las tablas en SQL Server consisten en un esquema lógico de tablas y otros objetos en los cuales, la estructura de datos se utiliza para organizar registros. Este esquema lógico se almacena físicamente en un set de archivos que la base de datos utiliza, con los registros de datos que se escriben en esos archivos.
Archivos de bases de datos
Existen tres tipos de archivos de bases de datos utilizados en SQL Server, los archivos de datos primarios, los secundarios y los archivos log de transacción.
Archivos primarios de datos
El archivo primario de datos es el punto de inicio de la base de datos. Todas las bases de datos tienen un archivo primario único. Además de contener páginas de datos, el archivo de datos primario contiene punteros a los otros archivos en la base de datos. Los archivos de datos primarios suelen usar la extensión de archivo .mdf. El uso de esta extensión no es obligatorio, pero es muy recomendable.
Archivos secundarios de datos
Los archivos de datos secundarios son archivos adicionales opcionales, definidos por el usuario, que se pueden utilizar para distribuir datos en más ubicaciones de almacenamiento por razones de rendimiento y/o mantenimiento.
Se puede utilizar archivos secundarios para distribuir datos en varios discos colocando cada archivo en una unidad de disco diferente. Además, si una base de datos excede el tamaño máximo para un solo archivo de Windows, se puede usar archivos de datos secundarios para que la base de datos pueda seguir creciendo. La extensión recomendada para este tipo de archivos es .ndf.
Archivos log de transacción
Los archivos log o de registro de transacciones, contienen información que se puede usar para recuperar la base de datos cuando sea necesario. Debe haber al menos un archivo de registro para cada base de datos.
Todas las transacciones se escriben en el archivo de registro utilizando el mecanismo de registro de escritura anticipada (WAL) para garantizar la integridad de la base de datos en caso de falla y para respaldar las reversiones de transacciones. La extensión recomendada para estos archivos es .ldf.
Cuando las páginas de datos deben cambiarse, se recuperan en la memoria y ahí se cambian. Las «páginas sucias» se escriben en el registro de transacciones de forma síncrona. Después, durante un proceso en segundo plano conocido como «punto de control», las página sucias se escriben en los archivos de la base de datos. Por esto, las páginas contenidas en el registro de transacciones son críticas para la capacidad de SQL Server de recuperar la base de datos a un estado comprometido conocido.
Páginas y extensiones
Los archivos de datos almacenan datos en páginas, mismas que se agrupan en extensiones.
Páginas de archivo de datos
Las páginas en un archivo de datos de SQL Server están numeradas secuencialmente, comenzando con cero para la primera página. Cada archivo en una base de datos contiene un número de identificación de archivo único.
Para identificar de forma exclusiva una página en una base de datos, se requieren tanto el ID del archivo como el número de página. Cada página tiene un tamaño de 8 KB. Luego de permitir la información de encabezado que se necesita en cada página, queda una región de 8.096 bytes restantes para almacenar datos. Las filas de datos pueden contener valores de columna de longitud fija y longitud variable.
Todas las columnas de longitud fija de una fila de datos deben caber en una sola página, dentro de un límite de 8.60 bytes. Las páginas de datos solo contienen datos de un único objeto de base de datos, como una tabla o un índice.
Extensiones
Los grupos de ocho páginas contiguas se denominan extensión. SQL Server utiliza extensiones para simplificar la administración de páginas de datos. Existen dos tipos de extensiones:
- Extensiones uniformes: Todas las páginas dentro de la extensión contienen datos de un solo objeto
- Extensiones mixtas: Las páginas de la extensión pueden contener datos de diferentes objetos
La primera asignación para un objeto es a nivel de página, y siempre proviene de una extensión mixta. Si son gratuitos, otras páginas de la misma extensión mixta se asignarán al objeto según sea necesario. Una vez que el objeto creció más que su primera extensión, todas las asignaciones futuras son de extensiones uniformes.
Tanto en los archivos de datos primarios como secundarios, se asigna un pequeño número de páginas para rastrear el uso de extensiones en el archivo.
Consideraciones para dispositivos de almacenamiento en disco
- Almacenamiento Adjunto Directo – Discos conectados por un controlador RAID
- Red de Área de Almacenamiento – Discos conectados por una red y disponibles para múltiples servidores
- Windows Storage Pools – Unidades de disco básicas agrupadas juntas para crear un espacio de almacenamiento más grande
Generalmente, un servidor de base de datos no tendrá suficientes discos internos para permitirle entregar los niveles de rendimiento requeridos, por lo que muchos servidores usan una matriz externa de discos para el almacenamiento de datos. En una matriz de discos, se combinan discos magnéticos y/o dispositivos de estado sólido para proporcionar redundancia y un mejor rendimiento. Los tipos de conjuntos de discos utilizados incluyen comúnmente:
Almacenamiento Adjunto Directo (DAS)
Cuando se utiliza DAS, los discos se almacenan en un gabinete y se conectar al servidor mediante un controlador RAID. Se puede usar el controlador para crear matrices RAID a partir de los discos DAS.
El sistema operativo Windows Server tratará cada matriz RAID como un volumen de almacenamiento, como lo haría con un disco interno. Por lo general, un gabinete DAS contiene entre 8 y 24 unidades. DAS ofrece buenos niveles de rendimiento y es relativamente económico. Sin embargo, DAS puede ser limitante porque normalmente no puede compartir el almacenamiento entre varios servidores.
Red de Área de Almacenamiento (SAN)
En una SAN, los discos de almacenan en gabinetes y se conectan mediante una red común. La red puede ser Ethernet, como en el caso de las SAN SCSI de Internet (iSCSI), o una red de canal de fibra. Los servidores se conectan al almacenamiento SAN por medio de adaptadores de bus host (HBA). Las SAN de canal de fibra generalmente ofrecen un mejor rendimiento, pero son más caras que las SAN iSCSI. Aunque es más costoso que el DAS, uno de los principales beneficios de una SAN es permitir que el almacenamiento se comparta entre servidores, lo cual es necesario para configuraciones como la agrupación de servidores.
En una SAN, es una práctica común garantizar que los componentes como HBA, puertos y conmutadores estén duplicados. Esto elimina puntos únicos de falla y ayuda a mantener la disponibilidad del servicio.
Grupos de Almacenamiento de Windows
En los grupos de almacenamiento de Windows, se puede agrupar unidades en un grupo y luego crear espacios de almacenamiento que son unidades virtuales. Esto permite utilizar hardware de almacenamiento de productos básicos para crear grandes espacios de almacenamiento y agregar más unidades cuando la capacidad del grupo es baja.
Se puede crear agrupaciones de almacenamiento desde discos duros internos y externos (incluidos USB, SATA y SAS) y desde unidades de estado sólido.
Niveles RAID
Muchas soluciones de almacenamiento utilizan hardware RAID para proporcionar tolerancia a fallas por medio de la redundancia de datos, y en algunos casos, para mejorar el rendimiento. También se puede implementar RAID 0, RAID 1 y RAID 5 controlados por software utilizando el sistema operativo Windows Server, y otros niveles pueden ser compatibles con SAN de terceros. Los tipos de RAID más utilizados son:
- RAID 0, trazado de discos. Un conjunto de bandas consiste en espacio de dos o más discos que se combina en un solo volumen. Los datos se distribuyen de forma uniforme en todos los discos, lo que mejora el rendimiento de E/S, particularmente cuando cada dispositivo de disco tiene su propio controlador de hardare. RAID 0 no ofrece redundancia, y en caso de que un disco falle, el volumen se vuelve inaccesible.
- RAID 1, duplicación de disco. Un conjunto espejo es un volumen de almacenamiento lógico que se basa en el espacio de dos discos, con un disco que almacena una copia redundante de los datos en el otro. La duplicación puede proporcionar un buen rendimiento de lectura, pero el rendimiento de escritura puede verse afectado. RAID 1 es costoso en términos de almacenamiento, ya que el 50% del espacio en disco disponible se utiliza para almacenar datos redundantes.
- RAID 5, creación de bandas de disco con paridad. RAID 5 ofrece tolerancia a fallas mediante el uso de datos de paridad que se escriben en todos los discos en un volumen dividido que se compone de un espacio de 3 o más discos. RAID 5 por lo general funciona mejor que RAID 1. Sin embargo, si falla un disco del conjunto, el rendimiento se degrada.
RAID 5 es menos costoso en términos de espacio en disco que RAID 1 porque los datos de paridad solo requieren el equivalente de un disco en el conjunto para almacenarlo. Por ejemplo, en una matriz de cinco discos, cuatro estarían disponibles para el almacenamiento de datos, lo que representa el 80 por ciento del espacio total en disco.
- RAID 10, reflejo con rayas. En un RAID 10, se refleja un conjunto de bandas RAID 0 sin tolerancia a fallas. Esta disposición ofrece el excelente rendimiento de lectura/escritura de RAID 0, combinado con la tolerancia a fallas de RAID 1. Sin embargo, RAID 10 puede resultar costoso porque al igual que RAID 1, el 50% del espacio total se utiliza para almacenar datos redundantes.
Determinación de la ubicación del archivo y el número de archivos
Cuando se crea una base de datos, es necesario decidir dónde almacenar los archivos de la base de datos. La elección de la ubicación de almacenamiento para los archivos de la base de datos es extremadamente importante, ya que puede tener un efecto significativo en el rendimiento, la resistencia, la capacidad de recuperación y la capacidad de administración.
Aislamiento de datos y archivos de registro
Es importante aislar los archivos de registro y datos pos motivos de rendimiento y recuperación. Este aislamiento debe estar en el nivel del disco físico.
Patrones de acceso
Los patrones de acceso de los archivos de registro y datos son muy diferentes. El acceso a los datos en un archivo de registro consiste principalmente en escrituras secuenciales y sincrónicas, con acceso aleatorio ocasional al disco.
El acceso a datos en archivos de datos ofrece predominantemente acceso asíncrono de disco aleatorio a los archivos de datos desde la base de datos. Un solo dispositivo de almacenamiento físico no tiende a proporcionar buenos tiempos de respuesta cuando se combinan estos tipos de acceso a datos.
Recuperación
Aunque los volúmenes RAID brindan cierta protección contra fallas del dispositivo de almacenamiento físico, aún podrían ocurrir fallas de volumen completas. Si se pierde un archivo de datos de SQL Server, la base de datos se puede restaurar a partir de una copia de seguridad y el registro de transacciones se puede volver a aplicar para recuperar la base de datos a un momento reciente.
Si se pierde un archivo de registro de SQL Server, la base de datos puede verse obligada a recuperarse de los archivos de datos, con la posibilidad de alguna pérdida de datos o inconsistencia en la base de datos. Sin embargo, si los archivos de datos y de registro están en un único subsistema de disco que se puede, las opciones de recuperación por lo general implican restaurar la base de datos desde una copia de seguridad anterior y perder todas las transacciones desde ese momento. El aislamiento de datos y archivos de registro puede ayudar a evitar los peores impactos de las fallas del subsistema del disco.
Gestión de archivos de datos
Lo más recomendable es que todos los archivos de datos que se definen para una base de datos deben ser del mismo tamaño. Los datos se distribuyen de manera uniforme en todos los archivos de datos disponibles. Las principales ventajas de rendimiento de esto se obtienen cuando los archivos se distribuyen en diferentes ubicaciones de almacenamiento.
La asignación de múltiples archivos de datos proporciona una serie de ventajas de gestión, entre ellas:
- La posibilidad de mover archivos y parte de los datos más tarde.
- Una reducción en el tiempo de recuperación al restaurar por separado un archivo de base de datos.
- Un aumento en el paralelismo en el canal de E/S.
- La capacidad de tener bases de datos más grandes que el tamaño máximo de un solo archivo de Windows.
Cantidad de archivos de registro
A diferencia de la forma en que SQL Server escribe en archivos de datos, el motor de base de datos de SQL Server solo escribe en un único archivo de registro en cualquier momento. Los archivos de registro adicionales solo se usan cuando hay espacio disponible en el archivo de registro activo.
La planificación de capacidad ayuda a garantizar que sus bases de datos tengan acceso al espacio requerido a medida que crecen. Calcular la tasa de crecimiento de la base de datos permite planificar los tamaños y el crecimiento de los archivos con mayor facilidad y precisión.
Al planificar la capacidad que se requiere, se debe estimar el tamaño máximo de la base de datos, los índices, el registro de transacciones y tempdb, por medio de un período de crecimiento previsto.
Para la mayoría de los sitios, se debe intentar crear archivos de base de datos que sean lo suficientemente grandes como para manejar los datos que se espera que se almacenen en los archivos por un período de 12 meses. De ser posible, basar la planificación de la capacidad en pruebas con las aplicaciones reales que almacenarán datos en la base de datos. Si esto no es posible, consultar al desarrollador o proveedor de la aplicación para determinar los requisitos de capacidad de datos realistas.
Crecimiento automático VS crecimiento planificado
SQL Server puede expandir de forma automática una base de datos de acuerdo con los parámetros de crecimiento que se definieron cuando se crearon los archivos de la base de datos. Si bien las opciones de crecimiento automático deben estar habilitadas para evitar el tiempo de inactividad cuando se produce un crecimiento inesperado, es importante evitar la necesidad de que SQL Server crezca automáticamente los archivos. En cambio, deben controlar el crecimiento de los archivos a lo largo del tiempo y asegurarse de que los archivos sean lo suficientemente grandes durante varios meses o años.
A muchos administradores les preocupa que los archivos de bases de datos más grandes de alguna forma aumenten el tiempo que lleva realizar copias de seguridad. El tamaño de una copia de seguridad de SQL Server no está relacionado directamente con el tamaño de los archivos de la base de datos, ya que solo se realiza una copia de seguridad de las páginas que realmente contienen datos.
Un problema importante que surge con el crecimiento automático es una compensación relacionada con el tamaño de los incrementos de crecimiento. Si se especifica un gran incremento, se puede experimentar un retraso significativo en la ejecución de la instrucción Transact-SQL que desencadena la necesidad de crecimiento.
Si el incremento especificado es demasiado pequeño, el sistema de archivos puede fragmentarse mucho y el rendimiento de la base de datos puede verse afectado porque los archivos de datos se han asignado en pequeños fragmentos en todo un subsistema de disco.
Planificación del crecimiento del archivo de registro
Si el registro de transacciones no está configurado para expandirse automáticamente, pronto puede quedarse sin espacio cuando ocurren ciertos tipos de actividad en la base de datos. Por ejemplo, realizar operaciones masivas a gran escala, como la importación masiva o la creación de índices, puede hacer que el registro de transacciones se llene muy rápido.
Aparte de expandir el tamaño del registro de transacciones, también es posible truncar un archivo de registro. Truncar el registro purga el archivo de transacciones inactivas, confirmadas y permite que el motor de la base de datos de SQL Server reutilice esta parte del registro de transacciones.
Sin embargo, se debe tener cuidado al truncar el registro de transacciones, ya que esto puede afectar la capacidad de recuperación de la base de datos en caso de falla. En general, el truncamiento de registros se gestiona como parte de una estrategia de copia de seguridad.
