• Categories
• Microsoft Sql Server
• Tabla (Base de datos)
• Archivo de computadora
• Software de gestión de datos
• Bases de datos

Citation preview

1  Bases de Datos   

SQL SERVER  COMPONENTES  Motor de base de datos de SQL Server El Motor de base de datos es el servicio principal para almacenar, procesar y proteger los datos. El Motor de base de datos proporciona acceso controlado y procesamiento rápido de transacciones para cumplir los requisitos de las aplicaciones consumidoras de datos más exigentes en el entorno empresarial.

Analysis Services - Datos multidimensionales Analysis Services admite OLAP al permitir al usuario diseñar, crear y administrar estructuras multidimensionales que contienen datos agregados de otros orígenes tales como bases de datos relacionales. Analysis Services - Minería de datos Analysis Services permite al usuario diseñar, crear y visualizar modelos de minería de datos. Estos modelos de minería de datos se pueden construir a partir de otros orígenes de datos empleando una amplia variedad de algoritmos de minería de datos estándar. SQL Server Integration Services (SSIS) Integration Services es una plataforma para generar soluciones de integración de datos de alto rendimiento, lo que incluye paquetes que proporcionan procesamiento de extracción, transformación y carga (ETL) para almacenamiento de datos.

Réplica de SQL Server La réplica es un conjunto de tecnologías destinadas a la copia y distribución de datos y objetos de base de datos desde una base de datos a otra, para luego sincronizar ambas bases de datos y mantener su coherencia. La réplica permite distribuir datos a diferentes ubicaciones y a usuarios remotos o móviles mediante redes de área local y de área extensa, conexiones de acceso telefónico, conexiones inalámbricas e Internet. SQL Server Reporting Services Reporting Services ofrece funcionalidad empresarial de informes habilitados para Web con el fin de poder crear informes que extraigan contenido a partir de una variedad de orígenes de datos, publicar informes con distintos formatos y administrar centralmente la seguridad y las suscripciones.

 

 

Ing. Rosa Navarrete 

2  Bases de Datos   

SQL Server Service Broker Service Broker ayuda a los programadores a crear aplicaciones de base de datos escalables y seguras. Esta nueva tecnología de Motor de base de datos proporciona una plataforma de comunicación basada en mensajes que permite a los componentes de aplicación independientes trabajar como un conjunto funcional. Service Broker incluye infraestructura para programación asincrónica que se puede utilizar para aplicaciones en una base de datos única o instancia única, y también para aplicaciones distribuidas.

BASES DE DATOS DEL SISTEMA  SQL Server incluye las siguientes bases de datos del sistema. MASTER: La base de datos MASTER registra toda la información de sistema de un sistema SQL Server. Dentro de esta información se incluyen los metadatos de todas las instancias, como las cuentas de inicio de sesión, los extremos, los servidores vinculados y la configuración del sistema. Asimismo, MASTER es la base de datos que registra la existencia de las demás bases de datos, la ubicación de los archivos de las bases de datos y la información de inicialización de SQL Server. Por lo tanto, SQL Server no puede iniciarse si la base de datos MASTER no está disponible. En SQL Server, los objetos de sistema ya no se almacenan en la base de datos MASTER, sino en la base de datos de recursos. El tamaño de estos archivos puede variar ligeramente para cada edición de SQL Server.

Archivo Datos

Nombre

Nombre

lógico

físico

master

master.mdf

principales Registro

Crecimiento del archivo Crecimiento automático del 10 por ciento hasta llenar el disco.

mastlog

mastlog.ldf

Crecimiento automático del 10 por ciento hasta un máximo de 2 terabytes.

Cuando trabaje con la base de datos master, tenga en cuenta las siguientes recomendaciones:

• •

Tenga siempre disponible una copia de seguridad actualizada de la base de datos master. Haga una copia de seguridad de la base de datos master lo antes posible después de realizar las siguientes operaciones:

• • • •

Crear, modificar o eliminar una base de datos Cambiar los valores de configuración del servidor o de la base de datos Modificar o agregar las cuentas de inicio de sesión

