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 de las transacciones, con el objetivo de almacenar la información necesaria para la gestión de las transacciones durante las modificaciones en 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

select * from dbo.CLIENTES  
with (updlock) 

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 ] 
[;] 

(NAME = nombreLógico, 
FILENAME = 'rutaYNombreArchivo' 
[,SIZE = tamaño [KB|MB|GB|TB]] 
[,MAXSIZE={tamañoMáximo[KB|MB|GB|TB]|UNLIMITED}] 
[,FILEGROWTH...

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:

Dado que el índice ordenado organiza físicamente los datos almacenados en la tabla, está asociado por defecto a la clave primaria de la tabla, ya que se trata de un dato estable y poco voluminoso. Sin embargo, no es obligatorio. Algunas veces puede ser útil elegir según otro criterio, por ejemplo, cuando la clave principal no tiene sentido. Cada tabla tiene a lo sumo un índice ordenado. Los índices no ordenados 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 están formados por un árbol binario (b-tree), en el que las páginas del nivel de las hojas contienen los datos de la tabla subyacente. 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 [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX nombreÍndice  
ON nombreTabla (listaColumnas)  
[ON grupoDeArchivos]; 

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 almacenar una tabla de grandes dimensiones en varios archivos. Cada una de estas particiones es más pequeña que la tabla inicial y, por lo tanto, más fácil de gestionar para SQL Server.

La partición se realiza según un criterio vinculado a las columnas de la tabla original; por ejemplo, con la fecha de pedido con el 1 de enero del año actual, como valor delimitador.

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.

Así pues, para hacer una partición en la tabla, es necesario definir una función y un esquema de partición, que van a indicar una clave de partición para el grupo de archivos donde se van a almacenar los datos.

Si dos tablas utilizan la misma función de partición y el mismo esquema de partición, entonces los datos relacionados se almacenarán en el mismo grupo de archivos. En este caso, se dice que los datos están alineados....

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 ordenado (CLUSTERED), los índices no ordenados 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 propuesto 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...