jul
18
2018

Bases de datos del sistema en SQL Server 101

El otro día antes de comenzar la última reunión de mi grupo de usuarios de SQL, alguien contó la historia del nuevo DBA que estaba bromeando sobre ejecutar “DROP DATABASE [master]” y hubo diferentes comentarios al respecto, desde caras de terror hasta risas.

Pero eso me dio la idea para esta publicación, en la que quiero hacer un breve resumen de las cosas que podemos hacer con las bases de datos del sistema o por el contrario, no estamos autorizados.

 
Bases de datos del sistema

Lo primero es lo primero, entonces vamos a ver cuáles son las bases de datos del sistema y cuál es su funcionalidad.

Cada instalación de SQL Server viene con algunas bases de datos instaladas por defecto. Estas se pueden encontrar en SQL Server Management Studio en el Explorador de Objetos listado bajo la carpeta “System Databases”

 
Aquí podemos ver cuatro bases de datos, [maser], [model], [msdb] y [tempdb].

Cada uno de ellas cumple una función en el motor de la base de datos, que
explicaré en un minuto.

Hay otra base de datos que es incluso más especial, ya que ni siquiera se puede encontrar aquí y no se puede acceder de la misma manera que las demás, que es la base de datos de recursos (mssqlsystemresource)

 
Para qué sirven las bases de datos del sistema

Como ya he dicho, cada base de datos del sistema tiene un propósito diferente, podemos ver la referencia completa en Libros en línea, pero si te sientes perezoso, para eso estoy yo aquí

  • master, registra información del nivel del sistema para una instancia de SQL Server, que incluye cuentas de inicio de sesión, puntos finales, servidores vinculados y configuraciones de configuración del sistema
  • model, se utiliza como plantilla para todas las bases de datos creadas en la instancia de SQL Server. Las modificaciones realizadas en la base de datos modelo, como el tamaño de la base de datos, la intercalación, el modelo de recuperación y otras opciones de bases de datos, se aplican a las bases de datos creadas posteriormente
  • msdb, utilizado por el Agente de SQL Server para programar alertas y trabajos
  • tempdb, es un espacio de trabajo para almacenar objetos temporales o conjuntos de resultados intermedios
  • mssqlsystemresource, es una base de datos de solo lectura que contiene objetos del sistema que se incluyen con SQL Server. Los objetos del sistema se mantienen físicamente en la base de datos de Recursos, pero aparecen lógicamente en el esquema sys de cada base de datos.

 
Esta es una descripción de muy alto nivel de para qué sirven las bases de datos del sistema, así que recomiendo leer más en Books online

Pero el propósito de esta publicación es enumerar lo que se puede o no se puede hacer con estas bases de datos del sistema y responder rápidamente a la pregunta que se hizo, no, no se puede eliminar master … por suerte 🙂

La mayoría de las bases de datos tienen un patrón similar, por lo que cualquier cosa que pueda comprometer todo el sistema, devolverán un error.

He encontrado que [msdb] parece ser más flexible y te permite hacer cosas que podrían ser realmente estúpidas, como configurarla en solo lectura (READ_ONLY). Esto evitará que el Agente SQL se inicie y, si se está ejecutando, crear nuevos trabajos o incluso ejecutarlos.

También podemos agregar grupos de archivos y configurarlos para que sean los DEFAULT, no estoy seguro de por qué alguien querría hacer eso, ya que no deberíamos crear tablas de usuario en las bases de datos del sistema de todos modos.

[tempdb], por ejemplo, no se puede hacer una copia de seguridad ni restaurar, lo que tiene sentido junto con cambiar el modelo de recuperación o ejecutar ciertas comprobaciones de coherencia. Es bastante gracioso que puedas ejecutar CHECKDB pero no puedes ejecutar algunos de los comandos que se ejecutan dentro de él, CHECKALLOC y CHECKCATALOG.

La lista completa de cosas que no podemos hacer es la siguiente.

 

tempdb master Model msdb
Cambiar intercalaciones. La intercalación predeterminada es la intercalación de servidor.
Eliminar la base de datos.
Eliminar el usuario guest de la base de datos.
Habilitar el mecanismo de captura de cambios en los datos.
Participar en el reflejo de la base de datos (mirroring).
Quitar el grupo de archivos principal, el archivo de datos principal o el archivo de registro.
Cambiar el nombre de la base de datos o del grupo de archivos principal.
Establecer la base de datos en OFFLINE.
Establecer la base de datos o el grupo de archivos principal en READ_ONLY.
Agregar archivos o grupos de archivos. X
Cambiar el propietario de la base de datos. Por defecto sa X
Realizar una copia de seguridad o restaurar la base de datos. Crear un catálogo de texto completo o un índice de texto completo. Crear procedimientos, vistas, o desencadenadores utilizando la opción WITH ENCRYPTION. La clave de cifrado está asociada a la base de datos en la que se crea el objeto. Los objetos cifrados creados en la base de datos modelo solo se pueden usar en model. X
Crear una instantánea de base de datos. Crear desencadenadores en las tablas del sistema de la base de datos. X X
Ejecutar DBCC CHECKALLOC. X X X
Ejecutar DBCC CHECKCATALOG. X X X

 
Además, cada base de datos viene con configuraciones predeterminadas que se pueden cambiar o no, según la base de datos en que nos encontremos.

