Download ConceptosGenerales_v2

1. Aspectos Generales de la Administración del Sistema
La administración de Bases de Datos incluye tareas como:






Instalación de motores de SQL Server y Backup Server
Asignación de roles y permisos a usuarios de SQL Server
Gestión y monitoreo de espacio en disco, memoria y conexiones
Copias de seguridad y restauración de bases de datos
Diagnóstico y solución de problemas
Afinación del sistema para optimizar el desempeño
Adicionalmente un administrador de sistema puede tener tareas como estimación de
espacio en disco con el objetivo de que los dispositivos de los usuarios no tengan
problemas. Para estas tareas será necesario comprender conceptos de umbrales (thresholds)
y alarmas.
Los conceptos adicionales de copias de seguridad y restauración de las mismas incluyen
donde y cada cuanto tiempo realizarlas. Al mismo tiempo instalación de los motores de
SQL Server y de Backup server para hacer copias de seguridad en servidores remotos.
Para poder administrar un sistema de base de datos es necesario conocer su arquitectura, es
decir, tanto los elementos que componen el sistema, como su funcionalidad e interacción.
Una vez establecidos estos puntos será posible establecer los procedimientos y políticas de
administración de un sistema de bases de datos.
1.1 Las tablas del sistema
El componente esencial de un sistema de bases de datos son las tablas y sus relaciones, por
esto, la gestión del sistema se basa en el manejo de tablas exclusivas del sistema en SQL
Server. Estas tablas se distinguen por su nombre (sysxxxxxxx). No es aconsejable
modificarlas por ISQL, ya que algunas no son tan fácilmente modificables. Es mejor
utilizar los procedimientos del sistema creados para tal fin (siempre y cuando se cuente con
los permisos necesarios). Existen algunas tablas son creadas por procesos del sistema de
manera dinámica y contienen información codificada, esta información no es visible
cuando la misma es consultada y puede originar errores de acceso, dejar objetos
inaccesibles, eliminar permisos ó terminar su sesión de usuario.
1.2 Procedimientos del sistema
Existen algunos procedimientos almacenados que proveen información sobre las tablas del
sistema, y estos son:
sp_commonkey
sp_configure
sp_dboption
sp_estspace
sp_help
sp_helpconstraint
sp_helpdb
sp_helpdevice
sp_helpgroup
sp_helpindex
sp_helpjoins
sp_helpkey
2
sp_helplanguage
sp_helplog
sp_helpremotelogin
sp_helprotect
sp_helpsegment
sp_helpserver
sp_helpsort
sp_helptext
sp_helpthreshold
sp_helpuser
sp_lock
sp_monitor
sp_spaceused
sp_who
1.3 Bases de datos del sistema
En un modelo de bases de datos, las componentes de funcionamiento a nivel de bases de
datos son bases de datos. Existen cuatro bases de datos que se instalan siempre con el
motor y dos más que son opcionales. Estas bases de datos son:
 La base de datos Master: Es la base de datos principal, desde la que se manejan las
otras, dado que contiene las tablas de organización .
 La base de datos Model: que es el patrón empleado en la creación de bases de datos.
 La base de datos de procedimientos sybsystemprocs: con los procedimientos
almacenados del sistema.
 La base de datos temporal tempdb: utilizada como espacio de trabajo en las
transacciones antes de hacerlo sobre los datos reales.
Otras dos bases de datos que son opcionales son:
 La base de datos sybsecurity: para ejecutar los procesos de auditoría de la base de
datos.
 La base pubs2: base de datos de ejemplo.
La base de datos Master, contiene información acerca de:
 Cuentas de Usuarios (en syslogins)
 Cuentas de usuarios remotos (en sysremotelogins)
 Servidores remotos con los que puede interactuar el servidor local (en sysservers)
 Procesos en ejecución (en sysprocesses)
 Variables de ambiente configurables en el sistema (en sysconfigures)
 Mensajes de error del sistema (en sysmessages)
 Las bases de datos existentes en SQL Server (en sysdatabases)
 El espacio de almacenamiento asignado para cada base de datos (en sysusages)
 Los dispositivos (cintas y discos) montados en el sistema (en sysdevices)
 Locks (candados) activos (en syslocks)
 Conjuntos de caracter (en syscharsets) e idiomas (en syslanguages)
 Usuarios con roles a nive de servidor (en sysloginroles)
 Roles de Servidor (en syssrvroles)
 Motores de SQL Server en línea (en sysengines)
3
Cuando se instala SQL Server, las bases de datos del sistema, los segmentos definidos por
el sistema y los dispositivos de base de datos se organizan como sigue:




Las bases de datos master , model y tempdb se instalan en el dispositivo master
d_master .
La base de datos sybsystemprocs se instala en el dispositivo que usted elija durante la
instalación.
Se crean tres segmentos en cada base de datos: system , default y logsegment.
El dispositivo de almacenamiento predeterminado para todas las bases de datos creadas
por usuarios es d_master .
Nota: Tras inicializar nuevos dispositivos para el almacenamiento predeterminado, retire
el dispositivo master del área de almacenamiento predeterminado con s p_diskdefault . No
almacene bases de datos ni objetos de usuario en d_master .

Si ha instalado la base de datos de auditoría, sybsecurity , la encontrará ubicada en su
propio dispositivo.
2. Roles en SQL Server
En SQL Server un rol se asemeja a un comportamiento,
2.1 Roles de Administración de Sistema y de Seguridad
En SQL Server los usuarios pueden recibir permisos especiales para operar y administrar el
sistema. Los roles son asignados a nivel de login y proveen a los usuarios de capacidades
para desempeñar tareas de administración. Para asignar y/o revocar roles se utiliza el
procedimiento sp_role.
Los roles existentes son:
 SA: Administrador del Sistema.
 SSO: Oficial de Seguridad del Sistema.
 Oper: Rol de Operador del Sistema.
Adicionalmente hay dos tipos de propietarios en el sistema:
 El propietario de la base de datos de usuario.
 El propietario de los objetos de base de datos.
El SA posee desempeña tareas específicas a nivel de aplicación (lo pueden tener ser varios
logins). Dentro de las tareas más representativas del SA estan:
 Instalación de SQL Server.
 Administración de almacenamiento
 Asignación de permisos a usuarios de SQL Server
 Creación de bases de datos de usuario
 Asignación y revocatoria del rol de SA (a otros usuarios, el mismo no puede hacerlo)
 Conformación de grupos para facilitar tareas de asignación de permisos y roles.
 Afinación de la configuración del sistema
 Diagnóstico de problemas
 Manejo de cuentas a nivel de login.
