Introducción
Continuando con las propiedades de las bases de datos a través de Microsoft SQL Server, en esta ocasión hablare de los archivos y grupos de archivos, que se presentaran en las paginas correspondientes. Antes de ver el contenido de estas, es conveniente llevar a cabo una recapitulación de estos conceptos. Si bien, en la ventana de propiedades se presentan en dos paginas, estas propiedades están relacionadas y por lo mismo es importante hablar de ello en una sola entrega.
Archivos
Cuando una base de datos es creada, se generan de forma automática dos archivos de sistema operativo:
Archivo de datos – como su nombre indica contiene los datos y objetos (tablas, indices, vistas, procedimientos almacenados y otros)
Archivo de registro - contiene la información necesaria para recuperar todas las transacciones en la base de datos.
Es preciso indicar que los archivos de datos se pueden agrupar en grupos de archivos con fines de asignación y administración.
Antes de hablar de los grupos de archivo, es importante indicar que dentro de los tipos de archivo que podemos encontrar están, además de los mencionados archivos de datos y archivos de registro de transacciones, los archivos dispersos (sparse) y Filestream.
Es necesario indicar que los archivos de datos dentro de Microsoft SQL Server se pueden encontrar dos tipos:
Primario - Contiene información de inicio para la base de datos y apunta a los otros archivos de la base de datos. Solo se puede tener un archivo de datos principal. La extensión de nombre de archivo, dentro del sistema operativo, para estos archivos es .mdf, este es asignado por omisión.
Secundario – Estos son opcionales y definidos por el usuario. Son utilizados cuando los datos se pueden distribuir en varios discos colocando cada archivo en una unidad de disco diferente. La extensión de nombre de archivo, dentro del sistema operativo, para estos archivos .ndf.
El archivo de registro de transacciones contiene la información que se utiliza para recuperar la base de datos. Ya se ha indicado que debe haber al menos un archivo de registro para cada base de datos. La extensión de nombre de archivo, dentro del sistema operativo, para los registros de transacciones es .ldf.
Cuando se realiza una instantánea de base de datos se utiliza un archivo disperso que es un archivo esencialmente vacío que no contiene datos del usuario y aún no se le ha asignado espacio en disco para los datos del usuario. La forma de archivo para almacenar los datos de copia en escritura depende de si es utilizado por la instantánea creada por un usuario o si se utiliza internamente, como en el caso de la creada cuando se llevan a cabo las operaciones del comando DBCC CHECKDB.
FILESTREAM permite que las aplicaciones basadas en Microsoft SQL Server almacenen datos no estructurados, como documentos e imágenes, en el sistema de archivos.
De forma predeterminada, los registros de datos y transacciones se colocan en la misma unidad y ruta para manejar sistemas de un solo disco. Es posible que esta elección no sea óptima para entornos de producción. Le recomendamos que coloque los datos y los archivos de registro en discos separados.
Grupo de archivos
Cuando se menciona grupo de archivos se piensa en dos características:
- El grupo de archivos contiene el archivo de datos principal y los archivos secundarios que no se colocan en otros grupos de archivos.
- Se pueden crear grupos de archivos definidos por el usuario para agrupar archivos de datos con fines administrativos, de asignación de datos y de ubicación.
Cuando se crean objetos en la base de datos sin especificar a qué grupo de archivos pertenecen, se asignan al grupo de archivos predeterminado. En cualquier momento, se designa exactamente un grupo de archivos como grupo de archivos predeterminado, denominado PRIMARY. Los archivos del grupo de archivos predeterminado deben ser lo suficientemente grandes para contener cualquier objeto nuevo que no esté asignado a otros grupos de archivos.
Todos los archivos de datos se almacenan en los grupos de archivos que se enumeran en la siguiente tabla.
PRIMARY El grupo de archivos que contiene el archivo principal. Todas las tablas del sistema forman parte del grupo de archivos principal.
Datos optimizados para memoria Un grupo de archivos optimizado para memoria se basa en un grupo de archivos de flujo de archivos
FILESTREAM
Definido por el usuario Cualquier grupo de archivos que crea el usuario cuando el usuario crea por primera vez o posteriormente modifica la base de datos.
Página de archivos
Utilice esta página para crear una nueva base de datos o ver o modificar las propiedades de la base de datos seleccionada. Este tema se aplica a las propiedades de la base de datos (página de archivos) para las bases de datos existentes y a la nueva base de datos (página general).
La parte superior de esta pagina muestra la siguiente información:
Archivos de base de datos
Nombre de la base de datos - El nombre de la base de datos.
Propietario - El propietario de la base de datos. En caso de que se desee cambiar de propietario esto puede realizarse, escribiendo directamente el usuario u oprimiendo el botón de tres puntos, para que pueda ser seleccionado de la lista. Cuando se crean las bases de datos, el propietario es el que las creó de forma predeterminada. Esta propiedad otorga al creador permisos adicionales, y esto puede ser un problema en un entorno seguro bloqueado donde debemos respetar el principio de privilegio mínimo.
Utilice la indexación de texto completo - Esta casilla de verificación está marcada y deshabilitada porque la indexación de texto completo siempre está habilitada en Microsoft SQL Server 2019 (15.x).
La parte inferior de la pagina, muestra información de los archivos de base de datos, donde se puede ver, agregar, modificar o eliminar archivos para la base de datos, y una cuadricula que contiene las siguientes columnas o propiedades de los archivos:
Nombre lógico - Muestra el nombre interno del archivo, este puede ser el mismo en diferentes bases de datos, pero debe ser único entre los nombres de archivo lógico de la base de datos. Puede modificarse el nombre lógico en cualquier momento.
Tipo de archivo - Indica el tipo de archivo, puede ser Data, Log o Filestream Data. No puede modificarse en un archivo existente. Si se agrega un nuevo archivo, seleccione el tipo de archivo de la lista. Es importante seleccionar Filestream Data si está agregando archivos (contenedores) a un grupo de archivos optimizado para memoria.
Para agregar archivos (contenedores) a un grupo de archivos de datos de Filestream, el uso de FILESTREAM debe estar habilitado. Puede habilitar el uso de FILESTREAM mediante el cuadro de diálogo Propiedades del servidor (página avanzada).
Grupo de archivos - Indica el grupo de archivos al que pertenece el archivo. Cuando se crea o adiciona un nuevo archivo, se debe seleccionar el grupo de archivos para el archivo de la lista. De forma predeterminada, el grupo de archivos es el denominado PRIMARY.
Puede crearse un nuevo grupo de archivos seleccionando <nuevo grupo de archivos> e ingresando información sobre el grupo de archivos en el cuadro de diálogo Nuevo grupo de archivos.
También se puede crear un nuevo grupo de archivos en la página Grupo de archivos.
No puede modificar el grupo de archivos de un archivo existente. Al agregar archivos (contenedores) a un grupo de archivos optimizado para memoria, el campo Grupo de archivos se completará con el nombre del grupo de archivos optimizado para memoria de la base de datos.
Tamaño inicial - Se muestra el tamaño inicial del archivo en megabytes. Cuando se crea o adiciona un archivo, se mostrará, de forma predeterminada, el valor que se indica en la base de datos model.
Éste campo no es válido para archivos FILESTREAM. Para archivos en grupos de archivos optimizados para memoria, este campo no se puede modificar.
Autocrecimiento - Indica la forma en que se llevará a cabo el crecimiento automático del archivo. Se debe recordar que controla cómo se expande el archivo cuando se alcanza su tamaño máximo de archivo.
Pueden modificarse estos valores, para editar los valores de crecimiento automático, haga clic en el botón editar junto a las propiedades de crecimiento automático del archivo que desee y cambie los valores en el cuadro de diálogo Cambiar crecimiento automático. De forma predeterminada, estos son los valores de la base de datos model.
Este campo no es válido para archivos FILESTREAM. Para archivos en grupos de archivos optimizados para memoria, este campo debe ser Ilimitado.
Ruta - Muestra la ruta del archivo seleccionado. Cuando se crea o adiciona un nuevo archivo, para especificar una ruta para un nuevo archivo, haga clic en el botón editar junto a la ruta del archivo y navegue hasta la carpeta de destino. No puede modificarse la ruta de un archivo existente, utilizando esta ventana.
Para los archivos FILESTREAM, la ruta es una carpeta. El motor de base de datos de SQL Server creará los archivos subyacentes en esta carpeta.
Nombre del archivo - Muestra el nombre del archivo físico.
Este campo no es válido para archivos FILESTREAM, incluidos los archivos en grupos de archivos optimizados para memoria.
Finalmente se aprecian dos botones, para ser usados con la cuadricula anterior:
- Añadir - Agregue un nuevo archivo a la base de datos.
- Eliminar - Elimina el archivo seleccionado en la cuadricula de la base de datos. Es necesario indicar que no se puede eliminar un archivo a menos que esté vacío. El archivo de datos principal y el archivo de registro no se pueden eliminar de la base de datos.
Página Grupos de Archivos
Esta página es utilizada para ver los grupos de archivos que tiene una base de datos o para agregar un nuevo grupo de archivos a la base de datos seleccionada. Actualmente los tipos de grupos de archivos se separan en los siguientes:
• Grupos de archivos de datos - contienen datos regulares y archivos de registro
• Datos de FILESTREAM - contienen archivos de datos de FILESTREAM
• Grupos de archivos con optimización de memoria.
Los archivos de datos de FILESTREAM almacenan información sobre cómo se almacenan los datos de objetos grandes binarios (BLOB) en el sistema de archivos cuando se usa el almacenamiento FILESTREAM. Las opciones de estos grupos de archivo son las mismas que para los tipos de grupos de archivos de datos.
Recuérdese que, si FILESTREAM no está habilitado, la sección correspondiente a Filestream no estará disponible, por lo que será posible habilitar el almacenamiento de FILESTREAM utilizando Propiedades del servidor (página avanzada).
Es importante indicar que se requieren grupos de archivos optimizados para memoria para que una base de datos contenga una o más tablas optimizadas para memoria. Las tablas de datos optimizadas para memoria surgen en la versión de Microsoft SQL Server 2014, anteriormente no se usaba esta opción.
Opciones de grupo de archivos de datos y FILESTREAM
Nombre - Muestra el nombre del grupo de archivos.
Archivos - Muestra el recuento de archivos en el grupo de archivos.
Solo lectura - Indica que el grupo de archivos esta en un estado de solo lectura.
Defecto - Esta opción indica que este grupo de archivos se indicara como predeterminado. Puede tener un grupo de archivos predeterminado para las filas y un grupo de archivos predeterminado para los datos de FILESTREAM.
Opciones de grupos de archivos de datos optimizados para memoria
Nombre - Indica el nombre del grupo de archivos optimizado para memoria.
Archivos de FILESTREAM - Muestra el número de archivos (contenedores) en el grupo de archivos de datos optimizados para memoria. Puede agregar contenedores en la página Archivos.
Se muestran dos botones al final de cada cuadricula con la siguiente funcionalidad
- Añadir - Agrega una nueva fila en blanco a la cuadrícula correspondiente que enumera los grupos de archivos para la base de datos.
- Eliminar - Elimina la fila del grupo de archivos seleccionado de la cuadrícula correspondiente.
Conclusión
Como se ha visto, una base de datos, manejada por Microsoft SQL Server puede usar diversos archivos, agrupados en diversos tipos de archivo, lo que es importante recordar, es que solo puede contener un archivo primario, con la extensión .mdf y al menos un archivo de transacciones, con la extensión .ldf. El manejo de grandes objetos permitió la llegada de los archivos de FILESTREAM, y el manejo de las tablas optimizadas en memoria se hizo posible con la arquitectura de 64 bits, por lo que también estos grupos de archivos permiten el uso de este tipo de grupo de archivos de datos.
Si se requiere usar T-SQL para obtener la información que se muestra en las paginas indicadas, es posible usar las siguientes consultas.
Para la pagina de archivos y grupos de archivos se puede obtener:
-- Script : Get Database Files Properties
-- Author : Julio J Bueyes
-- julio.bueyes@outlook.com
--
-- Description : This script helps to get a detailed view of the database properties – Files page.
--
-- DISCLAIMER. This Code is provided for the purpose of illustration only and is not intended to be used in a production environment.
--
-- THIS CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED,
-- INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
**********************************************************************************/
-- Set the Database Name required
GO
-- Files Page
FROM sys.databases db
INNER JOIN sys.server_principals sp ON db.owner_sid = sp.sid
-- Get the database files information
CASE df.is_percent_growth
WHEN 1 THEN 'By ' + CAST(df.growth/1024 as varchar) + ' %, ' + CAST(df.max_size/1024 as varchar) + ' MB'
ELSE 'By ' + CAST(df.growth/1024 as varchar) + ' MB, ' + CAST(df.max_size/1024 AS varchar) + ' MB' END AS [Autogrowth / Maxsize],
df.physical_name AS [Path and Name]
FROM sys.database_files as df
LEFT OUTER JOIN sys.data_spaces as ds ON df.data_space_id = ds.data_space_id;
-- Filegroups Page
-- Get the database filegroups data information
FROM sys.data_spaces ds
INNER JOIN sys.database_files df ON ds.data_space_id = df.data_space_id
INNER JOIN sys.filegroups fg ON df.data_space_id = fg.data_space_id
WHERE ds.type = 'FG' ----<--- Indicate ROWS_FILEGROUP
GROUP BY ds.name, fg.is_read_only, ds.is_default, is_autogrow_all_files;
SELECT ds.name, COUNT(df.FILE_ID) as [FILESTREAM Files], fg.is_read_only as [Read-Only], ds.is_default as [Default]
FROM sys.data_spaces ds
INNER JOIN sys.database_files df ON ds.data_space_id = df.data_space_id
INNER JOIN sys.filegroups fg ON df.data_space_id = fg.data_space_id
WHERE ds.type = 'FD' ----<--- Indicate FILESTREAM
GROUP BY ds.name, fg.is_read_only, ds.is_default;
SELECT ds.name, COUNT(df.FILE_ID) as [FILESTREAM Files]
FROM sys.data_spaces ds
INNER JOIN sys.database_files df ON ds.data_space_id = df.data_space_id
WHERE ds.type = 'FX' ----<--- Indicate MEMORY_OPTIMIZED_DATA_FILEGROUP
GROUP BY ds.name;
Como puede observarse es muy fácil obtener los datos directamente de la ventana de propiedades de la base de datos, en las páginas Archivos y Grupos de Archivos, no obstante, el script funciona para obtener los mismos valores proporcionados por las páginas.
No hay comentarios.:
Publicar un comentario