TÉCNICO EN PROGRAMACIÓN DE SOFTWARE

Construir tablas que hacen parte del diseño del diagrama relacional en el motor de base de
datos empleando las cuatro formas de normalización

SQL SERVER
GUÍA DE LABORATORIO N° 01

Arquitectura de Bases de Datos SQL Server

La arquitectura interna de las bases de datos en SQL Server está compuesta por 2 tipos de
estructura, la estructura lógica y la estructura física. Es muy importante conocer cómo es que
estas estructuras están compuestas y cuál es la relación que tienen los objetos de base de datos
con cada una de estas estructuras.

Estructura Lógica:

Desde el punto de vista lógico, la base de datos debe tener al menos 1 “FileGroup” el cual
contiene a toda la metadata de la misma base de datos, es decir tablas y vistas de sistema, a este
“FileGroup” inicial se le conoce como “Primario” y está presente en todas las bases de datos.
Todos los objetos de usuario que contengan data, ya sean tablas o índices, deben estar ligados a
un “FileGroup”, esto se puede definir al momento de ejecutar la sentencia DDL de creación del
objeto, si no se indica a que “FileGroup” estará ligado ese objeto, este pertenecerá al “FileGroup”
por defecto definido en la base de datos. La base de datos solo puede tener definido 1 solo
default “FileGroup”.

Las bases de datos pueden tener hasta 32767 “FileGroups” definidos, según los límites
establecidos para la última versión de SQL Server, la cual es SQL Server 2008 R2. Uno de los
propósitos de los “FileGroups” es poder distribuir la data a través de varios discos duros físicos,
de esta manera se puede obtener mayor rendimiento en las operaciones de I/O debido a que más
de un disco trabajara al mismo tiempo. Otro de los propósitos es poder esconder la ubicación
física real de la información a los programadores, ya que para ellos la tabla “X” pertenece al
PREPARADO POR: INSTRUCTOR MIGUEL OLIVARES
SQL SERVER
 TÉCNICO EN PROGRAMACIÓN DE SOFTWARE
Construir tablas que hacen parte del diseño del diagrama relacional en el motor de base de
datos empleando las cuatro formas de normalización

“FileGroup” “A”, pero no saben en que data files físicamente se encuentra la información de la
tabla “X”.

Los “FileGroups” pueden contener 1 o más “Datafiles”, y cada uno de estos datafiles se pude
encontrar en un discos diferentes, lo cual también agilizara las consultas y los ingresos de
información a las tablas que se encuentren asignadas a este “FileGroup”, debido a que SQL Server
distribuirá la información uniformemente a través de todos los “DataFiles” del “FileGroup”.

Estructura Física:

Desde el punto de vista físico, como ya hemos visto, tenemos los “DataFiles” que son en realidad
los archivos de datos, es decir donde se guarda toda la información de la base de datos. Un
“DataFile” solo puede pertenecer a 1 “FileGroup”.

Internamente los “DataFiles” están divididos en “Extends” y estos a su vez en “Pages”. Las “Pages”
son la unidad minima de almacenamiento dentro de la base de datos. Un “Page” tiene 8 Kb de
tamaño en espacio de disco. Un “Extend” tiene 8 “Pages” contiguas que lo conforman, es decir, un
“Extend” tiene como tamaño 64 Kb de espacio en disco.

En un “Page” solo puede haber información de 1 sola tabla, es decir el espacio de un “Page” no es
compartido entre tablas o índices. En el caso de los “Extends”, estos pueden ser de dos tipos:

 “Mixed”: Los cuales son compartidos hasta por 8 objetos, uno por cada “Page”.
 “Uniform”: Los cuales solo pertenecen a un solo objeto, es decir que todos los “Pages”
pertenecen a un solo objeto.
Normalmente cuando se crea una nueva tabla esta es asignada a un “Extend” de tipo “Mixed”,
hasta alcanzar la utilización de hasta 8 “Pages”, a partir de ese momento se asignan “Extends” de
tipo “Uniform” para optimizar el uso del espacio en la tabla.

Los “DataFiles” normalmente tienen 2 extensiones de archivo, las cuales son estandar mas no
obligarias, la extencion “mdf” que se utiliza para el primer “Datafile” perteneciente al “FileGroup”
primario, y la extension “ndf” que se utiliza para los demas datafiles que se agregan
posteriormente a los demas “FileGroups” de la base de datos.

En el caso del “LogFile”, este no pertenece a un “FileGroup” en especifico, en cambio el archivo

