Modulo 7 (3 Creacion DB) Sybase

MMóódulo dulo 77AdministraciAdministracióón de bases de datos n de bases de datos

CreaciCreacióónn de Bases de Dde Bases de Datosatos

Armando Vega A.Armando Vega A.

MMóódulo 7 dulo 7 AdministraciAdministracióón de base de datosn de base de datos

Objetivos del módulo

Crear y modificar base de datosEntender las opciones de base de datosMonitorear el espacio de las bases de datosManejar la asignación de espacio del log de transaccionesEntender el mapeo de bases de datos a tablas del sistema

sysdatabasessysdevices


Mapa del Módulo

Creando y Dandoles Tamaño a las Bases de DatosPropiedad de la Base de DatosDesplegando Información de la Base de DatosPersonalizando Bases de DatosUso del EspacioExpansión de la Base de Datos


Que sucede cuando se crea unabase de datos?

model masterNew database

Logical device

Physical database


create database

Sintaxis:

Ejemplos:

size es especificado en megabytes

create database database_name[on database_device [= size]

[,database_device = size]]...][log on database_device = [size]]...]

(1) create database pubs2 (2) create database salesdb on data_dev1=5(3) create database salesdb on data_dev1=5

log on log_dev1 = 2


Prerequisitos al crear base de datos

Antes de crear base de datos, decida:El tamaño de la base de datosLa ubicación y el espacio necesarioSi un dispositivo de log es necesario y, si lo es, de que tamaño

Se debe estar en la base de datos master para ejecutar el comando create database

Database name pubs2Database size 5 MB

Location of log log_devDatabase location data_dev


Tamaño de una base de datos

El tamaño es en megabytes, minimo 2MBFácil de extender, no puede estar encogida

Para hacer más chica la base de datos , se debe volverla a crear y copiar datos de nuevo con la utilidad de copiado bcp

Al estimar el tamaño de una base de datos, considereprincipalmente:

TablasIndicesLog de transacciones

Deje algún espacio libre, dependiendo de la actividadanticipadaUse sp_estspace para estimar el tamaño de las tablas y susíndices


sp_estspace

Ejemplo, salida parcial:

sp_estspace titles, 10000

name type idx_level Pages Kbytes---------------------------------------------------------------------------------titles data 0 983 1966titleidind clustered 0 7 14titleidind clustered 1 1 2titleind nonclustered 0 279 558titleind nonclustered 1 9 18titleind nonclustered 2 1 2

Total_Mbytes: 2.50

(followed by summary data for titleidind, titleind)


Tamaño del log

El tamaño del Log depende de la actividad (tipo y cantidad de transacciones) y la frecuencia de los respaldos

Un buen punto de partida: 10-25% del tamaño global de la base de datosTodos los inserts, deletes, y updates son registradosPara select into y fast bulk copy: solo la asignación y desasignación de espacio son registrados

Simule correr aplicaciones con el mismo número de usuarios; entonces mida el uso del log usando dbccchecktable(syslogs)El log es fácil de extender, imposible encoger


Colocando el log en un dispositivodiferente

Coloque el log en un dispositivo diferente

Deja sus respaldos del log de transacciones en separadoAyuda en el monitoreo del uso de espacio y manejo del espacioPermite el duplicado de disco del log para un recuperación a tiempo

data_dev1 log_dev1

database


create database: Sintaxis

Ejemplos:

Nota: Es recomendado poner el log en un dispositivo diferente

create database database_name[on {default | database_device} [= size]

[,database_device [= size]]...][log on database_device [= size,...]

[, database_device [= size]]...][with override][for load]

(1) create database pubs2 on default = 4(2) create database mydb on default = 3,

data_dev1 = 2(3) create database salesdb on sales_dev1 = 2

log on sales_dev1 = 2 with override


Mapa del Módulo



Quién crea las bases de datos?

Los administradores del sistema (logines con el rol SA) pueden crear bases de datosEl login que crea una base de datos es propietario de ellainicialmente


Transfiriendo la propiedad de la base de datos

Para transferir la propiedad de la base de datos, accese a esabase da datos usando use dbname seguido de sp_changedbowner en otro loteSintaxis: sp_changedbowner login_name

El login debe ser válido en el servidor y no puede estar comousuario en la base de datos a transferir la propiedad

Ejemplo:

Nota: La propiedad de la base de datos master no puedetransferirse