El SSO (System Security Officer) cumple con tareas como:
 Creación de cuentas a nivel de servidor
 Asignación y revocatoria de roles de SSO y Operador a los usuarios del sistema.
 Cambios de password para cualquier cuenta
 Administración de la auditoría del sistema
 Configurar la expiración de passwords de los logins
El Oper tiene permisos para ejecutar tareas de tipo rutinario como:
 Comandos de volcado
 Carga de bases de datos
 Backups
5
2.2 Propietarios en SQL Server
Las dos clases de propietario definidas en SQL Server son los propietarios de bases de
datos de usuario y los propietarios de objetos de base de datos.
Propietarios de Base de Datos
El propietario de una base de datos ingresa a SQL Server con el login de la cuenta que le
sea asignado y es reconocido por SQL Server como “dbo” en su Base de datos. El puede:
 Por medio del procecimiento sp_adduser adicionar usuarios a su base de datos.
 Dar permisos o revocarlos para crear objetos y ejecutar comandos en su base de datos.
El propietario de la base de datos tiene propiedad sobre todos los objetos de su base de
datos.
Propietarios de objetos de la base de datos
Los objetos de la base de datos son tablas, índices, vistas, valores por defecto, triggers,
procedimientos almacenados, reglas y restricciones. El usuario que los crea es el
propietario. Y el puede dar permisos a otros usuarios de la base de datos sobre objetos, sus
utilización y sus ejecuciones ó consultas.
2.3 Administración de roles en SQL Server
Después de hacer la instalación del SQL Server el usuario SA puede hacer creación de
logins de servidor para que otros usuarios puedan tener sus mismas funciones. Cabe hacer
la anotación que independientemente del login con el que se ingrese si tiene el rol SA, las
transacciones que realice quedan cargadas como SA.
El procedimiento sp_role pemite asignar y/o revocar roles de usuarios, su sintaxis es:
sp_role {"grant" | "revoke"},
{sa_role | sso_role | oper_role}, login_name
con el procedimiento sp_displaylogin es posible obtener información de una cuenta.
sp_displaylogin [ login_name]
3. Gestión de los recursos físicos
Para gestionar los recursos físicos es necesario conocer los elementos que permiten
gestionar el espacio, y los elementos que sirven de apoyo para estimar la cantidad de
espacio requerida. Los recursos físicos básicos para SQL Server son los dispositivos ya
que son la entidad principal de almacenamiento relacionada con la parte física. Existen tres
tipos de dispositivos básicos:
 Dispositivos para base de datos.
 Dispositivos espejo.
 Dispositivos para backups (cintas o discos).
3.1 Comandos de gestión de recursos
Hay algunos comandos que permiten manejar los dispositivos desde su inicialización hasta
su uso.
3.1.1 Inicialización de dispositivos de base de datos
Por dispositivo entendemos el contenedor de las tablas y los objetos del sistema de base de
datos. Este contenedor puede ser un archivo ó una partición del disco (partición RAW),
dependiendo del sistema operativo sobre el que se esté trabajando. Este contenedor debe
ser inicializado para que el sistema de base de datos lo reconozca como dispositivo válido,
es decir debe ser “formateado” de tal manera que sea accequible para SQL Server.
Los dispositivos pueden cumplir diversas funciones:
 Almacenar objetos del sistema.
 Asignarse como espacio disponible por defecto para la creación de bases de datos.
 Asignarse para almacenar el diario de transacciones de una base de datos.
Para determinar los parámetros de creación del dispositivo es necesario establecer qué uso
se le va a dar. Si el dispositivo va a ser asignado para la creación de una base de datos de
usuario primero hay que establecer qué espacio requiere, tanto la base de datos como el log
de transacciones ó si se han de colocar por separado. Las políticas de diseño de bases de
datos permiten establecer el tamañoque deberá tener la base de datos y por tanto el
dispositivo a crear.
Para inicializar un dispositivo se utiliza el comando disk init, cuya sintaxis es:
disk init
name = " device_name " ,
physname = " physicalname " ,
vdevno =
virtual_device_number
,
size =
number_of_blocks
[, vstart =
virtual_address
,
cntrltype =
controller_number
[, contiguous]
(
Sólo
para
]
OpenVMS)
7
donde los parámetros son:
name: nombre lógico del dispositivo y es el nombre con el que SQL Server lo identificará
una vez creado.
Physname: nombre físico del dispositivo, es decir, nombre del archivo o de la partición.
Vdevno: número virtual de dispositivo, que es un número que identifica unívocamente a un
dispositivo inicializado y en uso. Al eliminar este dispositivo éste número es liberado, pero
sólo esta disponible después de reinicializar el servidor.
Size: es el tamaño del dispositivo en bloques de 2K. Mínimo 512 bloques (1MB), máximo
1.048.576 bloques (2GB).
Vstart: hace referencia a una dirección virtual de arranque dentro del dispositivo una vez
inicializado.
Cntrltype: es requerido cuando hay necesidad de especificar un controlador (número de
controlador).
Para obtener información acerca de los dispositivos se utiliza el procedimiento almacenado
sp_helpdevice. Que muestra información de la tabla master..sysdevices.
Para eliminar un dispositivo se utiliza el procedimiento almacenado sp_dropdevice, con el
que se libera de manera lógica el dispositivo, allí se hace necesario liberar el dispositivo
físico por sistema operativo, esto es, borrarlo o deshacer la partición.
Cuando el dispositivo es creado es posible adicionarlo al conjunto de dispositivos por
defecto (predeterminados) en el que se crean las bases de datos de usuario y objetos que no
cuentan con dispositivos específicos. Para adicionar al (o eliminar del) grupo de
predeterminados se utiliza el procedimiento sp_diskdefault.
Los dispositivos predeterminados son utilizados por el sistema para almacenar todas las
bases de datos y/u objetos. Por esto mismo asegurese que no sean dispositivos por defecto
aquellos donde almacene la base de datos master, sybsecurity o bases de datos que
requieren un rendimiento óptimo.
3.1.2 Dispositivos espejo
El dispositivo espejo es un dispositivo que permite tener una copia en ejecución de una
base de datos o de su log de transacciones. Esto implica que la información es en todo
momento duplicada, pero al mismo tiempo que es posible hacer una recuperación de una
base de datos sin interrumpir la operación del sistema. Para inicializar un dispositivo como
disco espejo se utiliza el comando disk mirror. Cuando sucede un error de disco SQL
Server interrumpe la duplicación y sólo es posible reiniciarla con el comando disk
remirror. Para eliminar el disco espejo se utiliza el comando disk unmirror.
Nota: un dispositivo espejo no se inicializa con el comando disk init.
8
Los dispositivos espejo también son almacenados en la tabla sysdevices y los comandos
mencionados la modifican. A continuación la sintaxis de los comandos.
Comando disk mirror
disk mirror
name =
"device_name " ,
mirror =
"physicalname "
[ , writes = { serial | noserial }]
[ , contiguous ]
(Sólo
para
OpenVMS)
Comando disk remirror
disk remirror
name = " device_name"
Comando disk unmirror
disk unmirror
name = " device_name "
[, side = { "primary" | secondary }]
[, mode = { retain | remove }]
3.1.3 Dispositivos de volcado
Los dispositivos de volcado son las cintas y/o discos en los que se hacen copias de
seguridad. También son encuentran identificados en la tabla sysdevices.
3.2 Consideraciones sobre la administración del almacenamiento
Para asignar espacio en disco para una base de datos primero es necesario estimar el
tamaño requerido por la base de datos, sus objetos, su log de transacciones, etc. Luego con
los comandos vistos de disk init se inicializa el dispositivo teniendo en cuenta los
parámetros estimados. Y por último es fundamental asignar los objetos que han de residir
en dicho dispositivo.
La asignación de bases de datos a dispositivos se hace en el momento de su creación. Por
medio del comando create database es posible indicarle en qué dispositivo se va a ubicar,
si este dato no es suministrado, es creada en las bases de datos por defecto.
Asimismo cuando es necesario utilizar más de un dispositivo para almacenar una base de
datos ó alguna de sus componentes es posible crear un segmento. Un segmento es una
agrupación de dispositivos ó parte de un dispositivo que se comporta como una unidad
lógica para soportar el manejo disperso de espacio físico real.
9
El administrador del sistema de una instalación de SQL Server debe tomar decisiones
respecto a la asignación física de espacio a las bases de datos de SQL Server. Los
principales aspectos a considerar son:


Recuperación : La duplicación de discos y/o el mantenimiento de diarios en un
dispositivo físico distinto suponen dos mecanismos para la recuperación total en
caso de fallos físicos de los discos.
Rendimiento : Para ciertas tablas o bases de datos en que la velocidad de las
lecturas y escrituras en disco es crucial, la ubicación correcta de los objetos de base
de datos en dispositivos físicos produce mejoras en el rendimiento. La duplicación
de discos reduce la velocidad de las escrituras en disco.
3.2.1 Recuperación
La recuperación es el motivo fundamental para utilizar varios dispositivos de disco. La
recuperación ininterrumpida puede lograrse duplicando los dispositivos de base de datos.
La completa recuperación también puede garantizarse almacenando el diario de una base de
datos en un dispositivo físico aparte.
La recuperación ininterrumpida en caso de fallo del disco duro está garantizada por el
mecanismo de duplicación de los dispositivos de SQL Server en otro disco físico. La
duplicación de los dispositivos de base de datos con los datos e índices (no sólo del
dispositivo con el diario de transacciones) es necesaria para que la recuperación pueda
realizarse sin necesidad de detener SQL Server.
3.2.2 Rendimiento
El rendimiento del sistema puede mejorarse situando los diarios y objetos de base de datos
en dispositivos distintos:


Situar una tabla en un disco duro y los índices no agrupados en otro, garantiza que
las lecturas y escrituras físicas sean más rápidas, ya que el trabajo se reparte entre
dos unidades de disco.
Dividir las tablas de gran tamaño en dos discos puede mejorar el rendimiento, en
especial con aplicaciones multiusuario.
3.3 Segmentos
Los segmentos son llamados subconjuntos de los dispositivos de base de datos disponibles
para una base de datos SQL Server, en particular. Un segmento puede ser definido como
una etiqueta que apunta a uno o más dispositivos de base de datos. Los nombres de
segmento pueden ser utilizados en los comandos create table y create index para colocar
10
tablas o índices en dispositivos de bases de datos específicos. El uso de segmentos puede
incrementar el desempeño de SQL Server.
Los segmentos son creados dentro del dispositivo asignado a una base de datos. Cada base
de datos SQL Server puede tener hasta 32 segmentos, y deben haber sido inicializados y
estar disponibles para la base de datos antes de que se les asignen nombres.
Cuando se crea una base de datos se crean tres segmentos:
 system: que almacena las tablas de sistema.
 logsegment: que almacena el log de transacciones.
 default: almacena todos los otros objetos de la base de datos.
A menos que se hayan creado segmentos adicionales utilizando create table ... on
segment_name o create index ... on segment_name.
Tres tablas del sistema almacenan la información de los segmentos:
la tabla
master..sysusages y dos tablas de sistema en la base de datos de usuario sysindexes y
syssegments.
3.3.1 Procedimientos con segmentos
 Creación de segmentos con el procedimiento sp_addsegment. Sintaxis:
sp_addsegment segname, dbname, devname
Luego, los objetos pueden ser ubicados en este segmento haciendo referencia al mismo con
la clausula on.
create table table_name (col_name datatype...)
[on segment_name]
create [clustered|nonclustered] index index_name
on table (col_name)
[on segment_name]
 Extensión (adición de dispositivos a un segmento) de los segmentos con el
procedimiento sp_extendsegment. Sintaxis:
sp_extendsegment segname, dbname, devname
 Soltado (drop) de segmentos con el procedimiento sp_dropsegment. Sintaxis:
sp_dropsegment segname, dbname
11
además es posible usarlo para remover dispositivos de base de datos de un segmento.
sp_dropsegment segname, dbname, devname
Para obtener información de los segmentos esta el procedimiento almacenado
sp_helpsegment.
3.4 Creando Bases de Datos de Usuario
Se utiliza el comando create database. Las bases de datos son creadas mientras se usa la
base de datos master. Por tanto se debe ser un usuario valido y debe tener permisos de
creación.
El comando create database limpia todas las páginas del dispositivo de la base de datos. Si
esta creando una base de datos desde una carga de datos puede utilizar una opción que se
salta la limpieza y es ejecutada después de que se completa la carga. create database
syntaxis:
create database
database_name
[on {default |
database_device } [=
size
[,
database_device
[=
size ]...]
[log on
database_device
[ =
size
]
[,
database_device
[=
size ]]...]
[with override]
[for load]
]
Solo se puede crear una base de datos al tiempo y es creada en los dispositivos por defecto
disponibles en su servidor. Todos los dispositivos que se utilicen en la creación de la base
de datos deben haber sido inicializados con disk init. Por ejemplo:
3.4.1 Como son creadas las bases de datos de usuarios
Cuando una sentencia create database es utilizada, SQL Server realiza los siguientes pasos:




Verifica la unicidad del nombre de base de datos.
Se asegura que el dispositivo de base de datos especificado esta disponible.
Halla un número de identificación único.
Asigna espacio para la base de datos en los dispositivos especificados y actualiza esta
información en master..sysusages para registrar estas asignaciones.
 Inserta una fila en sysdatabases.
 Hace una copia de la base de datos model en el nuevo espacio de base de datos, creando
las nuevas tablas de sistema en la nueva base de datos.
 Limpia las páginas restantes (a menos que se cree por una carga de datos).
12
La nueva base de datos hereda todas las características que tenga la base de datos model.
3.4.2 Permisos para Crear Bases de Datos
Normalmente el Administrador del sistema tiene el “monopolio” de creación de las bases
de datos con el fin de centralizar el control de las ubicaciones y la asignación de
dispositivos de base de datos. En tal caso se crea la base de dtos y luego se transfiere la
propiedad al usuario apropiado.
Para hacer ésto son tres pasos:
 Crear la base de datos.
 Conmutar a la base de datos con el comando use.
 Ejecutar el procedimiento de sistema sp_changedbowner.
El Administrador del Sistema puede, además, dar permisos para crear bases de datos. En
este caso él opera los servicios de protección del sistema por fuera como una precaución.
3.4.3 Asignando Bases de Datos a Dispositivos de Bases de Datos
En la sentencias create database o alter database se especifica en cual se ha de crear en
uno o si se van a utilizar varios dispositivos,o si se coloca default o se omite se crea en el
dispositivo por defecto. También se indica en que dispositivo se creará el log de
transacciones (si no se hace se utiliza el “default”).
Tamaño de la base de datos (size): Si el parámetro es omitido se crea con la cantidad por
defecto en la variable de configuración (sp_configure) o la base de datos model (alter
database).
3.4.4 Colocando el Log de Transacciones en un dispositivo separado: log on
El log de transacciones (la tabla syslogs) es ubicado en el dispositivo que se especifíque en
la clausula log on . Por defecto se utilizan 2 megabytes de almacenamiento. Se puede
colocar separado porque:
 Permite hacer descarga del log de transacciones de manera aislada.
 Permite establecer un tamaño fijo eliminandolo de la competencia de recurso físico de
almacenamiento.
 Crea umbrales de espacio libre por defecto para el log y para la base de datos por aparte.
 Incrementa su desempeño.
 Asegura recuperación total en el evento de daño físico de disco.
13
El tamaño del log de transacciones es “a ojo” del 10% al 25% del tamaño requerido por la
base de datos.
3.4.5 La opción de for load para recuperación de bases de datos
Es una opción que se emplea cuando se va a utilizar la base de datos desde una descarga,
sea por un daño físico o se va a cambiar de una máquina a otra. for load utiliza una versión
modificada de create database para crear una base de datos que puede ser usada solo para
cargar.
El procedimiento sp_logdevice permite cambiar el dispositivo mueve las futuras
asignaciones para el log de transacciones para otro dispostivo (cuando el actual se
complete). Su sintaxis:
sp_logdevice
database_name
,
device_name
Ese dispositivo de base de datos debe haber sido inicializado con disk init y asignado a la
base de datos con create o alter database .
3.4.6 Cambiando la Propiedad de una Base de Datos
Para esto se utiliza el procedimiento sp_changedbowner. La sintaxis es:
sp_changedbowner
loginame
[, true ]
El nuevo usuario debe tener login name en SQL Server, pero no puede ser un usuario o
tener un alias en la base de datos. Para garantizar esto se pueden usar los procedimientos
sp_dropuser o sp_dropalias antes de hacer el cambio. El parámetro true transfiere alias y
permisos.
3.4.7 Incrementando el tamaño de una base de datos
Es posible incrementar el tamaño de la base de datos con el comando alter database
adicionando espacio, tanto para los objetos de la base de datos como para el log de
transacciones o ambos a la vez. Los permisos para usar el comando alter database son, por
defecto, los del propietario de la base de datos y no pueden ser cambiados con grant o
revoke.
alter database
database_name
[on {default |
database_device } [=
size ]
[,
database_device
[=
size ]]...]
[log on {default |
database_device } [=
size
[,
database_device
[=
size ]]...]
[with override]
]
14
[for load]
El mínimo incremento que se puede especificar es 1 megabyte (512 2K páginas).
3.5 El comando drop database
Este comando remueve una base de datos del servidor borrando la base de datos y todos sus
objetos, liberando el espacio asignado a la base de datos y borrando las referencias a ésta en
las tablas de la base de datos master. Sintaxis:
Drop database database_name [, database_name]...
4. Logins y Usuarios de Bases de Datos
En este capítulo se describe el manejo de las cuentas de login y los usuarios de base de
datos.
4.1 Adicionando nuevos usuarios
Para adicionar usuarios a un sistema de base de datos se debe hacer lo siguiente:
 Un SSO debe crear las cuentas de login a nivel de servidor (sp_addlogin).
 Un SA o propietario de la base de datos adiciona al usuario (user) a la base de datos
(sp_adduser) o lo adiciona a un grupo (sp_addgroup).
 Un SA o propietario de la base de datos asigna los permisos necesarios.
Sintaxis del procedimiento sp_addlogin
sp_addlogin login_name, password [, defdb]
[, deflanguage [, fullname]]]
donde el login_name es el nombre con el que se iniciará una sesión en SQL Server, el
password es la clave, y defdb es la base de datos por defecto. Si este último es omitido la
base de datos por defecto ese master y se puede modificar con el procedimiento
almacenado sp_modifylogin. La tabla que modifican estos procedimientos es la
master.dbo.syslogins. Esto sólo lo puede hacer un SSO.
Con el comando sp_addgroup es posible crear un grupo de usuarios ó para adicionar
usuarios conservando las características que se le establezcan al grupo mismo.
Para eliminar cuentas, usuarios y grupos existen tres procedimientos sp_droplogin,
sp_dropuser, sp_dropgroup. Sintaxis:
sp_droplogin login_name
sp_dropuser name_in_db
sp_dropgroup groupname
Con el comando sp_locklogin se puede prevenir que un usuario ingrese, es decir es posible
bloquear temporalmente una cuenta.
sp_locklogin [ login_name, "{lock | unlock}"]
Otra forma de asignar a varios usuarios un mismo comportamiento es por medio de la
utilización de alias. Un alias es un identidad de usuario colectiva que asigna los permisos y
propiedades del login de usuario inicial. Se asignan alias por medio del procedimiento
sp_addalias y para liberar el alias se utiliza el procedimiento sp_dropalias.
16
Con el procedimiento sp_who es posible conocer la información de los usuarios actuales y
los procesos en ejecución.
Con el procedimiento sp_displaylogin obtenemos información acerca de cuentas de
usuario.
Con el procedimiento sp_helpuser información de usuarios y alias en la base de datos.
Con el procedimiento sp_helpgroup información de grupos en la base de datos.
5. Administrando Permisos de Usuario
Los comandos grant y revoke se utilizan para asignar permisos de uso, consulta o ejecución
sobre los objetos de la base de datos y constituyen un mecanismo de seguridad.
Los administradores del sistema tienen permisos y propiedad sobre todos los objetos del
sistema. El propietario de una base de datos no recibe automáticamente los permisos sobre
los objetos en su base de datos, pero puede tener acceso a ellos por medio del comando
setuser. Una vez obtenidos los permisos necesarios sobree los objetos de la base de datos
puede utilizar grant para asignarse permisos.
Los tipos de usuarios y sus privilegios en SQL Server son:
 Administrador del sistema.
 Oficial de seguridad del sistema.
 Operador.
 Propietario de Base de Datos.
 Propietario de objeto de base de datos.
 Otros usuarios (“public”).