esta ligado directamente a la base de datos. Las bases de datos de SQL Server solo pueden tener
un solo “LogFile” activo al mismo tiempo, si bien se pueden crear multiples “LogFiles” en la base
de datos, solo uno podra ser escrito, ya que solo uno puede estar activo, cuando este archivo se
llene, la base de datos pasara a escribir al siguiente archivo de transacciones, y asi
sucesivamente. Por esta razon no es muy conveniente ni util tener más de un “LogFile”.

PREPARADO POR: INSTRUCTOR MIGUEL OLIVARES

SQL SERVER
 TÉCNICO EN PROGRAMACIÓN DE SOFTWARE
Construir tablas que hacen parte del diseño del diagrama relacional en el motor de base de
datos empleando las cuatro formas de normalización

Crear una Base de Datos

El primer paso para implementar físicamente una base de datos es crear los objetos de la base de
datos. Usando la información que obtuvo cuando se determinaron los requerimientos de diseño,
y los detalles que identificó en el diseño lógico de la base de datos, Ud. puede crear los objetos
de la base de datos y definir sus características. Podrá modificar estas características después
que haya creado los objetos de la base de datos.

Cuando cree una base de datos, deberá primero definir su nombre, su tamaño, y los archivos y
grupos de archivos usados para soportarla. Deberá considerar varios factores antes de crear la
base de datos:

· Por defecto solo tienen permiso para crear bases de datos los miembros de los roles
“sysadmin” y “dbcreator”, Ud. podría no tener asignados ninguno de dichos roles
pero aún contar con la autorización para crear bases de datos en caso que el
administrador se los hubiera otorgado.
· El usuario que crea una base de datos se convierte en el dueño de la base de datos
· Un máximo de 32.767 bases de datos pueden ser creadas sobre un servidor.
· El nombre de la base de datos debe seguir las reglas de los identificadores.

Como ya indicamos, se usan tres tipos de archivos para almacenar una base de datos: archivos
primarios, que contienen la información de arranque para la base de datos; archivos secundarios,
que hospedan a todos los datos que no caben en el archivo primario; y registro de transacciones,
que contienen la información de la transacciones, usadas para recuperar la base de datos. Toda
base de datos tiene al menos dos archivos: un archivo primario y un registro de transacciones.

Cuando se crea una base de datos, los archivos se llenan de ceros para sobrescribir cualquier
otro dato que archivos que han sido borrados puedan haber dejado en el disco. Aunque esto
significa que los archivos pueden tardar en ser creados, esta acción evita al sistema operativo
tener que llenar con cero los archivos al momento de la efectiva grabación de los datos durante la
normal operación de la base de datos, mejorando la performance operacional de cada día.

Cuando Ud. crea una base de datos, deberá especificar el tamaño máximo que un archivo tiene
autorizado a alcanzar. Esto previene que el archivo crezca, cuando los datos son ingresados,
hasta que el espacio en disco se termine.

SQL Server implementa una nueva base de datos en dos pasos:

· SQLServer usa una copia de la base de datos “Model” para inicializar la nueva base de
datos y sus metadatos.

PREPARADO POR: INSTRUCTOR MIGUEL OLIVARES

SQL SERVER
 TÉCNICO EN PROGRAMACIÓN DE SOFTWARE
Construir tablas que hacen parte del diseño del diagrama relacional en el motor de base de
datos empleando las cuatro formas de normalización

· SQLServer luego llena el resto de la base de datos con páginas vacías (excepto aquellas
páginas que tienen grabados datos internos como el espacio usado)

Cualquier objeto definido por el usuario en la base de datos “Model” es copiado a todas las bases
de datos que sean creadas. Se pueden agregar objetos a la base de datos “Model”, tales como
tablas, vistas, procedimientos almacenados, tipos de datos, etc. que serán incluidos en las nuevas
bases de datos. Además, cada nueva base de datos hereda la configuración de las opciones de la
base de datos “Model”.

Se puede usar el comando CREATE DATABASE para crear una base de datos y los archivos
almacenados en una base de datos. El comando CREATE DATABASE le permitirá especificar una
serie de parámetros que definirán las características de la base de datos.

Por ejemplo, se puede especificar el máximo tamaño que puede alcanzar un archivo o el
incremento que puede experimentar dicho archivo. Si sólo utiliza CREATE
DATABASE nombre_de_la_base_de_datos la base de datos es creada del mismo tamaño de la base
de datos “Model”.

