Herramientas adicionales

La auditoría de la actividad de SQL Server

SQL Server ofrece numerosas herramientas de seguimiento del rendimiento del servidor y de la actividad de los usuarios. Esta vigilancia es importante, ya que permite detectar los cuellos de botella y mejorar los tiempos de respuesta del servidor.

Antes de iniciar la auditoría, conviene definir claramente:

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.

La escritura en el diario de seguridad solo es posible si el contexto de seguridad en el que se ejecuta el servicio SQL Server dispone de este permiso. Es el caso por defecto de las cuentas de sistema local, servicio local y servicio de red. Para las otras cuentas, es necesario aplicar la regla Crear auditorías de seguridad.

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...

El generador de perfiles

Para capturar la actividad del servidor y así ser capaces de analizar la carga de trabajo a la que está sometido un servidor es necesario utilizar los eventos extendidos desde SQL Server Management Studio. Esta nueva funcionalidad de SQL Server 2016 permite unificar el comportamiento de las instancias SQL Server instaladas localmente y el de las instancias presentes en Azure.

En efecto, la noción de evento extendido está presente en los dos tipos de instancias SQL Server. En el siguiente ejemplo, presentamos la configuración a través de SQL Server Management Studio, pero es posible definir sesiones mediante consultas SQL. El análisis de los datos, aunque la información se almacene en archivos externos, puede hacerse bajo la forma de consultas SQL. Esta solución permite paliar la interfaz relativamente simple para realizar el análisis de la información.

Para identificar los elementos a capturar, debemos definir en primer lugar las sesiones. Las sesiones son comparables a filtros. Así, en vez de capturar el conjunto de la actividad del servidor, precisamos a nivel de las sesiones el o los eventos que deseamos analizar, y también la cantidad de detalle que deseamos disponer de cada evento.

Para ayudarnos a definirlo, SQL Server proporciona plantillas de sesiones en las cuales nos podemos apoyar para definir más rápidamente la sesión.

La creación de sesiones

Para capturar la actividad, primero es necesario definir una nueva sesión desde la rama Management - Extend Events - Sessions.

La creación de una nueva sesión se puede hacer con el asistente o parametrizando directamente la sesión. El asistente permite construir paso a paso una plantilla mientras que el paso por el cuadro de diálogo permite crear la sesión definiendo las diferentes opciones en el orden deseado. La modificación de una sesión se realiza siempre desde el cuadro de diálogo de propiedades sea cual sea el modo de creación elegido.

Ejemplo

Creación de una sesión apoyándose en una plantilla TSQL

Desde la sección Events es posible seleccionar los eventos. En el caso de utilizar una plantilla, los eventos están predefinidos para su análisis.

Ejemplo

Eventos definidos por defecto en una plantilla TSQL

Por último, la sección Data Storage es importante ya que define la ubicación donde se almacenarán estos datos de tipo evento.

En el caso en el que queramos almacenarlos bajo la forma de un archivo, debemos elegir la opción event file e indicar el nombre y la ubicación del archivo así como su tamaño máximo y el número máximo de archivos almacenados para los eventos extendidos que se pueden crear.

Cuando todos los archivos están completos, esto no bloquea...

El monitor de rendimiento (monitor de sistema)

Se trata del monitor de rendimiento de Windows, al que se han añadido numerosos contadores durante la instalación de SQL Server.

Además de estos contadores de serie, es posible definir diez contadores de usuario para poder adaptar la utilización del Monitor de rendimiento a las necesidades personales.

Los principales objetos específicos de SQL Server son:

Optimización de la memoria y de la unidad central

Por defecto, SQL Server gestiona automática y dinámicamente la cantidad de memoria que necesita. Esta opción debe conservarse en la mayoría de los casos. Sin embargo, es posible fijar las cantidades de memoria mínima y máxima y el tamaño del trabajo.

El Monitor de rendimiento va a permitir vigilar la utilización de la memoria para asegurarse de que el consumo permanece dentro de los límites razonables y que ningún proceso incluido en SQL Server eche en falta memoria. Estos criterios corresponden a los contadores de Página/s y Bytes disponibles del objeto Memoria.

El contador Memoria: Página/s indica el número de páginas de memoria que son extraídas o bien escritas en disco por razones de paginación. Un valor elevado puede indicar una falta de memoria física.

El contador Memoria permite asegurarse de que la actividad del disco no está asociada al proceso de paginación de la memoria.

Vigilancia de la memoria del sistema

Para averiguar la cantidad de memoria utilizada por SQL Server, es necesario vigilar los contadores siguientes en el Monitor de rendimiento:

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.

Desde SQL Server Management Studio, esta opción es configurable en la ventana de las propiedades del servidor, en la página Connections, activando la casilla Use query governor to prevent long-running queries y precisando el coste máximo...

El plan de ejecución de una consulta

