En este post quiero dar una breve explicacion de que son las bases de datos del sistema y de que podemos o no hacer con ellas 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!