Generalidades de bases de datos SQL Server

Qué es una base de datos en SQL Server

Una base de datos, como ya se ha  visto en entradas  que trataban aspectos generales de arquitectura y diseño, no es más que una colección organizada y estructurada de datos. Esta definición, no expresa la idea de una base de datos en SQL Server. 

Bloques de colores.

SQL Server extiende el concepto teórico de una base de datos convirtiéndolo en una entidad lógica en la cual es posible crear y almacenar otros tipos de objetos, como procedimientos almacenados, triggers, vistas y otros elementos que interaccionan con los datos. Y son sólo eso, sino que además, no puede existir ningún objeto en SQL Server que no esté  dentro de la base de datos. 

Con el Administrador Corporativo resulta fácil gestionar las bases de datos pues permite prácticamente todas las acciones de creación, eliminación, modificación y mantenimiento desde esta herramienta visual., También es posible utilizar las sentencias SQL de modificación de bases de datos. 

Donde ubicar una base de datos

Una base de datos en SQL Server debe estar asociada al menos a un dispositivo de base de datos. Cuando se crea una base de datos se le otorga un espacio determinado de almacenamiento, en uno o varios dispositivos. Esta asociación hace que la base de datos pierda su abstracción en la cual esta se independizaba del almacenamiento físico.

Sabemos la ubicación física de la base de datos, esto es una información importante que puede reflejarse en el rendimiento de las operaciones que con los datos almacenados puedan llevarse a cabo. 

Relación y control de transacciones

Cada base de datos tiene asociada una relación de transacciones o transaction log. Esta relación de transacciones, (transaction .log) es  una zona de almacenamiento en la que SQL Server mantiene una lista de las transacciones que se han llevado a cabo sobre la base de datos.

SQL Server anota en el transaction log todas y cada una de las operaciones que se lleva a cabo sobre la base de datos antes de ejecutarlas en la misma. Este modo de operación permite manejar y mantener la integridad de la base de datos frente a imprevistos como errores o cortes de suministro eléctrico. 

Control de transacciones

Permite ejecutar por lotes las operaciones, es decir las instrucciones a ejecutar se escriben entre dos instrucciones, la primera es begin transaction y la última commit transaction. Esto hace que primero se escriban las instrucciones en el  transaction log pero no se ejecutan sobre la base de datos hasta que damos la orden ejecutando la última instrucción commit transaction al final. Esto permite controlar las transacciones por programa o manualmente de tal modo que si algo sale mal en mitad del proceso podemos ejecutar rollback transaction  y esta operación deshace todas las instrucciones ejecutadas después del begin transaction. 

Relación de transacciones

Por otro lado, el transaction log almacena un historial de todas las operaciones efectuadas sobre la base de datos, de tal forma que si se detecta un problema se puede consultar que operaciones se realizaron sobre la base de datos y revertirlas si fuera necesario. Como inconveniente, si se realizan muchas operaciones sobre la base de datos el transaction log puede hacerse demasiado grande y ocupar mucho espacio en disco. Pero si verificamos que la base de datos permanece íntegra, siempre podemos vaciar el transaction log para liberar espacio en disco 

La base de datos temporal tempdb

Esta base de datos es utilizado por SQL Server para volcar datos de manera temporal cuando le resulte necesario. Normalmente lo hace cuando se llevan a cabo consultas que contienen combinaciones (Joins), aunque también es posible que un usuario solicite la creación de un objeto temporal cualquiera. 

En principio, y salvo situaciones especiales, es conveniente destinar a la base de datos temporal un 25 % del tamaño de la base de datos más grande que tengamos en nuestro sistema. 

Aspectos básicos acerca de la creación de bases de datos 

El proceso de creación de una base de datos es una tarea a la que debe prestarse cierta atención, pues determinará en buena medida la respuesta de la misma a nuestras necesidades de almacenamiento. 

Dónde se almacenan las bases de datos

Ya comentamos con detalle los aspectos relativos al almacenamiento físico de las bases de datos en SQL Server 

Vamos, a recordar algunos conceptos básicos relativos a la ubicación de bases de datos en ficheros. 

Ficheros y bases de datos 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 uno de estos ficheros a más de una base de datos. Cada fichero de datos está unívocamente asociado a una base de datos.

Cada base de datos tiene asociado, como mínimo, dos ficheros, uno para los datos y otro para el transaction log. Los ficheros de datos se almacenan en archivos del sistema operativo.

Existen tres tipos de ficheros asociados a una base de datos:

Fichero primario (primary data file): En este fichero se almacenan las tablas de sistema. Existe un único fichero  para cada base de datos.

Ficheros secundarios (secondary data files): Pueden existir tantos como se quiera. Son opcionales y permiten ubicar tablas e índices en lugares específicos. Permiten el crecimiento de la base de datos hacia nuevos dispositivos físicos de almacenamiento cuando la base de datos ya no se dispone de espacio en los ficheros de datos existentes.