use productsdbgosp_changedbowner fredgo


Otorgando permisos para create database

Los administradores del sistema pueden otorgar permisos de create database a usuarios especificos:

El control de uso de disco puede estar restringido en la autorización en la creación de base de datos

grant create database to mary


Efectos sobre las tablas del sistema

Cuando se crea una base de datos, las siguentes tablas del sistema en master son afectadas:

sysdatabasesContiene un registro por cada base de datos en el SQL Server, incluyendo el nombre de la base de datos y su propietariosysusagesContiene un registro para cada fragmento del dispositivo paracada base de datos, indicando el tamaño y la dirección del disco del comienzo lógico para ese fragmento

Para desplegar esta información, consulte estas tablas o ejecute sp_helpdb


Mapa del Módulo



Desplegando información de base de datos

Ejemplo de salida de sp_helpdb

Cuando es seguido por un nombre de base de datos, sp_helpdb reporta información sobre esa base de datos

name db_size owner dbid ...status--------------------------------------------------------------------------------------master 8.0 MB sa 1 ... no options setmodel 2.0 MB sa 3 ... no options setpubs2 2.0 MB sa 4 ... no options setsalesdb 6.0 MB sa 5 … select into/bulkcopytempdb 2.5 MB sa 2 … select into/bulkcopy


Desplegando ubicación de la base de datos

Cómo sabe SQL Server en que dispositivo esta una base de datos?

(continued…)

master database

sysdevicessysdevices(low, high, name, phyname, mirror)

sysusagessysusages(dbid, . . . , size, vstart), . . . , size, vstart)

sysdatabasessysdatabases(name, dbid, . . . )


Desplegando ubicación de la base de datos

Ejemplo de selects from sysusages, sysdevices:

Para desplegar el mapeo de uno de estos fragmentos de dispositivos:

select * from sysusages where dbid = db_id("smalldb")

dbid segmap lstart size vstart pad unreservedpgs5 3 0 1024 16777216 NULL 6805 4 1024 512 33554432 NULL 496

dbid segmap lstart size vstart pad unreservedpgs5 3 0 1024 16777216 NULL 6805 4 1024 512 33554432 NULL 496

select low, high, name, phyname, mirrorname from sysdeviceswhere 16777216 between low and high

low high name physname mirrorname16777216 16782335 data_dev1 /syb/data_dev1.dat NULLlow high name physname mirrorname16777216 16782335 data_dev1 /syb/data_dev1.dat NULL


Desplegando ubicación de la base de datos y uso

sp_helpdb db_name despliega ubicación y uso del disco paraesa base de datosEjemplo: sp_helpdb smalldb

name db size owner dbid created statussmalldb 3.0 MB sa 5 May 5, 1993 no options set

device fragments size usage free kbytesdata_dev1 2.0 MB data only 1376log_dev1 1.0 MB log only 1008

name db size owner dbid created statussmalldb 3.0 MB sa 5 May 5, 1993 no options set

device fragments size usage free kbytesdata_dev1 2.0 MB data only 1376log_dev1 1.0 MB log only 1008


Mapa del Módulo



Personalizando base de datos

Cuando se crea una base de datos, el contenido de model escopiado a la nueva base de datos

Puede personalizar model a que contenga procedimientosalmacenados, tablas, reglas, tipos de datos de usuario, privilegios, y opciones para todas las futuras bases de datosSolo el administrador del sistema puede actualizar model

model new database


Fijando las opciones de base de datos

Usando sp_dboption, las siguientes opciones de base de datos pueden ser fijadas:

abort tran on log fullallow nulls by defaultdbo use only ddl in tranidentity in nonunique indexno chkpt on recovery no free space acctgread only select into/bulkcopy/pllsortsingle user trunc log on chkpt

(continued…)


Fijando las opciones de base de datos

SSO pueden habilitar o deshabilitar free-space accounting; losdemás serán fijados por el dueño de la base de datos (o un SA actuando como un dbo)Las opciones no podrán ser cambiadas en la base de datosmaster


sp_dboption

Sintaxis:

Ejemplo de uso:

Cualquier usuario puede usar sp_helpdb para desplegar lasopciones actuales de la base de datos

sp_dboption [dbname, option_name, {true|false}]

use master /* must be in master */gosp_dboption "smalldb", "read only", truegouse smalldb /* must use database and... */gocheckpoint /* checkpoint the database */go


