Funciones de las bases de datos del sistema?

MASTER, MSDB, MODEL, TEMPDB, DISTRIBUTION y MSSQLSYSTEMRESOURCE. ¿Qué función tiene cada una de las bases de datos del sistema? ¿Para qué sirve MASTER? ¿Para qué sirve TEMPDB? ¿Para qué sirve MODEL? ¿Para qué sirve MSSQLSYSTEMRESOURCE? Este capítulo explica cuál es la función de cada una de las base de datos del sistema, algo de vital importancia para conocer SQL Server, su funcionamiento, consideraciones cara al diseño de planes de contingencia (backup y restores) y optimización de rendimiento (tunning) de base de datos, etc.

SQL Server incluye varias bases de datos del sistema (MASTER, MSDB, MODEL, TEMPDB, DISTRIBUTION, MSSQLSYSTEMRESOURCE), cada con unas finalidades específicas, que a continuación se indican: MASTER. ¿Para que sirve MASTER? Almacena información de configuración de la instancia de SQL Server, como puede ser la definición de los inicios de sesión (Logins), de las bases de datos, de los errores del sistema, etc. Hay que tener en cuenta, que no toda la configuración de la instancia se almacena en MASTER, pues por ejemplo, el modo de autenticación (Windows o Mixto) se almacena en el registro. También contiene los procedimientos almacenados extendidos, y muchos otros procedimientos almacendos del sistema sólo disponibles en MASTER. MSDB. ¿Para que sirve MSDB? Principalmente tiene la función de dar soporte al Agente de SQL Server, de tal modo que almacena la definición y planificación de JOBs, Planes de Mantenimiento, etc. (incluyendo su historial de ejecución), así como almacena la definición de otros objetos como Operadores, Alertas, etc. Esto implica, que el Agente de SQL Server, que cómo sabemos es un servicio de Windows, se conectará a esta base de datos, y por ello será necesario parar el Agente de SQL Server si deseamos hacer un RESTORE de la misma, pues necesitaremos exclusividad. Sin embargo, MSDB también se utiliza cuando el Agente de SQL Server no está presente, como es el caso de SQL Express (que no incluye éste servicio) o en los casos en que no se utiliza (ej: el Agente de SQL Server está parado). Por poner ejemplos, siempre que se realiza una copia de seguridad (BACKUP DATABASE o BACKUP LOG) o una restauración (RESTORE DATABASE o RESTORE LOG), se almacena en MSDB (tablas backupfile, backupfilegroup, backupmediafamily, backupmediaset, backupset, restorefile, restorefilegroup, restorehistory). Por último, también permite servir de ubicación de almacenamiento de paquetes DTS (SQL Server 2000) y DTSX (SQL Server 2005 - SSIS). MODEL. ¿Para que sirve MODEL? Esta base de datos sirve de modelo. Siempre que se crea una nueva base de datos (CREATE DATABASE), se realiza una copia de la base de datos MODEL, heredando de ésta su configuración y contenido, salvo que se especifique lo contrario. Por ejemplo, es posible establecer el Modo de Recuperación o Modo de Registro de MODEL en SIMPLE (o sencillo), con el fin de evitar que al crear una base de datos por defecto se utilice el Modo de Recuperación FULL (completo). TEMPDB. ¿Para que sirve TEMPDB? Almacena tanto los objetos temporales (tablas temporales, procedimientos almacenados temporales, etc.), como los resultados intermedios que pueda necesitar crear el motor de base de datos, por ejemplo durante la ejecución de consultas que utilizan las cláusulas GROUP BY, ORDER BY, DISTINCT, etc. Además, TEMPDB se crea de nuevo siempre que se inicia la instancia SQL Server, tomando su tamaño por defecto. Dado que según necesite más espacio, TEMPDB crecerá hasta el tamaño que necesite, y dado que el crecimiento de un fichero implica esperas debidas a la entrada/salida, es muy importante en entornos críticos dimensionar correctamente TEMPDB para que se cree con un tamaño apropiado, y evitar dichas esperas de entrada/salida en tiempo de ejecución, justo cuando estamos ofreciendo servicio a los usuarios. Evidentemente, estamos desplazando dichas esperas al momento de inicio de la instancia, pero también conseguimos una ventaja adicional al crear de una vez TEMPDB: limitamos la fragmentación (también muy importante en entornos críticos). A partir de SQL Server 2005, también se utiliza al habilitar el modo de aislamiento SNAPSHOT, así como al crear o reconstruir índices con la opción SORT_IN_TEMPDB. DISTRIBUTION. ¿Para que sirve DISTRIBUTION? No existe por defecto. Se crea al habilitar una instancia de SQL Server como Distribuidor en un entorno de Replicación. Durante el proceso de configuración del Distribuidor, se puede elegir el nombre de ésta base de datos, por lo que no resulta estrictamente necesario que se llame DISTRIBUTION. Almacena información como la definición de las Publicaciones, la definición de los Agentes de Instantánea, etc. MSSQLSYSTEMRESOURCE. ¿Para que sirve MSSQLSYSTEMRESOURCE? Esta base de datos es nueva en SQL Server 2005. Aparentemente está oculta (no podremos verla desde SQL Server Management Studio). Es de sólo lectura y no contiene datos, sino por el contrario, contiene el código de todos los objetos del sistema de SQL Server 2005. No es accesible directamente, puesto que es necesario establecer el modo de usuario único (single_user) para poder acceder a esta base de datos (USE MSSQLSYSTEMRESOURCE). En cualquier caso, jamás se debe acceder a esta base de datos. Una de las principales diferencias entre SQL Server 2000 y SQL Server 2005, está en que en SQL Server 2005 ya no es posible actualizar directamente las tablas del catálogo del sistema, es decir, no funciona la opción 'allow update' de sp_configure. Todas las manipulaciones de éste tipo, se deben de realizar a traves de los procedimientos almacenados del sistema o de las propias sentencias del motor de base de datos (CREATE, ALTER, DROP, etc.).