Microsoft SQL Server Para DBA Accidental, Monografías, Ensayos de Programación de Bases de Datos

¡Descarga Microsoft SQL Server Para DBA Accidental y más Monografías, Ensayos en PDF de Programación de Bases de Datos solo en Docsity!

Prólogo

Recuerdo una noche de otoño del 1993, cuando abrí el manual de SQL Server 4.21A, y me dispuse a instalar SQL Ser por primera vez en mi vida. Hasta ese momento, yo me consideraba un buen programador en varios dialectos de Basic (la ignorancia es muy atrevida), sobre todo Visual Basic, y manejaba decentemente algún sistema de bases de datos como dBase III (y IV), y hasta las primeras versiones de Access.

Pero esto de SQL Server era un animal muy distinto. Su proceso de instalación (sobre todo el análisis de requisitos) me recordó la primera vez que instalé Novell en un proyecto en Indonesia.

Para ser sincero, nunca había estudiado nada sobre bases de datos relacionales, y lo poco que sabía de tablas y consultas lo había aprendido con sistemas que en poco se parecían a SQL Server. La primera sensación tras instalar SQL Server fue de una desorientación total. Devices… Segments… ¿y yo para qué necesitaba meterme en esos berenjenales?

La verdad es que no tenía más remedio que “subirme” a un gestor de bases de datos “serio” porque, intentando utilizar Access como motor de bases de datos de un gran proyecto, aprendí cuales son esos dos ingredientes que hacen que la estabilidad de un sistema de base de datos peligre: datos y usuarios. Access funcionaba muy bien en mi equipo de desarrollo, pero al llevarlo a producción, añadir la ingente cantidad de datos que el proyecto del gasoducto requería, y los muchos usuarios con sus ingeniosas e inesperadas formas de consultar dichos datos, topé con los límites de esta herramienta, y muy probablemente con mis límites como programador para resolverlos adecuadamente.

Muchos años han pasado desde entonces, y con esos años, muchas versiones de SQL Server también. Pero una constante permanece en el mercado: muchos profesionales de diferentes áreas se enfrentan por primera vez a un motor de bases de datos relacional, como SQL Server, sin que nadie se haya preocupado antes de formarles para enfrentarse adecuadamente a este reto, convirtiéndose en DBAs (Administradores de Bases de Datos) accidentales sin quererlo o, al menos, sin tener muy claro qué obligaciones comporta este rol.

El proceso de instalación ha mejorado mucho. Es muy intuitivo, y facilita el trabajo en la gran mayoría de los casos. Sin embargo, una vez instalado, muchos DBAs accidentales siguen experimentando esta desorientación que inundó mis primeras horas con SQL Server.

sobre consultas y procedimientos almacenados. Prestad atención a esos capítulos, porque su aplicación os reportará enormes beneficios.

Tengo la suerte de contar con compañeros que atesoran mayores conocimientos de bases de datos que los que yo nunca tuve, y esto me enorgullece.

Espero que disfrutéis de estas páginas y que os resulten verdaderamente prácticas para vuestro trabajo. Quizá no tengáis intención de ser DBAs profesionales a tiempo completo, pero seguro que estas páginas os ayudarán a disfrutar del plácido ronroneo de un motor de SQL Server bien engrasado y mantenido, gracias a vuestros atentos cuidados.

Fernando G. Guerrero

Ex-MCDBA, ex-MVP, ex-RD, ex-MCT, ex-MCSD, ex-MCSE, pero siempre disfrutando de mis compañeros y amigos de SolidQ

Estructura de la serie de volúmenes

Este volumen es el número 4 de la serie. La serie consta de los siguientes volúmenes:

Volumen Agenda

0: Introducción e Instalación

 Introducción a productos incluidos en SQL Server o (^) Motor Relacional o (^) Reporting Services o (^) Analysis Services

0: Introducción e Instalación

 Instalación y configuración inicial de SQL Server o (^) Proceso de instalación (asistente de instalación) o (^) Configuración estándar  Procesadores  Memoria  Protocolos de Red o (^) Configuración avanzada  Sp_configure

0: Introducción e Instalación

 Herramientas de SQL Server o (^) Management Studio o (^) Database Engine Tuning Advisor o (^) SQL Profiler