SQL Server analiza las consultas, los procedimientos y los triggers. El optimizador de la consulta va a almacenar el plan de ejecución en la memoria de SQL Server, más concretamente en la zona de memoria llamada memoria caché del plan. Es posible analizar esta versión compilada de la consulta para comprender mejor las selecciones del optimizador de la consulta y reaccionar para permitir una ejecución más rápida de la consulta. Esto puede traducirse en una reescritura de la consulta, la adición de un índice, la actualización de las estadísticas...

La optimización de las consultas no es el único punto que hay que tener en cuenta para resolver los problemas de rendimiento, pero sí es un aspecto importante. Efectivamente, centrarse en los problemas de memoria cuando la consulta está mal escrita puede ocultar momentáneamente los malos tiempos de respuesta, pero el problema aparecerá de nuevo cuando aumente el volumen de datos.

Para visualizar el plan de ejecución en SQL Server Management Studio, hay dos opciones:

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 como se puede ver a continuación.

El ejemplo anterior muestra que el almacén de consultas de la base de datos GESCOM dispone de un espacio de 100MB (que es el tamaño por defecto). Este espacio de almacenamiento se realiza en el grupo de archivos Primary.

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.

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 Query Store de las Database Properties y allí...

Plan de mantenimiento

Para las operaciones clásicas del administrador, tales como las copias de seguridad regulares de la base de datos y de los diarios de transacciones o las operaciones de mantenimiento de los índices, es posible definir planes de mantenimiento. La ejecución de estas diferentes tareas se planifica durante la definición del plan de mantenimiento.

Los planes de mantenimiento se pueden definir con un asistente, pero la creación manual de un plan de mantenimiento ofrece más opciones de configuración. Por lo tanto, esta última opción es más interesante.

Los planes de mantenimiento se definen como paquete SIS y es SQL Server Agent quien se encarga de ejecutar el trabajo que lanza este paquete. Los planes de mantenimiento solo se pueden definir en las bases de datos que ofrecen un nivel de compatibilidad superior o igual a 80.

La definición de un nuevo plan de mantenimiento se puede establecer fácilmente llamando al asistente de definición de un nuevo plan. Este asistente se puede ejecutar desde el menú contextual asociado al nodo Management - Maintenance Plans, desde el explorador de objetos.

El resultado de la ejecución de las diferentes tareas relativas al plan de mantenimiento se puede escribir en un archivo...

El asistente de configuración del motor de base de datos

El asistente de configuración del motor de base de datos tiene como objetivo ofrecer la estructura física óptima enfrentando la organización actual con una carga de trabajo.

La carga de trabajo corresponde a una traza capturada con anterioridad por el generador de perfil de SQL Server o a un script Transact SQL.

A partir de esta carga de trabajo, la herramienta va a ofrecer una reorganización del esquema lógico añadiendo índices adicionales, particionando algunas tablas o bien proponiendo además la creación de vistas indizadas. Las propuestas hechas por el asistente tienen como objetivo reducir el coste estimado por el optimizador de consultas para la carga de trabajo analizada.

Durante el análisis de una carga de trabajo, es necesario configurar tres elementos:

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 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.

La información...

Los triggers de conexión

Los triggers de conexión permiten ejecutar una o varias instrucciones Transact SQL durante la conexión de un usuario al servidor. Este tipo de trigger puede considerarse un trigger DDL especial. Se ejecuta después de la validación de la autenticación del usuario en el servidor y antes de que el servidor permita el acceso al usuario. Por lo tanto, la conexión de usuario se establece y se abre cuando se ejecuta el trigger. Dado que la ejecución tiene lugar antes de que el usuario pueda operar directamente sobre la consola, los mensajes que pueden producirse (error, impresión) se redirigen directamente al registro de errores de SQL Server.

Los triggers de conexión pueden utilizarse para seguir o limitar las conexiones de usuario. Por ejemplo, a este nivel es posible limitar el número de sesiones abiertas simultáneamente por un mismo usuario. También es posible a este nivel registrar en una tabla la fecha y hora de establecimiento de la conexión. En todos los casos, la función EVENTDATA permite obtener toda la información relativa a la conexión que acaba de establecerse.

Es posible definir varios triggers de conexión. En este caso, el procedimiento almacenado sp_settriggerorder permite definir qué trigger se ejecutará primero y cuál el último. No es posible fijar el orden de ejecución de los otros...

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 PowerShell, que integra todas las bibliotecas necesarias para definir scripts de administración eficaces, fiables y condensados. 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 dos applets Invoke-Sqlcmd y Invoke-PolicyEvaluation.

Por ejemplo, para obtener la ayuda del proveedor SQL Server, es necesario ejecutar la instrucción Get-Help SQLServer. De manera más general, para obtener la ayuda del conjunto de proveedores se usa Get-Help -category provider.

Sobre los applets de comando, es posible utilizar las opciones -Full, -Parameters * y -Examples para visualizar diferentes niveles de ayuda.

Ejemplo

La herramienta sqlps ejecuta la consola PowerShell en modo restringido por defecto...

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...

Creación de copia en espejo