2. administracion de archivos de una bd

7/24/2019 2. Administracion de Archivos de Una BD

1/49

MAESTRAENTECNOLOGAS DE

LAINFORMACINAdministracin de Archivos de una Base de DatosSQL Server.


2/49

Realiz: M. Lic. Octavio Cruces M.

INTRODUCCINALAESTRUCTURADEDATOS

Cmo se almacenan los datos ?a) Todas la Bases de datos tienen:

1. Archivo de Datos (.mdf)2. Log de Transacciones (.ldf)

3. Archivo de datos secundario (.ndf)

La ruta donde se encuentran dichos archivos es:c:\archivos de programa\Microsoft Sql Server\ MSSQLn\Data\

Donde n es un nmero nico identificador de la instancia de

SQL Server

Archivo de Datos:.mdf .ndf

LOG de transacciones:.ldf


3/49


INTRODUCCINALAESTRUCTURADEDATOS

Cmo se almacenan los datos ?b) Los datos son almacenados en bloques de 8 Kilobytes de

espacio en disco contiguo llamados pginas, esto significaque SQL Server puede almacenar 128 pginas por 1

megabyte (MB).c) Cuando se genera una Base de Datos, una copia de la Base

de datos Model, el cual incluye tablas de sistema, escopiada a la Base de Datos y el resto de la Base de datos sellena con pginas en Blanco.

d) El LOG de transacciones contiene informacin necesariapara la recuperacin de la Base de Datos en eventos dondefalla el sistema.


4/49


COMOTRABAJAELLOG DETRANSACCIONES

1. Modificaciones aDatos son enviadaspor una aplicacin

2. Se graba enmemoria cache lainformacin

4. Se realiza la escritura enel archivo de datos (Commit)

3. Las modificaciones songrabadas en el LOG detransacciones en Disco.


5/49


COMOTRABAJAELLOG DETRANSACCIONES

SQL Server registra cada transaccin en un LOG de transacciones

manteniendo la consistencia de la Base de Datos y aadiendo modelo de

recuperacin. SQL Server registra modificaciones en el LOG de disco

antes de que ellas sean escritas en la Base de Datos.


6/49


CONSIDERACIONESPARALALOCALIZACINDELOSARCHIVOS

Tipos de Archivos

Primaria

Secundaria

LOG de Transacciones

Localizacin de archivos

Colocar los archivos en diferentes discos para mejorperformance y modelo de recuperacin.


7/49Realiz: M. Lic. Octavio Cruces M.


Tipos de Archivos Primaria:

Contiene informacin de la Base de Datos y apunta a otrosarchivos de la Base de datos

Los Objetos de la BD y los usuarios pueden almacenarse enestos archivos primarios Cada Base de Datos tiene un archivo primario. La extensin del archivo primario de datos es .mdf

Secundaria Se puede almacenar informacin y usuarios en archivos

secundarios.Algunas Bases de Datos son muy grandes y se debern de

separar en archivos secundarios




Secundaria La extensin de los archivos secundarios es (.ndf)

LOG de transacciones Cada Base de Datos debe tener un LOG de transacciones El nombre de la extensin del LOG de Transacciones es

(.ldf)



EJEMPLO

a) Creacin de Base de datos (Transact SQL Server).VentasTipo de archivo Primario

ventas.mdf

Log de Transaccionesventas_log.ldf

____________________________________________________CREATE DATABASE ventas ON PRIMARY

(NAME = N'ventas',filename=N'c:\sqlserver\DATA\ventas.mdf')LOG ON(NAME = N'ventas_log',

filename=N'c:\sqlserver\DATA\ventas.ldf')GO


10/49


EJEMPLO

b) Creacin de Base de Datos (SQL ServerManagement Studio)

Salle

Tipo de archivo PrimarioSalle.mdfTipo de Archivo Secundario

Salle2.ndx

Log de transaccionesSalle_log.ldf


11/49



Localizacin de los Archivos

Proveer mejor rendimiento e implementa tolerancia a

fallas administrando la localizacin de los archivos de

datos y del Log de Transacciones en el Disco.

El performance se refiere en parte a la velocidad de

lectura y escritura de las operaciones.

