ADMON SQL

Tema 1: Configuraciónde archivosde registrode datos.

Una base de datos SQL. Server 2012 cuenta con dos tipos de archivos operativos:los archivos de datos y los archivos de registro. Los archivos de datos contienendatos y objetos tales como tablas e índices; los archivos de registro contienen elregistro de transacciones necesario para recuperar las transacciones de la base dedatos. Es posible agrupar los archivos de datos en grupos de archivos con el fin deque puedan ser más fácilmente administrados y puedan ofrecer un mayor rendimiento. A continuación explicaremos los diferentes tipos de archivo existentes, veremos las diferentes opciones de configuración disponibles, y lasmejores prácticas para diseñar sus archivos de manera que tanto su recuperacióncomo su rendimiento sean óptimos.

Archivos de datos.

En una base de datos SQL Server 2012, pueden crearse dos tipos de archivos dedatos: primarios y secundarios.

• El archivo primario de datos es obligatorio y contiene información de arranque para el catálogo de la base de datos, señalando a otras bases de datos.Este archivo primario puede contener también objetos y datos de los usuarios. La extensión recomendada para el archivo de datos primarios es .mdf.

• El archivo secundario de datos, que es opcional y viene definido por elusuario, contiene tanto objetos como datos del usuario. Es posible colocarcada uno de los archivos secundarios en un disco diferente para mejorar elrendimiento. Una base de datos puede contener un máximo de 32,766archivos secundarios. La extensión recomendada para este tipo de archivos.ndf.

Por ejemplo, usted puede tener una base de datos sencilla o una base de datosque utiliza en raras ocasiones con un único archivo primario de datos que almacena tanto la información del catálogo como las tablas, vistas, procedimientos ydatos. Para una base de datos mayor o que se utilice más frecuentemente, podríaconfigurar un archivo primario de datos, así como varios archivos secundariosalojados en diferentes discos para obtener un mejor rendimiento y una majordisponibilidad y estabilidad.

Archivos de bases de datos.

Es recomendable almacenar todo los datos y objetos en archivos secundarios y dejar el catálogo en la base de datos en el archivo primario. Estaconfiguración ayuda a reducir la contención del acceso al disco.

Archivos de registro.

Todas las bases de datos SQL Server 2012 tienen un registro de transaccionesque registra todas las modificaciones que realiza cada transacción en la base dedatos. SQL Server almacena esta información en los archivos de registro. Deberá tener al menos un archivo de registro para cada base de datos. Puede creararchivos de registro múltiples por cada base de datos para facilitar así unarecuperación más rápida. La extensión recomendada para los archivos de registro es .ldf.

Grupos de archivos.

Un grupo de archivos es una estructura lógica que permite a los administradoresde la base de datos agrupar archivos de datos y tratarlos como si fueran unaunidad lógica. Con el fin de mejorar el rendimiento, puede destinar objetos de labase de datos, como por ejemplo las tablas, a grupos de archivo específicos. Aldividir los objetos de la base de datos en varios grupos de archivo, pueden aprovecharse los diferentes subsistemas de disco y permitir que SQL Server realice operaciones de disco paralelas. Además, con la configuración de grupos de archivo múltiples, los archivos pueden ser copiados y restaurados individualmente.

SQL Server soporta dos tipos de archivos de grupo: primarios y definidos por elusuario.

• Un grupo primario de archivos contiene el archivo de datos primario y todoslos archivos de datos secundarios que no hayan sido almacenados en otrogrupo. Todos los sistemas de tablas son asignados al grupo primario dearchivos.

• El grupo de archivos definido por el usuario se crea con la finalidad deagrupar archivos secundarios y asignar objetos de la base de datos a gruposde archivos. Una base de datos puede contener hasta 32,766 grupos de archivo definidos por el usuario.

A la hora de administrar grupos de archivos, deberá prestar especial atención alas siguientes propiedades de los mismos, propiedades que podrá establecer ymodificar desde el SSMS o utilizando el comando ALTER DATABASE.

• Cada base de datos contiene un grupo primario de archivos. Cuando se creaun objeto de la base de datos sin especificar un grupo de archivo, SQL Serverasigna el objeto al grupo de archivos predeterminado.

• Se puede configurar un grupo de archivos como de sólo lectura. Los gruposde archivos de sólo lectura pueden utilizarse para objetos de la base de datosque no deban ser modificados, como por ejemplo las tablas de historia.

Todos los grupos de archivo pueden ser configurados como de sólo lecturasalvo el grupo primario de archivos.