No cree objetos de usuario en la base de datos master. De lo contrario, deberá realizar una copia de seguridad de la base de datos master con más frecuencia.

 

 

Ing. Rosa Navarrete 

3  Bases de Datos    MSDB El Agente SQL Server utiliza la base de datos msdb para programar alertas y trabajos. Otras características como Service Broker y Correo electrónico de base de datos también usan esta base de datos.

Archivo

Nombre

Nombre físico Crecimiento del archivo

lógico Datos

MSDBData

MSDBData.mdf Crecimiento automático de 256 KB hasta llenar el

principales Registro

disco. MSDBLog

MSDBLog.ldf

Crecimiento automático de 256 KB hasta un máximo de 2 terabytes.

Las siguientes operaciones no se pueden realizar en la base de datos msdb:

• • • • • • • • •

Cambiar intercalaciones. La intercalación predeterminada es la intercalación de servidor. Eliminar la base de datos. Eliminar el usuario guest de la base de datos. Habilitar el mecanismo de captura de cambios en los datos. Participar en la creación de reflejo de la base de datos. Quitar el grupo de archivos principal, el archivo de datos principal o el archivo de registro. Cambiar el nombre de la base de datos o del grupo de archivos principal. Establecer la base de datos en OFFLINE. Establecer el grupo de archivos principal en READ_ONLY.

MODEL La base de datos model se utiliza como plantilla para todas las bases de datos creadas en una instancia de SQL Server. Como tempdb se crea de nuevo cada vez que se inicia SQL Server, la base de datos model siempre tiene que existir en un sistema con SQL Server. Cuando se emite una instrucción CREATE DATABASE, la primera parte de la base de datos se crea mediante la copia del contenido de la base de datos model. El resto de la nueva base de datos se llena a continuación con páginas vacías. Si modifica la base de datos model, todas las bases de datos creadas posteriormente heredan los cambios. Por ejemplo, se podrían establecer permisos u opciones de base de datos o agregar objetos, como tablas, funciones o procedimientos almacenados. Las siguientes tablas muestran los valores de configuración iniciales de los archivos de datos y registro de model. El tamaño de los archivos puede variar ligeramente según la edición de SQL Server.

 

 

Ing. Rosa Navarrete 

4  Bases de Datos   

Archivo Datos

Nombre

Nombre

lógico

físico

modeldev

model.mdf

principales Registro

Crecimiento de archivos Crecimiento automático del 10 por ciento hasta llenar el disco.

modellog

modellog.ldf

Crecimiento automático del 10 por ciento hasta un máximo de 2 terabytes.

TEMPDB La base de datos del sistema tempdb es un recurso global disponible para todos los usuarios conectados a la instancia de SQL Server y se utiliza para incluir lo siguiente:

•

Objetos de usuario temporales creados explícitamente como: tablas temporales locales o globales, procedimientos almacenados temporales, variables de tabla o cursores.

•

Objetos internos creados por SQL Server Database Engine (Motor de base de datos de SQL Server), por ejemplo, tablas de trabajo para almacenar resultados intermedios para colas u ordenación.

•

Versiones de fila generadas por las transacciones de modificación de datos en una base de datos que utiliza transacciones de lectura confirmada que usan transacciones de aislamiento de control de versiones de filas o de aislamiento de instantáneas.

•

Versiones de fila que se generan mediante transacciones de modificación de datos para características como operaciones de índice en línea, conjuntos de resultados activos múltiples (MARS) y desencadenadores AFTER.

Las operaciones realizadas en tempdb se registran con un nivel mínimo. Esto permite que se reviertan las transacciones. tempdb se vuelve a crear cada vez que se inicia SQL Server, de forma que el sistema siempre se inicia con una copia limpia de la base de datos. Las tablas y los procedimientos almacenados temporales se quitan automáticamente en la desconexión y ninguna conexión permanece activa cuando se cierra el sistema. Por tanto, en la base de datos tempdb no hay nada que deba guardarse de una a otra sesión de SQL Server. No se permite realizar operaciones de copia de seguridad y restauración en tempdb. En la tabla siguiente se muestran los valores iniciales de configuración de los archivos de datos y registro de tempdb. El tamaño de estos archivos puede variar ligeramente para cada edición de SQL Server.

