La arquitectura interna de las bases de datos en SQL Server están compuestas 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 «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 los 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 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 mas de un «LogFile».
En conclusión espero que sea de ayuda estas explicaciones sobre la arquitectura de una base de datos de SQL Server, si desean temas por favor no duden en solicitarlo, haré lo posible para poder cubrir los temas solicitados en el mas corto tiempo.
Memorias de un DBA 
Me encantó tu blog y me ha servido mucho para aprender un poco sobre SQL Server. Espero lo retomes!
Muchas gracias, en eso estoy.
me dejaron una tarea de realizar un diseño logico, tengo 4 sistemas (Facturación, Inventario, Contabilidad y Recursos Humanos, un servidor, me pides hacer el Diseño delas Unidades lógicas, del Almacenamiento de Datos (base de datos), Estructura lógica de almacenamiento (FileGroup), Distribución física de los dataFiles y Roles de Accesos. Me puedes dar una ayuda.? Desde ya te lo agradezco. me puedes escribir a mi correo
Claro, pero tendrias que darme más detalles del tema.
Trabajo de Servidores de Bases de Datos
Introducción
El Magnate hombre de negocios Casimiro Miranda dueño de media ciudad capital, acaba de
adquirir la ferretería el Gran Clavo S.A. la cual tiene mas de 20 años de existencia en el país, es un
marco de referencia para todo el mercado nacional para encontrar todo tipo de clavos, pernos,
tornillos y cualquier artículo ferretero que puedan existir, maneja grandes volúmenes de
inventarios.
El Sr. Casimiro es asesorado por su yerno, el Ing. Arturo Saavedra que acaba de tomar una
maestría en línea de la universidad openMaster. Al hacer la evaluación de la infraestructura de
base de datos se encuentran que toda la base de datos descansa en fox 4.0, la cual son archivos
planos, a pesar que durante el día se realiza cualquier cantidad de ventas. Mucha información
importante se lleva en Excel y cada vez que hay un fallo en la base de datos se pierden todos las
transacciones que han ocurrido desde el último respaldo hasta el momento del fallo.
El Ing. Saavedra convence a don Casimiro que tiene que hacer una inversión en el Gran Clavo para
que tenga un crecimiento y puede convertirse en el Clavo más grande de nuestro país.
Dada las circunstancias, el Ing. Saavedra ha decidido contratar los servicios de un Arquitecto de
Sistema que acaba de tomar una Especialidad en bases de datos en una de las principales
universidades de X. El cual realizará un estudio profesional de la institución.
Planteamiento
Dentro del plan de Reingeniería que el Ing. Saavedra tiene trazado, para salvar al Gran Clavo, ha
decidido rehacer los sistemas que corresponden a los siguientes módulos:
Facturación
Inventario
Contabilidad
Recursos Humanos
El Ing. Saavedra requiere lo siguiente:
1. Diseño lógico de los nuevos sistemas, detallado en lo siguiente:
a. Diseño de Unidades lógicas
b. Almacenamiento de Datos (base de datos, schemas, Esquema de
almacenamiento)
c. Estructura lógica de almacenamiento (FileGroup, TablaSpace)
d. Distribución física de los dataFiles.
e. Roles de Accesos
f. Diseño de plan de Respaldo y modo de recuperación
Este diseño se tiene que hacer los por cada uno gestores de bases de datos: Oracle, SQL
Server y MySQL
2. Una Matriz comparativa entre los siguientes aspectos
a. Estructura lógica
b. Distribución física de DataFiles
Ej. Por cada sistema se debe hacer
Sistema Característica /Gestor Oracle SQL Server MySQL
Facturacion
…
Estructura lógica Schema Base de datos EsquemaAlmacenamiento
Distribución Física Datos Und H Datos Und H Datos Und H
Index Und I Index Und I
Logs Und L Archive log Und L
Und : Unidad
Excelente aporte, muchas gracias
Si tengo una duda, cuando se cuenta con un UNICO disco para ubicar todos los archivos MDF, es recomendable que estos esten en un subdirectorio de un directorio general que se puede llamar DATA (D:\DATA\Drh_data D:\DATA\SGM_data ,ó es mejor que los difentes archivos esten en su propio directorio en la raíz del disco: Ejemplos: D:\Drh_data D:\SGM_data
Hola Rafael, con respecto a tu duda, te comento que en relación a la performance y la arquitectura de las bases de datos, el hecho detener un solo disco para los datafiles hace que sea indiferente colocarlos en uno o varios directorios dentro del mismo disco, ya que al final la misma aguja del disco fisico sera la que trabajará cuando se guarde la información en la base de datos. Sin embargo por orden quizas si sea mejor tener separados los datafiles de las bases de datos en diferentes directorios, eso ya depende del estilo de cada persona.
Excelente aporte, espero haya mas post, igual para troubleshooting si pudieses postear, seria genial, saludos.