Si su base de datos tiene una tabla a la que se accede con mucha frecuencia (porejemplo, Detalles de compra), es posible crear múltiples archivos secundarios de datos para la misma, almacenar los archivos en diferentes unidades dedisco, y agrupar todos estos archivos en un mismo grupo de archivos. Posteriormente, podrá almacenar la tabla Detalles de compra en este grupo, demanera que todas las consultas que se realicen sobre esta tabla se extenderán portodos los discos disponibles.

Diseño de grupos de archivo.

Cree al menos un grupo de archivos definido por el usuario para albergararchivos secundarios de datos y objetos de la base de datos. Configure estegrupo de archivos como grupo predeterminado, de modo que SQL Serveralmcene todos los objetos creado en dicho grupo.

Cómo configurar archivos de datosy archivos de registro.

Los archivos de datos y de registro se pueden configurar utilizando el comando de Transact-SQL CREATE DATABASE. Es posible modificar la configuración delos mismos mediante el comando ALTER DATABASE. De forma alternativa,estos archivos pueden también configurarse a partes de la página de Propiedades de la Base de Datos en SSMS. La tabla 1 contiene las opciones de configuración para cada tipo de archivo.

Tabla 1. Opciones de configuración de archivos.

Opción Descripción

Name El nombre lógico para el archivo.

Filename Ruta completa del sistema operativo y nombre de archivo.

Size Tamaño del archivo. Si no se específica un tamaño para elarchivo primario, el motor de la base de datos utilizará eltamaño del archivo primario en la base de datos modelo. Sise especifica un archivo secundario o archivo de registro sinla opción de tamaño, el motor de la base de datos creararchivos de 1 MB.

Maxsize Es el tamaño máximo del archivo. Si no se especifica untamaño máximo, o si se especifica el valor UNLIMITED, lafila crecerá hasta que la unidad esté llena. En SQL Server2012, un archivo de registro tiene un tamaño máximo de 2terabytes, y los archivos de datos, un máximo de 16 terabytes.

Filegrowth Especifica el crecimiento automático permitido para el archivo. Puede especificarse el valor en kilobytes, megabytes,gigabytes o terabytes, o como porcentaje del tamaño de archivo actual. Si se especifica un valor 0, no crecerá.

Como normal general, deberá crear archivos de base de datos lo más grandesposible, basándose en la cantidad máxima de datos que estima que contendrá la base de datos para poder acomodar el crecimiento futuro de la misma. Al creargrandes archivos, puede evitarse la fragmentación de archivos y obtener un mejor rendimiento de la base de datos. En muchos casos, es posible permitir elcrecimiento automático de la base de datos; asegúrese, no obstante, de limitar alautocrecimiento especificando un tamaño de crecimiento máximo que deje librecierta cantidad de espacio en el disco duro. Al agrupar diferentes grupos dearchivo en discos diferentes, podrá también eliminar la fragmentación física de sus archivos a medida que éstos vayan creciendo.

Nota: Para el siguiente ejercicio, cree una carpeta llamada C:\AdSQL y dentro de ella dos carpetas, Project_Data y Project_Data2. Si cuenta con una unidad de disco externa, cree Project_Data2 en esa unidad. En el ejemplo asumiremos que existe una unidad externa “E:”. De no contar con ella, cambie la ruta del ejemplo a C:\AdSQL\Project_Data2.

CREATE DATABASE Projects

ON

PRIMARY

(NAME =ProjectPrimary,

FILENAME=' C:\AdSQL\Projects_Data\Projectprimary.mdf' ,

SIZE = 100MB,

MAXSIZE = 200),

FILEGROUPProjectsFG

(NAME = ProjectData1,

FILENAME = 'E:\AdSQL\Projects_Data2\ProjectData1.mdf' ,

SIZE = 200 MB,

MAXSIZE = 1200,

FILEGROWTH =100),

FILEGROUP ProjectHistoryFG

( NAME = ProjectHistory1,

FILENAME=' E:\AdSQL\Projects_Data2\ProjectHistory1.mdf' ,

SIZE = 100 MB,

MAXSIZE = 500,

FILEGROWTH = 50)

LOG ON

( NAME = Archlog1,

FILENAME =' C:\Projects_Data\Projectflata1.mdf ' ,

SIZE= 300 MB,

MAXSIZE = 800,

FILEGROWTH = 100)

Es posible añadir, eliminar o modificar las propiedades del archivo mediante elcomando ALTER DATABASE. El siguiente ejemplo añade un nuevo archivo a labase de datos Projects. Lleve a cabo el siguiente ejercicio:

ALTER DATABASE Projects

ADD FILE

(NAME=ProjectsData4,

FILENAME= ' E:\Projects_Data\ProjectData4 .ndf',

SIZE=100MB,

MAXSIZE=500MB,

FILEGROWTH=75MB) TO FILEGROUP ProjectsFG

Estas

...