Archivo Datos

Nombre

Nombre

lógico

físico

tempdev

tempdb.mdf

principales Registro

Crecimiento del archivo Crecimiento automático del 10 por ciento hasta llenar el disco

templog

templog.ldf

Crecimiento automático del 10 por ciento hasta un máximo de 2 terabytes

 

 

Ing. Rosa Navarrete 

5  Bases de Datos    El tamaño de tempdb puede afectar al rendimiento de un sistema. Por ejemplo, si el tamaño de tempdb es demasiado pequeño, el procesamiento del sistema podría estar demasiado ocupado con el crecimiento automático de la base de datos y, por tanto, no podría satisfacer las necesidades de carga de trabajo cada vez que se inicia SQL Server. Para evitar esta sobrecarga, aumente el tamaño de tempdb. RESOURCE La base de datos Resource es de solo lectura y contiene todos los objetos del sistema que se incluyen con SQL Server. Los objetos del sistema de SQL Server, tales como sys.objects, persisten físicamente en la base de datos Resource, pero aparecen lógicamente en el esquema sys de cada base de datos. La base de datos Resource no contiene datos o metadatos del usuario. La base de datos de recursos hace que el procedimiento de actualizar a una versión nueva de SQL Server sea más rápido y sencillo. En versiones anteriores de SQL Server, la actualización requiere quitar y crear los objetos del sistema. Como el archivo de la base de datos Resource contiene todos los objetos del sistema, ahora para realizar una actualización basta con copiar el único archivo de base de datos Resource en el servidor local. De forma similar, para revertir los cambios de los objetos del sistema de un Service Pack solo se tiene que sobrescribir la versión actual de la base de datos Resource con la versión antigua. Los nombres de archivos físicos de la base de datos de recursos son mssqlsystemresource.mdf y mssqlsystemresource.ldf. Estos archivos se encuentran en :\Archivos de programa\Microsoft SQL Server\MSSQL10_50.\MSSQL\Binn\. Cada instancia de SQL Server tiene un solo archivo mssqlsystemresource.mdf asociado y las instancias no lo comparten.

 

ARQUITECTURA   La unidad fundamental del almacenamiento de datos en SQL Server es la página. El espacio en disco asignado a un archivo de datos (.mdf o .ndf) de una base de datos se divide lógicamente en páginas numeradas de forma contigua de 0 a n. Las operaciones de E/S de disco se realizan en el nivel de página. Es decir, SQL Server lee o escribe páginas de datos enteras. Las extensiones son una colección de ocho páginas físicamente contiguas; se utilizan para administrar las páginas de forma eficaz. Todas las páginas se almacenen en extensiones.

PÁGINAS  En SQL Server, el tamaño de página es de 8 KB. Esto significa que las bases de datos de SQL Server tienen 128 páginas por megabyte. Cada página empieza con un encabezado de 96 bytes, que se utiliza para almacenar la información del sistema acerca de la página. Esta información incluye el número de página, el tipo de página, el espacio disponible en la página y el Id. de unidad de asignación del objeto propietario de la página.

 

 

Ing. Rosa Navarrete 

6  Bases de Datos    En la siguiente tabla se muestran los tipos de página utilizados en los archivos de datos de una base de datos de SQL Server.

Tipo de página

Contenido

Datos

Las filas de datos con todos los datos, excepto los datos text, ntext, image, nvarchar(max), varchar(max), varbinary(max) y xml, cuando text in row está establecido en ON.

Índice

Entradas de índice.

Texto o imagen

Tipos de datos de objetos grandes:

•

Datos text, ntext, image, nvarchar(max), varchar(max), varbinary(max) y xml.

Columnas de longitud variable cuando la fila de datos sobrepasa 8 KB:

• Mapa de asignación global,

varchar, nvarchar, varbinary y sql_variant.

Información acerca de si se han asignado las extensiones.

Mapa de asignación global compartido Espacio disponible en páginas

Información acerca de la asignación de páginas y el espacio disponible disponible en las páginas.

