La primavera ya está aquí y probablemente te entren ganas de hacer una limpia en tus bases de datos, y eso está muy bien.
En publicaciones anteriores, he explicado cómo tener grupos de archivos dedicados para datos de usuarios puede mejorar nuestro RTO, en caso de desastre, recuperando primero los datos críticos y luego el resto.
El problema es que cuando manejamos bases de datos que no fueron creadas de esta manera, necesitamos mover los datos de un grupo de archivos a otro antes de poder aplicar este tipo de técnicas.
Aquí es donde esta publicación puede mostrar uno de los problemas que se pueden encontrar durante este proceso.
Antecedentes
De acuerdo con Books Online
Puede usar la cláusula DROP_EXISTING para reconstruir el índice, agregar o quitar columnas, modificar opciones, modificar el orden de clasificación de las columnas o cambiar el esquema de partición o el grupo de archivos
Pero supongamos que tiene bases de datos antiguas que se crearon en su día sin planificar el crecimiento futuro con solo un grupo de archivos y un archivo de datos y que ahora le está causando algunos problemas en forma de disputa o un tiempo de recuperación muy largo.
También digamos que algunas de sus tablas usan tipos de datos LOB como MAX o incluso los BLOBS antiguos (TEXT, NTEXT, IMAGE).
Para imaginar esto mejor, vamos a crear una base de datos simple de la vieja escuela y una tabla
USE master CREATE DATABASE LOB_allocations GO USE LOB_allocations GO -- Create table with NVARCHAR(MAX) column (LOB) CREATE TABLE dbo.blobs ( Id INT IDENTITY NOT NULL CONSTRAINT PK_blobs PRIMARY KEY , Column1 VARCHAR(50) DEFAULT 'a' , blob_column NVARCHAR(MAX) DEFAULT REPLICATE(CONVERT(NVARCHAR(MAX), N'b'), 10000) ) GO -- populate the table INSERT INTO dbo.blobs ( Column1, blob_column ) DEFAULT VALUES GO 20
Esto nos pone el escenario para comenzar, ahora dejadme mostraros como se distribuyen nuestros datos por tipo de unidad de asignación.
-- Check the different allocation units SELECT OBJECT_NAME(ix.object_id) , ix.name , FILEGROUP_NAME(au.data_space_id) AS [Filegroup_name] , au.* , p.rows FROM sys.indexes AS ix INNER JOIN sys.partitions AS p ON p.object_id = ix.object_id AND p.index_id = ix.index_id INNER JOIN sys.allocation_units AS au ON au.container_id = p.hobt_id WHERE ix.object_id IN (OBJECT_ID('dbo.blobs'))
Aquí vemos los diferentes tipos de asignación y el grupo de archivos al que pertenecen.
Y porque estamos en el modo limpieza de primavera, queremos mover nuestra table a un nuevo grupo de archivos y asi dejar PRIMARY solo para tablas de sistema. Primero hay que crear un nuevo grupo de archivos.
-- Add new filegroup and file ALTER DATABASE LOB_allocations ADD FILEGROUP [USER_DATA] GO ALTER DATABASE LOB_allocations ADD FILE (NAME='LOB_allocations_user_data' , FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQL2016\MSSQL\DATA\LOB_allocations_user_data.ndf' , SIZE= 16MB , FILEGROWTH=16MB) TO FILEGROUP [USER_DATA]
Y ahora podemos seguir las instrucciones y mover nuestra tabla de un grupo de archivos a otro si reconstruimos nuestro indice cluster con WITH DROP_EXISTING.
-- Move the clustered index to the new filegroup CREATE UNIQUE CLUSTERED INDEX PK_blobs ON dbo.blobs (Id) WITH (DROP_EXISTING = ON) ON [USER_DATA]
Ahora nuestra tabla está almacenada en un grupo de archivos diferente. Vamos a verlo
-- check again the allocation units. SELECT OBJECT_NAME(ix.object_id) , ix.name , FILEGROUP_NAME(au.data_space_id) AS [Filegroup_name] , au.* , p.rows FROM sys.indexes AS ix INNER JOIN sys.partitions AS p ON p.object_id = ix.object_id AND p.index_id = ix.index_id INNER JOIN sys.allocation_units AS au ON au.container_id = p.hobt_id WHERE ix.object_id IN (OBJECT_ID('dbo.blobs'))
Ejem, eso no mola nada… Parece que reconstruir el índice cluster no afecta a las unidaded de asignación LOB, así que para mover nuestros datos del todo, tenemos que crear una nueva tabla en el nuevo grupo de archivos y volcar los datos, con el consiguiente trastorno.
Conclusión
Obviamente es una buena idea el separar de manera lógica y física nuestros datos usando multiples grupos de archivos, pero siempre es mejor si planeamos esto con la debida antelacion. De esta forma nos ahorraremos situaciones incómodas en fases mas avanzadas.
Y una vez mas, podemos ver la importancia de tener un poco de conocimiento sobre el funcionamiento internos y cómo los datos son distribuidos por las diferentes unidades de asignación que se usan en SQL Server.
Espero que esta entrada haya side de ayuda y por favor usad los comentarios para cualquier pregunta que pueda surgir.
Gracias!