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
2. El concepto de transacción
a. ¿Qué es una transacción?
Una transacción es un conjunto indivisible de sentencias Transact SQL. Una transacción se ejecuta de manera completa y no se admite la ejecución de una sentencia individual de manera aislada. El motor SQL debe ser capaz, mientras la transacción no ha terminado, de restaurar los datos al estado inicial. Durante la ejecución de la transacción, se colocan bloqueos en los datos para evitar que se produzcan conflictos de acceso con otros usuarios. SQL Server puede administrar automáticamente estos bloqueos. Sin embargo, a menudo será necesario recuperar el control para gestionarlos, bien con el SET TRANSACTION ISOLATION LEVEL o con las tablas de indicadores en las consultas.
El siguiente ejemplo establecerá bloqueos de edición en lugar de bloqueos de lectura (bloqueos predeterminados).
select * from dbo.CLIENTES
with (updlock)
Ejemplo de transacción: un ejemplo seguro conocido y representativo del concepto de transacción es el de la retirada...
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.
Una instancia de SQL Server no puede contener más de 32.767 bases de datos.
1. Crear una base de datos
La creación de una base de datos es una etapa puntual, realizada por un administrador de SQL Server. Antes de intentar crear una base de datos, es importante definir cierto número de elementos de manera precisa:
-
El nombre de la base de datos, que debe ser único en el servidor SQL.
-
El tamaño de la base de datos.
-
Los archivos utilizados para el almacenamiento de los datos.
Para crear una nueva base de datos, SQL Server se basa en la base Model, que contiene todos los elementos que van a ser definidos en las bases de datos de usuario. Por defecto, esta base Model contiene las tablas de sistema. Sin embargo, es perfectamente posible añadir elementos a esta base. Todas las bases de usuario creadas posteriormente dispondrán de estos elementos adicionales.
Una base se puede crear de dos maneras diferentes:
-
Por medio de la instrucción Transact SQL CREATE DATABASE.
-
Por medio de SQL Server Management Studio.
Una base de datos siempre está compuesta, como mínimo, de un archivo de datos principal (extensión mdf) y de un archivo diario (extensión ldf). Se pueden definir archivos de datos secundarios (ndf) en el momento de la creación de la base o bien posteriormente.
La información relativa a los archivos de datos y los archivos de registro se guardan en la base Master.
a. Sintaxis Transact SQL
CREATE DATABASE nombreBaseDeDatos
[ ON
[PRIMARY] [ <especificaciónArchivo> [,n]]
[LOG ON < especificaciónArchivo > [,n]]
]
[ COLLATE intercalación ]
[;]
Para especificaciónArchivo existen los siguientes elementos de sintaxis:
(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.
1. Creación de un grupo de archivos
Antes de poder utilizar los grupos de archivos, es necesario crearlos. En el momento de la creación de la base, se crea el grupo de archivos...
Instrucciones INSERT, SELECT... INTO
La instrucción SELECT INTO permite insertar los datos resultantes de un comando SELECT en una tabla, que se creará también por la propia consulta SELECT.
La tabla creada no dispone de ninguna restricción de integridad.
select nombre,apellidos
into cli
from dbo.CLIENTES;
El comando INSERT también es capaz de insertar el resultado de un comando SELECT en una tabla ya creada anteriormente mediante la instrucción SQL DDL CREATE TABLE.
En esta ocasión, es perfectamente posible incluir la definición de restricciones de integridad en el momento de la creación de la tabla.
insert into cli(nombre,apellido)
select nombre,apellido
from dbo.clientes;
Estructura de los índices
SQL Server ofrece dos tipos de índices:
-
Los índices ordenados o clúster.
-
Los índices no ordenados o no clúster.
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];
2. Los índices no ordenados
El otro tipo de índices...
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.
La compresión solo es posible para los datos de usuario. Las tablas de sistema no se pueden comprimir.
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:
-
Dos columnas de tipo datetime2. Estas dos columnas corresponden a las fechas y horas de inicio y final de validez de los datos.
-
Una tabla espejo (en términos de estructura) a la tabla que contiene los datos activos para conservar el histórico. A esta tabla se la conoce a menudo con el nombre de "tabla de histórico". Puede ser creada automáticamente por SQL Server o por el administrador.
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
1. Dimensionar los archivos
Si se desea evaluar el tamaño de los archivos necesarios para almacenar la información contenida en la base de datos, es necesario tener en cuenta numerosos criterios.
Para los archivos de datos
-
Distinguir las tablas de sistema y de usuario.
-
Tener en cuenta el número de líneas de las tablas.
-
Identificar los valores indexados (clave, número de línea, factor de llenado).
Solo después de una detallada evaluación de la cantidad del espacio ocupado, es posible fijar el tamaño inicial de los archivos de datos. El método más simple consiste en evaluar la longitud media de una línea, calcular cuántas líneas pueden almacenarse en un bloque de 8 KB y por último encontrar el número de bloques necesarios para almacenar todas las líneas de la tabla. A partir de este número de bloques utilizados por la tabla, conviene tomar el múltiplo de 8 inmediatamente superior y a continuación dividir por 8 para obtener el número de extensiones.
Para los archivos diarios
-
La actividad.
-
La frecuencia.
-
El tamaño de las transacciones.
-
Las copias de seguridad.
Si tenemos en cuenta estos criterios y consultamos las opciones de la base de datos, podremos fijar un tamaño óptimo para el archivo diario. Inicialmente, puede ser útil fijar el tamaño del diario entre el 10 % y el 25 %...
Ejercicio: crear una base de datos
1. Enunciado
La primera etapa consiste por lo tanto en crear la base de datos LibroTSQL y, como su nombre indica, se creará con la ayuda de un script Transact SQL. Crearemos una segunda base de datos, llamada LibroSSMS, desde la interfaz gráfica del Management Studio.
Los archivos de datos se definirán en un directorio específico. Conviene crear el directorio c:\datos. Evidentemente, guardar los datos en el disco c:\ es producto del ejemplo pedagógico.
Parámetros de la base de datos LibroTSQL
Archivo de datos
-
Tamaño: 10 MB
-
Nombre físico: c:\datos\LibroTSQL.mdf
-
Nombre lógico: LibroTSQL
Archivo de log
-
Tamaño: 8 MB
-
Nombre físico: c:\datos\LibroTSQL_log.ldf
-
Nombre lógico: LibroTSQL_log
Parámetros de la base de datos LibroSSMS
Archivo de datos
-
Tamaño: 15 MB
-
Nombre físico: c:\datos\LibroSSMS.mdf
-
Nombre lógico: LibroSSMS
Archivo de log
-
Tamaño: 8 MB
-
Nombre físico: c:\datos\LibroSSMS_log.ldf
-
Nombre lógico: LibroSSMS_log
2. Solución
Creamos la base de datos LibroTSQL con ayuda del siguiente script:
CREATE DATABASE LibroTSQL
ON PRIMARY (
NAME=LibroTSQL,
FILENAME='c:\datos\LibroTSQL.mdf',
SIZE=10Mb...
Ejercicio: añadir un grupo de archivos
1. Enunciado
En la base de datos LibroTSQL, añadimos el grupo de archivos Data. Este grupo de archivos se compone de dos archivos: data1.ndf y data2.ndf.
El archivo data1.ndf posee un tamaño fijo de 50 MB mientras que el archivo data2.ndf tiene un tamaño inicial de 10 MB que puede después crecer hasta un tamaño de 50 MB en pasos de 10 MB.
2. Solución
Para añadir un grupo de archivos y definir los archivos pertenecientes a este grupo, puede hacerlo a través del cuadro de diálogo que presenta las propiedades de la base de datos desde SSMS, o también a través de un script Transact SQL. A continuación presentamos este script.
En un primer paso, el grupo de archivos se define con la instrucción ALTER DATABASE.
ALTER DATABASE LibroTSQL
ADD FILEGROUP Data;
El grupo de archivos está definido pero todavía no es posible utilizarlo ya que ningún archivo pertenece a este grupo. El primer archivo (data1) se crea con la ayuda del siguiente script.
ALTER DATABASE LibroTSQL
ADD FILE (
NAME=data1,
FILENAME='c:\datos\data1.ndf',
SIZE=50Mb,
MAXSIZE=50Mb...