Arquitectura física de SQL Server, 2ª Parte

Ficheros de datos,  grupos de ficheros y creación de una base de datos

Aquí la primera parte de Arquitectura Física de SQL Server

En SQL Server, cada base de datos se almacena directamente en un conjunto de ficheros del sistema operativo, sin que sea posible la asignación de cada uno de estos ficheros a más de una base de datos. Es decir, cada fichero está unívocamente asociado a una base de datos. Cada base de datos tiene asociados, como mínimo, dos ficheros: uno para los datos y otro para almacenar el transaction log. Los ficheros de datos se almacenan en archivos del sistema operativo como archivos de datos de extensión .mdf y la de los ficheros que almacenan el transaction log es  .ldf  

Además si hay archivos secundarios de base de datos, estos llevan extensión .ndf  y otros archivos adicionales llevan .dat. También pueden existir varios archivos para el transaction log con extensión .ldf

En el enlace se explica cómo hacer un backup de una base de datos y luego como recuperarlo. Al final del post se indica cómo proceder si nos han proporcionado más de dos archivos.

Tipos de ficheros asociados a una base de datos

Existen básicamente tres tipos de ficheros asociados a una base de datos:

Fichero primario (Primary data file): En él se almacenan las tablas de sistema. Existe uno y sólo un fichero por cada base de datos. Es el .mdf que hemos visto anteriormente.

Ficheros secundarios (Secundary data files): Pueden existir tantos como se quiera.  Son opcionales y permiten ubicar tablas e índices en lugares específicos como alternativa a los segmentos en SQL Server. También permiten que la base de datos crezca hacia nuevos dispositivos físicos de almacenamiento cuando esta ya no dispone de espacio para que los ficheros de datos existentes puedan crecer.

Ficheros de log: Almacenan el transaction.Iog Cada base de datos debe tener como mínimo uno.

Qué es un grupo de ficheros

Los ficheros de datos pertenecientes a una base de datos se integran en grupos de ficheros. Los grupos de ficheros tienen una función fundamentalmente lógica, para facilitar la administración y la realización eficiente de ciertas tareas como la copia de seguridad. También permiten mejoras de rendimiento, ya que es posible ubicar en estos grupos de forma explícita tablas y otros objetos.

Los ficheros de un grupo forman una unidad desde el punto de vista del crecimiento automático de los ficheros. Si uno de los ficheros de un grupo se llena, SQL Server no le obligará a crecer a no ser que no quede espacio libre en alguno de los ficheros integrantes del grupo. Cada fichero puede pertenecer únicamente a un grupo de ficheros y cada grupo de ficheros sólo puede asociarse a una base de datos.

Si se añade información a un objeto de la base de datos, el gestor interno la reparte automáticamente entre todos los ficheros pertenecientes al mismo grupo al que el objeto está asignado. Esto  permite que los ficheros del grupo se llenen de forma paralela.

En las sentencias de creación de  bases de datos es posible  prescindir de los grupos de ficheros, pero aunque lo hagamos, SQL Server siempre considerará un grupo de ficheros por defecto, y en él se incluirán todos los ficheros asociados a la base de datos por defecto.

Gracias a los ficheros y grupos de ficheros se facilita la  tarea de optimizar el  rendimiento de la base de datos. Pues es posible ubicar explícitamente en ficheros o grupos de ficheros concretos los diferentes objetos de la base de datos, y estos ficheros o grupos a su vez pueden colocarse en dispositivos  (discos duros) específicos. Colocar objetos que se usan con frecuencia en discos separados permite independizar los procesos de lectura y escritura en esos objetos del resto, con la consiguiente mejora de rendimiento. Además, esta técnica permite realizar lecturas y escrituras en paralelo, siempre que se disponga de dispositivos separados de control de disco para cada uno de ellos.

Tipos de grupos de ficheros

Análogamente a lo que sucedía antes con los ficheros existen tres tipos de Grupos:

Grupos primarios (primary filegroup)

En él se almacena como mínimo, el fichero primario que es el que contiene su catálogo, es decir  las tablas del sistema de la base de datos. Por este motivo es importante ajustar cuidadosamente el tamaño de los ficheros que  lo integran, o configurarlos para que crezcan automáticamente, ya que si todos ellos se llenasen, no podría actualizarse el catálogo de la base de datos y quedaría temporalmente inaccesible.

Grupo por defecto (default filegroup)

Contiene todos los  a los objetos a los que no se especificó en el momento de su creación el grupo de ficheros en el que debían ubicarse. Sólo existe uno para cada base de datos.

Grupos de usuario (user defined filegroups)

Pueden existir tantos como se desee y son opcionales.

Grupos de sólo lectura