La tolerancia a fallas se refiere a la habilidad de el

sistema para continuar funcionando sin perdida de

datos cuando alguna parte del sistema falla.


12/49



Recomendaciones

Para Bases de Datos grandes, se debe dividir en

archivos fsicos dependiendo el nmero de discos y

agrupar los archivos en FILEGROUPS.

Crear LOG de Transacciones en Discos separados de

los archivos de datos o usar Arreglo de Discos RAID.

RAID Provee tolerancia a Fallas. Localizar la Base de Datos TEMPDB en un

subsistema de mayor velocidad I/O para asegurar un

optimo performance


13/49


FILEGROUPS

Son arreglos de archivos utilizados para simplificar la localizacin delos datos.

TIPOS DE FILEGROUPS.

SQL Server tiene un Filegroup Primario y puede contener Filegroupsdefinidos por los usuarios.

El Filegroup primario contiene el archivo de datos primario con lastablas de sistema. Tambin puede contener archivos secundariosdefinidos por el usuario y que no estn localizados en algn otroFilegroup.

Los FileGroup definidos por el usuario consisten en archivo de datosque son agrupados con propsitos administrativos y por localizacin


14/49


FILEGROUPS

El Log de Transacciones no forma parte de unFileGroup.


15/49


FILEGROUPS


16/49


CONSIDERACIONESPARACREARUNFILEGROUP

Existen dos razones para controlar la localizacinde los datos:1. Performance2. Administracin

Se puede dividir las tablas de sus ndicesnonclustered para optimizar el acceso a datos.

Crear Filegroups para separar los datos condiferentes requerimientos de administracin. Porejemplo separar las tablas con datos que semodifican frecuentemente de las que sonrelativamente estticas.


17/49


CREACINDEBASEDEDATOS

Se puede crear una Base de Datos usando SQL Server

Management Studio o por medio de comandos de

TRANSACT SQL.


18/49