Mapa del Módulo



Monitoreando el uso del espacio

La base de datos, tanto el segmento de log como el de datos, pueden llenarse tanto como uso se le de a la base de datos

Si esto sucede, todas las modificaciones de datos son suspendidas

Puede usar las siguientes herramientas para monitorear el usodel espacio en la base de datos:

sp_helpdbsp_helpsegmentsp_spaceusedThreshold Manager


Que hacer cuando no se tieneespacio

Si no se tiene espacio en el log, se tiene que truncarloPosibles soluciones: Dump/truncate frecuentemente

Si no se tiene espacio en los segmentos de datos, intenteliberar espacio eliminando objetos no usadosOtra alternativa: Extender la base de datos (datos y/o log)

ExpansionDatabase

Logical device


Mapa del Módulo



Extendiendo una base de datos

Usando alter database, los propietarios de base de datos y administradores del sistema asignan espacio adicional a unabase de datos sobre el mismo o diferentes dispositivos

El tamaño de la base de datos no puede ser disminuido

ORExpansion

data_dev1 data_dev2

Expansion

data_dev1


alter database

Sintaxis:

El tamaño es especificado como incrementos de espacioadicional; predeterminado, 1MBEjemplos:

alter database database_name[on {default | database_device} [= size]][log on database_device [= size]]

alter database pubs2alter database pubs2 on data_dev1 = 3alter database pubs2 on default = 2

(continued…)


alter database

Ejemplo de secuencia: create – alter – altercreate database salesdb on data_dev1 = 5alter database salesdb on data_dev2 = 2alter database salesdb on data_dev3 = 1


Tips para la expansión de una base de datos

Puede expandir una base de datos mientras este en usoSolo se puede expandir la base de datos master en el dispositivo masterSi tempdb o model es expandido y el dispositivo master esrenconstruido entonces se vuelve a expandir

No haga a model más grande que tempdbSi model es más grande que tempdb, SQL Server no reiniciará


Separando el log y moverlo a un dispositivo diferente

Para una base de datos creada sin la opción log on (esto es, sin un log separado), haga lo siguiente:

Respalde el log al truncarloModifique la base de datos hacia un nuevo dispositivoEjecute sp_logdevice para hacer ese dispositivo en dispositivode log

Realice estos pasos con un minimo de tiempo entre cada unopara evitar que los registros sean escritos al dispositivooriginal

(continued…)


Separando el log y moverlo a un dispositivo diferente

Efecto:Las paginas antiguas serán asignadas al dispositivo anteriorLos registros existentes serán desasignados tanto el log sea truncadoResultado: El log crece en el nuevo dispositivo


Expandiendo el log

En el mismo dispositivo:

En un nuevo dispositivo:

alter database pubs2 log on log_dev1 = 1

alter database pubs2 log on log_dev2 = 4

Expansion

data_dev1 log_dev1 log_dev2

data_dev1 log_dev1


Eliminando base de datos

Los DBO y SAs pueden borrar una base de datos ejecutandodrop databaseLa base de datos no debe estar en usoCuando borra una base de datos:

Base de datos experimentales o antiguas, se libera el espacioAntes se trata de recuperar base de datos dañadas


Repaso Creación de Bases de Datos

1. Qué sucede cuando crea una base de datos?2. Nombre algunas consideraciones para darle el tamaño a una base de datos.3. Que procedimiento para ayudarle a estimar tamaños de tablas e índices?4. Por qué es buena práctica poner el log en un dispositivodistinto?5. Qué procedimiento despliega información acerca de lasbases de datos?6. Que hacer si el espacio del archivo de log se agota? En la base de datos?


Lab 4: Creando Bases de Datos

Descripción del EjercicioHasta ahora...

Ha creado los dispositivos de base de datosMetas

Crear una base de datosEntender las tablas del sistema sysdevices, sysusages y sysdatabases

(continued…)


Lab 4: Creando Bases de Datos

Tareas GeneralesCrear una base de datos de 6 MB (4 MB datos, 2 MB log)Verificar que la base de datos haya sido creadaActive o desactive la opción dbo use only en la base de datosEscriba consultas que usen sysdevices, sysusagessysdatabases

Lab SetupCrear sólo una base de datos a la vez

Documents

Modulo 7 (3 Creacion DB) Sybase