Creación de Base de Datos

Base de datos del sistema

Las 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 Usuario

Una 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 FILEGROUP

Un 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 master


GO


CREATE 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 )


GO

Si 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 Secundary

Por 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).aspx


Modificació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 GO

Si 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] DEFAULT

Despues 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 FULL

Las 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