1: Seguridad  Seguridad en SQL Server o (^) Claves maestras de servicio y BBDD o (^) Inicios de sesión o (^) Usuarios de bases de datos o (^) Permisos a nivel de objeto o (^) Introducción a encriptación

2: Bases de datos y Mantenimiento

 Bases de datos o (^) Archivos de datos o (^) Agrupaciones lógicas de datos (Filegroups) o (^) BBDDs especiales

2: Bases de datos y Mantenimiento

 Copias de seguridad

2: Bases de datos y Mantenimiento

 Mantenimiento de bases de datos

3: Objetos de Bases de Datos

 Tablas o (^) Introducción o (^) Introducción a estructura física

El Equipo de SolidQ – Relacional en España

 Eladio Rincón  Rubén Garrigós

 Enrique Catalá (^)  Luis J. Morán

 Rubén Serna (^)  Miguel Ángel Magán

Introducción ................................................................... Error! Bookmark not defined.

Bases de datos

Antes de comenzar a indicar como mantener las bases de datos vamos a dar un pequeño repaso de los tipos de bases de datos que tenemos.

Bases de Datos de Sistema

Master

Contiene información de:

 La instancia  Seguridad  Configuración  Las bases de datos contenidas en la instancia  Procedimientos de arranque

Es una base de datos imprescindible ya que sin ella SQL Server no puede arrancar ni trabajar. Hay muchas acciones que son habituales o posibles en las bases de datos que no se pueden realizar en la master:

 Habituales o Añadir filegroups o archivos o Renombrar la base de datos o Poner la base de datos offline  Posibles o Cambiar la intercalación (collation). Su intercalación será la de la instancia que se define en la instalación o Cambiar el propietario de la base de datos o Borrar la base de datos o Configurar el método de alta disponibilidad mirroring o Borrar el filegroup primary, el archivo de datos o el log o Establecer la base de datos en modo read_only o Crear triggers en tablas de sistema

Model

Es una base de datos que se configura como plantilla para crear las base de datos de usuario. Cualquier propiedad que se configure en esta base de datos se aplicará en las bases de datos que creen después del cambio. Esta base de datos siempre debe existir cada vez que se arranca SQL Server.

Tiene las mismas restricciones que la base de datos master más la siguiente:

 Crear procedimientos, vistas o triggers con la propiedad “with encryption”

Msdb

Esta base de datos de sistema es utilizada por utilidades secundarias de SQL Server:

 El agente de SQL Server  Services Broker  Database Mail  Backups  Información de Log Shipping

Tiene las mismas restricciones que la base de datos master excepto:

 Añadir ficheros  Añadir filegroups

Resource Database

Es una base de datos de solo lectura que contiene todos los objetos incluidos en SQL Server. Esta base de dato no aparece en la lista de base de datos de sistema pero realmente existe, de hecho el nombre de los archivos físicos son:

 mssqlsystemresource.mdf  mssqlsystemresource.ldf

Esta base de datos proporciona dichos objetos en el esquema sys de cada base de datos. Además sirve para proporcionar un upgrading más rápido a una nueva versión de SQL Server.

Tempdb

Esta base de datos tiene las siguiente utilidades:

 Almacenar los objetos temporales ya sean de usuario o de sistema  Reconstrucción de índices  Versionado de registros en los modos read commited snapshot o allow snapshot isolation

Se crea cada vez que se reinicia la instancia, por lo cual nos puede servir para saber cuándo ocurrió por última vez este evento. Su modo de recuperación es modo simple y no se puede cambiar (Ver apartado Modos de recuperación en backups).

Las siguientes operaciones no se pueden realizar en la tempdb:

 Añadir filegroups  Hacer backup o restore  Cambiar la intercalación de la base de datos

En SQL Server actualmente no existes las bases de datos completamente autocontenidas, solo existen las siguientes opciones:

 No contenidas (“None”)  Parcialmente Contenidas (“Partial”)

Estas opciones se configuran en la propiedad tipo de contención (“Containment Type”) de la base de datos:

