Estudiar, analizar, aprender.. : Administración de SQL Server 2014

Introducción a la plataforma SQL Server 2014

El siguiente post recoge información de utilidad, describiendo de manera general las diferentes posibilidades disponibles desde el punto de vista de la administración de bases de datos en la plataforma SQL Server 2014 de Microsoft.

SQL Server es un sistema de gestión de base de datos relacional (SGBDR), lo que le confiere una gran capacidad para gestionar datos, conservando su integridad y su coherencia.

SQL Server está completamente integrado en Windows a varios niveles:

Visor de eventos. Utiliza registro aplicaciones para grabar los errores generado por SQL Server.
Analizador de rendimientos. Detección de cuellos de botella para optimizar rendimiento.
Tratamiento en paralelo. Cada instancia de SQL se ejecutan en su propio proceso de ejecución y varios hilos distintos de Windows.
Seguridad. Gestionada por Windows, lo que permite a los usuarios con un único usuario/contraseña.
Servicios de Windows. Parada, inicio y suspensión del servidor es más fácil.
Active Directory. Permite buscar instancias de SQL Server en ejecución.

SQL Server puede gestionar dos tipos de bases de datos:

OLTP (OnLine Transactional Processing). La información se almacena directamente para reutilizarla tal y como fue almacenada.
OLAP (OnLine Analtytical Processing). Contienen información estadística para obtener información en forma de cubos multidimensionales, lo que permite extraer conclusiones y ayudar a la toma de decisiones.

Arquitectura de SQL Server

SQL Server es una plataforma integrada, para la gestión de datos de la empresa con un coste bajo. Es un sistema robusto y estable, gestiona datos de la organización y el análisis de los mismos.
Ofrece alta disponibilidad de los datos (24/7), con capacidades para minimizar los tiempos de inactividad potencial.
Desde el punto de vista de la seguridad, se ha construido desde cero con los mayores estándares de seguridad integrada con Windows.
Las diferentes versiones de SQL Server permiten una escalabilidad adecuada para cubrir las diferentes necesidades que se presentan en las empresas.
Comentar que el costo de propiedad es bajo, y cuenta con herramientas de administración de fácil manejo .

Componentes de SQL Server

Database Engine. Motor de base de datos relacional basado en el lenguaje SQL.
Analysis Services. Motor de procesamiento analítico en línea (trabaja por cubos).
Integration Services. Coordinan el movimiento de datos entre los componentes SQL Server y los sistemas externos.
Reporting Services. Generación de informes basados en servicios web y portal web.
Master Data Services. Herramienta para la gestión de datos maestros o de referencia.
StreamInsight. Plataforma de aplicaciones para procesar eventos de alta velocidad.
Data Mining. Permite derivar conocimientos y percepciones de cualquiera de los datos OLAP o relacionales.
Full-Text Search. Permite la construcción de sofisticadas opciones de búsqueda en las aplicaciones.
PowerPivot. Permite el análisis de grandes volúmenes de datos desde diferentes lugares de forma rápida.
Replication. Proceso que permite mover datos entre los servidores que se adaptan a las necesidades de distribución de datos.
Data Quality Services. Permite la construcción de una base de conocimientos para la limpieza de datos.
PowerView. Permite una rápida visualización de datos desde SharePoint.

Instancias de SQL Server

Un servidor SQL Server puede estar compuesto por una o varias instancias independientes.
Una instancia podemos entenderla como una instalación de SQL Server. A veces es útil instalar más de una copia de un componente de SQL Server en un mismo servidor. Muchos componentes de SQL Server se pueden instalar más de una vez como instancias independientes, esto resulta útil por ejemplo, para tener diferentes entornos de seguridad para conjuntos de bases de datos.
Cada instancia de SQL tienes sus propias opciones de configuración de servidor, y pueden instalarse también varias ediciones de SQL Server diferentes.
Las aplicaciones pueden requerir diferentes intercalaciones de nivel de servidor. Para ello podríamos realizar varias instalaciones con varias instancias en una máquina, y cada aplicación que accediese al servidor correspondiente.
En cada máquina puede existir una instancia predeterminada, y el resto se conoce como instancias con nombre.
Cada instancia tiene su propio conjunto de programas específicos de instancias y de archivos de datos, así como archivos comunes que comparten todas las instancias en el equipo.
Las instancias de algunos componentes como Analysis Services o Reporting Services, tambien tienen su propio conjunto de programas y archivos de datos.
Cada instancia funciona de manera independiente, y las aplicaciones se pueden conectar a cualquiera de ellas.
La instancia predeterminada, se identifica mediante el nombre de red del equipo en el que se ejecuta, y se denomina MSSQLSERVER.
Las instancias con nombre se identifican como Nombreequipo\nombreinstancia. El nombre de la instancia puede comenzar con una letra, el símbolo & o el carácter de subrayado.
Cada instancia con nombre se compone de un conjunto de servicios y puede tener una configuración diferente para las intercalaciones, la seguridad u otras opciones del servidor.
La estructura de directorios, de registros y los nombres de servicios reflejan el nombre de instancia que se especifica.
A partir de la versión 2005 es posible tener 50 instancias con nombre. Desde la versión 2012 se permiten tener varias instancias de SQL Server Integration Services. Por tener varias instancias en una misma máquina, no es necesario instalar las herramientas y utilidades de SQL Server más de una vez. Podemos gestionar todas instancias de manera centralizada.

Ediciones de SQL Server

Parallel Data Warehouse. Utiliza procesamiento paralelo masivo para ejecutar consultas de gran cantidad de datos de forma masiva.
Enterprise. Proporciona altos niveles de fiabilidad para cargas de trabajo exigentes.
Business Intelligence. Plataforma que permite compilar y ejecutar soluciones BI, seguras y escalables.
Standard. Plataforma de gestión de datos completa.
Express. Edición gratuita para web y pequeñas aplicaciones basadas en servidor.
Compact. Edición gratuita para uso en aplicaciones móviles que se conectan ocasionalmente. Optimizado para poca memoria.
Developer. Permite probar toda la funcionalidad de SQL Server.
Web. Plataforma segura, rentable y escalable para sitios web públicos y aplicaciones.
SQL Azure. Aplicaciones SQL basada en la nube.

Herramientas de administración de SQL Server

SQL Server Management Studio (SSMS)
Entorno integrado para tener acceso, configurar, administrar y desarrollar todos los componentes de SQL Server y bases de datos. Combina herramientas gráficas y scripts enriquecidos. Esta es la herramienta utilizada para realizar la mayoría de las operaciones por parte de los administradores.

SQL Server Configuration Manager.
Herramienta para administrar los servicios asociados a SQL Server, para configurar los protocolos de red utilizados y administrar la configuración de conectividad de red de los equipos cliente de SQL Server.
En un complemento accesible desde el menú inicio.

SQL Server Profiler
Captura eventos de SQL Server. Estos se guardan en archivo de traza que posteriormente se pueden analizar o utilizar para reproducir series de pasos para diagnosticar un problema.
Se utiliza para seguir los pasos de consultas con problemas. También para monitorizar consultas de ejecución lenta. Capturar la serie de ejecuciones Transact-SQL que ha causado un problema, o supervisar el rendimiento de SQL Server para optimizar las cargas de trabajo. También permite establecer correlaciones entre indicadores de rendimiento para diagnosticar problemas.

SQL Server Database Engine Tuning Advisor (DTA)
Asistente para la optimización del motor de la base de datos. Ayuda a seleccionar y crear un conjunto de índices, vistas indexadas y particiones sin necesidad de conocer la estructura de la base de datos.
Analiza una carga de trabajo y la implementación física de una o más bases de datos.

SQL Server Import and Export
Asistente para realizar importaciones y exportaciones desde SQL Server a otras aplicaciones o viceversa.

Utilidad sqlcmd
Nos permite especificar instrucciones procedimientos del sistema y archivos de script de Transact-SQL en el Símbolo del Sistema.

Utilidad bcp
Se utiliza para copiar datos entre una instancia de SQL Server y un archivo de datos especificado por el usuario.

Windows PowerShell
Se pueden configurar y realizar consultas de SQL Server.

SQL Server Management Studio

Es la principal herramienta suministrada por Microsoft, para interactuar con los servicios de SQL Server.
Es un entorno integrado creado dentro de la plataforma de Visual Studio. Se podrá realizar:

Consultas de datos con Transact-SQL
Tareas de administración y desarrollo de forma gráfica para configuración de bases de datos y servidores.
Gestión de conexiones a componentes de SQL Server.

NOTA: La vista habitual que se muestra en la herramienta de Management Studio es la dos paneles principales. A la izquierda, el explorador de objetos, donde es posible conectarse a diferentes servidores y bases de datos. Y a la derecha, el panel de escritura de código Transact-SQL, donde se escribirán las consultas a la base de datos.
Es importante destacar, que la conexión en cada uno de los paneles puede ser distinta, utilizando un usuario distinto e incluso trabajando contra un servidor distinto.

Utilidad sqlcmd

Esta utilidad nos permite trabajar en línea de comandos. No siempre es posible trabajar con una herramienta gráfica como SQL Server Management Studio.
Permite ejecutar instrucciones y sentencias Transact-SQL a partir del Símbolo del Sistema, y programar trabajos por lotes. Utiliza OLEDB para ejecutar los lotes de sentencias Transact-SQL.

Ejemplos de uso:

C:\> sqlcmd : Trata de conectarse a la instancia predeterminada local usando autentificación de Windows, ya que no se especifica ni servidor ni credenciales de identificación.
C:\>sqlcmd -E: Conectarse a la instancia predeterminada con autentificación de Windows.
C:\>sqlcmd -S[NOMBRE_MAQUINA\NOMBRE_INSTANCIA] -E: Se desea conectar a una instancia con nombre, utilizando la autentificación de Windows.
C:\>sqlcmd -Uusuario -Pcontraseña: Conexión a la instancia predeterminada con una autentificación concreta.
C:\>sqlcmd -E -i"c:\archivo.sql": Conexión a la instancia predeterminada, con autentificación de Windows y lo que debe ejecutar se encuentra en el fichero indicado.
C:\>sqlcmd -E -i"c:\archivo.sql" -o"c:\resultado.txt": Conexión a la instancia predeterminada con autentificación de Windows, ejecutar las intrucciónes recogidas en el fichero "archivo.sql", y depositar la información de salida en el fichero "resultado.txt"
Se puede consultar el resto de argumentos disponibles en para esta utilizar en la ayuda de SQL Server. (Ejecutar en CMD el comando "sqlcmd -?")

Configuración de los servicios de SQL Server

La herramienta SQL Server Configurarion Manager se utiliza para administrar los servicios asociados configurar los protocolos de red utilizados y administrar la configuración de la conectividad de red de los equipos cliente de SQL.
Esta herramienta se utiliza para iniciar, pausar, detener o reiniciar los servicios de Windows asociados a SQL Server. Además, para cada uno de los servicios, es posible configurar el modo de inicio y las cuentas de servicio asociadas así como propiedades avanzadas como ciertos parámetros de inicio.
Además de la herramienta anteriormente mencionada, es posible también utilizar la herramienta de Servicios de Windows, además de utilizar la línea de comandos.

Instalación de SQL Server 2014

Pasos:

Revisar los requisitos de instalación, las comprobaciones de la configuración del sistema y las consideraciones de seguridad para una instalación de SQL Server.
Ejecutar el programa de instalación.
Utilizar las utilidades de SQL Server para configurar SQL Server.

Requisitos de hardware y software:

Utilizar sistemas de archivo NTFS.
.NET Framework 3.5 SP1. Si se utilizarán los componentes del motor de base de datos, Reporting Service, Replicación o SQL Server Management Studio.
Windows PowerShell 2.0 es un requisito previo de instalación para los componentes del motor de base de datos y SQL Server Management Studio.
Software de red. Los sistemas operativos admitidos para SQL Server 2014 tienen software de red integrados. Serán necesarios los protocolos de memoria compartida, canalizaciones con nombre, TCP/IP y VIA, éste último en desuso.
6GB de disco duro como mínimo. 4GB de memoria mínima recomendada y 2Ghz mínimo de procesador.
Internet para poder completarse la instalación

Proceso de instalación:

Validación de reglas de instalación.
Selección de los componentes a instalar. Instalar solamente los necesarios, de esta manera, se evita la sobrecarga con elementos que no se van a utilizar. Es posible instalar componentes posteriormente. Inicialmente podríamos seleccionar: Motor de la base de datos, componentes de la documentación y las herramientas de administración.
Configuración de la instancia (nombre y identificador). Se pueden instalar una o varias instancias del motor de la base de datos. Cada una será independiente y se podrá configurar de manera distinta. Cuando hay varias instancias se utiliza el nombre de cada una para diferenciarlas.
Cuentas de servicio. Los servicios instalados tienen asociadas unas cuentas de inicio. Estas cuentas pueden ser cuentas de dominio, cuentas de usuario local, cuentas del sistema integradas, cuentas de servicio administradas o cuentas virtuales. En este paso se pueden asociar estas cuentas a los servicio. Los servicios más habituales a configurar son el motor de la base de datos SQL Server, el agente SQL Server y SQL Server Browser. Los tipos de cuentas de inicio son: cuentas integradas del sistema (cuenta de servicio local, cuenta de servicio de red y cuenta de sistema local), cuentas de usuarios locales, cuentas de usuarios de dominio y cuentas virtuales. La cuenta del servicio local (NT AUTHORITY/LOCAL_SERVICE) es una cuenta integrada que tiene el mismo nivel de acceso que el grupo de usuarios. Esta cuenta no se admite para los servicios de SQL Server ni del agente ya que accede a los recursos de red como una sesión con credenciales nulos. La cuenta de servicio de red tiene un mayor nivel de acceso a recursos mediante credenciales de la cuenta de equipo con el formato nombre_dominio\nombre_equipo (NT AUTHORIY\Servicio de red), asignada por defecto para el motor de base de datos y agente. La cuenta de sistema local cuenta con un alto nivel de privilegios en el sistema local y actua com el equipo en la red (NT AUTHORIY\System). Las cuentas de usuario locales son útiles cuando el equipo no pertenece a un dominio pero sin permisos de administrador de Windows. Las cuentas de usuario de dominio se utilizan cuando los servicios deben interactuar con servicios de red, o tener acceso a recursos de dominio (como los recursos compartidos de archivos), o si utiliza conexiones con el servidor vinculadas a otros equipos que utilizan SQL Server. Debe de tener privilegios mínimos y el administrador de dominio debe de haber creado esta cuenta previamente. Finalmente, las cuentas virtuales (solo con Windows 7 y Windows Server 2008 R2) puede tener acceso a la red en un entorno de dominio (NET SERVICE\nombreservicio). Los servicios que se ejecutan con cuentas virtuales, acceden mediante las credenciales de la cuenta del equipo (nombredominio\nombremaquina). Debe de dejarse la contraseña en blanco.
Configuración de intercalación. Permite definir las reglas de comparación y ordenación. Existen tres tipos de intercalación: Intercalación de Windows (se basan en los parámetros de los idiomas definidos en Windows), con ello, las sentencias de comparación y ordenación se adaptan automáticamente al idioma del servidor. Intercalaciones binarias, adecuadas por su rapidez de tratamiento, se basan en el código binario utilizado para registrar cada caracter de información en formato UNICODE. Intercalaciones SQL Server, que aseguran la compatibilidad con las versiones anteriores de SQL Server (recomendable no seleccionar esta opción en una instalación nueva)
Configuración del motor de la base de datos. Se podrá especificar el modo de autentificación de Windows o modo mixto. En el primer caso, sólo se permitirá conectarse al servidor SQL Server mediante el inicio de Windows, y en el segundo caso, se podrán utilizar inicios de sesión de Windows y SQL Server, especificando en éste último una contraseña.
Comprobación de reglas de validación.
Inicio del proceso de instalación y reinicio de la máquina.

Comprobaciones Post-Instalación:

Herramienta SQL Server Configuration Manager. Comprobaremos si el servicio principal SQL Server ha arrancado. Podemos aprovechar para cambiar la configuración del mismo. Adicionalmente, se comprobarán los protocolos habilitados para la instancia.
Herramienta SQL Server Management Studio. Comprobaremos si es posible conectarse al servidor de SQL Server. Tras indicar la autentificación, deberemos observar como en el explorador de objetos se muestran todas las carpetas que cuelgan del servidor. Para comprobar que el servidor responde a peticiones de cliente, realizaremos una consulta de selección (p.e. Select * FROM SYS.sysdatabases).

Administración de la seguridad de SQL Server

Modos de autentificación en SQL Server

La autentificación es el proceso por el cual se comprueba que la identidad del usuario es válida, y es quien dice ser. Para ello, se distinguen dos tipos de autentificación:

Autentificación de Windows: Los usuarios se autentican mediante una cuenta de usuario de Windows, obteniendo acceso a SQL Server. Los usuarios se conectan a través de una conexión de confianza.
Autentificación de Windows y SQL Server (Modo mixto): Los usuarios se conectan mediante una conexión que no es de confianza, proporcionando un nombre de inicio de sesión y una contraseña válida. En todos servidores SQL Server existirá un inicio de sesión llamado "sa", que se deshabilitará en caso de iniciar sesión con Windows.

Directivas de contraseña

SQL Server utiliza las mismas directivas de contraseña que Windows (complejidad y expiración). Se aplican a un inicio de sesión que utiliza la autentificación SQL Server y a un usuario con contraseña de una base de datos independiente. Las contraseñas deben de cumplir algunas de las siguientes directrices:

La contraseña no debe de contener el nombre de la cuenta de usuario.
Debe contener 8 caracteres como mínimo.
Deben utilizarse letras en mayúsculas, minúsculas, números y caracteres no alfanuméricos (almohadilla, signos de admiración, signo de moneda, porcentaje.. ).
Hasta 128 caracteres. Se recomienda utilizar las más largas posible.

La aplicación de las directivas de contraseña se pueden configurar independientemente para cada inicio de sesión de SQL Server mediante Transact-SQL (ALTER LOGIN) o mediante Management Studio.

Administrar los inicios de sesión

La administración de los inicios de sesión, puede realizarse mediante la herramienta Management Studio, o mediante Transact-SQL (CREATE LOGIN, ALTER LOGIN, DROP LOGIN).

Autorizar el acceso a las bases de datos

Es necesario distinguir entre autenticación y autorización:

Autenticación: Es la verificación de la entidad de seguridad. Es la comprobación de ser quien dice ser.
Autorización: Es la asignación de permisos a una entidad de seguridad sobre un protegible o asegurable.

Entidad de seguridad son aquellas entidades autenticadas en un sistema SQL Server. Es cualquier identidad autenticada a la que se puede conceder permiso para poder tener acceso a un objeto del sistema de datos. Pueden distinguir:

Entidades de seguridad principal indivisible. Son identidades únicas como por ejemplo son los inicios de sesión.
Entidades de seguridad de colección. Son colecciones de identidades, como por ejemplo las funciones de servidor.

Las entidades de seguridad existen en tres niveles, a nivel de Windows, a nivel de SQL Server y a nivel de base de datos.

Entidades de seguridad a nivel de Windows: Cuentas de usuario local, cuantas de usuario de dominio y los grupos de Windows.
Entidades de seguridad a nivel de SQL Server: Inicios de sesión de SQL Server y funciones de servidor.
Entidades de seguridad a nivel de Base de Datos: Usuarios de la base de datos, las funciones de base de datos y las funciones de aplicación y grupos de base de datos (esto último por compatibilidad con versiones anteriores).

Los protegibles son aquellos objetos cuyo acceso está regulado por el sistema de autorización de SQL Server. Los protegibles se organizan en jerarquías anidadas llamadas ámbitos (que también se pueden proteger).

Los protegibles a nivel de Windows son:

Archivos
Claves de registro

Los ámbitos protegibles a nivel de SQL Server son:

Ámbito de Servidor: Inicios de sesión, Extremos y Bases de Datos.
Ámbito de Base de datos: Usuarios, Funciones, Funciones de aplicación, Certificados, Claves simétricas y asimétricas, Eventos DDL y Esquemas.
Ámbito de Esquema: Tablas, Vistas, Funciones, Procedimientos, Tipos, Sinónimos y Agregados.

Hay que tener en cuenta, que una entidad de seguridad puede ser también un protegible. Por ejemplo, un inicio de sesión puede tener permisos sobre otro inicio de sesión.

SQL Server usa permisos para controlar el acceso a los protegibles por parte de entidades de seguridad. Los permisos son las reglas que gobiernan el nivel de acceso de las entidades de seguridad a los protegibles. Estos permisos se pueden otorgar, revocar o denegar.
Todos los protegibles tienen permisos asociados que pueden otorgarse a cada entidad de seguridad. Estos permisos se pueden administrar mediante la herramienta Management Studio o mediante instrucciones Transact-SQL como GRANT, REVOKE o DENY.
Los permisos concretos asociados a cada protegible varían según los tipos de acciones compatibles con cada protegible.

Determinados permisos se pueden heredar a través de un permiso concedido en un nivel más alto en la jerarquía del protegible.

Una entidad de seguridad puede realizar una determina acción si el permiso se ha concedido explícitamente a la entidad o a una colección a la que pertenece la entidad de seguridad, y además, el permiso no se ha denegado explícitamente a dicha entidad.

Algunos de los permisos que se pueden conceder, revocar o denegar sobre los deferentes objetos existentes a nivel de sevidor, de base de datos o esquema son: CREATE, ALTER DROP, CONTROL, CONNECT, SELECT, EXECUTE, UPDATE, DELETE, INSERT, TAKE OWNERSHIP, VIEW DEFINITION y BACKUP.

Usuarios Especiales

Son usuarios predefinidos con funciones especiales como permitir el acceso a la base de datos con permiso de administrador o invitado. Existen dos usuarios especiales:

Usuario dbo. Este usuario existe en todas las bases de datos. A este usuario se asigna el inicio de sesión "sa" y los miembros de la función "sysadmin". Todos inicios de sesión con permisos de administrador acceden a la base de datos con esta cuenta de usuario. Esta cuenta no puede eliminarse.
Usuario guest (invitado). Esta cuenta de usuario está disponible en todas las bases de datos, aunque se encuentra deshabilitada de forma predeterminada, excepto en la base de datos "master" y "tempdb", en las que se encuentra siempre habilitado. Es el usuario que permite que aquellos inicio de sesión que no tienen cuenta de usuario en una base de datos, puedan realizar el acceso. Los permisos que se pueden aplicar a este usuario son los mismos que al resto.

Funciones de Servidor

Los inicios de sesión nos permiten establecer conexión con el servidor en lo que se conoce como proceso de autentificación.
Mediante las cuentas de usuario, podemos acceder a las bases de datos bajo una serie de permisos concretos. Tanto los inicios de sesión como las cuentas de usuario están sujetos a permisos que limitan el margen de actuación de manera controlada.
Estos permisos se pueden asignar directamente sobre el inicio de sesión o cuenta de usuario, o también pueden asignarse mediante el uso de las funciones de servidor.
Las funciones de servidor permiten agrupar usuarios en una misma unidad, a la cual es posible aplicar permisos.
Existen una serie de funciones conocidas como funciones fijas de servidor. Tienen una serie de permisos establecidos a nivel de servidor que no es posible modificar.

Funciones fijas de servidor:

Sysadmin: Permite realizar cualquier actividad, es decir, aquellos inicios de sesión que pertenezcan a Sysadmin, tendrán permisos de administrador a nivel de servidor.
Dbcreator: Permite crear y modificar bases de datos.
Diskadmin: Asigna permisos para poder administrar archivos de disco.
Serveradmin: Permite configurar las opciones de servidor.
Securityadmin: Permite administrar y auditar inicios de sesión en el servidor.
Processadmin: Realizar la administración de procesos de SQL Server.
Bulkadmin: Permite ejecutar la instrucción BULK INSERT de inserción masiva de datos.
Setupadmin: Configura servidores de réplica y servidores vinculados.

En estas funciones, lo único que es posible realizar es agregar o eliminar inicios de sesión asociadas a las mismas.

Existe una función de servidor especial llamada "Public", que permite especificar los permisos que interesa que tenga. Todos los inicios de sesión pertenecen a la función "Public", por tanto, los permisos especificados para esta función serán asignadas a todos los inicios de sesión.

Los permisos que tiene asignados por defecto son:

VIEW ANY DATABASE: Cualquiera puede listar las bases de datos.
CONNECT en los extremos predeterminados. Permite conectarse mediante cualquiera de los protocolos predeterminados de SQL Server (TSQL Local Machine, TSQL Named Pipes, TSQL Default TCP, TSQL Default VIA).

Funciones definidas por el usuario:

Permiten especificar los permisos asociados para cada una de las funciones de usuario creadas (Disponible a partir de SQL Server 2012). Es decir, se pueden crear funciones de usuario personalizadas con una serie de permisos concretos, y asignar los inicios de sesión que interese.

Una función de usuario es posible crearla desde la herramienta SQL Server Management Studio, o mediante Trantact-SQL:

CREATE SERVER ROLE NOMBRE_FUNCION

A partir de aquí asignaríamos los permisos correspondiente y añadiríamos los inicios de sesión que interesasen.

Permisos de servidor

Es posible asignar permisos a nivel de servidor, sin utilizar las funciones fijas de servidor, las funciones de usuario o la función Public (añadiendo inicios de sesión a las mismas). Es decir, es posible asignar permisos directamente a nivel de servidor.
Los permisos asignados comúnmente a nivel de servidor son a tres protegibles concretos, el propio servidor, los inicios de sesión y las bases de datos:

CONNECT SQL (Servidor): Permite conectarse al servidor.
CREATE LOGIN (Servidor): Crea un inicio de sesión.
CREATE DATABASE (Servidor): Permite crear bases de datos.
ALTER ANY DATABASE (Servidor): Modifica cualquier base de datos.
CONTROL SERVER (Servidor): Permite realizar un control administrativo de todo el sistema.
SHUTDOWN (Servidor): Permite realizar paradas del servidor.
VIEW SERVER STATE (Servidor): Muestra el estado del servidor.
ALTER (Inicio de sesión): Permite modificar un inicio de sesión.
IMPERSONATE (Inicio de sesión): Permite suplantar la identidad de un inicio de sesión.
CREATE TABLE (Base de datos): Habilita la posibilidad de crear tablas en la base de datos.
ALTER ANY USER (Base de datos): Modifica cualquier usuario en la base de datos.
CONTROL (Base de datos): Asigna control completo sobre la base de datos.
CREATE SCHEMA (Base de datos): Permite crear esquemas en la base de datos.
CREATE USER (Base de datos): Habilita la creación de usuarios en la base de datos.
VIEW DATABASES STATE (Base de datos): Permite ver el estado de la base de datos.
BACKUP DATABASE (Base de datos): Permite realizar copias de seguridad de la base de datos.

La asignación de estos permisos a nivel de servidor, es posible hacerlo desde la herramienta SQL Server Management Studio o mediante sentencias Transact-SQL. Para ello se utiliza la palabra clave GRANT con la siguiente sintaxis:

GRANT {securable_permission [,...n]}
[ON securable_type :: securable_name]
TO login [,...n]
[WITH GRANT OPTION]
[AS {group | role}]

Los parámetros configurables son los siguientes:

Securable_permission: Es el permiso específico que se concede al protegible.
Securable_type: Es el tipo de protegible del ámbito de servidor al que se le aplica el permiso. Este cláusula se omite si se aplica al propio servidor.
Securable_name: Es el nombre del protegible del ámbito de servidor.
Inicio de sesión: Al que se le otorga el permiso.
With GRANT Option: Opción que permite que el cesionario conceda el mismo permiso a otros.
AS group | role: Es una especificación de una entidad de seguridad con los permisos necesarios para conceder este permiso del ámbito de servidor. Se requiere en escenarios en los que el otorgante no tienen todos los permisos necesarios para conceder el permiso, pero es miembro de una función o grupo que sí los tiene.

Para los permisos de protegibles del ámbito de servidor, la instrucción GRANT debe de ejecutarse en la base de datos MASTER.

Funciones de Base de Datos

Para administrar con facilidad los permisos sobre las bases de datos, SQL Server proporciona una serie de funciones, funciones similares en concepto a las funciones de servidor. Estas funciones se aplican a toda la base de datos en lo que respecta al ámbito de permisos.

SQL Server proporciona dos tipos de funciones:

Funciones fijas de base de datos. Proporcionan a las agrupaciones privilegios administrativos sobre la base de datos para las tareas comunes. Por ejemplo, la función "Db_accessadmin" permite agregar o quitar usuarios, grupos y funciones de base de datos. "Db_backupoperator" realizar una copia de seguridad de la base de datos. "Db_datareader" lee datos desde cualquier tabla. "Db_datawriter" agrega, cambia o elimina datos de cualquier tabla. "Db_ddladmin" agrega, modifica o elimina objetos de la base de datos. "Db_denydatareader" no permite la lectura de datos de ninguna tabla. "Db_denydatawriter" que elimina el permiso de poder cambiar los datos de ninguna tabla. "Db_owner" que permite realizar cualquier actividad en la base de datos. "Db_securityadmin" que posibilita el cambiar las funciones de la base de datos, cambiar las funciones de aplicación o crear esquemas. "Public" que permite mantener los permisos predeterminados. Es una función de base de datos especial, a la que pertenece cada usuario de la base de datos (no se puede eliminar). Un usuario posee como mínimo los permisos recogidos en la función Public (p.e. ejecutar instrucciones que no requieren de permisos como la instrucción Print, puede visualizar información de las tablas del sistema, ejecutar ciertos procedimientos almacenados en la base de datos Master y las bases de datos que tenga acceso y podrá obtener acceso a cualquier base de datos con la cuenta de usuario Guest si está habilitada.
Funciones de base de datos definidas por el usuario. Si las funciones de base de datos no se ajustan a nuestras necesidades, es posible utilizar una función de usuario personalizada. De esta manera se permite crear un grupo de usuarios con un conjunto de permisos comunes.

Ejemplos de sentencias para gestionar funciones de datos de usuario, donde se una función de usuario, se añade una cuenta de usuario y después se elimina:

USE Nombre_BBDD

CREATE ROLE Nombre_Funcion

USE Nombre_BBDD

ALTER ROLE Nombre_Funcion ADD MEMBER Usuario

USE Nombre_BBDD

ALTER ROLE Nombre_Funcion DROP MEMBER Usuario

Funciones de aplicación

Las funciones de aplicación permiten cumplir con la seguridad para una aplicación determina. Proporcionan un contexto de seguridad alternativo para que un usuario tenga acceso a una base de datos. El usuario ejecuta un aplicación asociada a un función de aplicación, y el contexto de seguridad de la función de aplicación se usa en lugar de la del usuario individual.

Las funciones de aplicación difieren del resto de funciones de la base de datos.
A continuación se expone una lista de dichas diferencias:

Las funciones de aplicación no tienen miembros. Se activan para los usuarios cuando éstos ejecutan la aplicación.
Las funciones de aplicación permiten que los usuarios dispongan de permisos especiales cuando usan la aplicación y evitan la necesidad de conceder permisos directamente a los usuarios.
Las funciones de aplicación exigen activar una contraseña. Esta contraseña se utiliza para activar la función de aplicación.

Al activar una función de aplicación, los usuarios:

Pierden todos los permisos existentes en la base de datos actual para sus cuentas de usuario y cualquier función a la que pertenezcan, salvo los permisos que se aplican a la función Public.
Heredan todos los permisos concedidos a la función de aplicación en la base de datos actual.

Solamente los miembros de las funciones "Db_owner", "Db_securityadmin" y "sysadmin" pueden crear funciones de aplicación.

Transact-SQL para crear funciones de aplicación:

CREATE APPLICATION ROLE Nombre_Funcion

WITH PASSWORD='contraseña'

Para activar la función de aplicación se realizará de la siguiente manera:

EXEC SP_SETAPPROLE 'Nombre_Funcion", 'contraseña'

Permisos de ámbito de base de datos

En relación a los permisos que tienen que ver con una base de datos, existen dos perspectivas distintas a tener en cuenta:

Permisos de base de datos: son permisos para que una entidad de seguridad pueda ejecutar ciertas tareas dentro de la base de datos.
Permisos del ámbito de base de datos: son permisos que pueden aplicarse a protegibles en el ámbito de base de datos, como usuarios, esquemas, funciones, procedimientos, tablas..

Algunos permisos del ámbito de base de datos son:

ALTER: para protegibles tipo Usuario, con el que se modifica el usuario especificado.
SELECT: para protegibles de tipo Esquema, con el que se permite seleccionar filas de cualquier objeto del esquema.
ALTER: para protegibles tipo Esquema, con el que se permite modificar cualquier objeto del esquema.
TAKE OWNERSHIP: para protegibles tipo Esquema, con el que se puede hacerse con la propiedad del esquema.

Para conceder permisos a una base de datos, puede realizarse mediante el uso de la herramienta SQL Server Management Studio, o por medio de instrucciones Transact-SQL con la sintaxis siguiente:

GRANT {database_permission [,...n]}
TO security_account [,...n]
[WITH GRANT OPTION]
[AS {group | role}]

Los parámetros configurables son los siguientes:

Database_permission: Es el permiso específico que se concede a la base de datos.
Security_account: Entidad de seguridad en nivel de base de datos a la cual se otorga el permiso. Esta entidad de seguridad puede ser un usuario, una función de base de datos o una función de aplicación.
With GRANT Option: Opción que permite que el cesionario conceda el mismo permiso a otros.
AS group | role: Es una especificación de una entidad de seguridad con los permisos necesarios para conceder este permiso del ámbito de base de datos. Se requiere en escenarios en los que el otorgante no tienen todos los permisos necesarios para conceder el permiso, pero es miembro de una función o grupo que sí los tiene.

Para conceder permisos del ámbito de la base de datos, la sentencia GRANT a utilizar es la siguiente:

GRANT {securable_permission [,...n]}
ON securable_type :: -securable_name
TO security_account [,...n]
[WITH GRANT OPTION]
[AS {group | role}]

Los parámetros configurables son los siguientes:

Securable_permission: Es el permiso específico que se concede al protegible.
Securable_type: El tipo de protegible del ámbito de base de datos al que se aplica el permiso.
Securable_name: El nombre del protegible del ámbito de base de datos.
Security_account: Entidad de seguridad en nivel de base de datos a la cual se otorga el permiso. Esta entidad de seguridad puede ser un usuario, una función de base de datos o una función de aplicación.
With GRANT Option: Opción que permite que el usuario al que se le ha concedido el permiso, conceda el mismo permiso a otros.
AS group | role: Es una especificación de una entidad de seguridad con los permisos necesarios para conceder este permiso del ámbito de base de datos. Se requiere en escenarios en los que el otorgante no tienen todos los permisos necesarios para conceder el permiso, pero es miembro de una función o grupo que sí los tiene.

Administración de bases de datos y archivos

Almacenamiento de los datos

Sistema de archivos de una base de datos SQL Server

Todas las bases de datos tienen un archivo de datos principal (*.mdf) y un archivo de registro de transacciones (*.ldf). A su vez, tambien pueden existir una serie de archivos secundarios (*.ndf).

La ubicación predeterminada de estos archivos es:

C:\Program Files\Microsoft SQL Server\MSSQL<version.NombreInstancia>\MSSQL\DATA

Al crear una base de datos nueva, SQL Server lo que hace es una copia de la base de datos Model, que utiliza como plantilla, donde se incluyen las tablas del sistema, y el resto de la base de datos se rellena con páginas vacías.
Los datos se almacenan es bloques de 8KB de espacio contiguo en memoria, lo que se conoce como páginas (128 páginas/MB).

Las tablas e índices se almacenan en extensiones. Una extensión es una colección de 8 páginas físicamente contiguas (64 KB, son 16 Extensiones/MB).

Los archivos de registro de transacciones, contienen la información necesaria para la recuperación de la base de datos en caso de un error del sistema, pero no contienen las páginas de datos.

Funcionamiento del registro de transacciones

Una transacción es un conjunto de una o más instrucciones Transact-SQL tratadas como una única unidad de trabajo y recuperación.
Dentro de una transacción, las instrucciones Transact-SQL se deben realizar por completo o no realizarse. SQL Server realiza transacciones implícitas y explícitas.
SQL Server guarda todas las transacciones en un registro de transacciones para mantener la coherencia con la base de datos y contribuir en la recuperación.
Se trata de una área de almacenamiento que realiza automáticamente un seguimiento de los cambios efectuados en una base de datos. Se registran las modificaciones a medida que se van ejecutando y antes que se escriban en la base de datos.

Funcionamiento del registro de transacciones

Consideraciones para la ubicación de archivos

Al crear una base de datos, SQL Server crea un único archivo de datos principal (*.mdf) y un registro de transacciones (*.ldf) como mínimo, permitiendo definir la ubicación de los mismos. También pueden existir archivos secundarios (*.ndf) de manera opcional.

El archivo principal contiene la información de inicio de la BBDD y apunta al resto de ficheros. Los datos de usuario y los objetos se pueden almacenar en este archivo o en archivos secundarios.

La ubicación de los ficheros de datos y registros de transacciones conviene gestionarlo de manera adecuada para optimizar el rendimiento de la base de datos. Por ejemplo, es posible ubicar ficheros secundarios de grandes bases de datos en diferentes discos.

En el contexto de administración del almacenamiento en disco para SQL Server, es necesario tener presente las siguientes definiciones:

El rendimiento hace referencia en parte a la velocidad en las operaciones de lectura y escritura. Para bases de datos grandes, se debe distribuir los archivos de datos por las unidades de disco físicas individuales, de manera que se pueda acceder a los datos en paralelo mejorando el rendimiento. Además conviene agrupar los archivos de forma óptima.
La tolerancia a errores hace referencia a la capacidad del sistema para seguir funcionando sin perder datos cuando se produce un error en la parte del sistema.

Para optimizar el rendimiento, SQL Server es capaz de:

Acceder a los datos en paralelo, si el equipo tiene varios procesadores y varios discos. Para distribuir los datos de forma uniforme en todos los discos, se pueden usar las tecnologías de matriz redundante de discos independiente (RAID).
Acceder a los datos de una tabla en paralelo, si el grupo de archivos de una tabla contiene varios archivos. Si es necesario, se pueden utilizar grupos de archivos definidos por el usuario para distribuir los datos por varios conjuntos de secciones de hardware.

El registro de transacciones, conviene alojarlo en un disco independiente de donde se encuentran ubicados los datos, o usar RAID, dado que el archivo se escribe secuencialmente. De esta manera, los cabezales del disco se encuentran ubicados correctamente para continuar con la modificación del fichero. Usar RAID, proporciona tolerancia a errores.

La ubicación de la base de datos Tempdb, conviene colocarla en un subsistema de entrada salida rápido, independientemente de las bases de datos. Se puede utilizar también RAID para distribuir la base de datos Tempdb en varios discos, y así obtener un mayor rendimiento.

Los grupos de archivos

Son colecciones de archivos con nombre, y se utilizan para simplificar la ubicación de los datos.
Si la configuración de hardware incluye varios discos, podemos ubicar varios objetos y archivos específicos en discos individuales mediante la agrupación de los archivos de bbdd en uno o más grupos de archivos, mejorando el rendimiento al utilizar subprocesos paralelos para la ejecución de consultas.
Los grupos de archivos también pueden facilitar el mantenimiento de la base de datos.
SQL Server tiene un grupo de archivos principal y también puede tener un grupo de archivos definidos por el usuario.
El grupo de archivos principal contiene el archivo principal de la base de datos con las tablas del sistema, y contiene archivos secundarios definidos por el usuario que no estén asignados a otros grupos de archivos.
Un grupo de archivos definido por el usuario consta de archivos de datos, agrupados con fines de ubicación y administración. S
QL Server mantiene una asignación de archivos que asociada cada objeto de base de datos con su ubicación en el disco.
Los archivos del registro de transacciones no forma parte de ningún grupo de archivos. El espacio de registro de transacciones se administra por separado de espacio de datos.
Es en el grupo Primary, donde se ubica el archivo principal de datos (*.mdf).

Consideraciones a tener en cuenta por un administrador de bases de datos en relación a los grupos de archivos:

Determinar los grupos de archivos necesarios. Se deben tener en cuenta los requisitos de rendimiento y administración. El rendimiento se puede mejorar si se crean varios archivos en discos físicos diferentes de un único grupo de archivos, y se asignan tablas que tienen acceso con frecuencia a ese grupo de archivos. La estrategia de relleno proporcional que utiliza SQL Server para guardar los datos en los diferentes archivos del grupo, implica que los datos se distribuyen eficazmente por los archivos físicos, por lo que se mejora el rendimiento I/O. También se pueden agrupar los archivos, para separar las tablas de sus índices no agrupados, lo que en algunos casos puede optimizar el acceso a los datos. En cuanto a la utilización de grupos de archivos para favorecer la administración de los datos, se pueden separar datos que se actualizan con frecuencia, de aquellos datos que son relativamente estáticos o sólo lectura, e implementar diferentes estrategias de copias de seguridad según la volatibilidad de los datos. También se pueden separar los indices susceptibles a la fragmentación de aquellos que se modifican con menor frecuencia para optimizar las tareas de fragmentación y minimizar su impacto en la base de datos.
Especificar el grupo de archivos predeterminado. SQL Server asigna un grupo de archivos predeterminado, estableciéndose en el grupo de archivos principal de forma predeterminada en el momento de creación de la base de datos. Este grupo contiene todas las páginas de tablas e índices cuando no tienen un grupo específico cuando se crean. Si el grupo de archivos principal sigue siendo el grupo de archivos predeterminado, es necesario establecer el tamaño apropiado para este archivo, estableciendo un crecimiento automático y evitando que se quede sin espacio. El grupo de archivos principal, debe ser lo suficientemente grande para contener todas las tablas del sistema, y cualquier tabla o índice que no se haya asignado a un grupo de archivos definido por el usuario. Si el grupo de archivos principal se queda sin espacio, no será posible trabajar con la base de datos añadiendo más información. Sin embargo, si es un grupo de archivos de usuario el que se queda sin espacio, solo se verán afectados los archivos de este grupo, pero se podrá seguir utilizando la base de datos.
Utilización de grupos de archivos de sólo lectura. Cualquier grupo de archivos, a excepción del grupo de archivos principal, se puede configurar como sólo lectura, con lo que no se podrán modificar de ningún modo. Para evitar la modificación accidental de un conjunto de datos, es posible colocar las tablas pertinentes en un grupo de archivos, y marcarlo como sólo lectura.

Bases de datos del sistema

Hay 5 bases de datos del sistema existentes en todos servidores de SQL Server, y se crean en el momento de la instalación. Contienen metadatos del sistema y no es posible eliminarlas. Estas son:

MASTER: Contiene información de todo el sistema (opciones de configuración del servidor). Cualquier elemento que se define a nivel de servidor se almacena aquí (p.e. Inicios de sesión, referencias a las bases de datos nuevas, mensajes de error definidos por el usuario..). Si esta base de datos está dañada, SQL Server no arranca, con lo que es conveniente realizar copias de seguridad de la misma.
MSDB: Contiene información relacionada con el agente SQL Server. Aquí se almacenan los trabajos, operadores, alertas.. todo relacionado con el agente. Conviene hacer copias de seguridad de forma periódica para asegurar que los trabajos, programaciones, historiales de copias de seguridad, restauraciones, planes de mantenimiento.. no se pierdan.
MODEL: Se utiliza como plantilla para todas las bases de datos creadas en la instancia de SQL
Server. Las modificaciones hechas a la base de datos Model (tamaño de la bbdd, la intercalación, el modelo de recuperación..), se aplicarán a las bases de datos que se creen con posterioridad. SQL Server no se iniciará si esta base de datos no está o está dañada.
TEMPDB: Área de trabajo que contiene objetos temporales o conjuntos de resultados intermedios. Esta base de datos se crea cada vez que se inicia SQL Server, con lo que no es necesario realizar copias de seguridad.
RESOURCE: Es una base de datos de sólo lectura que permanece oculta. Contiene todos los objetos del sistema que se asignan al esquema Sys en cada una de las bases de datos. Por tanto, estos objetos se ubican físicamente en la base de datos Resource, pero se muestran de manera lógica, en el esquema Sys en cada una de las bases de datos. Contiene además, todos los procedimientos almacenados del sistema, vistas, funciones del sistema..

Base de datos Tempdb

Es una base de datos temporal fundamental para el rendimiento general del sistema. Es un recurso global para todos los usuarios conectados a la instancia de SQL Server. Se utiliza para incluir los siguientes elementos:

Objetos temporales de usuarios creados explícitamente como pueden ser tablas temporales locales o globales, procedimientos almacenados temporales, variables de tabla o cursores.
También se almacenan objetos internos creados por el motor de base de datos de SQL Server, como tablas de trabajo para almacenar resultados intermedios en algunas operaciones de agrupación y ordenación.
Se almacenan las versiones de fila generada por las transacciones de modificación de datos en una base de datos que utiliza transacciones de lectura confirmada, que usa transacciones de aislamiento de versiones de fila o aislamiento de instantáneas.

La base de datos tempdb, se crea cada vez que se inicia SQL Server.

El rendimiento de la base de datos tempdb, es fundamental para el rendimiento general de la mayoría de las instalaciones de SQL Server.

El tamaño de la base de datos puede afectar al rendimiento del sistema, si éste, tiene que encargarse del crecimiento automático de los archivos de Tempdb. Para evitar esto, conviene aumentar el tamaño de Tempdb.

Para optimizar el rendimiento de Tempdb se debe considerar:

Establecer el modelo de recuperación de Tempdb en SIMPLE. Este modelo recupera espacio del registro de transacciones para mantener bajos los requisitos de espacio.
Permitir que los archivos de Tempdb crezcan automáticamente cuando sea necesario.
Establecer el incremento de crecimiento de archivos en un tamaño razonable, para evitar que los archivos de la base de datos Tempdb crezcan en un porcentaje demasiado pequeño. Si este crecimiento de los archivos es muy pequeño, comparado con la cantidad de datos que se escriben en Tempdb, es posible que sea necesario expandir Tempdb constantemente. Esto afectará mucho al rendimiento. Se recomienda seguir unas directrices al establecer el valor del incremento de Tempdb. Si hay archivos de 0-100MB conviene un incremento de 10MB. Si hay archivos de 100-200MB conviene un crecimiento de 20MB, y para archivos mayores de 200MB, conviene un incremento del 10%.
Asignar espacio previamente para todos los archivos de Tempdb, estableciendo el tamaño de archivo en un valor lo suficientemente alto para acomodar la carga de trabajo habitual del entorno. Con ello se evita, que Tempdb se expanda con demasiada frecuencia, afectando al rendimiento.
Crear tantos archivos como sea necesario para maximizar el ancho de banda del disco. El uso de varios archivos reduce la contención de almacenamiento de Tempdb y produce una escalabilidad superior. No obstante no conviene crear demasiados archivos, ya que afectaría negativamente al rendimiento. Como regla general, se creará un archivo de datos por CPU del servidor.
Asignar a cada archivo de datos el mismo tamaño, para obtener un rendimiento óptimo de relleno proporcional.
Colocar la base de datos Tempdb en un subsistema de E/S rápido, creando bandas en disco si hay muchos discos conectados recientemente.
Colocar la base de datos Tempdb en discos diferentes de los que utilizan las bases de datos de usuario.

Para modificar los parámetros de tamaño y de crecimiento de los archivos de la base de datos Tempdb, es posible hacerlo desde la herramienta SQL Server Management Studio.

Creación de bases de datos de usuario

Cada vez que se crea una base de datos de usuario, se realiza una copia de la base de datos Model (plantilla). Para realizar este proceso, es posible llevarlo a cabo mediante SQL Server Management Studio, o mediante instrucciones Transact-SQL.
Al crear la base de datos, deberemos especificar como mínimo las características del archivo principal de la base de datos y del registro de transacciones. En caso de ser necesarios archivos secundarios también será necesario especificar sus características.

Sobre cada uno de los archivos deberemos especificar las siguientes opciones:

Nombre: Es el nombre lógico que se utilizará cada vez que se haga referencia a los archivos de la base de datos.
Tamaño: Puede especificar el tamaño de los archivos de datos y de registro de transacciones. El tamaño inicial es igual al valor usado en la base de datos Model.
Crecimiento de archivos: Puede especificar si un archivo crecerá en tamaño en caso de ser necesario (ya ha ocupado el espacio inicialmente asignado). Esta opción es denominada crecimiento automático.
Tamaño máximo: Puede especificar el tamaño máximo de un archivo en megabytes o en un porcentaje. Si no hay indicación de tamaño máximo, el límite será el del disco.
Ubicación física: Dónde se almacenarán los archivos físicamente.
Intercalación: Una intercalación es un criterio de ordenación para datos que determina el orden en que se muestran los valores cuando los datos se ordenan secuencialmente. Las distintas intercalaciones ordenan los datos de diferente manera, en función si la intercalación distingue entre mayúsculas y minúsculas, de las reglas de ordenación para las letras acentuadas, los caracteres especiales etc.. De forma predeterminada, una base de datos hereda la intercalación especificada a nivel de servidor, aunque en el momento de la creación de la bbdd se puede especificar una opción diferente.

Opciones de configuración de una base de datos

Para cada base de datos, es posible configurar una serie de opciones que van a determinar sus características. Estas opciones son únicas para cada base de datos y no afectan al resto.
Cuando se crea una base de datos, si no se indica nada, estas opciones permanecen en unos valores predeterminados. Estos valores pueden ser modificados mediante el uso de la cláusula SET de la instrucción ALTER DATABASE o mediante SQL Server Management Studio.

Existen más de 20 opciones configurables, que se agrupan en diferentes categorías:

Automático. Controla los comportamientos automáticos como las estadísticas, el cierre de la base de datos y la reducción.
Cursor. Controla el comportamiento y ámbito del cursor.
Estado. Controla el estado de la base de datos.
Modelo de recuperación. Controla el modelo de recuperación de la base de datos.

Opciones de la categoría Automático:

Cerrar automáticamente. Especifica si la bbdd se cierra sin problemas y libera los recursos cuando sale el último usuario (True/False).
Crear estadísticas automáticamente. Especifica si la bbdd crea automáticamente estadísticas de optimización que faltan (True/False).
Reducir automáticamente. Especifica si los archivos de bbdd están disponibles para reducirse de forma periódica.
Actualizar estadísticas automáticamente. Especifica si la bbdd actualiza automáticamente las estadísticas de optimización no actualizadas (True/False).
Actualizar estadísticas automática y asíncronamente. Las consultas que inician una actualización automática de estadísticas obsoletas, no esperan a que las estadísticas se actualicen antes de la compilación. Las consultas posteriores utilizan las estadísticas actualizadas si están disponibles.

Opciones de la categoría Cursor:

Cierre de cursor al confirmar habilitado. Especifica si los cursores se cierran tras confirmar la transacción que abre el cursor (True/False). Con True, se cierran los cursores que están abiertos cuando se confirma o se revierte una transacción. Con False, esos cursores se mantienen abiertos cuando se confirma una transacción. Con False, si se revierte una transacción, se cierran todos los cursores excepto los definidos como INSENSITIVE o STATIC.
Cursor predeterminado. Especifica el comportamiento predeterminado del cursor. Con True, el valor predeterminado de las declaraciones de cursor es LOCAL. Con False, el valor predeterminado de los cursores de Transact-SQL es GLOBAL.

Opciones de la categoría Estado:

Base de datos de solo lectura. Especifica si la base de datos es de solo lectura (True/False). Con True, los usuarios solo pueden leer los datos de la bbdd.
Estado de base de datos. Muestra el estado actual de la base de datos. No se puede editar.
Restringir acceso. Especifica los usuarios que pueden tener acceso a la bbdd. Los valores posibles son: Multiple (Estado de una bbdd en producción, permite que varios usuarios tengan acceso al mismo tiempo), Single (Se utiliza en acciones de mantenimiento, sólo un usuario puede tener acceso a la base de datos), Restricted (Solo los miembros de los roles "db_owner", "db_creator" y "sysadmin" pueden utilizar la bbdd).
Cifrado habilitado. Se permite cifrar la bbdd mediante el uso de una clave de cifrado.

Opciones de la categoría Modelo de Recuperación:

Completo. Proporciona la posibilidad de recuperación completa de errores de los medios y es el valor predeterminado.
Registro masivo. Usa menos espacio de registro porque el registro es mínimo, pero tiene un riesgo mayor de exposición.
Simple. Sólo recupera la bbdd hasta la última copia de seguridad completa o hasta la última copia de seguridad diferencial de la base de datos.

Reducción de archivos y bases de datos

Inicialmente, al configurar los ficheros de la base de datos creada, y esperando que recojan una gran cantidad de información, se han podido crear con un tamaño importante que ahora interesa reducir.

En otras ocasiones, cuando se realiza el truncamiento o eliminación de tablas, se generan espacios inutilizados que conviene eliminar.

Es posible la reducción de la base de datos, o la reducción de cada uno de los archivos que componen la base de datos.

La reducción de los archivos de datos, permite recuperar espacio moviendo páginas de datos del final del archivo a espacio desocupado próximo al principio del archivo.

Cuando se crea suficiente espacio disponible al final del archivo, las páginas de datos situadas al final del mismo se pueden desasignar y devolver al sistema de archivos.

Mediante las siguientes instrucciones es posible realizar las operaciones anteriores:

DBCC SHRINKDATABASE. Para reducir el tamaño de una base de datos. El tamaño máximo que se conseguirá no podrá ser menor del tamaño mínimo de la bbdd (especificado en el momento de creación de la bbd). Las bbdd que se comprimen no tienen que estar en modo de usuario único, otros usuarios pueden estar trabajando cuando ésta se encuentra en proceso de reducción (incluyendo las bases de datos del sistema). No es posible reducir una base de datos mientras se está realizando una copia de seguridad de la misma y viceversa.
DBCC SHRINKFILE. Para reducir el tamaño de un fichero concreto.

Metadatos de las bases de datos

Para visualizar los metadatos contenidos en una bbdd, es posible utilizar la herramienta SQL Server Management Studio. Cuando es una aplicación la que necesita obtener estos metadatos, es necesario utilizar Transact-SQL, de manera que sea posible consultar las vistas de catálogo, las funciones de metadatos y los procedimientos almacenados del sistema:

Vistas de catálogo: Proporcionan metadatos acerca de objetos de base de datos que devuelen filas de información.
Funciones de metadatos. Devuelven un valor único de información de metadatos por función.
Procedimientos almacenados del sistema. Recuperan metadatos usando procedimientos almacenados.

Mover bases de datos

En ocasiones, necesitamos mover una base de datos de un servidor a otro. O también se puede dar el caso que haya que cambiar la ubicación física de alguno de los archivos de la base de datos.

Separar y adjuntar la base de datos. Cuando se separa una base de datos, no se eliminan los archivos, lo que se eliminan son las entradas de metadatos, es decir, el servidor pierde toda referencia a la base de datos que había hasta ese momento, pero los archivos físicos siguen estando en el mismo lugar. Una vez separada la bbdd, se pueden mover o copiar esos archivos, colocarlos en la nueva ubicación. Una vez hecho el cambio de ubicación, es necesario adjuntar de nuevo la bbdd para que el servidor vuelva a tener referencia sobre esta bbdd y poder trabajar con ella. Este proceso es muy útil para mover bbdd entre distintos servidores.
Mediante ALTER DATABASE con la bbdd offline. Para poder cambiar la ubicación de un archivo de una bbdd dentro del mismo servidor.

Los esquemas

Los objetos de una base de datos se crean dentro de un esquema. Un esquema es un espacio de nombres para objetos de la base de datos. El nombre completo de un objeto en una base de datos tiene la forma de servidor.basededatos.esquema.objeto.
Si se utilizan nombres parciales (no indicamos ni servidor ni base de datos), se utiliza el servidor al que se encuentra conectado y la base de datos en la que se está posicionado. En el caso de omitir el esquema, el modo en el que SQL Server resuelve el direccionamiento del objeto es:

Si el usuario tiene un esquema predeterminado, SQL Server intenta encontrar el objeto en ese esquema predeterminado.
Si el objeto no se encuentra en el esquema predeterminado del usuario, o si el usuario no tiene ningún esquema predeterminado, SQL Server intenta encontrar el objeto en el esquema "dbo".

Todas las bases de datos tienen un esquema denominado "dbo". El esquema dbo es el esquema predeterminado para todos los usuarios que no tienen un esquema predeterminado definido explícitamente.

Planificación de estrategias de copias de seguridad

Es importante tener un buen plan de copias de seguridad para hacer frente a los diferentes escenarios a los que puede enfrentarse la base de datos. Es necesario garantizar que todos los sistemas y datos, puedan recuperar rápidamente su funcionamiento. Para elaborar este plan, es necesario tener en cuenta los distintos desastres que pudieran ocurrir, incluidos los desastres naturales (p.e. incendios) o desastres técnicos (p.e. errores en disco).

Operadores de copia de seguridad

Los operadores de copia de seguridad son las personas encargadas de realizar las copias de seguridad. Para realizar estas copias de seguridad, es necesario tener permiso para ello (si el usuario no es administrador del servidor o de la base de datos).

Las personas que puedan realizar copias de seguridad van a ser aquellas cuyos inicios de sesión pertenezcan a la funcion fija de servidor "sysadmin", o bien pertenezcan a las funciones fijas de base de datos "db_owner" o "db_backupoperator". Tambien pueden crearse funciones adicionales y conceder permisos para la realización de las copias de seguridad.

Medios de copia de seguridad

Para realizar un copia de seguridad, debe de considerarse que tipo de medios van a utilizarse para almacenar dichas copias.
SQL Server puede realizar copias de seguridad en un archivo de disco o en una unidad de cinta (esto último en desuso). Los archivos de disco pueden ser unidades locales o unidades de red.
El primer paso para realizar una copia de seguridad es crear los archivos donde se guardarán las copias. Un archivo de copia de seguridad que se crea antes de utilizarse en una operación de copia de seguridad es lo que se conoce como dispositivo de copia de seguridad. Estos dispositivos de copia de seguridad pueden crearse mediante SQL Server Management Studio o mediante un procedimiento almacenado.

Las copias de seguridad se pueden almacenar en varios archivos de copia de seguridad. SQL Server tiene la capacidad de escribir en paralelo en diferentes archivos de copia de seguridad.
Cuando se dispone de varios archivos de copia de seguridad, los datos se distribuyen por todos los archivos disponibles. Estos archivos forman un conjunto de copia de seguridad distribuida.
El conjunto de copia de seguridad, es el resultado de una sola operación de copia de seguridad en un único archivo o en varios.
Realizar una copia de seguridad en varios archivos ubicados en diferentes discos, contribuye a reducir el tiempo de finalización de la operación.
Un conjunto de medios es una recopilación de archivos que se usan para contener uno o más conjuntos de copias de seguridad.

Estructura del registro de transacciones

Arquitectura lógica del registro de transacciones:

El registro de transacciones de SQL Server funciona desde el punto de vista lógico como si fuese una cadena de entradas de registro. Cada entrada del registro está identificada por un número de secuencia de registro(LSN, Log Sequence Number). Las nuevas entradas del registro se escriben al final lógico del registro con un LSN mayor que el de las entradas anteriores.
Las entradas del registro se almacenan en la secuencia en la que se crean.
Cada entrada del registro contiene el ID de la transacción a la que pertenece.
Los registros de modificaciones de datos registran la operación lógica llevada a cabo o las imágenes anterior y posterior de los datos modificados. La imagen anterior es una copia de los datos antes de llevar a cabo la operación; la imagen posterior es una copia de los datos después de haber realizado la operación.

Los pasos para recuperar una operación dependen del tipo de registro:

Registro de la operación lógica. Para poner al día la operación lógica, se vuelve a ejecutar la operación. Para revertir la operación lógica, se ejecuta la operación lógica inversa.
Registro de las imágenes anterior y posterior. Para poner al día la operación lógica, se aplica la imagen posterior. Para revertir la operación se aplica la imagen anterior.
En el registro de transacciones se registran diferentes tipos de operaciones, como inicio y final de transacción, todas las modificaciones de los datos (inserción, actualización y eliminación, incluyendo las modificaciones de las tablas, tablas de sistema, procedimientos almacenados del sistema o instrucciones del lenguaje de definición de datos DDL), las asignaciones o cancelaciones de asignación de páginas y extensiones o la creación y eliminación de una tabla o un índice. También se registran las operaciones de reversión. Cada transacción reserva espacio en el registro de transacciones para asegurarse de que existe suficiente espacio de registro para admitir una reversión provocada por una instrucción de reversión o un error. Este espacio se libera cuando se completa la transacción. La sección del archivo de registro para una reversión completa de la base de datos se denomina parte activa del registro o registro activo. No es posible truncar ninguna parte del registro activo.

Arquitectura física del registro de transacciones:

El registro de transacciones se utiliza para garantizar la integridad de los datos de la base de datos y para la recuperación de datos.
El registro de transacciones de una base de datos está asignado a uno o varios archivos físicos.
Conceptualmente, el archivo de registro es una cadena de entradas de registro.
Físicamente, la secuencia de entradas del registro se almacena de forma eficaz en el conjunto de archivos físicos que implementa el registro de transacciones.
SQL Server Database Engine, segmenta cada archivo de registro físico internamente en una serie de archivos de registro virtuales. Los archivos de registro virtuales no tienen un tamaño fijo y no hay un número fijo de archivos de registro virtuales para un archivo de registro físico. El motor de la base de datos elige dinámicamente el tamaño de los archivos de registro virtuales al crear o ampliar los archivos de registro.
Los archivos de registro virtuales sólo afectan al rendimiento del sistema cuando se definen con valores pequeños de tamaño y crecimiento.
Se recomienda que los archivos de registro se definan con un valor de tamaño cercano al tamaño final necesario y con un valor de crecimiento relativamente alto.
El registro de transacciones es un archivo de registro virtual.
Si las entradas antiguas se truncan con la frecuencia suficiente para disponer siempre de espacio para las nuevas entradas de registro que se van a crear hasta el próximo punto de comprobación, el registro no se llena nunca.
Si el registro lógico llega la principio del registro lógico, o bien se produce un incremento automático del archivo, o bien, si no se dispone de la opción de incremento automático, se genera el error 9002.

Modelos de recuperación

Las operaciones de copias de seguridad y restauración de SQL Server, se producen dentro del contexto del modelo de recuperación de la base de datos.
Los modelos de recuperación se han diseñado para controlar el mantenimiento del registro de transacciones.
Un modelo de recuperación es una propiedad de la base de datos que controla la forma en que se registran las transacciones, si se requiere que se realice la copia de seguridad y los tipos de operaciones de restauración disponibles.

Los modelos de recuperación conservan los datos en caso de un error del servidor, pero presentan diferencias notables en el modo de recuperación de la información. Normalmente se usa el modelo completo o simple, pero es posible cambiar de un modelo a otro cuando sea necesario.

Tipos de modelos de recuperación:

Simple: Se utiliza este modelo para bbdd pequeñas o con modificaciones poco frecuentes. Este modelo usa copias completas o diferenciales de la bbdd y la recuperación se limita a la restauración de la información hasta el momento de la última copia de seguridad. Los cambios realizados después de la copia de seguridad se perderán. El principal punto fuerte de este modelo es que ocupa poco espacio para los archivos de registro, siendo el modelo más sencillo de implementar.
Completo (Full): Se utiliza cuando la recuperación de los medios dañados es la prioridad máxima. Este modelo usa copias de la bbdd y toda la información del registro. Dado que se registran todas las transacciones, la recuperación puede realizarse en cualquier momento. SQL Server admite la inserción de marcas con nombre en el registro de transacciones, para poder efectuar recuperaciones hasta una marca concreta. Dada que esto ocupa espacio, solo deben utilizarse para transacciones que tengan una función importante en la estrategia de recuperación de la bbdd. El inconveniente de este modelo es el gran tamaño que pueden llegar a tener los archivos de registro de transacciones y los costos de almacenamiento y rendimiento que conlleva.
Registro masivo (Bulk Logged): Similar al anterior, la diferencia radica en que se usan tanto las copias de seguridad de la bbdd, como las copias de seguridad del registro para volver a crear una bbdd. Sin embargo, este modelo de recuperación utiliza menos espacio del registro para ciertas operaciones como son las operaciones de carga masiva, las operaciones de creación de índices etc.. y las operaciones masivas suelen ser más rápidas. Mediante el uso de este modelo pueden restaurarse todos los datos, pero una tiene una desventaja, y es que no es posible la restauración de sólo una parte de una copia de seguridad (como la restauración hasta una marca con nombre). Este es la diferencia principal con respecto al modelo completo.

Tipos de copia de seguridad de SQL Server

Completa: Es la primera copia que debe realizarse de cualquier bbdd. Contiene todos los archivos y parte del registro de transacciones. Representa la bbdd en el momento de realizarse la copia de seguridad, y se usa como base en caso de producirse un error del sistema.
Diferencial: Se realizan para minimizar el tiempo necesario para restaurar una bbdd modificada con frecuencia. Solo pueden utilizarse si previamente se ha realizado una copia completa de la misma. SQL Server realiza una copia de las partes de la bbdd que han cambiado desde que se realizó la copia de seguridad completa. (BACKUP DATABASE nombre TO dispositivo WITH DIFFERENTIAL)
Registro de transacciones: Se registra cualquier cambio de la bbdd y se realizan normalmente junto con las copias de seguridad completa. No debe de realizarse una copia de seguridad del registro de transacciones a menos que no se haya realizado previamente una copia de seguridad completa. Tampoco es posible realizar copias de seguridad del registro de transacciones cuando está configurado el modelo de recuperación simple en la bbdd.
Copia del final del registro: Es una copia de seguridad que incluye la parte del registro del que previamente no se ha efectuado ninguna copia de seguridad (parte activa del registro). Este proceso se suele utilizar cuando se intenta realizar un proceso de recuperación de bbdd para intentar evitar la pérdida de información. Se trata de intentar guardar aquella parte del registro de la que no hay una copia de seguridad. Se considera como un primer paso del proceso de restauración. Este tipo de copia se puede utilizar por ejemplo, cuando los ficheros de datos son inaccesibles, pero el fichero de registro no está dañado.
Copia de seguridad de archivos o grupos de archivos: Si el hacer una copia de seguridad completa de una bbdd muy grande, en ocasiones no resulta muy práctico, es posible realizar copias de seguridad de archivos o grupos de archivos de la bbdd.
Copia de seguridad parcial: Son similares a una copia de seguridad completa pero no contiene todos los grupos de archivos. Este tipo de copias incluyen todos los archivos del grupo primario, todos los grupos de archivos de lectura/escritura y cualquier archivo de sólo lectura que se haya especificado. Una copia de seguridad parcial de una bbdd de sólo lectura, contiene sólo el grupo de archivos principal o primario. Es posible tambien realizar copias de seguridad diferenciales-parciales. Estas copias registran solamente los datos que han cambiado en los grupos de archivos desde la copia de seguridad parcial anterior y se denominan la base para la diferencial.
Sólo de copia: Este tipo de copia no tiene efectos sobre los procedimientos de copia de seguridad y de restauración generales para la bbdd. No interfieren en el historial de copias de seguridad de la bbdd. Se suelen utilizar normalmente cuando es necesario hacer una copia de seguridad en un momento puntual. Todos los modelos de recuperación son compatibles con la copia de seguridad de sólo copia. Este tipo de copia no puede utilizarse como copia se seguridad de base, y no afecta a las copias de seguridad diferenciales existentes. Las copias de seguridad diferenciales de solo copia, son idénticas a las copias de seguridad diferenciales normales. (BACKUP DATABASE nombre TO dispositivo WITH COPY_ONLY)

Estrategia de copia de seguridad completa de una base de datos

Se trata de un método de recuperación que implica la realización, periódicamente, de copias de seguridad completas de la base de datos.
Si se produce un error en la bbdd, se puede realizar una restauración completa de la misma con la copia completa más reciente.

Se debe implementar un estrategia de copia de seguridad completa si:

La base de datos es pequeña (el tiempo necesario para realizar una copia completa es razonable).
La base de datos sufre pocos cambios o es de sólo lectura.

Cuando se implementa esta estrategia de copia de seguridad y la base de datos implementa el modelo de recuperación completo, es probable que el registro de transacciones se llene, momento en el que SQL Server impide la actividad en la base de datos.
Para evitar este problema, se puede configurar el modelo de recuperación simple, o también es posible borrar periódicamente el registro de transacciones.

Estrategia de copia de seguridad completa de base de datos y registro de transacciones

Con la realización de copias del registro de transacciones y copias seguridad completas lo que se pretende es guardar toda la actividad ocurrida en la base de datos entre dos copias de seguridad completas. Cuando se aplica esta estrategia, la restauración de la bbdd se realiza con la última copia de seguridad completa, para luego aplicar todas las copias del registro de transacciones creadas desde la última.
Esta estrategia es recomendable para las bases de datos modificadas con frecuencia y en las que la realización de las copias de seguridad tanto de la base de datos como del registro de transacciones se lleven a cabo en un periodo de tiempo razonable.

Estrategia de copia de seguridad diferencial

Esta estrategia implica la realización de copias de seguridad completas de la bbdd con la intervención de copias de seguridad diferenciales.
Opcionalmente es posible realizar copias de seguridad del registro de transacciones entre la realización de las copias de seguridad completas y diferenciales.
Esta estrategia es aconsejable cuando la base de datos se modifica a menudo y se desea minimizar el tiempo empleado en la realización de la copia de seguridad.
Debe de utilizarse esta estrategia para reducir el tiempo de recuperación si se daña la base de datos.
Ante una situación problemática, se restauraría la última copia completa, para luego aplicar los cambios de la última copia diferencial realizada. Si a partir de aquí hubiese copias de registro de transacciones se aplicarían nuevamente.

Estrategia de copia de seguridad de archivos o grupos de archivos

La estrategia consiste en la realización de una copia de seguridad periódica de archivos o grupos de archivos. Es habitual que se realice también la copia de seguridad del registro de transacciones entre las copias de archivos o grupos de archivos.
Esta estrategia es compleja y no mantiene automáticamente la integridad referencial, sin embargo, requiere de menos espacio en disco, ya que solamente se realiza la copia de seguridad sobre algunos archivos o grupos de archivos (no es una copia de toda la bbdd).
Este tipo de estrategia se implementa en grandes bases de datos que contienen información que tienen diferentes características de actualización.
La principal desventaja frente a la copia completa es la complejidad administrativa adicional en el momento de la restauración.

Cuando se combinan esta estrategia con copias de seguridad habituales del registro de transacciones, constituye una alternativa cuando se tiene limitación temporal para la realización de la operación de restauración frente a las copias de seguridad completas. Por ejemplo, si solamente se dispone de una hora para la realización de la restauración, cuando la restauración completa tarda cuatro horas, lo que podría realizarse en su lugar, serían copias de seguridad de archivos concretos cada noche y seguir garantizando la coherencia de los datos.

Copias de seguridad comprimidas

La compresión de una copia de seguridad permite aumentar la velocidad de creación de la misma de forma significativa, reduciendo al mismo tiempo el tamaño necesario.
Una copia de seguridad comprimida y sin comprimir no pueden coexistir al mismo tiempo en un mismo conjunto de medios. Por otro lado, la creación de una copia comprimida aumenta significativamente el uso de la CPU del servidor, afectando a las operaciones que puedan realizarse de forma simultánea. Se aconseja por tanto realizar copias de seguridad comprimidas de prioridad baja, en una sesión en la que el uso de la CPU esté regulado por el administrador de recursos.

Información sobre las copias de seguridad realizadas

SQL Server almacena en la base de datos "msdb", un historial completo de todas las operaciones de copia de seguridad y restauración de una instancia de servidor.

Las tablas de la base de datos "msdb" que almacenan esta información de historial son:

backupfile: Contiene una fila por cada archivo de datos o registro del que se hace una copia de seguridad.
backupfilegroup: Contiene una fila por cada grupo de archivos de conjunto de copia de seguridad.
backupmediafamily: Contiene una fila por cada familia de medios. Si una familia de medios reside en un conjunto de medios reflejado, la familia tiene una fila independiente por cada reflejo del conjunto de medios.
backupmediaset: Contiene una fila por cada conjunto de medios de una copia de seguridad.
backupset: Contiene una fila por cada conjunto de copia de seguridad.
restorefile: Contiene una fila por cada archivo restaurado. Se incluyen los archivos restaurados indirectamente por nombre de grupo de archivos.
restorefilegroup: Contiene una fila por cada grupo de archivos restaurado.
restorehistory: Contiene una fila por cada operación de restauración.

Las instrucciones Transact-SQL para tener acceso al historial de copias de seguridad son:

RESTORE FILELISTONLY (Tabla backupfile): Devuelve un conjunto de resultados que contiene la lista de archivos de base de datos y de registro del conjunto de copia de seguridad especificado.
RESTORE HEADERONLY (Tabla backupset): Obtiene la información de encabezado sobre todos los conjuntos de copias de seguridad de un dispositivo determinado. El resultado de la ejecución es un conjunto de resultados.
RESTORE LABELONLY (Tabla backupmediaset): Devuelve un conjunto de resultados que contiene información acerca del medio de copia de seguridad de un dispositivo de copia de seguridad especificado.

Procedimientos almacenados para la realización de limpieza y eliminaciones del historial de copias:

sp_delete_backuphistory [@oldest_date =] 'oldest_date': Elimina filas antiguas de las tablas del historial de las copias de seguridad y restauración. Después de realizar cada operación de copia de seguridad o restauración, se agregan filas adicionales a las tablas del historial de copias de seguridad, por lo que es recomendable ejecutar este procedimiento periódicamente.
sp_delete_database_backuphistory [@databese_name =] 'database_name': Elimina todas las filas de la tabla del historial de copias de seguridad y restauración de una base de datos determinada.

Restauraciones de bases de datos

A continuación se va a comentar como restaurar bases de datos, registros de transacciones y archivos o grupos de archivos.

Funcionamiento del proceso de restauración

Un proceso de restauración consiste en recuperar los datos guardados en una copia de seguridad, y aplicar una serie de transacciones registradas a los datos para poner la información al día hasta el punto de recuperación objetivo.
La recuperación es el conjunto de operaciones que confieren coherencia a la base de datos y permiten que su estado sea utilizable.
Una copia de datos o diferencial, contiene suficientes registros de transacciones como para permitir poner al día las transacciones activas como parte de la restauración de cada copia de seguridad. Posteriormente, para poner la base de datos utilizable, tambien es necesario deshacer las transacciones no confirmadas.

El proceso de aplicar los cambios registrados a los datos de una bbdd se conoce como puesta al día. El conjunto de todos los datos restaurados se conoce como el conjunto de puestas al día. Un conjunto de puestas al día se define con la restauración de una o más copias de seguridad de datos (completa, parcial o conjunto de copia de seguridad de archivos).
Si la copia de seguridad completa contiene entradas de registro, los datos guardados se pondrán al día mediante el uso de este registro.
Una secuencia de restauración son todos y cada uno de los pasos que es necesario aplicar ante un determinado escenario de restauración. Cada paso corresponde una instrucción RESTORE independiente.

Una restauración es un proceso que se realiza en varias fases:

Copia de datos. Implica copiar los datos, el registro y las páginas de índices, desde el medio de copia de seguridad hasta los ficheros de la base de datos. Este proceso de realiza utilizando una o varias copias de seguridad completas, y de forma opcional copias diferenciales.
Puesta al día (rehacer transacciones). Se aplican las transacciones registradas a los datos copiados desde la copia de seguridad para poner al día estos datos hasta el punto de recuperación. En este caso se procesan las copias de seguridad de registros conforme se restauran, comenzando por el registro contenido en las copias de seguridad completas. Normalmente en este punto, una bbdd tiene transacciones no confirmadas y se encuentra en un estado inutilizable, por lo tanto se requiere de una fase de deshacer como parte de la recuperación de la base de datos. Con el modelo de recuperación completa, se puede especificar el punto de recuperación como un momento determinado, una transacción marcada o un número de secuencia de registro. Con el modelo de recuperación optimizado para cargas masivas de registros, solamente es posible realizar la restauración a un momento dado si no se ha realizado ninguna operación masiva desde la copia de seguridad de registros anterior.
Fase de reversión (deshacer transacciones). Es la primera parte de la recuperación y revierte cualquier transacción no confirmada, y hace que la bbdd esté disponible para los usuarios. Es decir, después de que la recuperación es transaccionalmente coherente, la recuperación conecta la base de datos. Después de la fase de reversión no es posible restaurar las copias de seguridad subsiguientes, dándose por finalizado el proceso de restauración.

Un proceso de restauración RESTORE finaliza tras la puesta al día o continúa con la fase de deshacer, según se haya especificado el uso de las opciones NORECOVERY y RECOVERY.

WITH RECOVERY: Se recupera la base de datos e incluye las fases de rehacer y deshacer. No se podrán restaurar otras copias de seguridad. Se trata de la opción de RESTORE predeterminada. Si el conjunto de puestas al día no se ha puesto al día lo suficiente para ser coherente con la bbdd, la fase de deshacer no se podrá producir (error de SQL Server). Si por el contrario sí es coherente, se podrá finalizar el proceso de recuperación con conectar finalmente con la base de datos.
WITH NORECOVERY: Se omite la fase de deshacer para preservar las transacciones no confirmadas. Al omitir la fase de deshacer se pueden restaurar otras copias de seguridad para poner al día la bbdd a otro momento posterior. En ocasiones pone al día los datos hasta donde son coherentes con la bbdd. En estos casos, SQL Server emite un mensaje indicando que puede utilizarse la opción RECOVERY. La opción NORECOVERY se usa cuando deben utilizarse varias copias de seguridad.

Escenarios de restauración

Un escenario de restauración es el proceso de restaurar datos de una o más copias de seguridad, y a continuación recuperar la base de datos. Los escenarios admitidos dependen del modelo de recuperación de la base de datos, estos pueden ser:

Restauración completa: Puede implicar simplemente la restauración y recuperación de una copia de seguridad completa de base de datos. Por otro lado puede consistir en restaurar y recuperar un copia de seguridad completa, más una copia diferencial. En el caso de tener un modelo de recuperación simple en la base de datos, nos es posible tener copias del registro de transacciones. En el caso de tener un modelo de recuperación completo o de registro masivo, además de restaurar la copia completa y diferencial, se podrían restaurar todas copias de seguridad de registros de transacciones posteriores a la copia diferencial en orden secuencial.
Restauración de bases de datos del sistema: Requiere de una operativa especial para evitar que se produzcan mayores problemas. Por ejemplo, si la bbdd "master" se queda en un estado incoherente, SQL Server no arrancará hasta que se recupere dicha bbdd.
Restauración de archivos: Consiste en restaurar archivos dañados de sólo lectura sin llegar a restaurar la base de datos. Este tipo de restauración sólo es posible si la bbdd tiene como mínimo un grupo de archivos de sólo lectura.
Restauración de páginas: Se pueden restaurar una o varias páginas dañadas. Esta restauración puede realizarse mientras la bbdd está sin conexión. Las páginas que se están restaurando permanecen sin conexión. Es necesario que haya una cadena intacta de copias de seguridad de registros hasta el archivo de registro actual. Por ello, no se admite el caso de tener la bbdd con el modelo de recuperación simple.
Restauración por etapas: Esta restauración comienza por el grupo de archivos principal para pasar a todos los grupos de archivos secundarios de lectura/escritura.
Restauración en línea: Se trata de realizar la restauración de datos mientras la base de datos se encuentra en línea. Ésta es la opción por defecto para las restauraciones de archivos, páginas o etapas.

Como restaurar una base de datos

En el proceso de restauración de una base de datos desde una copia de seguridad, SQL Server recrea la bbdd y todos sus archivos asociados y a continuación los coloca en su ubicación original.

Un proceso de restauración puede realizarse cuando el disco físico está dañado, cuando la bbdd está dañada o cuando se precisa ubicar una base de datos en otra instancia de SQL Server.

En la restauración es posible utilizar algunas directrices como:

RECOVERY o NORECOVERY: para controlar el comportamiento de recuperación. Si solamente hay una copia de seguridad completa y no hay registro de transacciones o copia diferencial, se puede utilizar la opción RECOVERY y el proceso quedaría finalizado. Si en cambio, existe copia de seguridad completa y copia de transacciones o copia diferencial, se puede especificar la opción NORECOVERY para posponer el proceso de recuperación hasta que se restaura la última copia de seguridad.
MOVE TO: Para cambiar la ubicación de los archivos de la base de datos en caso de utilizar una diferente. Si no se utiliza esta cláusula, los archivos serán colocados en su ubicación original.
REPLACE: Para reemplazar una base de datos existente con datos de una copia de seguridad de otra base de datos. En este caso, SQL Server no realizará ninguna comprobación de seguridad, algo que hace por defecto para evitar que una bbdd sea restaurada con una copia de seguridad que no le corresponde.

Como restaurar el registro de transacciones

Tras la restauración de la última copia de seguridad completa y de la última copia de seguridad diferencial (en caso de haberla), es el momento de comenzar a utilizar las copias del registro de transacciones ocurridas desde la última copia completa (o diferencial si la hubiere).
Para ello, previamente, se habrá realizado una copia del final del registro de transacciones para cubrir la actividad ocurrida desde la última copia de registro de transacciones y el error producido.

Como ocurre en el caso de la restauración de varias copias de seguridad consecutivas, es necesario utilizar las sentencias NORECOVERY y RECOVERY para dejar la base de datos operativa tras la restauración de la última copia de seguridad. En el caso de la restauración de copias del registro de transacciones ocurre exactamente lo mismo.

La sentencia Transact-SQL a utilizar para la restauración de copias del registro intermedias es:

RESTORE LOG <nombre_base_datos>

FROM <dispositivo_backup>

WITH NORECOVERY

Del mismo modo, la sentencia Transact-SQL para la restauración de la última copia del registro y así dejar la base de datos operativa es:

RESTORE LOG <nombre_base_datos>

FROM <dispositivo_backup>

WITH RECOVERY

Si por error, esta última sentencia se ha ejecutado en modo NORECOVERY, dejando la base de datos no operativa, es posible utilizar la sentencia siguiente para finalizar el proceso de recuperación:

RESTORE DATABASE <nombre_base_datos> WITH RECOVERY

Restauración por etapas

La restauración por etapas implica una serie de secuencias de restauración, empezando por el grupo de archivos principal y en algunos casos, uno o varios archivos secundarios.
Una vez terminada la secuencia de restauración, los archivos recuperados pueden ponerse en línea directamente.
La restauración por etapas funciona con todos los modelos de recuperación, aunque su flexibilidad es mayor para los modelos de recuperación completa y registro masivo que para el modelo simple.
La restauración por etapas comienza con una secuencia de restauración inicial denominada secuencia de restauración parcial. Esta secuencia restaura y recupera el grupo de ficheros principal, y con el modelo de recuperación simple, todos los grupos de archivos de lectura-escritura.
Durante la restauración por etapas, toda la base de datos debe ponerse en modo "sin conexión". Después de este momento, la base de datos estará en línea y los grupos de archivos restaurados estarán disponibles. Sin embargo, los archivos que no se ha restaurado, permanecerán sin conexión.

La secuencia de restauración parcial, se inicia con una instrucción RESTORE DATABASE de una copia de seguridad completa de la base de datos especificando la opción PARTIAL. Esta opción solamente de especificarse en la primera parte del proceso de restauración.
Cuando el proceso finaliza y la base de datos está en línea, a los archivos restantes se les asigna el estado de "pendiente de recuperación", dado que su recuperación se ha pospuesto.
Posteriormente, una restauración por etapas incluye una o varias secuencias de restauración, que se denominan "secuencias de restauración de grupos de archivos".
Estas secuencias de restauración posteriores se pueden retrasar en el tiempo todo lo necesario. Estas secuencias restauran y recuperan uno o varios grupos de archivos sin conexión a un punto coherente con la base de datos.

La planificación y el número de secuencias de restauración de grupos de archivos depende del objeto de la recuperación, del número de grupos de archivos sin conexión que se desea restaurar y de cuantos de éstos se restauraran por secuencia de restauración de grupos de archivos.
Los requisitos concretos para realizar una recuperación por etapas dependen del modelo de recuperación de la base de datos.

Bases de datos del sistema y recuperación

Las bases de datos del sistema almacenan información importante de SQL Server y de las bases de datos de usuario, por lo que es conveniente realizar copias de seguridad periódicamente.
La base de datos "master", almacena toda la información relacionada con el servidor SQL Server (cuentas de inicio de sesión, opciones de configuración del sistema, las credenciales, así como la información necesaria para tener acceso a las demás bases de datos).
Se recomienda programar copias de seguridad completas rutinarias de la base de datos "master", con la frecuencia necesaria para proporcionar suficiente protección de datos.
Si la base de datos "master" está dañada, no se podrá iniciar una instancia de servidor, para ello, será necesario volver a generar "master", con lo que se revertirán todas las bases de datos del sistema a su estado original.

Una copia de seguridad de la bbdd "master" debe realizarse:

Al crear o eliminar bases de datos de usuario.
Al agregar o quitar archivos de una base de datos (cambia la estructura física).
Al agregar inicios de sesión u otras operaciones relativas de los inicios de sesión.
Al cambiar opciones de configuración que afectan a todo el servidor o a bases de datos.
Al crear o eliminar dispositivos lógicos de copias de seguridad.
Al configurar el servidor para consultas distribuidas y llamadas a procedimientos remotos (p.e. agregar servidores vinculados o inicios de sesión remotos).

En el caso de la base de datos del sistema "msdb", debe de realizarse una copia de seguridad de esta base de datos después de modificarla, dado que contiene información de los trabajos, alertas y operadores utilizados por el agente SQL Server, además de información del historial de copias de seguridad y restauración.

La base de datos "model" es la plantilla que SQL Server utiliza para crear nuevas bases de datos de usuario. Todas las opciones de "model", incluidas las opciones de configuración de la base de datos, se copian en la nueva base de datos. Por tanto, será necesario realizar una copia de seguridad de esta bbdd si se ha modificado para incluir alguna configuración predeterminada para todas las bases de datos nuevas.

Si se dañan las bases de datos del sistema, será necesario restaurar o generar dichas bases de datos. La decisión sobre si restaurar o generar la base de datos, se basará en si es posible iniciar el servicio de SQL Server.

Si se inicia el servicio, se realizará una restauración utilizando la copia de seguridad disponible más reciente. Si no es posible iniciar el servicio, se deberán generar nuevamente las bases de datos.

Una vez que las bases de datos del sistema se hayan generado de nuevo, es necesario restaurar las bases de datos del sistema en el orden siguiente:

MASTER
MSDB
MODEL

Al realizar la restauración de "master", si se ha utilizado una copia de seguridad válida, contendrá referencias a cada base de datos de usuario, pero si no es así, se deberá restaurar las bases de datos de usuario desde cada una de las copias de seguridad o asociar los archivos de base de datos de usuario existentes a la nueva base de datos "master" (Mediante procedimiento "SP_ATTACHDB" o con la opción "Adjuntar base de datos" de SQL Management Studio).

Proceso de restauración de la bbdd "master", si hay acceso a la instancia SQL Server:

Iniciar SQL Server en modo de usuario único (SQLSERVR.EXE -c -m)
Restaurar la última copia de seguridad de "master" desde la utilidad Sqlcmd. Para la bbdd "master" solamente es posible utilizar copias de seguridad completas, con lo que el proceso de restauración terminará ahí.
RESTORE DATABASE master FROM <backup_device> WITH REPLACE
Reiniciar el servidor.

Automatización de tareas administrativas en SQL Server

Una de las funciones de un administrador de SQL Server es el mantenimiento de sus bases de datos. Esto incluye tareas que deben realizarse de manera programada, además de tareas adicionales para anticiparse a los problemas antes de que se produzcan.

El trabajo de administrador conlleva una serie de funciones que no varían a lo largo del tiempo, lo que puede llegar a ser aburrido. Para ello, es posible automatizar todas estas tareas rutinarias y configurar SQL Server para supervisar ciertos tipos de problemas antes de que aparezcan.

Ventajas de la automatización

Reducción de la carga administrativa, asociada a tareas repetitivas y a la supervisión de SQL Server. Al implementar trabajos y alertas, se puede configurar SQL Server para responder automáticamente a los problemas que surjan, e incluso evitar que algunos de ellos aparezcan. Esto permite a los administradores dedicarse a otras tareas como planear cambios en las bases de datos u optimizar rendimientos.
Reducción del riesgo de que se omitan tareas de mantenimiento fundamentales.
Reducción del riesgo de error humano.
Administración proactiva a través de alertas, indicando la acción que debe realizarse para impedir que se produzcan problemas.

El Agente SQL Server

Se trata del componente encargado de la automatización de las tareas administrativas de SQL Server. Para que este agente pueda ejecutar trabajos y enviar alertas, debe estar en funcionamiento siempre, debiendo de disponer de los permisos suficientes.
En todos los sistemas operativos Windows, el agente SQL Server se ejecuta como servicio de Windows, y debería de programarse automáticamente para que se inicie cuando arranca el sistema operativo. Además, puede configurarse el servicio del agente para que se reinicie automáticamente si se detiene de forma inesperada desde el administrador de configuración SQL Server en el Management Studio.
Para que se reinicie automáticamente, la cuenta del servicio asociado al agente debe ser un miembro del grupo local de administradores. De forma predeterminada, el agente no está configurado para iniciarse automáticamente (Modo manual en lugar de modo automático).

Cada instancia de SQL Server tiene su propio servicio de Agente SQL Server, denominado SQLSERVERAGENT para las instancias predeterminadas, o SQLAgent$nombre_instancia para aquellas que tengan nombre.

Cuando se inicia el servicio Agente, se conecta a SQL Server y ejecuta el procedimiento almacenado extendido SP_SQLAGENTMONITOR en la base de datos "master". Este procedimiento supervisa el estado del servicio SQL Server y lo reinicia si se detiene inesperadamente.

Implementación de trabajos y operadores

Para realizar las tareas administrativas repetitivas de manera automatizada, es posible apoyarse en lo que se denomina trabajos. También es posible utilizar los operadores, a los que se puede notificar cuando ha finalizado un trabajo o se ha activado alguna alerta.

Los operadores son los alias para personas o grupos que pueden recibir notificaciones cuando los trabajos han finalizado o se hayan activado alertas. Deben definirse antes que las alertas y antes de la creación de trabajos. Cuando un trabajo finaliza, o si cualquiera de los pasos del trabajo falla, puede notificarse a un operador mediante un localizador, mediante correo electrónico o mediante el comando NET SEND. Los principales atributos de un operador son:

Nombre del operador (Alias)
Información de contacto del operador (dirección electrónico, dirección IP)

La definición del operador se almacena en la tabla "sysoperators" de la base de datos "msdb", y se pueden crear mediante Management Studio, o mediante el procedimiento almacenado SP_ADD_OPERATOR.

También puede definirse u operador a prueba de errores, para que responda a una alerta cuando las notificaciones mediante localizador enviadas a los operadores definidos fallen (sólo para notificaciones dirigidas a localizadores). Por ejemplo, si todos los localizadores no están disponibles cuando se produce una alerta, se pondrá en contacto con el operador a prueba de errores. Solamente puede haber un operador a prueba de errores.

Implementación de trabajos

Un trabajo es una serie especificada de acciones realizadas secuencialmente por el Agente SQL Server. Puede ejecutar una amplia variedad de sentencias, incluidas scripts Transact-SQL, comandos de sistema operativo, secuencias de comandos de Microsoft ActiveX, paquetes de integración SSIS, comandos y consultas de análisis service o tareas de replicación.
Los trabajos se pueden programar para ejecutarse una vez o de forma repetitiva, pudiendo notificar a los operadores del estado del trabajo mediante alertas, incluido la finalización del mismo.
Un trabajo puede ejecutarse manualmente, o pueden programarse según una determinada programación o como respuesta a alertas.

Para crear un trabajo, el usuario debe ser miembro de una de las funciones fijas de base de datos del agente SQL Server, o de la función fija de servidor "sysadmin". Solamente podrá editar el trabajo el propietario de éste o los miembros de dicha función.
Se puede escribir un trabajo para que se ejecute en la instancia local o en varias instancias. En el caso de ejecutar trabajos en varios servidores, debe de configurarse al menos un servidor como principal y el resto como servidores de destino.
Para crear trabajos, es posible hacerlo mediante Managenment Studio o mediante la ejecución del procedimiento almacenado SP_ADDJOB. La definición del trabajo se almacena en la tabla "sysjobs" de la base de datos "msdb".

Al definir un trabajo debe de indicarse los siguientes parámetros:

Indicación de si el trabajo está habilitado. Un trabajo se habilita de manera predeterminada. Si un trabajo está deshabilitado no podrá ejecutarse tal y como estaba programado, pero sí se podrá ejecutar manualmente si se inicia en el administrador corporativo de SQL Server.
Indicación del propietario responsable de la realización del trabajo. De forma predeterminada, el propietario es la cuenta de inicio de sesión del usuario de Windows o SQL Server que ha creado el trabajo.
Debe de indicarse si el trabajo se ejecuta en un servidor local o en varios servidores remotos.
Categoría del trabajo al que pertenece. Es posible crear nuevas categorías para mantener organizados los trabajos.

Los pasos de trabajo, son cada unas de las acciones que realiza un trabajo en una base de datos o en un servidor.

Cada trabajo debe de estar formado como mínimo por un paso, y pueden ser programas ejecutables, comandos del sistema operativo, instrucciones Transact-SQL incluidos los procedimientos almacenados y los procedimientos almacenados extendidos.
Los pasos de trabajo tambien pueden ejecutar scripts de PowerShell, scripts de Microsoft ActiveX, tareas de replicación, tareas de análisis service y paquetes de integración.

En un paso es posible ejecutar una o varias sentencias. Si son varias sentencias deben de ser del mismo tipo. Los pasos de trabajo se almacenan en la table "sysjobsteps" de la tabla "msdb".

Todos los pasos de trabajo se ejecutan en un contexto de seguridad determinado. Si se especifica un proxy, se ejecuta en el contexto de seguridad de la credencial del proxy.
Si en el paso de trabajo no se especifica un proxy, se ejecuta en el contexto de seguridad de la cuenta de servicio del agente SQL Server.
Sólo los miembros de la función de servidor "sysadmin" pueden crear trabajos en los que no se especifique un proxy de forma explícita.

Puesto que los pasos de trabajo se ejecutan en el contexto de un usuario específico de Windows, dicho usuario debe disponer de los permisos y de la configuración necesaria para que se ejecute el paso de trabajo.

Los comando del sistema operativo pueden utilizarse en los pasos de trabajo. Estos archivos tienen extensión BAT, CMD, CON o EXE. Se debe especificar en estos casos el código de salida del proceso que se devuelve si el comando se ejecuta correctamente y la ruta de acceso completa a la aplicación ejecutable.

En el caso de difinir pasos de trabajo de scripts ActiveX, se debe de identificar el lenguaje de script en el que se ha escrito el paso de trabajo y escribir a continuación el script ActiveX.

Si se definen pasos de trabajo con paquetes de servicio de integración, debe de indentificarse el origen y ubicación del paquete, identificar los archivos de configuración que son necesarios para el paquete, identificar los archivos de comandos sin son necesarios para el paquete, identificar los orígenes de datos, identificar los proveedores de registro del paquete, especificar las variables y los valores que se deben establecer para ejecutar el paquete e identificar las opciones de ejecución.

El flujo de ejecución de los pasos de trabajo, de forma predeterminada es según el orden establecido, pasando al siguiente paso si se ejecuta correctamente o deteniéndose si no se ejecutó correctamente.

Se puede indicar el número de reintentos que deben de ejecutarse en un paso de trabajo en caso de producirse un error en él, especificando tambien el intérvalo de tiempo (minutos) entre cada reintento.

Es posible indicar distintos flujos de ejecución según la evolución y desarrollo del trabajo.

Monitor de actividad de trabajos

Permite supervisar la actividad actual de todos los trabajos definidos en una instancia de SQL Server. El agente SQL Server crea un sesión cada vez que se inicia el servicio. Al crear una sesión, la tabla "sysjobsactivity" de la base de datos "msdb" se rellena con todos los trabajos existentes. Esta tabla mantiene la última actividad para los trabajos cuando se reinicia el agente SQL Server. Cada sesión registra la actividad de trabajo normal del agente SQL Server desde el inicio del trabajo hasta que termina. La información acerca de estas sesiones se almacena en la tabla "syssession" de la base de datos "msdb".

Con el monitor de actividad es posible realizar varias tareas. Entre ellas están:

Iniciar y detener trabajos.
Ver las propiedades del trabajo.
Ver el historial de un determinado trabajo.
Actualizar la información de la cuadrícula de la actividad de trabajo del agente.
Establecer un intervalo de actualización automático.

Administrar la seguridad del Agente SQL Server

El agente SQL Server puede utilizarse para realizar tareas en la base de datos y en el servidor. El acceso a las funciones del Agente deben protegerse para que solamente los usuarios autorizados puedan ejecutar los trabajos programados para el Agente SQL Server.

Las funciones del Agente SQL Server incluyen SQLAgentUserRole, SQLAgentReaderRole y SQLAgenteOperatorRole, todas ellas almacenadas en la base de datos "msdb". De esta manera se controla en acceso al Agente para todos aquellos usuarios que no están dados de alta en la función fija de servidor "sysadmin". De manera predeterminada, ningún usuario es miembro de estas funciones de base de datos, la pertenencia a estas funciones se debe conceder explicitamente.

SQLAgentUserRole: Es la función que menos permisos asigna. Los miembros de esta función solo tienen permiso en los trabajos de los que son propietarios. No pueden utilizar trabajos multiservidor, ni pueden cambiar la propiedad de un trabajo del que no es propietario. Si que pueden ver una lista de los servidores proxy que pueden visualizarse en las propiedades del paso de trabajo.
SQLAgentReaderRole: Esta segunda función incluye todos los permisos de la anterior, así como permisos para ver la lista de trabajos multiservidor disponibles, sus propiedades y su historial. Tambien pueden ver la lista de trabajos y programaciones de todos los trabajos disponibles en el servidor y no solo de los trabajos y programaciones de los que son propietarios. Los miembros de esta función no pueden cambiar la propiedad de un trabajo, y solo está visible para los miembros de esta función el nodo trabajos en el explorador de objetos del Management Studio. Así, los usuarios pertenecientes a esta función pueden ver los trabajos existentes en el servidor pero no pueden cambiar las propiedades de estos trabajos.
SQLAgentOperatorRole: Es la función con más privilegios de todas las funciones fijas de base de datos del agente, incluyendo los permisos de las dos anteriores. Los miembros de esta función pueden ver las propiedades de operadores y servidores proxy, así como enumerar los servidores proxy y alertas disponibles en el servidor. Los miembros de esta función, tienen permisos adicionales en los trabajos locales y en las programaciones, pudiendo iniciar, detener o ejecutar todos los trabajos locales. Pueden eliminar también el historial de cualquier trabajo local del servidor. Pueden habilitar o deshabilitar todos los trabajos locales y programaciones del servidor. No pueden cambiar la propiedad de un trabajo para tener acceso a trabajos que no les pertenezcan. Para los miembros de esta función están visibles los nodos de trabajos, alertas, operadores y servidores proxy en el explorador de objetos del Management Studio. El único nodo que no está visible es el de registro de errores.

Proxy del Agente SQL Server

El agente SQL Server utiliza servidores proxy para administrar contextos de seguridad en los pasos de trabajo que no son del tipo Transact-SQL, y que tienen acceso a un subsistema en concreto (p.e. secuencias de comandos ActiveX, ejecución de comandos en el sistema operativo..).
Puede utilizarse un proxy en más de un paso de trabajo. Un proxy permite que el agente SQL Server tenga acceso a las credenciales de seguridad de un usuario de Windows. Cada proxy puede asociarse con uno o varios subsistemas.

Las credenciales son objetos de SQL Server que se utilizan para almacenar nombres y contraseñas de usuarios de Windows.
Un paso de trabajo que usa el proxy, puede tener acceso a los subsistemas especificados usando el contexto de seguridad del usuario de Windows.

Las cuentas de proxy del agente SQL Server utilizan credenciales para almacenar información acerca de las cuentas de usuario de Windows. El usuario especificado en las credenciales debe tener el permiso de iniciar sesión como proceso por lotes en el equipo en el que se ejecuta SQL Server.
El agente SQL Server comprueba el acceso al subsistema de un proxy, y da acceso al proxy cada vez que se ejecuta el paso de trabajo. Si el proxy ya no tiene acceso al subsistema, el paso de trabajo da error. De lo contrario, el agente SQL Server suplanta al usuario especificado en el proxy y ejecuta el paso de trabajo.
Para crear un paso de trabajo que utiliza un proxy determinado, el propietario del trabajo debe especificar un inicio de sesión asociado al proxy, o debe ser miembro de una función con acceso ilimitado a los servidores proxy.

Los miembros de la función fija de servidor "sysadmin" tienen acceso ilimitado a los servidores proxy. Los miembros de las funciones "SQLAgentUserRole", "SQLAgentReaderRole" y "SQLAgentOperatorRole", solo pueden utilizar servidores proxy para los que dispongan de acceso específico. Cada usuario que sea miembro de alguna de estas funciones de base de datos del agente SQL Server, debe tener acceso a servidores proxy específicos, para poder crear pasos de trabajo que utilicen estos proxys.

Correo electrónico de base de datos (Database Mail)

El correo electrónico de base de datos es un solución empresarial para enviar mensajes de correo electrónico desde SQL Server Database Engine (Motor de base de datos de SQL Server). Está diseñado para proporcionar confiabilidad, escalabilidad, seguridad y compatibilidad.
El correo electrónico de base de datos (que no está activo de forma predeterminada), permite a las aplicaciones de base de datos enviar mensajes d correo electrónico a los usuarios. Los mensajes enviados pueden incluir resultados de consultas y archivos de cualquier recurso de la red.

Confiabilidad: No se requiere Microsoft Outlook. Usa el protocolo estándar SMTP y es un proceso externo a SQL Server.
Seguridad: Para enviar un correo electrónico, se debe pertenecer al rol de base de datos "DatabaseMailUserRole" en la base de datos "msdb". Se ejecuta con la cuenta del servicio del motor de SQL Server.

Configuración de Database Mail:

Habilitar el correo electrónico, ya que por defecto está deshabilitado.
Crear un perfil de correo electrónico de base de datos, que incluya una cuenta de correo para utilizar el Agente SQL Server.
El perfil debe ser predeterminado para el usuario en la base de datos "msdb" que se asigna al inicio de sesión de la cuenta del servicio del Agente SQL Server.
Agregar al usuario para el inicio de sesión del servicio del Agente SQL Server a la función de base de datos "DatabaseMailUserRole" en la base de datos "msdb".
Configurar las propiedades del sistemas de alertas del Agente SQL Server, especificando el perfil creado en los pasos anteriores.
Reiniciar el servicio del Agente SQL Server.

Descripción de errores de SQL Server

La descripción de un error enviado por SQL Server, consta de varios atributos. Estos atributos son:

Número de error: Cada mensaje de error tiene un número de error único.
Cadena de mensaje de error: El mensaje de error contiene información de diagnóstico acerca de la causa del error. Muchos mensajes de error tienen variables de sustitución en las que se inserta información como, por ejemplo, el nombre del objeto que genera el error.
Gravedad: La gravedad indica la importancia del error. Un error de gravedad baja (1 o 2..) son mensajes informativos o de advertencia de bajo nivel. Los errores de alta gravedad indican problemas que deben ser atendidos tan pronto como sea posible.
Estado: Algunos mensajes de error se pueden generar en varios puntos del código de Motor de base de datos. Por ejemplo, el error 1105 se puede generar bajo diferentes condiciones. Cada condición específica bajo la que se genera un error asigna un código de estado único.
Nombre del procedimiento: Nombre del procedimiento almacenado o desencadenador en que se ha producido el error.
Número de línea: Indica qué instrucción de un lote, procedimiento almacenado, desencadenador o función ha generado el error.

Todos los mensajes de error se almacenan en la base de datos "master" en la vista de catálogo "sys.messages".

Niveles de gravedad:

Nivel 0-9: Son mensajes informativos que devuelven información de estado o informan sobre errores que no son graves. El motor de base de datos no muestra errores del sistema con gravedades de 0 a 9.
Nivel 10: Mensajes informativos que devuelven información de estado o informan sobre errores que no son graves. Por razones de compatibilidad, el motor de base de datos convierte la gravedad 10 en gravedad 0 antes de devolver la información de errores a la aplicación que hace la llamada.
Nivel 11-16: Indica errores que pueden ser corregidos por el usuario.
Nivel 17-19: Indica errores de software que no pueden ser corregidos por el usuario. Informe al administrador del sistema sobre el problema.
Nivel 20-24: Indica problemas del sistema y son errores irrecuperables, lo que significa que ya no está en ejecución la tarea de motor de base de datos que esté ejecutando una instrucción o lote. La tarea registra información sobre lo acontecido y, a continuación, finaliza. En la mayoría de los casos puede que tambien finalice la conexión de la aplicación a la instancia de la base de datos. Estos errores quedan registrados en el registro de errores.

Creación y adminstración de alertas en SQL Server

Las alertas son respuestas predefinidas a eventos concretos que pueden producirse en una instancia de SQL Server.

Se pueden definir alertas para ejecutar un trabajo o notificar a un operador cuando se produce un evento determinado o se excede un umbral de rendimiento.

SQL Server genera eventos que se incluyen en el registro de aplicación de Windows.
El Agente SQL Server lee el registro de aplicación y lo compara con las alertas definidas para activar la que corresponda. Tambien es capaz de generar alertas relacionadas con el rendimiento del sistema.

Auditorias de SQL Server

La auditoría de SQL Server permite registrar los eventos que se producen en el motor de base de datos, y crear auditorias de servidor que pueden contener diferentes especificaciones, donde es posible indicar los eventos, tanto a nivel de servidor como a nivel de base de datos, que se desea registrar en la auditoria.

SQL Server Audit

La auditoría de SQL Server permite crear auditorías de servidor que pueden contener especificaciones de auditoría de servidor para los eventos de servidor, y especificaciones de auditoría de base de datos para los eventos de base de datos.

Los eventos auditados se pueden escribir en los registros de eventos o en los archivos de auditoría .

Se pueden registrar grupos de acciones de auditoría en el servidor por instancia, así como grupos de acciones o acciones de auditoría en la base de datos por base de datos.

El evento de auditoría se producirá cada vez que se encuentre la acción auditable.

Una auditoría es la combinación de varios elementos en único paquete para un grupo específico de acciones de servidor o de base de datos.

Los componentes de SQL Server Audit se combinan para producir una salida denominada auditoría.

SQL Server Audit usa eventos extendidos para facilitar la creación de auditorías.

El objeto SQL Server Audit recopila una única instancia de acciones y grupos de acciones de nivel de servidor o de base de datos par su supervisión.
El objeto "Especificación de auditoría de servidor" pertenece a una auditoría. Puede crear una especificación de auditoría de servidor por cada auditoría, ya que ambos se crean en el ámbito de la instancia de SQL Server.
El objeto "Especificación de auditoría de base de datos" también pertenece a una auditoría de base de SQL Server. Puede crear una única especificación de auditoría de base de datos para cada base de datos de SQL Server y cada auditoría.
Destino. Los resultados de una auditoría se envían a un destino que puede ser un archivo, el registro de eventos de seguridad de Windows o el registro de eventos de aplicación Windows.

Configuración de SQL Server Audit. Información general:

Es posible utilizar SQL Server Management Studio o Transact-SQL para definir una auditoría.
Una vez creada y habilitada la auditoría el destino comenzará a recibir entradas.
El proceso general de creación y uso de una auditoría es el siguiente:

Crear una auditoría definiendo un destino.
Crear una especificación de auditoría de servidor o de base de datos que se asigne a la auditoría. Habilitar la especificación de auditoría.
Habilitar la auditoría.
Leer los eventos de auditría mediante le Visor de eventos, el Visor de archivos de registro de Windows o la función "fn_get_audit_file".

Grupos de Acciones o Acciones de SQL Server Audit

Las auditorías de SQL Server constan de cero o más elementos de acción de auditoría. Estos elementos pueden ser grupos de acciones o acciones individuales.

Las auditorías pueden tener las siguientes categorías de acciones:

Nivel de servidor. Estas acciones incluyen las operaciones del servidor, como cambios de administración y operaciones de inicio y cierre de sesión.
Nivel de base de datos. Estas acciones comprenden operaciones de lenguaje de manipulación de datos (DML) y de lenguaje de definición de datos (DDL).
Nivel de auditoría. Son las acciones relacionadas con el proceso de auditoría.

Para evitar la necesidad de muchas acciones individuales, se proporcionan los grupos de acciones.

Grupos de acciones de auditoría en el nivel de servidor (algunos de ellos):

BACKUP_RESTORE_GROUP: Este evento tiene lugar cuando se emite un comando de copia de seguridad o de restauración.
DATABASE_CHANGE_GROUP: Este evento se desencadena al crear, modificar o quitar una base de datos.
DATABASE_OBJECT_ACCESS_GROUP: Este evento se desencadena siempre que se tenga acceso a los objetos de la base de datos.
DATABASE_OBJECT_CHANGE_GROUP: Este evento se desencadena al ejecutar una instrucción CREATE, ALTER o DROP en objeto de base de datos.
FAILED_LOGIN_GROUP: Indica que una entidad de seguridad intentó iniciar una sesión de SQL Server, pero no lo consiguió.

Grupos de acciones de auditoría en el nivel de base de datos (algunos de ellos):

DATABASE_CHANGE_GROUP: Este evento se desencadena al crear, modificar o quitar una base de datos.
DBCC_GROUP: Este evento se desencadena cuando una entidad de seguridad emite un comando DBCC.
SCHEMA_OBJECT_CHANGE_GROUP: Este evento se desencadena al realizar una operación CREATE, ALTER o DROP en un esquema.
DTABASE_OBJECT_CHANGE_GROUP: Este evento se desencadena al ejecutar un instrucción CREATE, ALTER o DROP en objetos de la base de datos.

En la ayuda de SQL Server es posible consultar la lista completa de grupos de acciones tanto a nivel de servidor como de base de datos.

Crear una auditoría de servidor y una especificación de auditoría de servidor

La auditoría de una instancia de SQL Server o de una basse de datos de SQL Server implica el seguimiento y registro de los eventos que se producen en el sistema.

La auditoría se realiza en el nivel de instancia de SQL Server, y es posible tener varias auditorías por cada instancia de SQL Server.

El objeto Especificacion de auditoría de servidor pertenece a una auditoría y solamente es posible crear un objeto de este tipo por cada auditoría, ya que ambos se crean en el ámbito de la instancia de SQL Server.

Realizar el mantenimiento de las bases de datos de SQL Server

El motor de base de datos SQL Server es capaz de funcionar de manera continua sin ningún tipo de mantenimiento, sin embargo, se obtendrán mejores resultados en cuento a rendimiento de las bases de datos si se llevan a cabo operaciones de mantenimiento rutinarias.

Una de las tareas más importantes en el mantenimiento rutinario de una base de datos es comprobar si se corrompe o se ha corrompido.

Otro de los puntos a tener en cuenta son los índices de SQL Server. Es necesario llevar un control de estos objetos para comprobar la fragmentación que se va produciendo en los índices y evitar que dicha fragmentación no sea demasiado alta.

SQL Server dispone de un asistente de planes de mantenimiento de la base de datos, y permite crear un trabajo del Agente SQL Server que aglutine todas estas tareas de mantenimiento, de manera que sea posible conseguir un rendimiento óptimo.

Mantenimiento de la integridad de la base de datos

Es particularmente raro que el motor de base de datos pueda causar la corrupción directamente, sin embargo, depende de la plataforma hardware en al que se ejecuta y puede causar la corrupción. Particularmente los problemas de memoria y los subsistemas E/S, pueden conducir a la corrupción dentro de las bases de datos.
Si la corrupción en la base de datos no se detecta poco después de haberse producido, pueden surgir una serie de problemas posteriormente. No tiene mucho sentido intentar recuperar una base de datos corrupta utilizando copias de seguridad que contienen la base de datos ya con problemas. En este caso habrá que retroceder lo necesario en el tiempo para obtener una copia limpia, aunque habrá que aceptar pérdida de información.

Existe el comando DBCC CHECKDB, que permite detectar la corrupción en bases de datos, y en algunas circunstancias incluso corregirlas.

La integridad de la base de datos es la integridad física y lógica de la base de datos:

Integridad física: Las páginas de datos se escriben en el almacenamiento físico como SQL Server solicitó y también se pueden leer correctamente.
Integridad lógica: Los datos dentro de las páginas están lógicamente colocados de forma correcta. Por ejemplo, cada entrada de índice apunta a la fila de datos correcta y cada fila de datos está referenciada por una entrada de índice.

Conviene realizar comprobaciones periódicas de la integridad de la base de datos para garantizar el buen estado de salud del sistema.

La sentencia DBCC CHECKDB comprueba la integridad física y lógica de todos los objetos de la base de datos especificada, validando:

El contenido de cada vista indizada de la base de datos.
La coherencia de nivel de vínculo entre los metadatos de la tabla y los directorios y archivos del sistema de archivos cuando almacena datos varbinary(max) en el sistema de archivos mediante FILESTREAM.
Los datos de Service Broker en la base de datos.

Ejecutar la sentencia DBCC CHECKDB es el resultado de ejecutar los siguientes comandos de manera consecutiva:

DBCC CHECKALLOC: Comprueba la consistencia de las estructuras de asignación de espacio en disco para na base de datos especificada.
DBCC CHECKTABLE: Comprueba las páginas asociadas con una tabla especificada y los punteros entre las páginas que están asociados con la tabla. DBCC CHECKDB ejecuta DBCC CHECKTABLE para cada tabla en la base de datos.
DBCC CHECKCATALOG: Comprueba el catálogo de base de datos mediante la realización de comprobaciones de coherencia lógicas en las tablas de metadatos en la base de datos. Estas tablas de metadatos se utilizan para mantener la información que describe el sistema y tablas de usuario y otros objetos de base de datos. DBCC CHECKCATALOG no comprueba las tablas de usuario.

Las opciones de configuración de DBCC CHECKDB son:

PHYSICAL_ONLY: Limita la comprobación a la integridad de la estructura física de los encabezados de página y registro y la coherencia de la asignación de la base de datos. Esta opción se recomienda utilizarla en bases de datos grandes y para uso frecuente debido a que puede costar menos tiempo. Aun así se recomienda realizar una comprobación completa de manera frecuente. Esta frecuencia dependerá de factores específicos de cada empresa y de los entornos de producción.
NOINDEX: Especifica que no se deben realizar comprobaciones intensivas de índices no clúster para las tablas de usuario.
EXTENDED_LOGICAL_CHECKS: Si el nivel de compatibilidad lo permite, realiza comprobaciones de coherencia lógica en una vista indizada, en índices XML y en índices espaciales, en caso de que los haya.
TABLOCK: Hace que DBCC CHECKDB obtenga bloqueos en lugar de utilizar una instantánea de base de datos interna.
ALL_ERRORMSGS: Muestra todos los errores notificados por objeto.
NO_INFOMSGS: Suprime todos los mensajes de información.
ESTIMATEONLY: Muestra la cantidad de espacio para la base de datos "tempdb" que se estima necesario para ejecutar DBCC CHECKDB con todas las demás opciones especificadas. No se realiza la comprobación real de la base de datos.

Las opciones de reparación configurables en DBCC CHECKDB son:

Es necesario indicar que la base de datos de estar en modo único. Se creará un archivo de volcado (SQLDUMP nnnn.txt) en el directorio LOG de SQL Server cada vez que DBCC CHECKDB detecta un eror relacionado con datos dañados.
REPAIR_REBUILD: realiza reparaciones que no tienen ninguna posibilidad de pérdida de datos. Pueden ser reparaciones rápidas, como la reparación de filas que faltan en índices no clúster, o reparaciones que consumen más tiempo como la regeneración de un índice.
REPAIR_ALLOW_DATA_LOSS: Intenta reparar todos los errores indicados. Estas reparaciones pueden ocasionar alguna pérdida de datos.

Si DBCC CHECKDB detecta problemas, se recomienda restaurar la base de datos desde una copia de seguridad en lugar de ejecutar los comandos de reparación anteriores. Si no hay disponible ninguna copia de seguridad reciente al ejecutar estos comandos se corregirán los errores notificados. La opción de reparación a utilizar se especifica al final de la lista de los errores notificados por DBCC CHECKDB.

Mantenimiento de índices

Un aspecto importante de SQL Server que requiere un mantenimiento continuo para un rendimiento óptimo es la gestión de los índices.
Los índices se utilizan para acelerar las operaciones en las que SQL Server necesita acceder a los datos de una o varias tablas. Con el tiempo estos índices pueden fragmentarse, reduciéndose el rendimiento de las aplicaciones de base de datos que necesitan acceder a la información.

SQL Server realiza el acceso a los datos de dos maneras:

Escanado de la tabla: SQl Server lee todas las páginas de datos de las tablas y extrae la información solicitada.
Índices: SQL Server utiliza las páginas de índice para encontrar la información.

Un motón (heap) es una tabla sin un índice agrupado. Las páginas se almacenan sin ningún orden concreto. Esto puede ser aceptable si se trata de pequeñas tablas.

Un índice es una estructura de disco asociada con una tabla o una vista, que acelera la recuperación de filas de la tabla o de la vista. Un índice contiene claves generadas a partir de una o varias columnas de la tabla o la vista. Dichas claves están almacenadas en una estructura de arbol, que permite que SQL Server busque de forma rápida y eficaz.

Estructura de un índice. Nodos raiz y nodos hoja.

Tipos de índices:

Índice agrupado: Tiene páginas de datos a nivel de hoja, que es donde se almacenan. Los índices agrupados determinan el orden lógico de las filas dentro de la tabla. Conceptualmente es como un diccionario, cuyos términos son la clave del índice. Sólamente hay un índice agrupado por cada tabla.
Índice no agrupado: Tiene punteros a las filas de datos en el nivel de hoja. Es posible tener hasta 999 índices no agrupados por tabla. Los índices no agrupados (nonclustered) son estructuras separadas con punteros a la ubicación de los datos. Conceptualmente es similar al glosario impreso en la parte posterior de un libro.

La fragmentación de los índices se produce debido a que SQL Server reorganiza las páginas de índice cuando se modifican los datos y esto hace que las páginas de índice se dividan.
Un índice muy fragmentado puede reducir el rendimiento de la consulta, ralentizando la respuesta de la aplicación.

Existe fragmentación interna (las páginas no están llenas) y externa (las páginas no están en la secuencia lógica). La fragamentación puede detectarse mediante SQL Server Management Studio (propiedades del índice) o mediante la función del sistema "sys.dm_db_index_physical_stats".

Opciones para incorporar espacio libre a los índices:

FILLFACTOR: determina la cantidad de espacio libre en los nodos de hoja. Porcentaje de espacio relleno en el nivel de hoja.
PAD_INDEX: determina la cantidad de espacio libre en los nodos de índice que no son de hoja. Indica si se debe aplicar también el FILLFACTOR a los niveles intermedios y al nivel raiz.

Opciones para desfragmentar índices:

Reorganizar: Menor o igual a un 30% de fragmentación. ALTER INDEX REORGANIZE. Se necesita poco espacio en el registro de transacciones y proceso puede ser interrumpido conservándose el trabajo hasta el momento.
Volver a generar: Mayor a un 30% de fragmentación. ALTER INDEX REBUILD. Es necesario disponer de espacio libre en la base de datos. El proceso se realiza en una única transacción, con lo que se requiere de gran cantidad de espacio en el registro de transacciones.

Planes de mantenimiento

Permiten programar las tareas de mantenimiento principales, a fin de garantizar la realización de copias de seguridad de la base de datos con regularidad, que su rendimiento sea el adecuado, y que se lleven a cabo comprobaciones en busca de incoherencias.
El asistente para planes de mantenimiento, crea uno varios trabajos del Agente SQL Server, que realizan estas tareas de mantenimiento automáticamente a intervalos programados, o también se pueden ejecutar de forma manual.

Un plan de mantenimiento puede incluir diferentes tareas como:

Copia de seguridad.
Compactación de archivos, eliminando páginas vacías.
Controles de consistencia interna de las páginas de datos.
Reorganización y regeneración de índices.
Actualización de estadísticas.
Ejecución de trabajos.

Capturar la actividad de SQL Server

SQL Server proporciona la capacidad de seguir paso a paso la actividad de servidores de bases de datos, así como las actividades de inicio de sesión, de usuario y de aplicación.
Permite capturar los datos en una tabla, un archivo, o una secuencia de comandos Transact-SQL para un análisis posterior.
Esta información capturada, se podrá enviar al asistente de optimización de bases de datos el cual indicará una serie de recomendaciones en cuanto a índices fundamentalmente y particiones.

SQL Server Profiler

Es una herramienta de interfaz gráfica de usuario que permite supervisar una instancia del motor de base de datos SQL Server o de Analysis Services.
Permite guardar y capturar los datos de cada evento sobre un archivo o tabla para analizarlos posteriormente. Por ejemplo, es posible supervisar un entorno de producción para ver que procedimientos almacenados afectan al rendimiento debido a una ejecución demasiado lenta.
SQL Server Profiler muestra cómo SQL Sever resuelve las consultas internamente, lo que permite a los administradores saber que instrucciones Transact-SQL se envían al servidor, y como éste tiene acceso a la base de datos para devolver conjuntos de resultados.

Mediante SQL Server Profiler es posible:

Crear una traza paso a paso basada en una plantilla reutilizable.
Consultar los resultados de traza mientras se ejecuta.
Almacenar los resultados de la traza en una tabla o archivo para un análisis más detallado.
Iniciar, detener, poner en pausa y modificar los resultados de la traza cuando sea necesario.
Reproducir los resultados de la traza.

Esta herramienta debe utilizarse para supervisar sólo aquellos eventos que interesan, filtrando la información y obteniendo un subconjunto de datos. Supervisar demasiados eventos, puede sobrecargar al servidor y al proceso de supervisión, lo que puede producir que el archivo o la tabla de traza se hagan muy grandes, especialmente cuando el proceso de supervisión dura un largo periodo de tiempo.

Las actividades que se pueden supervisar son:

Consultas con un mal rendimiento.
Consultas que provocan recorridos de tabla.
Actividades de usuarios o aplicaciones individuales.
Rendimiento de la base de datos "tempdb".
Problemas de interbloqueo.
Intentos de inicio de sesión, errores, conexiones y desconexiones.
Lecturas y escrituras del disco lógico.
Uso de CPU a nivel de instrucción.
Tiempo de espera para todos los eventos posteriores a la ejecución.

Cuando se utiliza el SQL Server Profiler para crear una traza, se dispone de varias opciones para definir la actividad que se registrará y dónde se almacenará dicha información recopilada.

Es necesario seleccionar una plantilla en la que estará basada la traza, y sobre la que se realizarán ajustes (agregar o quitar columnas de eventos individuales y aplicando filtros basados en criterios concretos) para adecuar el resultado a la información que se desea registrar.

SQL Server Profiler ofrece varias plantillas predefinidas que constituyen un punto de partida para organizar la información que probablemente sea necesario recopilar.
Por ejemplo, la plantilla estándar ayuda a crear una traza genérica para registrar inicios y cierres de sesión, lotes finalizados e información de conexión. Es posible la creación de plantillas propias, o modificar las plantillas predefinidas.

La información recopilada se podrá almacenar en una tabla o en un archivo,y se utilizará como información de entrada para la herramienta DataBase Engine Tuning Advisor, que es una asistente que propone recomendaciones de actuación basándose en la información de traza recogida.

Solamente los miembros de la función fija de servidor "sysadmin" o el creador de la tabla, tienen acceso directamente a la información de traza recogida.

Las opciones disponibles al guardar una traza en una tabla son:

La ubicación y el nombre de la tabla.
El número máximo de filas que se almacenan en la tabla.

Las opciones disponibles al guardar una traza en un archivo son:

La ubicación y el nombre del archivo.
El tamaño máximo del archivo.

La información registrada en una traza se divide en categorías. Cada categoría contiene eventos, cada uno de los cuales tiene atributos definidos posteriormente por columnas.

Una categoría es una grupo de clases de eventos relacionados. Las clases de eventos están compuestos por tipos de eventos que se pueden seguir paso a paso. La clase de evento contiene todas las columnas de datos sobre las que un evento puede realizar informes.

Las categorías mostradas de forma predeterminada son:

Securiy audit: clases de eventos que se usan para auditar la actividad del servidor.
Sesiones: clases de eventos generadas por clientes que se conectan o desconectan de una instancia de SQL Server.
Procedimientos almacenados: incluyen clases de eventos generados por la ejecución de procedimientos almacenados.
Tansact-SQL: incluye clases de eventos generadas por ejecución de instrucciones Transact-SQL pasadas a una instancia SQL Server desde el cliente.

Un evento se define como la aparición de una acción dentro de una instancia del motor de base de datos de SQL Server. Los eventos se definen por sus atributos que se muestran en columnas de datos.

Lista de eventos:

Audit Login (Categoría Security Audit): Indica que un usuario ha iniciado sesión correctamente en SQL Server.
Audit Logout (Categoría Security Audit): Indica que un usuario ha cerrado la sesión de SQL Server.
ExistingConnection (Categoría Sesiones): Indica las propiedades de conexiones de usuario existentes cuando se inició la traza. El servidor provoca un evento ExistingConnection por cada conexión de usuario existente.
RPC completed (Categoría Procedimientos almacenados): Indica que una llamda de procedimento remoto ha finalizado.
SQL BatchCompleted (Categoría Transact-SQL): Indica que el lote de Transact-SQL ha finalizado.
SQL BatchStarting (Categoría Transact-SQL): Indica que un lote Transact-SQL se está iniciando.

Asistente para la optimización del motor de base de datos

Se trata de una herramienta que analiza los efectos sobre el rendimiento de las cargas de trabajo en una o más bases de datos.

Una carga de trabajo es un conjunto de instrucciones Transact-SQL que se ejecuta en las bases de datos que se desee optimizar. El origen la de carga de trabajo puede ser un archivo que contenga instrucciones Transact-SQL, un archivo de traza generado por el SQL Server Profiler o una tabla de información de traza generada por la misma herramienta anterior.

Es posible realizar el análisis mediante los modos siguientes:

Modo Evaluación: En este modo, el asistente para la optimización del motor de base de datos compara el costo de la configuración actual con la de una configuración especificada por el usuario para la misma carga de trabajo. La configuración actual es siempre una configuración real, ya que consta de estructuras de diseño físicas que existen actualmente en la base de datos, en comparación con la configuración especificada por el usuario, que es una configuración que consta de estructuras de diseño físicas, reales e hipotéticas. Si el asistente informa que el costo de la configuración de usuario es menor que el costo de la configuración actual, es probable que el diseño físico de la configuración de usuario funcione mejor que el de la configuración actual.
Modo Optimización: En este modo, el administrador de la base de datos ya conoce que parte del diseño físico de la base de datos hay que corregir, pero desea que el asistente recomiende las mejores estructuras de diseño físicas para el resto de la configuración.

Después de analizar los efectos de una carga de trabajo sobre la bases de datos, el asistente sugiere recomendaciones. Estas recomendaciones pueden ser nuevos índices, índices que deben eliminarse, y dependiendo de las opciones de optimización que se hayan establecido, recomendaciones incluso sobre particiones.

Las recomendaciones se muestran en forma de instrucciones Transact-SQL que efectuarían los cambios sugeridos.

Páginas

viernes, 18 de noviembre de 2016

Administración de SQL Server 2014

No hay comentarios:

Publicar un comentario