Es posible configurar grupos de ficheros completos como de sólo lectura, esta acción impedirá que todos los objetos que  se hayan ubicado explícitamente en estos ficheros no podrán ser modificados. Además, si lo intentamos con el grupo primario, que contiene las tablas de sistema de la base de datos, ésta no podrá ser modificada en ningún sentido, ni podrán crearse objetos nuevos, ya que cualquier modificación de la base de datos supone la actualización de las tablas de sistema.

Creación de una base de datos y gestión de ficheros

Para crear los ficheros de datos se puede hace de forma conjunta e inseparable con la base de datos, en el mismo comando CREATE DATABASE.

La sintaxis de este comando es la siguiente:

CREATE DATABASE nombre de la base de datos

[ON [PRIMARY]

[ lista de <características del fichero>]

[ lista de <características del grupo>]

]

[LOG ON { < características del fichero>}]

Donde

 < características del fichero> debe sustituirse por:

{ [ NAME = nombre lógico del fichero, ]

FILENAME =  ‘nombre físico del fichero’

[, SIZE = tamaño]

[, MAXSIZE = { tamaño máximo | UNLIMITED }]

[, FILEGROWTH = incremento de tamaño]} [, …n]

Y  de <características del grupo>] por:

FILEGROUP nombre grupo <características del fichero> [,…n]

Actualmente para crear una base de datos por defecto es tan sencillo como usar el administrador de SQL SERVER, sobre Bases de Datos pulsando el botón derecho del ratón.

Esta acción nos abre un cuadro de diálogo bastante intuitivo donde podemos indicarle los parámetros principales para crear una base de datos nueva, he recuadrado

Los nombres de los dos ficheros principales (el de datos y el log) que hemos visto anteriormente, si deseamos añadir uno nuevo, es aquí donde podemos pulsar el botón agregar, el cual nos mostrará una nueva línea en el cuadro de diálogo.

También he recuadrado en rojo la parte donde se indica el tamaño inicial de los ficheros y el crecimiento automático. Cuando pulsemos Aceptar los ficheros se crearán en la ruta indicada en  Ruta de acceso.

Una vez creada la base de datos de este modo también podemos ver el código que ha generado automáticamente haciendo esto

Con lo que obtenemos dicho código que nos puede servir de plantilla por si deseamos crear otra base de datos  por código para variar algún parámetro.

USE [master]

GO

/****** Object:  Database [dbPrueba]   ******/

CREATE DATABASE [dbPrueba] ON  PRIMARY

