Gestión de la base de datos

Nociones generales

Una vez realizada la instalación del servidor SQL, conviene definir los espacios lógicos de almacenamiento con el objetivo de reagrupar bajo un mismo nombre el conjunto de datos correspondientes a un mismo proyecto. Este conjunto es la base de datos, que va a permitirnos trabajar lógicamente con los objetos, tales como las tablas, sin tener que preocuparnos del almacenamiento físico. SQL Server permite realizar asociaciones entre los archivos físicos y las bases de datos. En este capítulo, se tratarán la creación y la gestión de los archivos físicos al mismo tiempo que las bases de datos.

1. Relaciones entre la base de datos y la organización física

En el momento de la creación de una base de datos es necesario precisar, al menos, dos archivos. El primero servirá para almacenar los datos y el segundo será utilizado por el diario con el objetivo de almacenar las imágenes antes y después de una modificación de los datos.

Estos dos archivos son obligatorios y propios de cada base de datos. En SQL Server, no es posible compartir un archivo de datos o el diario entre varias bases de datos.

Separación entre los esquemas lógico y físico

Creación, administración y eliminación de una base de datos

Una base de datos gestiona un conjunto de tablas de sistema y de tablas de usuario. La información contenida en estas tablas de sistema representa, entre otras cosas, la definición de las vistas, los índices, los procedimientos, las funciones, los usuarios y los privilegios. Las tablas de usuario contienen la información introducida por los usuarios.

CREATE DATABASE nombreBaseDeDatos 
[ ON 
  [PRIMARY] [ <especificaciónArchivo> [,n]] 
  [LOG ON < especificaciónArchivo > [,n]] 
] 
[ COLLATE intercalación ] 
[;] 

Establecer grupos de archivos

Es posible precisar los archivos de datos que usa la base de datos, aunque desafortunadamente es imposible indicar sobre qué archivo se crea un objeto particular. Para resolver este problema, existe la posibilidad de crear una tabla o un índice sobre un conjunto de archivos. Este conjunto de archivos, también llamado grupo de archivos, se gestiona de manera muy sencilla.

¿Por qué es necesario trabajar con varios grupos de archivos? Porque en un sistema operativo es normal separar los archivos de sistema de los programas y datos de usuario. ¿Por qué lo será en una base de datos? Conviene reunir en un mismo grupo de archivos los datos del mismo tipo. Por ejemplo, los datos estables (como clientes, artículos), los que evolucionan (como pedidos, facturas...) simplemente porque los volúmenes no son los mismos y la manera de trabajar con ellos, tampoco. También puede ser interesante definir los índices y las tablas sobre grupos de archivos distintos para reducir los tiempos de actualización de los datos y de los índices. En el caso de datos sensibles, el reparto por grupos de archivos también se puede regir por la política de copias de seguridad adoptada.

Estructura de los índices

SQL Server ofrece dos tipos de índices:

Habida cuenta de que el índice organizado (u ordenado) organiza físicamente los datos almacenados en la tabla, por lo general está asociado a la clave primaria de la tabla, ya que se trata de datos estables y poco voluminosos. Sin embargo, no es obligatorio. Algunas veces puede ser útil organizar según otro criterio, por ejemplo cuando la clave primaria está desprovista de significado. Cada tabla tiene a lo sumo un índice organizado. Los índices no organizados no afectan a la estructura física de la tabla. En cambio, como se basan en la organización física de los datos, es necesario definirlos en un momento posterior.

1. Los índices ordenados

Estos índices organizan físicamente la tabla y están formados por un árbol en el que las páginas del nivel de las hojas contienen los datos de la tabla subyacente. Los niveles superiores del árbol permiten ordenar la información en función del valor indexado. Cuando se añade una línea de información, esta se inserta en función del valor de su clave.

Habida cuenta de que la clave del índice organiza físicamente la tabla, es necesario basar este índice en un valor estable, y por eso tradicionalmente se mantiene el índice por clave primaria.

El esquema siguiente ilustra de manera sintética la estructura de un índice ordenado. Además de las uniones que permiten recorrer el árbol de abajo hacia arriba (desde la raíz a las hojas), existe una doble unión que permite recorrer todas las páginas de un mismo nivel.

El índice ordenado se crea por defecto cuando se define una restricción de clave primaria sobre una tabla. Si el administrador desea que la definición de la restricción no vaya acompañada de la creación de un índice tal, es necesario especificar la palabra clave NONCLUSTERED cuando se define la restricción.

Sintaxis

ALTER TABLE nombreTabla  
ADD CONSTRAINT nombreRestricción PRIMARY KEY  
[CLUSTERED|NONCLUSTERED] (listaColumnas);
 

La segunda posibilidad es definir un índice con la opción CLUSTERED.

Sintaxis

CREATE...

La partición de tablas y de índices

El objetivo de la partición es ofrecer un mejor rendimiento al trabajar con tablas muy voluminosas en términos de datos y a las que acceden muchos usuarios.

La partición puede ser útil para dividir el método de acceso a los datos. Por ejemplo, sobre la tabla de peticiones, solo es posible modificar (update) los pedidos del año contable en curso. Los pedidos de los años anteriores deben estar en modo de solo lectura.

La partición de una tabla permite dividir una tabla de grandes dimensiones en varias tablas. Cada una de estas subtablas es más pequeña que la tabla inicial y, por lo tanto, más fácil de gestionar para SQL Server. Es la unión de los datos presentes en todas estas subtablas lo que se corresponde con la tabla de origen.

Cada una de estas subtablas se puede crear sobre un grupo de archivos diferente, lo que hace posible la gestión de parámetros de almacenamiento distintos para cada partición de la tabla inicial y adaptar, de esta manera, las condiciones de almacenamiento de los datos en función de su utilización.

La tabla sobre la que se ha realizado una partición permite optimizar el almacenamiento de la información sin que el número de tareas administrativas adicionales sea elevado. Es más: los elementos tales como las restricciones de integridad y los triggers se definen en la tabla sin tener en cuenta el espacio de almacenamiento físico utilizado.

El reparto de los datos entre las diferentes particiones de la tabla se efectúa automáticamente en función de los criterios de reparto definidos en el momento de la creación de la tabla.

También es posible dejar que SQL Server reparta automáticamente los datos entre los diferentes grupos de archivos. Aunque...

Compresión de datos

SQL Server 2012 ofrece la posibilidad de activar la compresión a nivel de tablas e índices. Si la compresión se puede definir sobre las tablas e índices existentes, no se tomará en cuenta hasta después de la reconstrucción de la tabla (ALTER tabla nombreTabla REBUILD) o del índice en cuestión. Si la compresión de la tabla implica la compresión del índice organizado (CLUSTERED), los índices no organizados no se ven afectados y es necesario habilitar la compresión sobre cada uno de ellos, uno a uno. En el caso de las tablas con particiones, la compresión puede tener lugar partición a partición.

El objetivo de la compresión es reducir el espacio de disco que utilizan los datos de la tabla. La compresión de los datos va a permitir almacenar más líneas de información en el mismo bloque de 8 KB. La compresión no permite aumentar el tamaño máximo de las líneas. De hecho, el mecanismo debe ser reversible.

En las tablas valorizadas, es posible conocer el impacto de la compresión de los datos ejecutando el procedimiento almacenado sp_estimate_data_compression_savings.

La compresión es una operación puntual. Por eso es preferible pasar por el asistente...

Cifrado de datos

SQL Server permite encriptar los archivos de datos y los diarios de log. Este encriptado es dinámico y se efectúa en el momento de cada escritura en disco. Es igual para la operación de desencriptado de datos. Esta funcionalidad de encriptado/desencriptado es transparente y se llama TDE o Transparent Data Encryption.

Establecer esta operación de encriptado permite garantizar una opacidad más grande de los archivos de datos y diarios para las diferentes herramientas de sistema de análisis de los archivos, o evitar una asociación/anulación de asociación de base de datos no autorizada. Sin embargo, esta operación de encriptado no tiene ninguna garantía adicional en lo que respecta a la comunicación entre el proceso cliente y el servidor. Cuando el encriptado de la base de datos está habilitado, las copias de seguridad también se encriptan con la misma clave. Por lo tanto, es necesario tener esta clave para poder restaurar los datos.

El encriptado de los datos se apoya en una clave de encriptado (DEK: Database Encryption Key) que se registra en la base master en forma de un certificado, por ejemplo.

Antes de poder establecer las funciones de encriptado en una base de datos, es necesario comenzar por definir una clave principal para obtener un certificado válido. Entonces, el certificado se utiliza para definir la clave de encriptado. La generación de esta...

Las tablas temporales

SQL Server propone hacer el seguimiento automático de las diferentes versiones de la información almacenada en una tabla. Es decir que SQL Server guardará una versión de cada fila antes y después de su modificación.

El nombre exacto de este mecanismo porpuesto por SQL Server es el de "Tablas de sistema temporales por versión" ya que SQL Server gestiona el periodo de validez de cada fila.

Para gestionar esta noción de temporalidad, SQL Server se apoya en:

Estas tablas, aunque sean singulares en su sistema de gestión de la información, se pueden administrar como cualquier otra tabla de la base de datos.

Sintaxis

CREATE TABLE nombreTabla(  
nombreColumnatipo, ...  
inicioValidez datetime2 GENERATED ALWAYS AS ROW START NOT NULL,  
finValidez datetime2 GENERATED ALWAYS AS ROW END NOT NULL,  
PERIOD FOR SYSTEM_TIME (inicioValidez, finValidez)  
) WITH (SYSTEM_VERSIONING=ON (HISTORY_TABLE= nombreTablaHistorico)) ; 

Planificación

Ejercicio: crear una base de datos

CREATE DATABASE LibroTSQL   
    ON PRIMARY (   
           NAME=LibroTSQL,   
           FILENAME='c:\datos\LibroTSQL.mdf',   
           SIZE=10Mb   
    )...

Ejercicio: añadir un grupo de archivos

ALTER DATABASE LibroTSQL   
     ADD FILEGROUP Data;
 

ALTER DATABASE LibroTSQL   
     ADD FILE (   
           NAME=data1,   
           FILENAME='c:\datos\data1.ndf',   
           SIZE=50Mb,   
           MAXSIZE=50Mb   
     )   ...