Aquí encuentro más flexibilidad en [model], porque es la plantilla para cada base de datos que se cree sin especificar ninguna configuración, por lo que nos permite personalizar nuevas bases de datos. Pero para ser sincero, creo que cada base de datos debe personalizarse de acuerdo con sus requisitos específicos, por lo que cada configuración predeterminada sería tan mala como las que se obtienen al salir de la caja.

master msdb model tempdb
Database option Valor por defecto Se puede modificar Valor por defecto Se puede modificar Valor por defecto Se puede modificar Valor por defecto Se puede modificar
ALLOW_SNAPSHOT_ISOLATION ON No ON No OFF Si OFF Si
ANSI_NULL_DEFAULT OFF Si OFF Si OFF Si OFF Si
ANSI_NULLS OFF Si OFF Si OFF Si OFF Si
ANSI_PADDING OFF Si OFF Si OFF Si OFF Si
ANSI_WARNINGS OFF Si OFF Si OFF Si OFF Si
ARITHABORT OFF Si OFF Si OFF Si OFF Si
AUTO_CLOSE OFF No OFF Si OFF Si OFF No
AUTO_CREATE_STATISTICS ON Si ON Si ON Si ON Si
AUTO_SHRINK OFF No OFF Si OFF Si OFF No
AUTO_UPDATE_STATISTICS ON Si ON Si ON Si ON Si
AUTO_UPDATE_STATISTICS_ASYNC OFF Si OFF Si OFF Si OFF Si
CHANGE_TRACKING OFF No OFF No OFF No OFF No
CONCAT_NULL_YIELDS_NULL OFF Si OFF Si OFF Si OFF Si
CURSOR_CLOSE_ON_COMMIT OFF Si OFF Si OFF Si OFF Si
CURSOR_DEFAULT GLOBAL Si GLOBAL Si GLOBAL Si GLOBAL Si
Database Availability Options ONLINE No ONLINE No ONLINE No ONLINE No
  MULTI_USER No MULTI_USER Si MULTI_USER Si MULTI_USER No
  READ_WRITE No READ_WRITE Si READ_WRITE Si READ_WRITE No
DATE_CORRELATION_OPTIMIZATION OFF Si OFF Si OFF Si OFF Si
DB_CHAINING ON No ON Si OFF No ON No
ENCRYPTION OFF No OFF No OFF No OFF No
MIXED_PAGE_ALLOCATION ON No ON No ON No OFF No
NUMERIC_ROUNDABORT OFF Si OFF Si OFF Si OFF Si
PAGE_VERIFY CHECKSUM Si CHECKSUM Si CHECKSUM Si CHECKSUM for new installations of SQL Server./NONE for upgrades of SQL Server. Si
PARAMETERIZATION SIMPLE Si SIMPLE Si SIMPLE Si SIMPLE Si
QUOTED_IDENTIFIER OFF Si OFF Si OFF Si OFF Si
READ_COMMITTED_SNAPSHOT OFF No OFF No OFF Si OFF No
RECOVERY SIMPLE Si SIMPLE Si Depends on SQL Server edition* Si SIMPLE No
RECURSIVE_TRIGGERS OFF Si OFF Si OFF Si OFF Si
Service Broker Options DISABLE_BROKER No ENABLE_BROKER Si DISABLE_BROKER No ENABLE_BROKER Si
TRUSTWORTHY OFF Si ON Si OFF No OFF No

 
Copias de seguridad y restauración de bases de datos del sistema

No quiero terminar esta publicación sin tener una mención especial al proceso de copia de seguridad y restauración.

La copia de seguridad de las bases de datos del sistema (excepto [tempdb] y Resource) es como cualquier otra base de datos y se recomienda encarecidamente.

Para restaurar las bases de datos del sistema tenemos en un lado [model] y [msdb] que pueden restaurarse como cualquier otra base de datos de usuario porque no hay requisitos especiales, [tempdb] no se puede copiar o restaurar, por lo que solo nos queda, [master] y [Resource].

Para restaurar [master], necesitamos iniciar SQL Server en modo de usuario único antes de intentarlo, el proceso se explica enBooks Online así que no voy a dedicar tiempo en ello, pero recomiendo intentarlo en un entorno que no sea de producción y practicar por si algún día lo necesiteis.

Y finalmente, la base de datos de [Resource] que, al no ser accesible como las demás, no se puede restaurar como los demás. Se puede realizar una copia de seguridad de esta base de datos y restaurarla como cualquier archivo en Windows, por lo que debemos detener SQL Server para que poder reemplazarla con la copia del archivo tomada anteriormente; de lo contrario, obtendremos un error.

 
Conclusión

Las bases de datos del sistema son vitales para el correcto funcionamiento de cada instancia de SQL Server y cada una de ellas cumple una función.

La recomendación es que llevemos un mantenimiento como cualquier otra base de datos de usuario en términos de realización de copias de seguridad, comprobaciones de consistencia, etc., y no recomendaría crear tablas de usuario o procedimientos (o funciones) allí. Sé que muchos scripts de la comunidad usan por defecto [master] pero, en su lugar, deberíamos tener una base de datos DBA dedicada para el mantenimiento del servidor y las bases de datos.

Espero que esto os haya parecido interesante y, si tenéis alguna pregunta, utilizad la caja de comentarios.

¡Gracias!
 

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *