Blog gratis
Reportar
Editar
¡Crea tu blog!
Compartir
¡Sorpréndeme!
img
img

 

 

SQL Server
Blog. (Apuntes y Recopilaciones) por Norman M. Pardell

img
img
06 de Enero, 2013 · PARTITION-SQL-Server

PARTITION SCHEME. SQL Server.

El tema del particionado, ya apareció en SQL Server 2005, y básicamente lo que nos va a permitir, es conseguir coger un único objeto relacional (una tabla), y particionarla en múltiples archivos sobre el sistema de discos. Es de cajón, que esto nos va a dar una serie de escenarios muy interesantes, que todos nos podemos imaginar o de hecho que todos probablemente conozcamos. Como es la posibilidad de mantener un único objeto con una gran cantidad de datos históricos y de evitar el tener que hacer tareas de mantenimiento que por naturaleza no tengan mucho sentido sobre estos datos históricos, como por ejemplo la fragmentación de índices, ya que no va ha ser un problema sobre los datos históricos, pero la fragmentación si hay que tenerla en cuenta para los datos mas transaccionales o actuales. Esto es en grandes rasgos lo que se dá en los casos básicos de particionado.

 

Otros, ejemplo muy típico es el backup. Hacer un backup con unas reglas o estrategias diferentes en función del rango de fechas,  ya que (por ejemplo) una tabla de pedidos del año en curso puede tener una estrategia totalmente diferente a una tabla con datos anteriores o históricos…

 

Sin entrar en detalle, comentaros que el particionado se implementa mediante dos objetos principales. Por un lado, la función de partición que nos va a permitir coger un tipo de datos y dividir su ámbito en N particiones. Y por otra parte, el schema de particiones que va a permitir asignar cada uno de esos valores a un filegroup (a uno o N ficheros…)

 

CREATE PARTITION SCHEME anual_schema

AS PARTITION anual_range to

(

            annual_min,                -- before 2009

            annual_2009,              -- 2009 data

            annual_2010,              -- 2010 data

            annual_2011,              -- 2011 data

            annual_2012               -- 2012 data

)

 

Creando tabla particionada e indices:

 

CREATE TABLE Order_History (

            Order_ID        bigint,

            Order_Date    datetime,

            Customer_ID  bigint

           

) ON annual_Schema (Order_Date)

 

CREATE INDEX Order_Cust_Idx

            ON Order_History (Order_ID)

ON annual_schema (Order_Date)

 

Podemos tener un escenario en el cual podemos agregar dinámicamente nuevas particiones, según n os van llegando nuevos años, o la formula que establezcamos… que evidentemente se pueden hacer particionados con otros tipos de datos, como puede ser por geografía…

 

Hay algo interesante que tiene que ver con la alta disponibilidad y con reducir muchísimo o intentar minimizar ciertas operaciones que pueden ser muy costosas a nivel de tiempo. Como Puede ser el truncar unos datos de un día o de un mes completo, hacer una gran actualización…para lo que hay una técnica (o truco) utilizando el particionado que consiste en intercambiar una partición con datos por una partición vacía, y posteriormente proceder a la eliminación completa con un truncado de esa partición que tenía los datos, manteniendo la partición vacía en el objeto relacional (tabla) con el que estamos trabajando. Hay otras muchas técnicas, como por ejemplo implementar updates masivos sobre particiones, etc…

 

Ejemplo:

 

--Creamos base de datos.

Create database ParticionadoTabla_ejem;

GO

--Nos conectamos a la base de datos:

use ParticionadoTabla_ejem;

GO

 

--Esta característica se encuentra en SQL Server 2005, SQL Server 2008 y SQL Server 2008 R2

 

--Crear una función de partición (PARTITION FUNCTION), en esta se definirá el rango que cada partición va a almacenar.

--Para este caso existen dos métodos de Rangos, Izquierda (left) o Derecha (right), la diferencia del uso de este método,

--es la manera de realizar el análisis de rangos.

--Un ejemplo de la sentencia para cada método serían los siguiente

 

 

CREATE PARTITION FUNCTION pfAnualR(int) AS RANGE RIGHT FOR VALUES(2008,2009,2010,2011)

--Primera Partición < 2008

--Segunda Partición >= 2008

--Tercera Partición >= 2009

--Cuarta Partición  >= 2010

--Quinta Partición  >= 2011

 

 CREATE PARTITION FUNCTION pfAnualL(int)   AS RANGE LEFT FOR VALUES(2008,2009,2010,2011)

--Primera Partición <= 2008

--Segunda Partición > 2008

--Tercera Particion > 2009

--Cuarta Particion  > 2010

--Quinta Particion  > 2011

 

 

 --Crear un esquema para la partición (PARTITION SCHEME) , en esta se definirán los FileGroupsdonde donde se almacenara

 --cada partición,

 --Un ejemplo es el siguiente:

 --(para la ejecución correcta de este script, es necesario que se creen o existan 5 FileGroups:

                    -- llamados FG01, FG02, FG03, FG04, FG05):

                   

 

--Creamos filegrups:

ALTER DATABASE ParticionadoTabla_ejem ADD FILEGROUP FG01;

GO    

ALTER DATABASE ParticionadoTabla_ejem ADD FILEGROUP FG02;

GO          

ALTER DATABASE ParticionadoTabla_ejem ADD FILEGROUP FG03;

GO          

ALTER DATABASE ParticionadoTabla_ejem ADD FILEGROUP FG04;

GO          

ALTER DATABASE ParticionadoTabla_ejem ADD FILEGROUP FG05;

GO                        

 

--Creamos ficheros de datos (mdf) para cada filegrups que hemos creado antes:

                    ALTER DATABASE ParticionadoTabla_ejem

                    ADD FILE

                    ( NAME = File_Data_FG01,

                    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50\MSSQLSERVER\MSSQL\DATA\FG01.mdf',

                    SIZE = 1MB,

                    MAXSIZE = 10MB,

                    FILEGROWTH = 1MB)

                    TO FILEGROUP FG01

                    GO

                    ALTER DATABASE ParticionadoTabla_ejem

                    ADD FILE

                    ( NAME = File_Data_FG02,

                    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50\MSSQLSERVER\MSSQL\DATA\FG02.mdf',

                    SIZE = 1MB,

                    MAXSIZE = 10MB,

                    FILEGROWTH = 1MB)

                    TO FILEGROUP FG02

                    GO          

                    ALTER DATABASE ParticionadoTabla_ejem

                    ADD FILE

                    ( NAME = File_Data_FG03,

                    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50\MSSQLSERVER\MSSQL\DATA\FG03.mdf',

                    SIZE = 1MB,

                    MAXSIZE = 10MB,

                    FILEGROWTH = 1MB)

                    TO FILEGROUP FG03

                    GO    

                    ALTER DATABASE ParticionadoTabla_ejem

                    ADD FILE

                    ( NAME = File_Data_FG04,

                    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50\MSSQLSERVER\MSSQL\DATA\FG04.mdf',

                    SIZE = 1MB,

                    MAXSIZE = 10MB,

                    FILEGROWTH = 1MB)

                    TO FILEGROUP FG04

                    GO                               

                    ALTER DATABASE ParticionadoTabla_ejem

                    ADD FILE

                    ( NAME = File_Data_FG05,

                    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50\MSSQLSERVER\MSSQL\DATA\FG05.mdf',

                    SIZE = 1MB,

                    MAXSIZE = 10MB,

                    FILEGROWTH = 1MB)

                    TO FILEGROUP FG05

                    GO                               

 

--Creamos esquema para la paraticion                                                                

CREATE PARTITION SCHEME psAnualR AS PARTITION pfAnualR TO ([FG01], [FG02], [FG03], [FG04], [FG05])

 

CREATE PARTITION SCHEME psAnualL AS PARTITION pfAnualL TO ([FG01], [FG02], [FG03], [FG04], [FG05])

 

 

--En la creación de la Tabla, la diferencia es que en lugar de definir un FileGroup donde se guardara el objeto,

-- define la Función de partición y la columna eje. Ejemplo

 

CREATE TABLE tblEstadosR (Ano int, Mes int, Dia int, Tipo varchar(30), Importe float) ON psAnualR(Ano)

 

CREATE TABLE tblEstadosL (Ano int, Mes int, Dia int, Tipo varchar(30), Importe float) ON psanualL(Ano)

 

 

--Una vez realizados estos pasos, se procede a realizar el llenado de las tablas particionadas

INSERT INTO tblEstadosR (Ano, Mes, Dia, Tipo, Importe) VALUES (2007,08,22,'Cargo',13.00)

INSERT INTO tblEstadosR (Ano, Mes, Dia, Tipo, Importe) VALUES (2008,08,22,'Cargo',34.00)

INSERT INTO tblEstadosR (Ano, Mes, Dia, Tipo, Importe) VALUES (2009,08,22,'Cargo',23.00)

INSERT INTO tblEstadosR (Ano, Mes, Dia, Tipo, Importe) VALUES (2010,08,22,'Cargo',78.00)

INSERT INTO tblEstadosR (Ano, Mes, Dia, Tipo, Importe) VALUES (2011,08,22,'Cargo',98.00)

INSERT INTO tblEstadosR (Ano, Mes, Dia, Tipo, Importe) VALUES (2012,08,22,'Cargo',14.00)  

GO

 

INSERT INTO tblEstadosL (Ano, Mes, Dia, Tipo, Importe) VALUES (2007,08,22,'Cargo',13.00)

INSERT INTO tblEstadosL (Ano, Mes, Dia, Tipo, Importe) VALUES (2008,08,22,'Cargo',34.00)

INSERT INTO tblEstadosL (Ano, Mes, Dia, Tipo, Importe) VALUES (2009,08,22,'Cargo',23.00)

INSERT INTO tblEstadosL (Ano, Mes, Dia, Tipo, Importe) VALUES (2010,08,22,'Cargo',78.00)

INSERT INTO tblEstadosL (Ano, Mes, Dia, Tipo, Importe) VALUES (2011,08,22,'Cargo',98.00)

INSERT INTO tblEstadosL (Ano, Mes, Dia, Tipo, Importe) VALUES (2012,08,22,'Cargo',14.00) 

 GO

 

-- Para saber en qué partición se encuentra la data se utilizara el siguiente comando Transac-SQL $PARTITION, este regresa el número de partición en el cual se encuentra la data. Para el ejemplo que estamos realizando, el nombre de la Base de Datos utilizada es BDPrueba

 

SELECT Ano, BDPrueba.$Partition.pfAnualR(Ano) FROM tblEstadosR

 

SELECT Ano, BDPrueba.$Partition.pfAnualL(Ano) FROM tblEstadosL

 

 

 --conceptos básicos de particionamiento de tablas, las ventajas principales al manejar esta característica son:

 

      --  1.Sliding Windows escenarios

 

      --  2.Mejora de actividades administrativas, como:

 

--Mantenimiento de Índices

--Compresión de particiones

--Manejo de Storage

 

 

Fuentes:

Microsoft, msdn, TechNet blog...

Apunte y recopilación por Norman M. Pardell

publicado por normanmpardell a las 21:20 · Sin comentarios  ·  Recomendar
Comentarios (0) ·  Enviar comentario
Esta entrada no admite comentarios.
img
.Sobre mí
FOTO

Norman M. Pardell

MCITP: Database Administrator & Database Developer, SQL Server 2008. MCC Award Certificate. Consultor Senior de bases de datos en Capgemini España, S.L. Asesoramiento en implementación, desarrollo y gestión de bases de datos en grandes compañías. Actualmente, asignado a proyecto en compañía líder en el sector energético global. Más de 10 años trabajando con SQL Server (y otros gestores de BBDD)

» Ver perfil

img
.Secciones
» Inicio
img
.Enlaces
» Microsoft MSDN Foros
» Windows Server 2012
img
.Más leídos
» Asignar la cantidad correcta de Memoria para SQL Server
» Base de Datos Sospechosa (Suspect)
» Como modificar la Intercalación (Collation) en SQL Server
» Como renombrar una instancia de SQL Server. sp_dropserver. sp_addserver
» Detectar bloqueos. SQL Server V.2005 y superiores
» Funciones SQL Server. Funciones escalares y funciones con valores de tabla.
» Integridad y corrupción en las bases de datos: DBCC CHECKDB
» Log de transacciones ( .ldf ). SQL Server.
» Migrando SQL Server 2005, 2008, 2008 R2 a SQL Server 2012
» Transacciones activas. SQL server 2008
img
.Nube de tags [?]
                                                           
img img
FULLServices Network | Crear blog | Privacidad