El comando puede ser ejecutado desde el SQL Query Analizer. El siguiente ejemplo crea una base
de datos llamadas “Productos” y especifica que se usará un solo archivo.

El archivo especificado será el archivo primario, y un archivo de registro de 1Mb se crea

automáticamente. Dado que ni megabytes (Mb) ni kilobytes (Kb) son especificados en el
parámetro SIZE para el archivo primario, el archivo será generado en megabytes. Además, al no
consignarse una especificación de archivo para para el archivo de transacciones, el archivo de
transacciones no tendrá un tamaño máximo (MAXSIZE) y podrá crecer hasta ocupar todo el
espacio en el disco.

CREATE DATABASE Sample

ON PRIMARY (NAME=SampleData,
FILENAME='c: \Archivos de programa\ Microsoft SQL Server\MSSQL\Data\Sample.mdf',
SIZE=10MB,
MAXSIZE=15MB,
FILEGROWTH=20%)
LOG ON (NAME=SampleLog,
FILENAME='c: \Archivos de programa\ Microsoft SQL Server\MSSQL\Data\Sample.ldf',
SIZE=3MB,
MAXSIZE=5MB,
FILEGROWTH=1MB)

PREPARADO POR: INSTRUCTOR MIGUEL OLIVARES

SQL SERVER
Al crear una base de datos se pueden establecer los siguientes parámetros:

PRIMARY

Este parámetro especifica los archivos del grupo de archivos principal. El grupo de archivos principal
contiene todas las tablas del sistema de la base de datos. También contiene todos los objetos no
asignados a grupos de archivos del usuario. Todas las bases de datos tienen un archivo de datos
principal. El archivo de datos principal es el punto de inicio de la base de datos y señala a los demás
archivos de la base de datos. La extensión de nombre de archivo recomendada para los archivos de
datos principales es .mdf. Si no se especifica la palabra clave PRIMARY, el primer archivo enumerado en
la instrucción es el archivo principal.

FILENAME

Este parámetro especifica el nombre del archivo del sistema operativo y la ruta de acceso al archi Este
parámetro especifica el tamaño de los archivos de datos o de registro. Puede especificar los tamaños
en megabytes (MB), que es el valor predeterminado, o en kilobytes (KB). El tamaño mínimo es de 512
KB, tanto para los archivos de datos como para los archivos de registro. El tamaño especificado para el
archivo de datos principal debe ser, al menos, como el tamaño del archivo principal de la base de datos
model. Cuando se agrega un archivo de datos o un archivo de registro, el valor predeterminado es 1
MB. vo. La ruta acceso de archivoSO debe especificar una carpeta del servidor en el que está instalado
SQL Server.

SIZE

Este parámetro especifica el tamaño de los archivos de datos o de registro. Puede especificar los
tamaños en megabytes (MB), que es el valor predeterminado, o en kilobytes (KB). El tamaño mínimo es
de 512 KB, tanto para los archivos de datos como para los archivos de registro. El tamaño especificado
para el archivo de datos principal debe ser, al menos, como el tamaño del archivo principal de la base
de datos model. Cuando se agrega un archivo de datos o un archivo de registro, el valor
predeterminado es 1 MB.

MAXSIZE

Este parámetro especifica el tamaño máximo hasta el que puede crecer el archivo. Puede especificar el
tamaño en megabytes (valor predeterminado) o en kilobytes. Si no se especifica el tamaño, el archivo
crece hasta que el disco esté lleno.
FILEGROWTH

Este parámetro especifica el incremento de crecimiento del archivo. El valor de FILEGROWTH de un

archivo no puede sobrepasar el valor de MAXSIZE. Un valor de 0 indica que no hay crecimiento. El valor
se puede especificar en megabytes (el valor predeterminado), en kilobytes o como porcentaje (%). Si no
se especifica FILEGROWTH, el valor predeterminado es el 10 por ciento y el valor mínimo es 64 KB (una
extensión). El tamaño especificado se redondea al múltiplo de 64 KB más próximo.

Grupos de archivos secundarios

Son los formados por archivos NDF, donde como hemos visto antes, se almacenan copias de
seguridad, vistas etc. Para crear un grupo de archivo secundario seguiremos los siguientes pasos:

1. Modificamos la base de datos para añadirle el grupo de archivos secundario, para ello usaremos
Add FileGroup

Alter Database Base de datos

Add FileGroup Grupodearchivosecundario

Alter Database Ejemplo

Add FileGroup MuyConsultados

Comandos completados con éxito.