Mapa de asignación de índices

Información acerca de las extensiones utilizadas por una tabla o un índice por unidad de asignación.

Mapa cambiado masivamente

Información acerca de las extensiones modificadas por operaciones masivas desde la última instrucción BACKUP LOG por unidad de asignación.

Mapa cambiado diferencial

Información acerca de las extensiones que han cambiado desde la última instrucción BACKUP DATABASE por unidad de asignación.

Nota: Los archivos de registro no contienen páginas, contienen series de registros.

Las filas de datos se colocan en las páginas una a continuación de otra, empezando inmediatamente después del encabezado. Al final de la página, comienza una tabla de desplazamiento de fila y cada una de esas tablas contiene una entrada para cada fila de la página. Cada entrada registra la distancia del primer byte de la fila desde el inicio de la página. Las entradas de la tabla de desplazamiento de fila están en orden inverso a la secuencia de las filas de la página.

 

 

Ing. Rosa Navarrete 

7  Bases de Datos   

EXTENSIONES  Las extensiones son la unidad básica en la que se administra el espacio. Una extensión consta de ocho páginas contiguas físicamente, es decir 64 KB. Esto significa que las bases de datos de SQL Server tienen 16 extensiones por megabyte. Para hacer que la asignación de espacio sea eficaz, SQL Server no asigna extensiones completas a tablas con pequeñas cantidades de datos. SQL Server tiene dos tipos de extensiones: Las extensiones uniformes son propiedad de un único objeto; sólo el objeto propietario puede utilizar las ocho páginas de la extensión. Las extensiones mixtas, que pueden estar compartidas por hasta ocho objetos. Cada una de las 8 páginas de la extensión puede ser propiedad de un objeto diferente. A las tablas o índices nuevos se les suelen asignar páginas de extensiones mixtas. Cuando la tabla o el índice crecen hasta el punto de ocupar ocho páginas, se pasan a extensiones uniformes para las posteriores asignaciones. Si crea un índice de una tabla existente que dispone de filas suficientes para generar ocho páginas en el índice, todas las asignaciones del índice están en extensiones uniformes.

 

 

Ing. Rosa Navarrete 

8  Bases de Datos   

ARCHIVOS Y GRUPOS DE ARCHIVOS  SQL Server asigna una base de datos a un conjunto de archivos del sistema operativo. Los datos y la información del registro nunca se mezclan en el mismo archivo, y cada archivo solo es utilizado por una base de datos. Los grupos de archivos se denominan colecciones con nombre de archivos que se utilizan como ayuda en tareas de colocación de datos y administrativas, como las operaciones de copias de seguridad y restauración.

ARCHIVOS DE LAS BASES DE DATOS  Las bases de datos de SQL Server utilizan tres tipos de archivos:

•

Archivos de datos principales El archivo de datos principal es el punto de partida de la base de datos y apunta a los otros archivos de la base de datos. Cada base de datos tiene un archivo de datos principal. La extensión recomendada para los nombres de archivos de datos principales es .mdf.

•

Archivos de datos secundarios Los archivos de datos secundarios son todos los archivos de datos menos el archivo de datos principal. Puede que algunas bases de datos no tengan archivos de datos secundarios, mientras que otras pueden tener varios archivos de datos secundarios. La extensión de nombre de archivo recomendada para los archivos de datos secundarios es .ndf.

•

Archivos de registro Los archivos de registro almacenan toda la información de registro que se utiliza para recuperar la base de datos. Como mínimo, tiene que haber un archivo de registro por cada base de datos, aunque puede haber varios. La extensión de nombre de archivo recomendada para los archivos de registro es .ldf.

SQL Server no exige las extensiones de nombre de archivo .mdf, .ndf y .ldf, pero estas extensiones ayudan a identificar las distintas clases de archivos y su uso.

NOMBRES DE ARCHIVO LÓGICO Y FÍSICO  Los archivos de SQL Server tienen dos nombres: logical_file_name LOGICAL_FILE_NAME es el nombre que se utiliza para hacer referencia al archivo en todas las instrucciones Transact-SQL. El nombre de archivo lógico tiene que cumplir las reglas de los identificadores de SQL Server y tiene que ser único entre los nombres de archivos lógicos de la base de datos. os_file_name

 

 

