Base de datos del sistemaLas base de datos del sistema son 5
Master: Almacena toda la información sobre las base de datos de usuario, debe hacerse un backup de ella cada vez que se crear o elimina una base de datos del servidor
Model: Esta base de datos es una especie de Template utilizado cuando se crea una base de datos nueva, es decir que la configuración por defecto de las nuevas base de datos serán tomadas de esta base de datos
Msdb:En esta base de datos se almacena toda la configuración del SQLAGENT y planes de mantenimiento
TempDB: En esta base de datos se almacena toda la información temporal necesaria para realizar una consulta como tablas temporales, procedimientos almacenados temporales. Se recomienda mover esta Base de datos para un solo disco y de esta manera mejorar el rendimiento del servidor.
Resource: Es una base de datos de solo lectura que contiene todos los objetos del sistema. (Sys.objects). El objetivo de esta base de datos es facilitar las actualizaciones de versiones de SQL Server
Creación de Base de Datos de UsuarioUna base de datos esta compuesta por lo menos por dos archivos. Un archivo de datos con extensión .mdf y un archivo de log con extensión .ldf. Estos archivos pertenecen a un
FILEGROUPUn
FILEGROUP se utiliza para facilitar el mantenimiento de la BD y mejorar el rendimiento. Mas adelante profundizaremos sobre estos
Para crear una BD de usuario se puede utilizar el asistente a través del
SQL SERVER MANAGEMENT STUDIO (SSMS) o por medio de
T-SQL.
La sintaxis para crearla por
T-SQL es:
CREATE DATABASE database_name
[ ON
[ <> [ ,...n ] ]
[ , <> [ ,...n ] ]
]
[ LOG ON { <> [ ,...n ] } ]
[ COLLATE collation_name ]
[ FOR LOAD FOR ATTACH ]
<> ::=
[ PRIMARY ]
( [ NAME = logical_file_name , ]
FILENAME = 'os_file_name'
[ , SIZE = size ]
[ , MAXSIZE = { max_size UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] ) [ ,...n ]
<> ::=
FILEGROUP filegroup_name <> [ ,...n ]Expliquémoslo por medio de ejemplos:
Supongamos que queremos crear una BD de llamada prueba que comience con un tamaño inicial de 10 MB, que tenga un tamaño máximo de 50 MB y un crecimiento de 5 MB con un log de tamaño inicial 5 MB, tamaño máximo de 25 MB y crecimiento de 5 MB
USE masterGOCREATE DATABASE PruebaON ( NAME = Prueba_dat, FILENAME = 'c:\BD\Prueba.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 )LOG ON( NAME = 'Prueba_log', FILENAME = 'c:\BD\Pruebalog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB )GOSi quisiéramos créala con mas de un archivo de datos tendríamos que agrégale después del primer archivo de datos una coma y escribir lo siguiente
( NAME = Prueba_dat2, FILENAME = 'c:\BD\Prueba2.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 )Note que el nombre del archivo termina en ndf, que es la estexion por defecto de archivos de datos secundarios.
Por defecto en el ejemplo anterior la BD fue creada en el FileGroup configurado por defecto, si este no ha sido modificado desde la instalación, La BD fue creada en el FileGroup Primary.
Si quisieramos crealo en otro FileGroup deberiamos especificarlo despues de la palabra ON.
CREATE DATABASE Prueba ON SecundaryPor ejemplo aquí se crearía la BD en el FileGroup Secundary.
Si los archivos de Bd ya existen en el servidor, la BD se puede crear de la siguiente manera
CREATE DATABASE PruebaON PRIMARY (FILENAME = 'c:\BD\Prueba.mdf')FOR ATTACH
La opción FOR LOAD es incluida por compatibilidad con versiones de SQL SERVER 7.
Con la opción COLLATE se especifica la colación por defecto de la BD, si esta no es definida se tomara por defecto la del servidor, para mas información consulte
http://msdn.microsoft.com/en-us/library/aa258237(SQL.80).aspxModificación de Base de Datos de Usuario
Después que se ha creado un BD, se pueden realizar muchas configuraciones, como agregar, modificar o quitar un archivo de datos o de Log de la BD o modificar las opciones de configuración de la BD
Primero vamos a explicar con ejemplo como se puede agregar, modificar o quitar archivos de datos siguiendo la siguiente sintaxis.
ALTER DATABASE database
{ ADD FILE <> [ ,...n ] [ TO FILEGROUP filegroup_name ]
ADD LOG FILE <> [ ,...n ]
REMOVE FILE logical_file_name
ADD FILEGROUP filegroup_name
REMOVE FILEGROUP filegroup_name
MODIFY FILE <>
MODIFY NAME = new_dbname
MODIFY FILEGROUP filegroup_name {filegroup_property NAME = new_filegroup_name }
SET <> [ ,...n ] [ WITH <> ]
COLLATE <>
}
Para agregar un tercer archivo de datos a la base de datos prueba debemos realizar lo siguiente
ALTER DATABASE Prueba ADD FILE ( NAME = Prueba_dat3, FILENAME = 'c:\BD\Prueba3.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB)Para eliminarlo hacemos lo siguiente
ALTER DATABASE Prueba REMOVE FILE Prueba_data GOSi queremos modificar el tamaño máximo de un archivo de datos o de log podemos hacer lo siguiente
ALTER DATABASE Prueba MODIFY FILE (NAME = Prueba_dat2, MAXSIZE = 200MB)
Ahora para modificar las opciones de configuración de la BD también utilizamos la sentencia Alter Database.
Para cambiar el FileGroup por defecto de un BD, debemos correr este script
ALTER DATABASE Prueba MODIFY FILEGROUP [Secondary] DEFAULTDespues de ejecutar este script todas la bases de datos que se creen y no se les especifique el filegroup seran creadas en el FileGroup Secondary
Es recomendable que las BD de usuario sean creadas en un FileGroup diferente a Primary por organización, mejoras de rendimiento si el Filegroup esta en otro disco y también para evitar que en ciertos casos las base de datos del sistema se queden sin recursos cuando estan siendo utilizados por la BD de usuarios.
Si queremos cambiar el modelo de recuperación de la Bd por script, deberiamo correr el siguiente codigo T-SQL
ALTER DATABASE Prueba SET RECOVERY FULLLas opciones son muchas, en la grafica se podrán visualizar algunas.
Opciones de configuración de BD
Collation : Cambia la colección de la BD
ALTER DATABASE PruebaCOLLATE {<>}
Recovery Model : Existen tres modo de recuperación:
*Simple: Utiliza muy poco el log de transacciones, es utilizado para ambientes de desarrollo en donde lno es tan importante recuperar información perdida cuando hay algún fallo en el sistema. En caso de un fallo se debe restaura el ultimo full backup realizado.
*Bulk-logged: Utiliza el log de transacciones, ignorando únicamente las inserciones masivas. Con este modelo es posible recuperar mas datos en caso de una falla, únicamente se perderían los datos insertados masivamente. Este modelo es utilizado en ciertos casos cuadno se quiere realizar inserciones masivas sin comprometer el rendimiento del Servidor
*Full: Registra todo en el Log de transacciones. Usando este modelo es posible restaurar la BD reduciendo la perdida de información en caso de un fallo.
ALTER DATABASE Prueba SET RECOVERY FULL
Compatibility Level : Especifica el nivel de compatibilidad de la BD, por defectos las bases de datos de SQL SERVER 2005 tiene el nivel de compatibilidad configurado con el valor 9, las de 2000 las tendría en 8 y así sucesivamente
Auto Close: Cierra la BD después de que el ultimo usuario se desconecta de la BD. Esta opción decremento la cantidad de recursos que necesita el servidor a para base de datos que se consulta muy poco.
Auto Create Statistics: Permite que se generen estadísticas automáticamente. El optimizador de consultas utiliza estas estadísticas para determinar la mejor manera de buscar los datos en la BD
Auto Shrink: Permite que la BD automáticamente libere espacio que no esta reservado para la BD pero no esta siendo utilizado. Esta acción es automáticamente realizada por el sistema cuando se realiza un backup de log de transacciones y debe realizarse manualmente cuando la cantidad de espacio libre reservado a la BD es superior al 25 %
Auto Update Statistics: Actualiza automáticamente las estadísticas
Auto Update Statistics Asynchronously: modo en que trabajara el Update Statistic
Close Cursor on Commit : Cierra automáticamente los cursores abierto cuando se realiza un commit
Default Cursor: Especifica el ámbito de cursor. Puede ser Global o Local
ANSI NULL Default: Cuando esta en ON cualquier comparación con nulo será igual a 0, en el caso contrario dará Nulo
ANSI NULLS Enabled: Especifica si el Ansi Null esta habilitado o no
ANSI Padding Enabled:Cuando esta en On los datos almacenados en un campo string que tengan longitud inferior al tamaño del campo serán rellenados con espacios en blanco, en el caso contrario será almacenado tal cual como fue insertado
ANSI Warnings Enabled : Genera alarmas cuando en una función agregada como SUM o AVG es encontrado un valor nulo
Arithmetic Abort Enabled: Cuando un error de división por cero se da la consulta es cancelada y el mensaje de error es ilustrado. Cuando es configurado en off la consulta continua
Concatenate Null Yields Null: Especifica que cualquier cosa que sea concatenado con nulo de nulo
Date Correlation Optimization Enabled: Cuando esta opción es configurada con ON, el servidor guarda estadísticas de la relación Foreingn Key que existe entre dos tablas
Numeric Round-Abort: Cuando es configurada en ON cualquier perdida de precisión generara u mensaje de error
Quoted Identifiers Enabled : Permite el uso de doble cuotaciones para especificar el nombre de un objeto en T-SQL
Recursive Triggers Enabled: Habilita los disparadores recursivos
Page Verify: Especifica como debe validar la información de las paginas de la BD. Checksum oTornPageDetection
Database Read-Only: Configura la BD como solo lectura
Restrict Access: Existen tres posible socnfiguraciones:
Multiple: todo ususrioa que tenga permiso accedera a la BD
Single Only: Solo un usaurio podra acceder ala BD al tiempo
Restricted: Unicamente los usuarios db_owner, dbcreator y sysadmin pueden acceder a la BD
Eliminación de una Base de datos de Usuario
Para eliminar una base de datos, usted puede hacerlo utilizando el siguiente código T-SQL
DROP DATABASE Prueba
