Herramientas adicionales

La auditoría de la actividad de SQL Server

SQL Server tiene la capacidad de realizar una auditoría de su funcionamiento.

Esta auditoría se puede definir en la instancia de SQL Server o en una o varias bases de datos. La auditoría va a seguir los eventos y registra el seguimiento de estos eventos en el diario de auditoría. Este diario de auditoría puede ser un archivo, el diario de eventos de seguridad o el diario de los eventos de aplicaciones de Windows. Sea cual sea el objetivo de la auditoría, el archivo se debe guardar regularmente con objeto de garantizar el espacio necesario para registrar los eventos.

Para poder realizar o modificar una auditoría, es necesario ser miembro del rol sysadmin. También es posible auditar cada modificación de la auditoría.

Es importante notar que la puesta en marcha de una auditoría puede tener un efecto negativo en el rendimiento, ya que la activación de los contadores de auditoría consume recursos del servidor y, por lo tanto, hay menos recursos disponibles para responder a las peticiones de los usuarios. Entonces, en general y con el fin de optimizar el funcionamiento del servidor, sólo se debe realizar una auditoría si se busca resolver un problema.

La limitación de los recursos utilizados por una consulta

El coste de una consulta corresponde a la duración estimada (en segundos) de su ejecución. La opción query governor cost limit permite especificar un límite superior para la ejecución de una consulta.

Por defecto, esta opción recibe el valor 0, lo que autoriza la ejecución de todas las consultas. Si se asigna un valor positivo diferente de 0, entonces el administrador no autoriza la ejecución de todas las consultas, cuyo coste estimado es superior a este valor.

Esta limitación se puede especificar en el servidor por medio de sp_configure o bien en cada base de datos con SET QUERY_GOVERNOR_COST_LIMIT.

La configuración con la opción SET solo está disponible para el período actual de actividad de la instancia. Esta configuración no se conserva para el siguiente inicio de la instancia. Para conservar este valor, es necesario configurar la opción con sp_configure o bien mediante las propiedades del servidor.

set query_governor_cost_limit 20 
 
-- o  
 
sp_configure 'show advanced options',1 
reconfigure 
 
sp_configure 'query governor cost limit', 20 
reconfigure 

Desde SQL Server Management Studio...

El almacén de consultas

Los planes de ejecución evolucionan en función de varios criterios, y para una misma consulta pueden existir varios planes de ejecución posibles. Una de las dificultades es seleccionar el plan que mejor se adapte.

SQL Server propone el almacén de consultas. Como su nombre indica, en el seno de cada base de datos, SQL pretende almacenar las consultas y los planes de ejecución asociados.

Para no sobrecargar la base de datos, esta funcionalidad está desactivada por defecto, y el espacio máximo que puede utilizar el almacén de consultas está definido por el parámetro MAX_STORAGE_SIZE_MB. Es posible conocer el valor de este parámetro consultando la vista sys.database_query_store_options. Su tamaño predeterminado es de 1 GB y este espacio de almacenamiento se toma del grupo de archivos principal.

Para activar el almacén de consultas debemos ejecutar la instrucción ALTER DATABASE para configurar a ON el valor del parámetro QUERY_STORE.

Alter database Gescom set query_store=on; 

Gracias a este mismo comando ALTER DATABASE, pero con la opción SET QUERY_STORE CLEAR, podremos liberar el espacio ocupado por el almacén de consultas.

También podemos ir a la sección Almacén de consultas de las Propiedades de base de datos y allí modificar el valor Modo de operación.

El almacén de consultas proporciona varios...

Los triggers DDL

SQL Server ofrece triggers de tipo DDL. Estos triggers de base de datos funcionan como los triggers asociados a una acción insert, update o delete que puede producirse sobre una tabla dada. La ejecución del trigger DDL está asociada a la ejecución de una instrucción CREATE, ALTER, DROP, GRANT, REVOKE, DENY y UPDATE STATISTICS.

Los triggers DDL se ejecutan después de la instrucción DDL a la que están asociados y DDL permiten seguir las modificaciones realizadas sobre el esquema o bien prohibir algunas modificaciones que puedan realizarse sobre el esquema.

Para prohibir una instrucción DDL en el trigger asociado, es necesario anular la transacción con la instrucción ROLLBACK.

Al definir un trigger DDL, es necesario indicar la instrucción DDL que permite su ejecución, así como su ámbito, es decir, el lugar en el que va a estar activo. El ámbito puede corresponder a una base de datos concreta o a la totalidad de la instancia.

Para permitir seguir lo mejor posible las diferentes ejecuciones de las instrucciones DDL, es posible utilizar la función EVENTDATA en los triggers DDL. Esta función permite capturar la información relativa a la ejecución del trigger y devuelve un flujo XML. Es posible extraer la información de este flujo XML utilizando una consulta XQuery.

PowerShell

Este Shell se introdujo en Windows Server 2008 y permite definir potentes scripts de administración. Esta versión de PowerShell se enriquece de las herramientas específicas de cada aplicación servidor que edita Microsoft.

Este shell permite ejecutar las instrucciones de manera directa o en forma de script.

SQL Server no es una excepción a la regla y aporta su lista de comandos PowerShell. Las aportaciones de SQL Server al PowerShell son:

La consola PowerShell se inicia por medio de la herramienta sqlps. Es posible ejecutar directamente los scripts PowerShell. El applet Get-Help permite, como siempre, encontrar toda la información relativa a los applets de comandos. Para SQL Server, es posible establecer inicialmente Invoke-Sqlcmd que se corresponde a la herramienta sqlcmd en PowerShell.

Ejemplo

La herramienta sqlps ejecuta la consola PowerShell en modo restringido por defecto, lo que impide la ejecución...

La gestión de las reglas

SQL Server introduce la noción de gestión por reglas. Este tipo de gestión permite definir las reglas de administración y aplicarlas a una o varias instancias de SQL Server. Con la multiplicación de las instancias de SQL Server, incluso sobre un mismo puesto, se hace necesario tener una herramienta para simplificar la administración individual de las diferentes instancias. Las reglas también pueden servir para administrar las bases de datos de usuario.

Las reglas van a permitir definir un comportamiento común para todas las bases de datos, instancias y servidores de SQL Server haciendo obligatoria o no la activación de algunos servicios o forzando explícitamente las reglas de nomenclatura, por ejemplo. Entre otras cosas, las reglas van a permitir adoptar una estructura similar en bases de datos distintas, lo que permite una comprensión más fácil de las diferentes bases y facilita la administración.

La gestión por reglas se puede descomponer en tres componentes importantes:

El término regla se puede precisar usando la noción de estrategia y de condición.

Una condición va a permitir asegurarse de que se respetan uno o varios criterios de configuración. La aplicación...