TRANSACT SQL SERVERCREATE DATABASE database_name[ CONTAINMENT = ([PARTIAL | NONE ])][ ON[ PRIMARY ] [ ( NAME = Logical_File_name, FILENAME = os_file_name

[, SIZE= size [ KB | MB | GB | TB] ][, MAXSIZE= { maxsize [ KB | MB | GB | TB] | UNLIMITED } ][, FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ) ]

[ FILEGROUP filegroup_name [DEFAULT]( NAME = logical_file_name, FILENAME = os_file_name[, SIZE = size [ KB | MB | GB | TB] ][, MAXSIZE= { maxsize [ KB | MB | GB | TB] | UNLIMITED } ][, FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ) ][, ..n] ]

[LOG ON[ ( NAME = logical_file_name, FILENAME = os_file_name[, SIZE = size [ KB | MB | GB | TB] ][, MAXSIZE= { maxsize [ KB | MB | GB | TB] | UNLIMITED } ][, FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ) ][, ..n] ]

[COLLATE collation_name ] ]


19/49



TRANSACT SQL SERVER

ARGUMENTOS:

Database_name: Nombre de la Base de Datos.ON: Permite definir los archivos usados para almacenar los datosPRIMARY: Especifica que la lista de elementos siguientes esta asociadadefine el archivo principalNAME logical_file_name: Especifica el nombre lgico para un archivo.FILENAME os_file_name Especifica el nombre del archivo de sistemaOperativo.SIZE: Especifica el valor inicial del archivo

MAXSIZE: Especifica el tamao mximo que puede crecer el archivo.UNLIMITTED: Especifica que el archivo puede crecer hasta que se llene eldisco duro.FILEGROWTH growth_increment: Especifica el incremento automtico decrecimiento de un archivo.FILEGROUP filegroup_name: nombre lgico del filegroupLOG ON: Listado de archivos de LOG de transacciones separados por coma.


20/49



Ejemplo: Crear Carpeta C:\sql2012\Data

Crear Carpeta C:\sql2012\Log

Crear la Base de Datos: Personal con las sig. caractersticas


21/49


UNICODE

Estndar de codificacin de caracteres

Simplifica la localizacin de software

Provee procesamiento multilengua de caracteres

Requiere el prefijo N en constantes LEN( )Nmero de caracteres

DATALENGTH( )Nmero de Bytes

Ejemplo:

DECLARE @saludo nvarchar(10)

SET @saludo=Hola;

SELECT @saludo


22/49


COLLATION

Cdigo de pgina que es utilizado para almacenar

datos Non-Unicode

Reglas de cmo ordenar y comparar valores paratipos Non-Unicode

Collation puede ser configurado a nivel instancia,

Base de datos y a nivel columna


23/49


COLLATION

Demostracin (Demo_Collation.sql)

Distingueentre

Maysculas yMinsculas

DistinguirAcentos

DistinguirKana

DistinguirAncho

CS AS KS WSCI AI


24/49


ELIMINACINDEBASEDEDATOS

Requerimientos: Exista la Base de datos Que no este en Uso

Con la cuenta que se desea eliminar deber decontar con derechos de: OWNER DDLADMIN SYSADMIN

Comando Transact-SQL:DROP DATABASE NombreBaseDeDatos


25/49


AMPLIACINODISMINUCINDEBASEDEDATOS.

Incrementar el Tamao de la Base de Datos:ALTER DATABASE database_nameMODIFY FILE( NAME = Logical_file_name

SIZE = size [ KB | MB | GB | TB] )

Ejemplo:ALTER DATABASE personal

MODIFY FILE

( NAME = NPersData1,SIZE = 10 MB )


26/49



Reducir el tamao de la Base de Datos o LOG de transacciones:DBCC SHRINKFILE( { NombreArchivo | file_id }

{ ,EMPTYFILE ]| [ [ , target_size ] [, { NOTRUNCATE | TRUNCATEONLY } ] ]

} )

EMPTYFILE: Mueve todos los datos en este archivo a otrosarchivos en el mismo filegroup

Target_size: Es el tamao para el archivo, en megabytes,expresado como un nmero entero

NOTRUNCATE: Mueve las pginas asignadas del final de un

archivo de datos a pginas no asignadas del principio delarchivo especificando o sin especificar target_percent. Elespacio disponible del final del archivo no se devuelve alsistema operativo y el tamao fsico del archivo no cambia. Portanto, si se especifica NOTRUNCATE, parecer que el archivono se reduce.


27/49



TRUNCATEONLY: Devuelve al sistema operativo todo elespacio disponible del final del archivo, pero no realizaningn movimiento de pgina dentro del archivo

Ejemplo:USE Personal

GO

DBCC SHRINKFILE

(NPersData1,8)


28/49


VERIFICARTAMAOLOG

1)DBCC sqlperf(

logspace)

2)Use DatabaseNamegoSelect name, size/128,filename from sysfilesgo


29/49


SCHEMAS


30/49


SCHEMAS

Un Schema es un namespace para los objetos de la Base de Datos

Beneficios:

Mayor flexibilidad cuando organizamos objetos de la Base de

Datos en namespaces.

Simplifica la administracin de Permisos. Los permisos se

asignan al schema y no a cada uno de las cuentas de usuario.Al borrar usuarios no es necesario renombrar todos los objetos a

los que era propietario el schema.


31/49


CREACINDESCHEMAS


33/49


SCHEMAS

Ejemplo:

CREATE SCHEMAMarketing AUTHORIZATION dbo

GO


34/49


ADMINISTRACINDEBASEDEDATOS

Opciones de Configuracin de las Bases de Datos.

Categora Propsito

Automatic Manejo de estadsticas, cerrar BD, Depuracinde BD

Cursor Manejo de CursoresRecovery Modelo de Recuperacin de la BD

Service Broker Habilita o deshabilita Service Broker