( NAME = N'dbPrueba', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\dbPrueba.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'dbPrueba_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\dbPrueba_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

EXEC dbo.sp_dbcmptlevel @dbname=N'dbPrueba', @new_cmptlevel=90

GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC [dbPrueba].[dbo].[sp_fulltext_database] @action = 'disable'

end

GO

ALTER DATABASE [dbPrueba] SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE [dbPrueba] SET ANSI_NULLS OFF

GO

ALTER DATABASE [dbPrueba] SET ANSI_PADDING OFF

GO

ALTER DATABASE [dbPrueba] SET ANSI_WARNINGS OFF

GO

ALTER DATABASE [dbPrueba] SET ARITHABORT OFF

GO

ALTER DATABASE [dbPrueba] SET AUTO_CLOSE OFF

GO

ALTER DATABASE [dbPrueba] SET AUTO_SHRINK OFF

GO

ALTER DATABASE [dbPrueba] SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE [dbPrueba] SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE [dbPrueba] SET CURSOR_DEFAULT  GLOBAL

GO

ALTER DATABASE [dbPrueba] SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE [dbPrueba] SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE [dbPrueba] SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE [dbPrueba] SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE [dbPrueba] SET  DISABLE_BROKER

GO

ALTER DATABASE [dbPrueba] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

GO

ALTER DATABASE [dbPrueba] SET DATE_CORRELATION_OPTIMIZATION OFF

GO

ALTER DATABASE [dbPrueba] SET TRUSTWORTHY OFF

GO

ALTER DATABASE [dbPrueba] SET ALLOW_SNAPSHOT_ISOLATION OFF

GO

ALTER DATABASE [dbPrueba] SET PARAMETERIZATION SIMPLE

GO

ALTER DATABASE [dbPrueba] SET READ_COMMITTED_SNAPSHOT OFF

GO

ALTER DATABASE [dbPrueba] SET RECOVERY FULL

GO

ALTER DATABASE [dbPrueba] SET  MULTI_USER

GO

ALTER DATABASE [dbPrueba] SET PAGE_VERIFY CHECKSUM 

GO

ALTER DATABASE [dbPrueba] SET DB_CHAINING OFF

GO

ALTER DATABASE [dbPrueba] SET  READ_WRITE

GO

A continuación se comentan algunos aspectos de los comandos precedentes.

Ficheros asociados a la base de datos: la cláusula ON precede a las especificaciones de los ficheros en los que se almacenará la base de datos. Si se omite, SQL Server creará por defecto un fichero para la base de datos y otro para el log.   En nuestro ejemplo ON  PRIMARY

Nombre lógico de cada uno de los ficheros: en la cláusula ON también debe indicarse una lista de características del fichero. Cada una de estas características estará encabezada por el nombre lógico de este y será el que servirá para hacer referencia a él en SQL server. Es el que se indica tras la cláusula NAME.  En nuestro ejemplo NAME = N'dbPrueba'

Nombre físico de cada uno de los ficheros: en cada una de las características del fichero debe incluirse el nombre real de este, en la cláusula FILENAME, incluyendo su ruta completa. SQL Server creará el fichero tras la ejecución del comando, y lo habilitará para albergar datos de la base de datos recién creada. En nuestro ejemplo FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\dbPrueba.mdf'

Tamaño inicial del fichero: al crear la base de datos, SQL Server crea cada uno de los ficheros con el tamaño que se especifica en la cláusula SIZE. El tamaño mínimo para el fichero es 512 KBytes, que es el tamaño de la allocation unit. En nuestro ejemplo SIZE = 3072KB

El tamaño por defecto de las bases de datos puede configurarse modificando el tamaño de la base de datos model que es la base de datos que se replica cada vez que creamos una base de datos nueva.Cada base de datos que creemos, sin especificar su tamaño, tendrá un único fichero primario para datos, con los mismos contenidos que el fichero primario de la base de datos model y con un tamaño de 1 MB. Todos los ficheros  de datos que especifiquemos aunque no asignemos tamaño inicial, serán creados con un tamaño de 1 MB.  Los ficheros se generan con un tamaño inicial, que ocupará en el sistema de archivos de la máquina, aunque la base de datos esté inicialmente vacía de información.

Tamaño máximo del fichero de datos: para cada fichero también se puede especificar el tamaño máximo que podrá alcanzar si se especifica que la base de datos tendrá crecimiento automático (auto-grow). Para ello bastará con indicar un valor tras la cláusula MAXSIZE. Si se omite este valor, o se si se incluye la cláusula UNLIMITED, el  fichero crecerá indefinidamente, mientras exista espacio fisico en el disco. En nuestro ejemplo MAXSIZE = UNLIMITED para la base de datos y MAXSIZE = 2048GB para el log.

Incremento para crecimiento automático: la cláusula  FILEGROWTH permite indicar el incremento del tamaño del archivo que añadirá SQL Sever cuando deba hacer crecer un fichero de datos. Puede especificarse en términos absolutos o en porcentaje del tamaño del fichero a expandir. Los incrementos siempre se producirán en aumentos de 64 KB, tamaño que es el mínimo que podemos especificar. El valor por defecto es el 10 por ciento. En nuestro ejemplo FILEGROWTH = 1024KB en la base de datos y FILEGROWTH = 10% en el log.

Grupo primario: si se ha utilizado ON, se puede añadirse la palabra PRIMARY, a la que seguirán las características de todos los ficheros asociados a la base de datos que se incluirán en el grupo primario, que como se ha dicho anteriormente, contiene catálogo,(las tablas de sistema de la base de datos). Si se omite, el primer fichero especificado en la lista que seguirá a ON será considerado por el gestor como el fichero primario.

Fichero de log: permite especificar los ficheros en los que se almacenará el transaction log. Si se omite, el gestor creará un fichero separado de los datos para albergar el log.

Restauración de una base de datos desde ficheros de datos existentes: Es posible recuperar una base de datos desde los ficheros que la contienen. Esto facilita transportar bases de datos entre servidores diferentes.

Modificación de los ficheros asociados a una base de datos

Los ficheros asociados a una base de datos se crean simultáneamente a ésta, con el comando CREATE DATABASE Una vez la base de datos ya ha sido creada, podemos añadir a esta nuevos ficheros mediante el comando ALTER DATABASE cuya sintaxis es la siguiente:

ALTER DATABASE nombre de la base de datos

{ADD FILE lista de <características del fichero>

[TO FILEGROUP nombre del grupo]

[ADD LOG FILE lista de < características del fichero >

[ REMOVE FILE nombre lógico del fichero >

[ ADD FILEGROUP nombre del grupo

[REMOVE FILEGROUP nombre del grupo

[MODIFY FILE  <características del fichero>]

[MODIFY GROUP nombre grupo, READONLY|READWRITE|DEFAULT

Donde  <características del fichero> debe Sustituirse por:

([ NAME = nombre lógico del fichero, ]

FILENAME = ‘nombre físico del fichero’

[, SIZE = Tamaño]

[, MAXSIZE = { tamaño máximo | UNLIMITED}]

[, FILEGROWTH = incremento de tamaño]) [,…n]

El comando ALTER DATABASE nos permite modificar el conjunto de ficheros y grupos asociados a una base de datos, añadiendo y eliminando ficheros, cambiando las propiedades de los grupos existentes, etc.

Nuevos ficheros asociados a la base de datos: la cláusula ADD FILE precede a las especificaciones de nuevos ficheros sobre los que la base de datos se expandirá.

Tamaño del fichero: nuevo tamaño del fichero, si se utiliza MODIFY FILE, o tamaño inicial, si se ha indicado ADD FILE. El tamaño mínimo para un fichero nuevo es de 512 KBytes, y para uno existente que se desee modificar, es el tamaño actual.

Tamaño máximo del fichero de datos e Incremento para crecimiento automático: igual que para el caso de creación de la nueva base de datos.

La única diferencia con lo anteriormente explicado es que a hora le tenemos que indicar explícitamente el grupo al que se añadirá en nuevo fichero: En la cláusula TO  FILE GROUP podemos especificar el grupo al que deseamos se añada el nuevo fichero.

Nuevo fichero de log: también podemos añadir nuevos ficheros  al transaction log, mediante la cláusula ADD LOG FILE.

Eliminación de ficheros: REMOVE FILE elimina un fichero, que debe estar completamente vacío, de la base de datos, borrándolo además del sistema de ficheros del servidor.

Adición o eliminación de grupos: las cláusulas  ADD FILEGROUP Y REMOVE FILEGROUP permiten añadir o eliminar grupos. En el caso de eliminación, todos los ficheros incluidos en él también son eliminados. Los grupos también deben estar vacíos antes de poder ser eliminados, situación que se aplicará a todos los ficheros que contiene.

Modificación de los ficheros: Las propiedades de los ficheros (su tamaño, nombre, incremento y tamaño máximo) pueden ser modificadas, una a una, utilizando la cláusula MODIFY FILE.

Modificación de grupos: la cláusula MODIFY GROUP permite hacer que un grupo sea de sólo lectura (READONLY), o eliminar esa característica (READWRITE), o definir el citado grupo como el por defecto de base de datos (DEFAULT). El grupo por defecto es en el que se ubicará cualquier objeto que no se ubique explícitamente en un grupo en el momento de su creación.

ALTER DATABASE dbPrueba

ADD FILEGROUP Grupo2

GO

ALTER DATABASE dbPrueba

ADD FILE

( NAME = 'FichNuevo',

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\FicNuevo.ndf',

SIZE = 30MB,

MAXSIZE = 200MB,

FILEGROWTH = 1MB),

( NAME = 'FichNuevo2',

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\FicNuevo2.ndf',

SIZE = 10MB,

MAXSIZE = 100MB,

FILEGROWTH = 2MB)

TO FILEGROUP Grupo2

ALTER DATABASE dbPrueba

MODIFY FILEGROUP Grupo2 DEFAULT

GO

Modificación del tamaño de los ficheros de datos

El tamaño de los ficheros de datos en SQL Server se puede modificar para expandirlos o para reducirlos. Esto se puede realizar a través del Administrador Corporativo o ejecutando comandos.

Modificación automática (Auto-Shrink y Auto-Grow)

Las bases de datos (y sus ficheros de datos asociados) pueden modificar su tamaño automáticamente a medida que va siendo necesario  más espacio de almacenamiento, también se puede configurar para que el tamaño se reduzca si se ha declarado más grande de lo que necesita aunque este último caso hay que dejarlo especificado en la creación de la base de datos.  

La tarea de modificación del tamaño de la base de datos se lleva a cabo en un segundo plano. El gestor examina el espacio libre en los ficheros para,  determinar si los ficheros contienen espacio no utilizado, en cuyo caso, los reducirá.

La reducción de un fichero ejecuta el desplazamiento de registros de páginas ubicadas al final del archivo a páginas en posiciones cercanas al inicio del mismo que estén vacías. Este desplazamiento se realiza en primer lugar por las estructuras de índice, para reducir en lo posible la reorganización de las mismas. De este modo, los nodos en la estructura de árbol balanceado de índice, es decir, las páginas indexadas en un nodo. Se desplazan conjuntamente.

Igual que los ficheros pueden reducir su tamaño automáticamente el gestor también se encarga de hacer crecer los archivos asociados a una base de datos cuando sus necesidades de almacenamiento de información así lo requieren.