Ficheros de log: Almacenan el transaction log. Cada base de datos debe tener al menos uno de ellos. 

La extensión de los archivos primarios de base de datos es .mdf,  los archivos secundarios tienen extensión .ndf, los que almacenan el transaction log tienen extensión .ldf.

Grupos de ficheros

Los ficheros de datos pertenecientes a una base de datos pueden integrarse en grupos de ficheros. Los grupos de ficheros tienen una función lógica, con el objetivo de facilitar la administración y una realización eficiente de ciertas tareas, como la copia de seguridad. También permiten mejoras de rendimiento, ya que es posible ubicar explícitamente tablas y otros objetos en grupos de ficheros.

Los ficheros de un grupo forman una unidad desde el punto de vista del crecimiento de los mismos. Cuando uno de los ficheros de un grupo se llena, SQL Server no le hará crecer a no ser que no quede espacio libre en ninguno de los ficheros 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. 

El almacenamiento de datos se lleva a cabo en SQL Server según una estrategia  basada en grupos de ficheros, no en ficheros individuales. Cuando se añade información a un objeto de una base de datos, el gestor la reparte entre todos los ficheros pertenecientes al grupo al que el objeto esté asignado, en orden de pertenencia y en porciones proporcionales al espacio libre restante en cada fichero. Esta estrategia permite que los ficheros del grupo vayan llenándose de forma paralela. 

No es necesario tener en cuenta esta disposición física de grupos de ficheros en nuestras sentencias de creación de bases de datos, aun así, SQL Server  considerará un grupo de ficheros por defecto, en el que incluirá todos los ficheros   asociados a la base de datos por defecto. Excepto los ficheros del transaction log que no pertenecen a ningún grupo de ficheros.

Los ficheros y grupos de ficheros facilitan a los administradores la tarea de optimizar el rendimiento de la base de datos. Los objetos pueden ubicarse explícitamente en ficheros o grupos de ficheros concretos, que su vez pueden colocarse en dispositivos físicos (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.

Tamaño de una base de datos

Podemos decir que el tamaño de una base de datos es la suma del espacio destinado al almacenamiento de datos más el tamaño del transaction log. El almacenamiento de datos, por su parte, incluirá los datos del usuario más las tablas de sistema. En el momento de la creación de la base de datos podemos  especificar el tamaño de la misma, aunque si no lo hacemos, esta tomará un valor por defecto y luego podrá ir aumentando o disminuyendo automáticamente o por acción  del administrador. 

Para permitir que una base de datos y sus ficheros puedan reducir su tamaño automáticamente, es necesario configurarla durante la creación de la misma.

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, si es así, los reducirá.

El gestor también se encarga de hacer crecer los archivos asociados a una base de datos cuando sus necesidades así lo requieran. 

Los ficheros crecerán hasta alcanzar el tamaño máximo indicado al crear la base de datos. Si no se indica ningún valor máximo, los ficheros crecerán mientras exista espacio libre en el disco. 

Documentación de la creación

Es una buena práctica documentar adecuadamente la secuencia de creación. Pues esta información será útil cuando se necesite reconstruir la base de o, simplemente, si se desea repetir dicho proceso en otros servidores. Esto puede llevarse a cabo, si se realiza la creación mediante scripts de SQL, creando y almacenando en un fichero las sentencias que se utilicen, y ejecutándolas de manera automática en su momento. 

Es necesario recopilar cierta información antes de crear la Base de datos. Por tanto es conveniente confeccionar una lista de las informaciones necesarias, que incluirán algunas de las que comentamos en los apartados siguientes.

Permisos de creación de bases de datos

Sólo el Administrador de sistema posee permiso para crear una base de datos, si bien puede otorgar permisos a otros usuarios para hacerlo. Del mismo modo él es el único que puede alterar las propiedades de las mismas. Para que un usuario pueda crear bases de datos basta con que tenga permisos necesarios para ello. 

Otorgando a otros usuarios permiso de creación

Para que un usuario tenga permisos de creación de Bases de Datos la manera más sencilla de hacerlo es ejecutar el comando GRANT. La sintaxis para otorgar este permiso es la siguiente: GRANT CREATE DATABASE TO nombre usuario 

Propiedad de la base de datos

Una base de datos es propiedad del usuario que la crea, es decir, del que ha ejecutado, directamente o a través del Administrador Corporativo el comando CREATE DATABASE. Es una práctica muy común transferir a otros usuarios los permisos de "base de datos”, sobre todo porque si cumplimos la norma de que la creación de base de datos es potestad exclusiva del administrador, tan sólo este podría poseer bases de datos. Para realizar  esta transferencia de propiedad del objeto, que permitirá propietario añadir usuarios a la base de datos, y, en general administrarla, deberá ejecutarse el procedimiento almacenado sp_changedbowner. 

sp_changedbowner  nombre nuevo propietario