State Estado de la BD, si es de Solo Lectura,Restricciones de Acceso (Single, Mltiple,

Restricted (Miembros db_owner,dbcreator,sysadmin)


35/49


ATTACHYDETACHDELABASEDEDATOS

Los comandos Attach y Detach de la Base de datosson tiles para mover los archivos de datos y logde transacciones a:

Una diferente localidad (Unidad de disco). Otra instancia de SQL Server. Diferente Servidor.


36/49


DETACH.

Pasos para realizar el Detach de la Base de datos:1. Presionar Botn derecho del mouse sobre la

Base de datos.2. Seleccionar Tareas3. Seleccionar opcin detach

Nota. Para poder realizar la operacin de

DETACH No debern de existir conexionesexistentes sobre la base de datos.


37/49


DETACH


38/49


ATTACH

Despus de cambiar los archivos de la Base dedatos (.mdf / .ndf / .ldf) a la nueva ubicacin,realizamos la tarea de ATTACH paranuevamente levantar la Base de datos sobre la

instancia de SQL Server.

1. Presionar Botn derechos sobre DATABASES.2. Seleccionar TASK3. Seleccionar ATTACH4. Buscar el archivo .mdf en la nueva ubicacin


39/49


ATTACH


40/49


ATTACH


41/49



FIN


42/49


SERVICEBROKER

Agente dedicado a la comunicacin entreaplicaciones (Envo de mensajes) Asncrono Confiable procesamiento de consultas Confiable coleccin de informacin Procesamiento distribuido del lado del servidor de

aplicaciones del Cliente Tecnologa SOA


43/49


SERVICEBROKER

Ejemplo.

1. Aplicaciones realizan una accin aunque no este fsicamente la persona2. Una regeneracin de Orden de Compra automtica, no requiere que

alguien se de cuenta de que ya no hay producto.

Aplicacin deCliente

(ASP.NET)

Agente de Service Broker


44/49


PRCTICA3

Objetivos:

1) Crear una Base de datos utilizando SQL Server ManagementStudio

2) Crear una Base de datos Utilizando Transact-SQL

Instrucciones:

1. Ejecutar SQL Server Management Studio 20122. En la Caja de Dialogo de Connect to Server especificar los

valores que se mencionan a continuacin:


45/49


PRCTICA3

3. Si el objeto Explorer no esta visible, en el menVIEW, clickObject Explorer

4. En Object Explorer, Presionar botn derecho del mouse sobreDatabases, y entonces selecciona New Database

5. En la caja de dialogo de New Database, especificar los valores de

la siguiente tabla

Propiedad Valor

Tipo de Servidor Database Engine

Authenticacin Windows

Propiedad Valor

Nombre de la Base de Datos TestDB

Tamao inicial para el archivode la BD TestDB

5 MB


46/49


PRCTICA3

6. Para el archivo de Datos TestDB, en la columna Autogrowth,

seleccionar el botn (..)

7. En la caja de dialogo de Change Autogrowth for TestDB,

limpiar el check box de Enable Autogrowth y luego presionar

OK.

8. Repetir los pasos 6 y 7 para el archivo TestDB_Log

9. En la caja de dialogo New Database, seleccionar OKpara crear

la Base de Datos.

10. En la carpeta de Databasesverificar que aparece la nueva Base

de datos TestDB.


47/49


PRCTICA3

Crear una Base de datos utilizando Transact-SQL

1. En SQL Server Management Studio, en la barra de herramientas

seleccionar el botn de New Query2. En el ventana que abre en blanco escribir el siguiente cdigo:

CREATE DATABASE TransactTestDB ON PRIMARY(Name = NTransactTestDB,FILENAME=NC:\SQL2012\DATA\TransactTestDB.mdf,SIZE = 5120KB, FILEGROWTH=10%)LOG ON(Name = NTransactTestDB_log,FILENAME=NC:\SQL2012\DATA\TransactTestDB_log.ldf,SIZE = 1024KB, FILEGROWTH=1024KB)GO


48/49


PRCTICA3

Crear una Base de datos utilizando Transact-SQL

3. En la barra de herramientas seleccione la opcin EXECUTE y

verifique que se completa correctamente el comando

4. En Object-Explorer,Seleccionar botn derecho del mouse sobre

Databases, y seleccionar Refresh.

5. Expandir el folder de Databases y verificar que la base de datos

TransactTestDB fue creada.

6. Presionar botn derecho sobre la Base de datos TransactTestDB y

seleccionar la opcin propiedades, verificar la configuracin

asignada.


49/49


Opciones de Configuracin de las Bases de Datos.

2. administracion de archivos de una bd

Documents