Un Administrador del sistema tiene permisos para:
 Creación de bases de datos.
 Asignación de permisos para creación de bases de datos.
Aunque lo más conveniente es que un único administrador del sistema tenga el monopolio
de la creación de bases de datos y de utilización y creación de dispositivos con el fín de
detentar el control en las tareas de asignación y desempeño
El SSO tiene los privilegios para crear cuentas a nivel de servidor, asignar y cambiar
passwords y asignar roles. Todas estas tareas únicamente a nivel de seguridad en el
sistema. Este tipo de usuario no tiene privilegios especiales sobre los objetos de la base de
datos.
Los usuarios Oper tienen privilegios para ejecución de tareas rutinarias como volcado de
dispositivos o carga de los mismos.
Los privilegios de los propietarios de una base de datos son: Asignación de permisos a
otros usuarios dentro de su base de datos (grant), verificaciones de consistencia en la base
de datos (dbcc), emular usuarios (set user), volcado y carga de bases de datos y/o sus logs
de transacciones, creación y borrado de objetos dentro de su base de datos.
Adicionalmente el procedimiento sp_changedbowner permite al administrador del sistema
cambiar el propietario de una base de datos.
6. Chequeo de la consistencia de la base de datos
La verificación de la consistencia, Database Consistency Checker (dbcc), es un conjunto de
utilidades que permiten hacer un chequeo lógico y físico de la consistencia de una base de
datos.
6.1 Distribución Física
Cuando se inicializa un dispositivo de base de datos, el comando disk init divide el nuevo
espacio en unidades de asignación de 256 páginas de datos de 2K. La primera página de
cada unidad es una página de asignación , que controla el uso de todas las páginas de la
unidad de asignación. Las páginas de asignación tienen una ID de objeto de 99; no son
objetos reales de base de datos y no aparecen en las tablas del sistema, pero los errores
detectados por dbcc en las páginas de asignación mencionan este valor.
Cuando una tabla o un índice requieren espacio, SQL Server asigna un bloque de 8 páginas
de 2K al objeto. Este bloque de 8 páginas se denomina sector . Cada unidad de asignación
de 256 páginas contiene 32 sectores. SQL Server utiliza los sectores como una unidad de
administración de espacio para asignar y desasignar espacio:




Cuando se crea una tabla o un índice, SQL Server asigna un sector para el objeto.
Cuando se añaden filas en una tabla existente, si las páginas existentes están llenas,
SQL Server asigna otra página. Si todas las páginas de un sector están llenas, SQL
Server asigna un sector adicional.
Cuando se omite una tabla o un índice, SQL Server desasigna los sectores que
ocupaba.
Cuando se eliminan suficientes filas de una tabla como para que disminuya en una
página, SQL Server desasigna la página. Si la tabla disminuye el sector, SQL Server
desasigna el sector.
Cada vez que se asigna o desasigna espacio en un sector, SQL Server registra el evento en
la página de asignación que controla los sectores de ese objeto. Esto proporciona un método
rápido de controlar las asignaciones de espacio en la base de datos, ya que los objetos
pueden menguar o crecer sin mucha sobrecarga.
Dbcc provee del comando checkalloc para verificar las páginas de asignación, indexalloc y
tablealloc para verificar la asignación de los objetos específicos de la base de datos.
6.2 Mapa de Asignación de Objetos (OAM)
Cada tabla y cada índice de una tabla tiene un mapa de asignación de objetos. El OAM se
almacena en las páginas asignadas a la tabla o al índice y se verifican cuando es necesario
una nueva página para el índice o la tabla. En una página OAM se pueden ubicar
correlaciones de asignaciónde 2.000 a 63.750 páginas de datos ó índice.
19
El enlace de las páginas relacionadas de un mismo objeto se realiza con un encabezado que
se ubica en la página misma, en el que se relaciona la página previa y la siguiente. Los
comandos dbcc comparan el enlace de páginas y verifican la información.
6.3 Comandos dbcc
La sintaxis de dbcc checktable es:
dbcc checktable ({
table_name
|
table_id
} [, skip_ncindex] )
El comando dbcc checktable verifica la tabla especificada chequeando:




Las páginas de índice y datos estén enlazadas correctamente.
Los índices estén ordenados correctamente.
Todos los punteros sean consistentes.
Las filas de datos de cada página tengan entradas en la primera página OAM cuyas
ubicaciones respectivas en la página coincidan.
La opción skip_ncindex permite saltar la verificación de enlace de páginas, de punteros y
de criterio de ordenación en índices no agrupados. El enlace y los punteros de índices
agrupados y las páginas de datos son esenciales para la integridad de las tablas. Si SQL
Server informa sobre problemas con el enlace de páginas o los punteros, es posible omitir y
volver a crear los índices agrupados con facilidad.
La sintaxis de dbcc checkdb es:
dbcc checkdb [(
database_name
[, skip_ncindex]) ]
El comando dbcc checkdb ejecuta las mismas verificaciones que dbcc checktable en cada
tabla de la base de datos especificada. Si no se suministra el nombre de una base de datos,
dbcc checkdb verifica la actual. dbcc checkdb devuelve mensajes similares a los
generados por dbcc checktable y realiza el mismo tipo de correcciones.
Si se especifica el modo opcional skip_ncindex , dbcc checkdb no verifica ninguno de los
índices no agrupados de las tablas de usuario de la base de datos.
La sintaxis de dbcc checkcatalog es:
dbcc checkcatalog [(
database_name
)]
El comando dbcc checkcatalog verifica la consistencia dentro y entre las tablas del sistema
de una base de datos en particular. Si no se suministra el nombre de una base de datos, dbcc
checkcatalog verifica la actual. Verifica que:
 Todo tipo incluido en syscolumns tenga una entrada correspondiente en systypes .
 Toda tabla y vista de sysobjects tenga al menos una columna en syscolumns .
20
 El último punto de verificación de syslogs sea válido.
También enumera los segmentos definidos para uso de la base de datos.
La sintaxis de dbcc checkalloc es:
dbcc checkalloc [(
database_name
[, fix | nofix] )]
El comando dbcc checkalloc verifica la base de datos especificada para ver que:
 Todas las páginas estén asignadas correctamente.
 Ninguna página esté asignada sin estar en uso.
 Ninguna página esté en uso sin estar asignada.
Si no se proporciona el nombre de una base de datos, dbcc checkalloc verifica la actual.
El modo predeterminado de dbcc checkalloc es nofix . Para utilizar dbcc checkalloc con la
opción fix , primero es necesario poner la base de datos en modo de usuario único con el
comando:
sp_dboption dbname, "single user", true
Este comando sólo puede ejecutarse cuando nadie esté usando la base de datos.
dbcc checkalloc informa la cantidad de espacio asignado y utilizado. La salida de dbcc
checkalloc consta de un bloque de datos para cada tabla, incluyendo las tablas del sistema y
los índices de cada tabla. Por cada tabla o índice, informa el número de páginas y sectores
utilizados.
La sintaxis de dbcc tablealloc es:
dbcc tablealloc ({ table_name
|
[, {full | optimized | fast | null}
[, fix | nofix]])
table_id
}
Nota: fix o nofix pueden especificarse sólo si se incluye un valor para el tipo de informe
(full , optimized , fast o null ).
Este comando realiza las mismas verificaciones que dbcc checkalloc en una sola tabla. Es
posible especificar el nombre de la tabla o su ID de objeto (la columna id de sysobjects ).
Con dbcc tablealloc pueden generarse tres tipos de informes: full , optimized y fast :
 La opción full equivale a dbcc checkalloc a nivel de tabla; informa de todos los
tipos de errores de asignación .
 La opción optimized produce un informe basado en las páginas de asignación
enumeradas en las páginas del mapa de asignación de objetos (OAM) de la tabla.
No informa ni puede corregir sectores no referenciados en las páginas de asignación
que no están presentes en las páginas OAM. Si el tipo de informe no se indica en el
comando, o si se indica null , optimized es el informe predeterminado.
21

La opción fast produce un informe de excepciones de páginas referenciadas pero no
asignadas en el sector (errores del nivel 2521). No produce un informe de
asignaciones.
La opción fix | nofix determina si tablealloc corrige o no los errores de asignación
encontrados en la tabla. El valor predeterminado para todas las tablas de usuario es fix; para
las del sistema, es nofix. Para utilizar la opción fix con las tablas del sistema, antes es
necesario poner la base de datos en modo de usuario único.
Esta es la sintaxis de dbcc indexalloc :
dbcc indexalloc ( { table_name
|
[, {full | optimized | fast | null}
[, fix | nofix]])
table_id
},
index_id
Not: Sólo puede especificarse fix o nofix si se incluye un valor para el tipo de informe
(full , optimized , fast o null ).
El comando dbcc indexalloc verifica el índice especificado para ver que:
 Todas las páginas estén asignadas correctamente.
 Ninguna página esté asignada sin estar en uso.
 Ninguna página esté en uso sin estar asignada.