2. Modificamos la base de datos para añadir un archivo NDF físico al grupo de archivos secundario,
para ello usaremos Add File. Sintaxis:

Alter Database Base de datos

Add File
(
Name = ‘Nombre’,
Filename = ‘Ruta\Archivo.Ndf’,
Size = Tamaño en MB
)

Alter Database Ejemplo

Add File
(
Name = 'Consulta',
Filename = 'C:\Consulta.Ndf',
Size = 5 MB
)
Ampliando la base de datos en 5.00 MB de disco 'Consulta'.
SP_HelpFile

Muestra información sobre archivos NDF. Sintaxis:

SP_HELPFILE @Filename = ‘Nombredearchivosinextensión’

SP_HELPFILE @Filename = 'Consulta' SP_SPACEUSED

Muestra el espacio usado de la base de datos que estamos usando actualmente .

USE EJEMPLO

Exec SP_SPACEUSED

Modificar y añadir archivos a grupos de archivos secundarios

Mediante la opción Modify File de Alter Database, podemos modificar un archivo secundario. Sintaxis:

Alter Database Base de datos

Modify File (name = ‘nombre’, propiedades y valores a modificar)

Alter Database Ejemplo

Modify File (name = 'ejemplolog', size = 15MB)

Con Add File, podemos añadir un nuevo archivo NDF al archivo secundario de la base de datos elegido.
Sintaxis:

Alter Database Base de datos

Add File
(Name = 'Nombre',
Filename = 'Ruta\Archivo.NDF',
Size = Tamaño por defecto,
Maxsize = Tamaño máximo)
To Filegroup Grupo de archivos secundario

Alter Database Ejemplo

Add File
(Name = 'Ejemplodata2',
Filename = 'C:\Ejemplo2.NDF',
Size = 10MB,
Maxsize = 15MB)
To Filegroup MuyConsultados
Ampliando la base de datos en 10.00 MB de disco 'Ejemplodata2'.

Alter Database Clientes

Remove File 'RelacionAnual2'
Go

El archivo 'RelacionAnual2' se quitó.

Podemos modificar el tamaño de un archivo secundario mediante ShrinkFile.

Sintaxis:

DBCC ShrinkFile (Nombrearchivo, %tamañoareducir)

DBCC ShrinkFile (EjemploData, 10, Opciones)

Opciones:

 EmptyFile: Indica que el tamaño del archivo reducido, pase a otros archivos secundarios del
mismo grupo.
 TruncateOnly: Indica que el tamaño reducido de la base de datos pasa al sistema operativo. Si
no ponemos ninguna opción esta es la opción por defecto
 NoTruncate: Indica que el tamaño reducido de la base de datos no pasa al sistema operativo.

ShrinkDataBase

Modifica el tamaño de la base de datos. Sintaxis:

DBCC Shrinkdatabase (Basededatos, %tamañoareducir, Opciones)
Opciones:
 EmptyFile: Indica que el tamaño del archivo reducido, pase a otros archivos secundarios del
mismo grupo.
 TruncateOnly: Indica que el tamaño reducido de la base de datos pasa al sistema operativo. Si
no ponemos ninguna opción esta es la opción por defecto
 NoTruncate: Indica que el tamaño reducido d e la base de datos no pasa al sistema operativo.

Ejemplo:

DBCC Shrinkdatabase (Ejemplo, 25, NoTruncate)

EJERCICIOS

1. Crear una base de datos de Clientes con un tamaño de 40 MB y con crecimiento de un 10%.

2. En el proceso CREATE DATABASE está asignando 25.00 MB en el disco 'Clientes_Data'.

3. En el proceso CREATE DATABASE está asignando 15.00 MB en el disco 'Clientes_Log'.

4. Mostrar si la base de datos está junto a las demás y el espacio usado de mi nueva base de
datos.

5. Crear un grupo de archivos en mi base de datos clientes que se llame Bajas. Añadir dos archivos
a ese grupo de archivos llamados RelacionAnual y que ocupen 5MB.

6. Mostrar las características de los archivos que acabamos de incluir y de su grupo.

7. Eliminar uno de los archivos del grupo Bajas y modificar el otro para darle un tamaño de 10 MB.

8. Reducir el tamaño de la base de datos en un 2%.

9. Volver a aumentar el tamaño de la base de datos en 5 MB más.

10. Poner el tamaño máximo de la base de datos a 60 MB.

11. Eliminar el grupo de Bajas de la base de datos junto con sus archivos. Eliminar también la base
de datos.