Ing. Rosa Navarrete 

9  Bases de Datos    OS_FILE_NAME es el nombre del archivo físico que incluye la ruta de acceso al directorio. Debe seguir las reglas para nombres de archivos del sistema operativo.

TAMAÑO DE ARCHIVO  Los archivos de SQL Server pueden crecer de forma automática a partir del tamaño especificado inicialmente. Cuando se define un archivo, se puede especificar un incremento de crecimiento. Cada vez que se llena el archivo, el tamaño aumenta en la cantidad especificada. Si hay varios archivos en un grupo de archivos, no crecerán automáticamente hasta que todos los archivos estén llenos. A continuación, el crecimiento tiene lugar por turnos. Cada archivo también puede tener un tamaño máximo especificado. Si no se especifica un tamaño máximo, el archivo puede crecer hasta utilizar todo el espacio disponible en el disco. Esta característica es especialmente útil cuando SQL Server se utiliza como una base de datos incrustada en una aplicación para la que el usuario no dispone fácilmente de acceso a un administrador del sistema. El usuario puede dejar que los archivos crezcan automáticamente cuando sea necesario y evitar así las tareas administrativas de supervisar la cantidad de espacio disponible en la base de datos y asignar más espacio manualmente.

Grupos de archivos de una base de datos

Los objetos y archivos de una base de datos se pueden agrupar en grupos de archivos con fines de asignación y administración. Hay dos tipos de grupos de archivos: Principal El grupo de archivos principal contiene el archivo de datos principal y los demás archivos asignados específicamente a otro grupo de archivos. Todas las páginas de las tablas del sistema están asignadas al grupo de archivos principal. Definidos por el usuario Los grupos de archivos definidos por el usuario son los grupos de archivos especificados mediante la palabra clave FILEGROUP en la instrucción CREATE DATABASE o ALTER DATABASE. Los archivos de registro nunca forman parte de un grupo de archivos. El espacio del registro se administra de forma independiente del espacio de datos. Ningún archivo puede pertenecer a más de un grupo de archivos. Las tablas, los índices y los datos de objetos grandes se pueden asociar a un grupo de archivos específico. En este caso, todas sus páginas se

 

 

Ing. Rosa Navarrete 

10  Bases de Datos    asignarán a dicho grupo de archivos o se pueden crear particiones en las tablas e índices. Los datos de las tablas e índices con particiones se dividen en unidades y cada una de ellas se puede colocar en un grupo de archivos independiente de una base de datos. Para obtener más información acerca de las tablas e índices con particiones, vea Tablas e índices con particiones. Un grupo de archivos de cada base de datos se designa como grupo de archivos predeterminado. Cuando se crea una tabla o un índice sin especificar un grupo de archivos, se supone que todas las páginas se asignarán a partir del grupo de archivos predeterminado. Solo un grupo de archivos puede ser el predeterminado en un momento dado. Los miembros del rol fijo de base de datos db_owner pueden cambiar el grupo de archivos predeterminado de un grupo a otro. Si no se especifica ningún grupo de archivos predeterminado, se considera como tal al grupo de archivos principal.

EJEMPLO DE ARCHIVOS Y GRUPOS DE ARCHIVOS En el siguiente ejemplo se crea una base de datos con una contraseña de SQL Server. La base de datos tiene un archivo de datos principal, un grupo de archivos definido por el usuario y el archivo de registro. El archivo de datos principal está en el grupo de archivos principal y el grupo de archivos definido por el usuario tiene dos archivos de datos secundarios. Una instrucción ALTER DATABASE hace que el grupo de archivos definido por el usuario sea el grupo predeterminado. A continuación, se crea una tabla que especifica el grupo de archivos definido por el usuario.