Esta es una versión a nivel de índice de dbcc checkalloc , que proporciona las mismas
verificaciones de integridad en un índice individual. Es posible especificar el nombre de la
tabla o su ID de objeto (la columna id de sysobjects) y la indid del índice en sysindexes .
dbcc checkalloc y dbcc indexalloc incluyen la ID del índice en su salida.
dbcc indexalloc produce los mismos tres tipos de informes que dbcc tablealloc : full,
optimized y fast (consulte t ablealloc ). La opción fix | nofix funciona igual en dbcc
indexalloc que en dbcc tablealloc.
Esta es la sintaxis de dbcc dbrepair :
dbcc dbrepair (
database_name
, dropdb)
El comando dbcc dbrepair dropdb omite una base de datos dañada. El comando drop
database de Transact-SQL no funciona en una base de datos que no pueda recuperarse o
usarse. Ejecute la instrucción dbrepair desde la base de datos master . Ningún usuario,
incluido el de dbrepair , puede estar usando la base de datos cuando se la omite.
El administrador del sistema o el propietario de la tabla deberán ejecutar dbcc reindex
después de cambiar los criterios de ordenación de SQL Server. Esta es la sintaxis de dbcc
reindex :
dbcc reindex ({
table_name
|
table_id
})
22
El comando dbcc reindex verifica la integridad de los índices de las tablas de usuario
ejecutando una versión "rápida" de dbcc checktable. dbcc reindex omite y reconstruye los
índices que sospecha que están corruptos, (es decir, el orden en que los índices ordenan los
datos no es consistente con el nuevo criterio de ordenamiento).
7. Copias de Seguridad y Restauración de Bases de Datos de Usuario
Las copias de seguridad hacen parte importante de las políticas de administracion de un
sistema de bases de datos ya que garantizan una recuperación del sistema en el eventual
suceso de una falla irrecuperable de disco o cualquier otro problema que desemboque en
inconsistencia de los datos.
Pero cómo se realiza la recuperación de una base de datos desde un backup? Es necesario
entender primero cómo almacena los cambios sufridos por una base de datos y cómo
estimar si los últimos cambios son validos para llegar al estado más próximo de la base de
datos antes del suceso del fallo.
SQL Server lleva un registro de todos los cambios hechos a una base de datos, por medio
del rastreo de las transacciones realizadas. Las transacciones son unidades de comandos de
SQL y de comandos de flujo y control tales que como una sola son válidas o no lo son.
Comunmente estas transacciones son encabezadas con la clausula begin transaction y
finalizadas con la clausula end transaction. Por tanto todos los comandos que se
encuentren dentro de éstas dos clausulas hacen parte integrante de la transaccion y junto
con ella o fallan, o son válidas.
Cada base de datos tiene su propio espacio de almacenamiento de transacciones, es
conocido como el log de transacciones. Para esto se utiliza la tabla de sistema syslogs.
Por tanto es posible obtener copias de seguridad de la base de datos o del log de
transacciones con el fin de ajustar de una forma garantizada la recuperación de la base de
datos.
7.1 Comandos de volcado y carga
Los principales comandos en la elaboración de copias de seguridad y restauración de las
mismas son:
dump transaction copia la información del log de transacciones, lo que garantiza una
recuperación de las transacciones exitosas.
Nota: Nunca utilice insert, update o delete sobre la tabla de log syslogs.
checkpoint permite hacer sincronización de una base de datos y de su log de transacciones
actualizando las páginas “sucias” (es decir las que no han sido actualizadas en disco sino
únicamente en memoria). Se puede ejecutar de manera automática o manual.
recovery interval es el tiempo estimado de recuperación de una base de datos en minutos.
Y este tiempo depende del tamaño de la base de datos.
24
trunc log on chkpt es una opción de configuración que permite truncar el log de
transacciones cada vez que se ejecuta un checkpoint automático.
El mecanismo de recuperación compara cada base de datos con su log de transacciones. Si
el registro es más reciente que la página de datos el mecanismo de recuperación aplica los
cambios. Si la transacción no fue completada con éxito se reversa hasta el punto de inicio.
Este mecanismo asegura que la transacción se completa de forma segura o falla.
Cuando SQL Server es iniciado (por shutdown o por caida abrupta) es inicializado en el
siguiente orden.
1. Recupera master
2. Recupera sybsecurity
3. Recupera model
4. Crea tempdb (copiando model)
5. Recupera sybsystemprocs
6. Recupera bases de datos de usuariode acuerdo a su dbid.
En el evento de un daño de disco se puede recuperar la base de datos en su totalidad (o al
máximo) siempre y cuando existan copias de seguridad de la base de datos y de su log de
transacciones. Para estas actividades se requieren los siguientes comandos dump
database, dump transaction, load database, load transaction.
Nota: no es posible recuperar una base de datos copiando con los comandos de sistema
operativo, esto puede generar un error de carga masiva.
Antes de sacar una copia de seguridad es recomendable hacer una verificación de la
consistencia para obtener una copia confiable. Para hacer una copia de seguridad al evento
de un fallo utilice la opción with no_truncate del comando dump transaction, lo que es
posible únicamente si el log de transacciones se encuentra fuera del dispositivo que ha
fallado y la base de datos master es accesible. Si se hace restauración de un backup de base
de datos, se modifica la base de datos y luego se intentan restaurar las transacciones del
backup del log de transacciones la operación falla.
Es posible recuperar una base de datos desde un backup incluyendo la opción for load en la
clausula create database.
El comando sp_volchanged puede ser ejecutado por cualquier usuario y se utiliza para
indicar a SQL Server que el volumen de cinta se ha cambiado.
Backup Server es un servidos dedicado exclusivamente a sacar y restaurar copias de
seguridad. Backup Server se encarga de hacer backups en red, particionar los datos de
manera que pueda dividir el backup en varios dispositivos (en el caso de las cintas), hacer
copias de seguridad remotas.
25
Los dispositivos para copias de seguridad (local) se encuentran enumerados en la tabla
sysdevices. Son adicionados por medio de la clausula sp_addumpdevice. Sintaxis:
sp_addumpdevice{ "tape" | "disk"} , device_name,
physicalname, size
donde physicalname representa la ubicación física del archivo (o partición) a utilizar como
dispositivo de copia de seguridad.
Para eliminarlo utilizar el procedimiento
sp_dropdevice.
Recomendación: hacer un dump de la base de datos master después de hacer un cambio
en discos, almacenamiento, bases de datos o segmentos. De la misma forma es conveniente
hacer copias de seguridad de model cuando sea modificada su estructura ya que de ella
parten todas las bases de datos que se produzcan.
La sintaxis de los comandos de copia y volcado es muy similar:
dump
load
dump {database | tran} database_name
to stripe_device
[at server_name]
[density = density,
blocksize = number_bytes,
capacity = number_kilobytes,
dumpvolume = volume_name,
file = file_name]
[stripe on stripe_device
[at server_name]
[density = density,
blocksize = number_bytes,
capacity = number_kilobytes,
dumpvolume = volume_name,
file = file_name] ...]
[with{
density = density,
blocksize = number_bytes,
capacity = number_kilobytes,
dumpvolume = volume_name,
file = file_name,
[nodismount | dismount],
[nounload | unload],
retaindays = number_days,
[noinit | init],
[notify = {client | operator_console}]}]
load {database | tran} database_name
from stripe_device
[at server_name]
[density = density,
blocksize = number_bytes
dumpvolume = volume_name
file = file_name]
[stripe on stripe_device
[at server_name]
[density = density,
blocksize = number_bytes,
dumpvolume = volume_name,
file = file_name] ...]
[with{
density = density,
blocksize = number_bytes,
dumpvolume = volume_name,
file = file_name,
[nodismount | dismount],
[nounload | unload],
[notify = {client | operator_console}]}]
7.2 Reglas para especificar dispositivos de volcado
Utilice las siguientes reglas para especificar el dispositivo de volcado:
El dispositivo de volcado puede especificarse como literal, variable local o parámetro de un
procedimiento almacenado.
26
No es posible volcar a o cargar del "dispositivo nulo" (en UNIX, /dev/null ; en OpenVMS,
cualquier dispositivo cuyo nombre comience con NL; no aplicable a plataformas PC ).
Al volcar a o cargar desde un dispositivo local, se puede utilizar cualquiera de las formas
siguientes para especificar el dispositivo de volcado:



Un nombre absoluto de ruta de acceso
Un nombre relativo de ruta de acceso
Un nombre lógico de dispositivo de la tabla del sistema sysdevices
El Backup Server resuelve los nombres relativos usando el directorio de trabajo actual de
SQL Server.
Al volcar o cargar a través de la red:



Debe especificarse el nombre absoluto de ruta de acceso del dispositivo de volcado. (No
es posible utilizar un nombre relativo de ruta ni un nombre lógico de dispositivo de la
tabla del sistema sysdevices .)
El nombre de ruta debe ser válido en la máquina donde se ejecuta el Backup Server.
Si el nombre incluye caracteres que no sean letras, números o el guión de subrayado
(_), debe ir entre comillas.
7.3 Procedimiento de recuperación
1.
2.
3.
4.
5.
6.
7.
8.
9.
Obtenga una copia de seguridad del log de todas las bases de datos en el dispositivo.
Examine el espacio utilizado por cada base de datos en el dispositivo.
Una vez haya consolidado esta información borre cada base de datos (drop database).
Suelte el dispositivo que falló.
Inicializar los nuevos dispositivos.
Re-crear las bases de datos, una a la vez.
Bloquear usuarios en cada base de datos.
Cargue el volcado más reciente en cada base de datos.
Aplique cada transacción del volcado del log en el orden en que fueron creados.
7.4 Procedimientos de recuperación de las base de datos de sistema
1. Haga copias de seguridad de las tablas delsistema necesarias para restaurar discos, bases
de datos y logins.
2. Si hay otras bases de datos en el dispositivo master (no es recomendable tenerlas), y son
accesibles sáqueles copia de seguridad.
3. Utilice buildmaster para construir una nueva base de datos master.
4. Reinicie SQL Server en modo mono-usuario con el comando startserver.
27
5. Si su base de datos es mayor de 3MB, debe re-crear las asignaciones en sysusages.
6. Si su Backup Server no es syb_bakcup, cambie el nombre en sysservers.
7. Verifique que su Backup Server este corriendo.
8. Utilice load database para cargar los más recientes copias de la base de datos master.
9. Reinicie SQL Server en modo mono-usuario con el comando startserver.
10. Si tiene dispositivos de bases de datos adicionales, utilice disk reinit para reconstruir
sysdevices.
11. Si corrió disk reinit, o si creo bases de datos o las modificó desde la última copia de la
base de datos haga copias de sysusages y sysdatabases y luego utilice disk refit para
reconstruir estas tablas de sistema.
12. Verifique la consistenacia, compare sus copias de sysusages y sysdatabases con la
nueva versión de la base de datos, ejecute dbcc checkalloc en cada base de datos y
examine las tablas importantes en cada base de datos
13. Si usted restauró completamente master restaure model.
14. Recargar las bases de datos de usuario.
15. Verifique syslogins si usted ha adicionado nuevos logins desde la última copia de
seguridad.
8. Administrando espacio libre con umbrales
Las limitaciones de espacio en los dispositivos (al momento de ser creados el espacio se
establece) implica que al momento en que se llenen se han de bloquear y no permitirán su
acceso. Por esto es necesario definir alarmas que se disparen en el momento en que se
rebasen los límites conocidos como umbrales.
La existencia de umbrales permiten definir las acciones a tomar en el evento que sean
alcanzados, tanto en el espacio de datos como en el diario de transacciones. Un umbral esta
asociado con un procedimiento almacenado y funciona como un trigger en el evento de ser
rebasado o simplemente alcanzado (sp_thresholdaction).
Por ejemplo cada log de transacciones tiene un umbral de ultima oportunidad que es el
espacio necesario para hacer el volcado del diario (espacio dado en páginas).
El umbral de última oportunidad otorga suficiente espacio de log libre para registrar un
comando dump transaction . Cuando se cruza el umbral de última oportunidad, SQL
Server suspende los procesos de usuario y muestra un mensaje de suspensión de
operaciones.
Cuando esto sucede es necesario suspender los procesos que estan activos y luego ejecutar
un checkpoint con el fín de que se actualicen las páginas sucias. Los procesos que quedan
suspendidos son automáticamente reiniciados cuando hay el suficiente espacio en el log de
transacciones para ser registrados.
Los Umbrales pueden ser modificados, o eliminados y además pueden ser creados nuevos
umbrales. Los procedimientos del sistema sp_addthreshold (crear nuevos umbrales),
sp_modifythreshold (modificar el evento programado ó el valor) y sp_dropthreshold
permiten crear, cambiar y eliminar umbrales. Con procedimiento del sistema
sp_helpthreshold se puede obtener información sobre todos los umbrales de una base de
datos.
Volcado del diario de transacciones
Si el procedimiento sp_thresholdaction asociado al evento del umbral incluye un comando
dump transaction, SQL Server vuelca el diario a los dispositivos indicados en el
procedimiento. El comando dump transaction trunca el diario de transacciones borrando
las páginas desde el principio del diario hasta la página situada justo antes de la que
contiene un registro de transacción no registrada.
En general, no se recomienda el volcado a un disco, especialmente a un disco que esté en la
misma máquina, o el mismo controlador de disco que el disco, de la base de datos. Sin
embargo, dado que los volcados iniciados por un umbral pueden ocurrir en cualquier
29
momento, tal vez convenga hacer un volcado al disco y luego copiar los archivos
resultantes a un medio fuera de línea.
La decisión dependerá de:
 Si hay un dispositivo de volcado dedicado en línea, que esté cargado y preparado para
recibir datos volcados.
 Si hay operadores disponibles para montar volúmenes de cinta durante los momentos en
que la base de datos está disponible.
 El tamaño del diario de transacciones.
 La cantidad de transacciones.
 La planificación periódica del volcado de las bases de datos y de los diarios de
transacciones.
 El espacio de disco disponible.
 Otros recursos de volcado y restricciones específicos del sitio.