En una base de datos parcialmente autocontenida hay datos que pertenecen a las base de datos y datos que pertenecen a la instancia. Datos que pertenecen a la base de datos son:

 Tablas de sistema  Usuarios y passwords

Datos que pertenecen a la instancia son:

 Algunas tablas de sistema ej: “sys.endpoints”  Usuarios vinculados a los logins  Tablas de otras bases de datos referencias por nombres de tres partes (“base de datos”.”esquema”.”tabla”)

Beneficios

Permite conectar directamente a la base de datos, permitiendo usuarios duplicados , uno pueden pertenecer a la instancia y otros a bases de datos parcialmente autocontenidas. Esto es especialmente útil en consolidaciones de muchas bases de datos donde es posible que usuarios coincidan pero a cada uno hubiera que darle permisos distintos.

Instantáneas de Bases de Datos (Snapshot)

Las instantáneas de bases de datos (“ Snapshot ”) son bases de datos de solo lectura “foto” de una base de datos en un momento determinado.

El funcionamiento es el siguiente:

 Una vez que se hace la “ foto ” de la base de datos se marcan las páginas de la base de datos de manera que cuando se vayan a modificar antes de la operación se copia dicha página a la base de datos snapshot. Esto implica que la base de datos snapshot irá creciendo y aumentando su tamaño conforme se vayan modificando los datos en la original

 A la hora de hacer una consulta sobre la base de datos snapshot si una página se encuentra en la base de datos snapshot lee sus datos, sino se encuentra consulta en la base de datos original ya que la página no se habrá modificado

Solo se puede crear bases de datos Snapshot en SQL Server con Transact SQL :

CREATE DATABASE Reducir_snapshot_1800 ON ( NAME = Reducir, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Reducir1dat_1200.ss') AS SNAPSHOT OF Reducir; GO

Donde:

 Reducir_snapshot_1800 es el nombre de la base de datos que vamos a crear. Se aconseja usar como nomenclatura poner el nombre de la base de datos original + sufijo snapshot + la hora o el día y la hora  “Name”, el nombre del archivo lógico de la base de datos original. Debe haber una equivalencia uno a uno entre los archivos de la base de datos original y la de snapshot  “Filename”, el nombre del archivo físico en el sistema operativo. A estos archivos se les llama archivos dispersos y pueden tener cualquier extensión aunque se aconseja (.ss)  Reducir es el nombre de la base de datos original

Beneficios  Antes de un proceso de modificación de datos puede servir como base de datos de respaldo para en caso de error proceder rápidamente a volver a modificar los datos de la base de datos original y dejarlos como estaban originalmente  Como base de datos de consulta. Siempre teniendo en cuenta que es una foto de un momento que ha pasado. Su tamaño será reducido siempre y cuando no haya habido muchas modificaciones en la base de datos original

Estándar

Podemos definirlas como las bases de datos que no son ni parcialmente autocontenidas ni snapshot. Tienen total dependencia con la instancia. Las operaciones que se pueden realizar sobre estas bases de datos se detallan a continuación.

4. El botón tipos de objeto (“Object Types”) muestra los tipos de objetos sobres los que se realizará la búsqueda. En este caso logins. Pulsamos el botón Ok
5. En la caja de texto si escribimos un login y pulsamos el botón “Check Names” validará el login

6. El botón “Browse…” permite seleccionar de la lista un login
7. Seleccionamos un login y pulsamos el botón OK

1. Nos situamos en la carpeta Bases de Datos (“Databases”) de SQL Server Management Studio, pulsamos el botón derecho del ratón y seleccionamos la opción tareas (“Tasks”). Después seleccionamos la opción Poner Offline (“Take Offline”)
2. Tras poner la base de datos offline, el icono de la base de datos cambia

Poner Base de Datos con Conexión (Online)

Poner la base de datos “Online” deshace la opción anterior, permitirá establecer conexiones sobre ella y por tanto trabajar sobre ella. Para hacer esta operación:

1. Nos situamos en la carpeta Bases de Datos (“Databases”) de SQL Server Management Studio, pulsamos el botón derecho del ratón y seleccionamos la opción tareas (“Tasks”). Después seleccionamos la opción Poner Offline (“Bring Online”).
2. Tras poner la base de datos online, el icono de la base de datos se reestablece