Pasos para mover tablas a otro FileGroup: Como mover tablas a otro FileGroup

JuanCastillo 30 octubre, 20173 noviembre, 2017 3 comentarios T-SQL

Como mover tablas a otro FileGroup

Como mover tablas a otro FileGroup, es algo tan importante como realizar un respaldo sin embargo, Existen empresas que por ahorrar dinero nuncan contratan a un DBA (administrador de base ded datos), luego vienen las quejas a los desarrolladores tales como

El aplicativo esta lento
El sistema vive colgado (Aqui me imagino al aplicativo con una cuerda en el cuello )
No funciona nunca( osea 24 horas?)
Otros

Pero cuando instalan el SQL Server y crean una base de datos, NEXT NEXT NEXT y asi hasta que se terminan las opciones han creado un solo archivo de base de datos

Cuando un archivo cualquiera Excel o txt es muy grande y lo intentamos abrir el tarda, porque tiene que leer mucha informacion en memoria, basado en este ejemplo si tenemos un unico archivo de base de datos nuestras consultas se veran afectadas en rendimiento para retornar nuestros datos

Para solucionar esto debemos redistribuir nuestras tablas en diferentes archivos ndf, de forma predeterminada, una base de datos tiene un solo grupo de archivos llamado PRIMARY y todas las tablas se almacenan en este grupo de archivos. Sin embargo, es posible crear otros grupos de archivos y almacenar tablas en estos otros grupos de archivos. Una de las razones más comunes para hacer esto es si tiene algunos discos de alto rendimiento y desea almacenar ciertas tablas en los discos más rápidos. Esto es especialmente útil para almacenes de datos y otras bases de datos grandes donde puede tener algunos datos a los que se accede con más frecuencia y donde los tiempos de respuesta son particularmente importantes, (DataWhereHouse, Sistemas CRM, otros)

Paso 1: identificar mis tablas y sus grupos de archivos

SELECT    tbl.name AS [Table Name],
CASE WHEN dsidx.type='FG' THEN dsidx.name ELSE '(Partitioned)' END AS [File Group]
FROM      sys.tables AS tbl
JOIN      sys.indexes AS idx
ON        idx.object_id = tbl.object_id
AND       idx.index_id <= 1
LEFT JOIN sys.data_spaces AS dsidx
ON        dsidx.data_space_id = idx.data_space_id
ORDER BY  [File Group], [Table Name]

SELECT tbl.name AS [Table Name],

CASE WHEN dsidx.type='FG' THEN dsidx.name ELSE '(Partitioned)' END AS [File Group]

FROM sys.tables AS tbl

JOIN sys.indexes AS idx

ON idx.object_id = tbl.object_id

AND idx.index_id <= 1

LEFT JOIN sys.data_spaces AS dsidx

ON dsidx.data_space_id = idx.data_space_id

ORDER BY [File Group], [Table Name]

Luego de ejecutar la consulta tendremos el siguiente resultado, una lista de nuestras tablas y sus respectivos FileGruop

Adicionalmente podemos dar click derechos sobre las tablas que deseamos mover y en la seccion de storage validar el grupo de archivos el cual esta alojado nuestra tabla, la cual deseamos mover

Como mover tablas en SQL — Mover file group

Paso 2:Creación de un nuevo grupo de archivos

Si el grupo de archivos al que desea mover la tabla no existe, entonces será necesario crearlo. Para ello, haga clic con el botón derecho en la base de datos dentro del Explorador de objetos y seleccione Propiedades. En la ventana de propiedades, seleccione el elemento ‘Grupos de archivos’ y haga clic en el botón ‘Añadir’. El nombre del nuevo grupo de archivos se puede escribir en el cuadro de texto como se muestra a continuación (he elegido llamarlo Prueba_SQListo):

Paso 3: Crear los archivos que se los cuales formaran parte del grupo de archivos

Vamos a la página ‘Files’ en la misma ventana Propiedades y agregar un archivo al grupo de archivos (un grupo de archivos siempre contiene uno o más archivos) usando el botón ‘Add’. También se puede configurar un nombre lógico y una información de tamaño como se muestra a continuación (asegúrese de seleccionar el nuevo grupo de archivos):

Paso 4: Mover una tabla existente al nuevo grupo de archivos

Para mover una tabla a un grupo de archivos diferente, es necesario mover el índice agrupado de la tabla al nuevo grupo de archivos. Aunque esto puede parecer extraño al principio esto no es sorprendente cuando recuerda que el nivel de hoja del índice agrupado contiene realmente los datos de la tabla. Mover el índice agrupado se puede hacer en una sola instrucción usando la cláusula DROP_EXISTING de la siguiente manera (usando las tablas de AdventureWorks como ejemplo):

CREATE UNIQUE CLUSTERED INDEX PK_Department_DepartmentID
ON HumanResources.Department(DepartmentID)
WITH (DROP_EXISTING=ON,ONLINE=ON) ON SECONDARY

CREATE UNIQUE CLUSTERED INDEX PK_Department_DepartmentID

ON HumanResources.Department(DepartmentID)

WITH (DROP_EXISTING=ON,ONLINE=ON) ON SECONDARY

Paso 5: Validamos que nuestro proceso tenga existo al realizar nuevamente el proceso del paso numero 1

Es posible que se pregunte cómo mover una tabla el grupo de archivos de una tabla sin un índice agrupado (es decir, un montón). Por lo que sé, la única manera es agregar temporalmente un índice agrupado en el nuevo grupo de archivos y luego soltarlo (si es necesario).

Por último, si está creando nuevas tablas, entonces no es necesario crearlas en el grupo de archivos PRIMARY y moverlas, solo puede crearlas directamente en el nuevo grupo de archivos.

Deje sus comentarios, Feliz dia

3 comentarios en «Pasos para mover tablas a otro FileGroup: Como mover tablas a otro FileGroup»

Luis Enrique

el 31 mayo, 2018 a las 6:23 pm
Enlace permanente

para usar este partition necesitas el sql server Enterprise?
Accede para responder
- JuanCastillo
  
  el 1 junio, 2018 a las 9:12 am
  Enlace permanente
  
  Si, men unicamente puedes en enterprice y developer, al igual que indices columnares y otras bondades unicamente disponibles en esa version
  Accede para responder