USE master;  GO  ‐‐ Create the database with the default data  ‐‐ filegroup and a log file. Specify the  ‐‐ growth increment and the max size for the  ‐‐ primary data file.  CREATE DATABASE MyDB  ON PRIMARY    ( NAME='MyDB_Primary',      FILENAME=         'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\ MyDB_Prm.mdf',      SIZE=4MB,      MAXSIZE=10MB,      FILEGROWTH=1MB),  FILEGROUP MyDB_FG1    ( NAME = 'MyDB_FG1_Dat1',      FILENAME =         'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\ MyDB_FG1_1.ndf',      SIZE = 1MB,      MAXSIZE=10MB,      FILEGROWTH=1MB),    ( NAME = 'MyDB_FG1_Dat2',      FILENAME = 

 

 

Ing. Rosa Navarrete 

11  Bases de Datos           'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\ MyDB_FG1_2.ndf',      SIZE = 1MB,      MAXSIZE=10MB,      FILEGROWTH=1MB)  LOG ON    ( NAME='MyDB_log',      FILENAME =         'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\ MyDB.ldf',      SIZE=1MB,      MAXSIZE=10MB,      FILEGROWTH=1MB);  GO  ALTER DATABASE MyDB     MODIFY FILEGROUP MyDB_FG1 DEFAULT;  GO    ‐‐ Create a table in the user‐defined filegroup.  USE MyDB;  CREATE TABLE MyTable    ( cola int PRIMARY KEY,      colb char(8) )  ON MyDB_FG1;  GO 

ARQUITECTURA LÓGICA DEL REGISTRO DE TRANSACCIONES  El registro de transacciones de SQL Server funciona desde el punto de vista lógico como si fuese una cadena de entradas de registro. Cada entrada del registro está identificada por un número de secuencia de registro (LSN, Log Sequence Number). Las nuevas entradas del registro se escriben al final lógico del registro con un LSN mayor que el de las entradas anteriores. Las entradas del registro se almacenan en la secuencia en la que se crean. Cada entrada del registro contiene el Id. de la transacción a la que pertenece. Por cada transacción, las entradas del registro asociadas a dicha transacción se vinculan individualmente en una cadena con punteros hacia atrás, para acelerar así la reversión de la transacción. Los registros de modificaciones de datos registran la operación lógica llevada a cabo o las imágenes anterior y posterior de los datos modificados. La imagen anterior es una copia de los datos antes de llevar a cabo la operación; la imagen posterior es una copia de los datos después de haber realizado la operación. Los pasos para recuperar una operación dependen del tipo de registro:

•

 

Registro de la operación lógica

 

Ing. Rosa Navarrete 

12  Bases de Datos    • • •

Para poner al día la operación lógica, se vuelve a ejecutar la operación. Para revertir la operación lógica, se ejecuta la operación lógica inversa.

Registro de las imágenes anterior y posterior

• •

Para poner al día la operación, se aplica la imagen posterior. Para revertir la operación, se aplica la imagen anterior.

En el registro de transacciones se registran muchos tipos de operaciones. Entre las operaciones se incluyen:

• •

El inicio y el final de cada transacción. Todas las modificaciones de los datos (inserción, actualización y eliminación). Esto incluye las modificaciones de las tablas, incluidas las tablas del sistema, hechas por procedimientos almacenados del sistema o por instrucciones del lenguaje de definición de datos (DDL).

• •

Las asignaciones o cancelaciones de asignación de páginas y extensiones. La creación o eliminación de una tabla o un índice.

También se registran las operaciones de reversión. Cada transacción reserva espacio en el registro de transacciones para asegurarse de que existe suficiente espacio de registro para admitir una reversión provocada por una instrucción de reversión explícita o cuando se produce un error. La cantidad de espacio reservado depende de las operaciones realizadas en la transacción, pero normalmente equivale a la cantidad de espacio utilizado para cada operación de registro. Este espacio reservado se libera cuando se completa la transacción. La sección del archivo de registro a partir de la primera entrada de registro que debe estar presente para una reversión correcta en toda la base de datos hasta la última entrada de registro escrita se denomina parte activa del registro o registro activo. Se trata de la sección del registro necesaria para una recuperación completa de la base de datos. No se puede truncar ninguna parte del registro activo.

 

 

Ing. Rosa Navarrete