Biblioteca Online : ¡La Suscripción ENI por 9,90 € el primer mes!, con el código PRIMER9. Pulse aquí
¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
  1. Libros
  2. SQL Server 2016
  3. Herramientas adicionales
Extrait - SQL Server 2016 Aprender a administrar una base de datos transaccional con SQL Server Management Studio
Extractos del libro
SQL Server 2016 Aprender a administrar una base de datos transaccional con SQL Server Management Studio Volver a la página de compra del libro

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:

  • Los objetivos que se persiguen.

  • La herramienta más apropiada. Las más flexibles son SQL Profiler y el Analizador de rendimiento de Windows.

  • Si es necesario monitorizar SQL Server o su entorno para conseguir los objetivos.

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.

Aunque...

La creación de sesiones

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

images/103-sessionsactivity.png

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

images/103-sessionsactivity2.png

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

images/103-sessionsactivity3.png

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

Iniciar una sesión

Para capturar la actividad, es necesario iniciar las sesiones.

Las sesiones están activas mientras no se detengan ni se alcance el límite del espacio de almacenamiento.

images/104-sessionstart.png

Analizar la información

Mientras una sesión este activa y contenga información, es posible analizar esta información. Esta operación se realiza también desde SQL Server Management Studio. Para ello debemos ejecutar la herramienta de vigilancia de datos activos. Esta herramienta está accesible desde el menú contextual asociado a la sesión.

images/105-sessionlivedata.png

Se muestra la pantalla de presentación de datos y podemos visualizar el detalle de la información.

images/105-sessionlivedata2.png

También es posible analizar los datos cuando la sesión ha dejado de estar activa. Este tipo de visualización es muy parecido al propuesto en el modo en vivo, aunque la forma de acceder es diferente ya que debemos solicitar en esta ocasión View Target Data.

images/105-sessionlivedata3.png

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.

images/106-perfmonitor.png

Los principales objetos específicos de SQL Server son:

  • Agente de replicación: vigila a los agentes de replicación en curso de ejecución.

  • Base de datos: vigila la utilización de la base de datos, como la cantidad de espacio de registro disponible y el número de transacciones activas.

  • Instantáneas: vigila las instantáneas de las replicaciones.

  • Distribución de replicación: vigila el número de comandos y transacciones leídas a partir de la base de datos de distribución.

  • Fusión de replicación: vigila la ejecución de cada fusión que desplaza las modificaciones de datos, del suscriptor hacia el editor o bien a la inversa.

  • Administrador de caché: permite vigilar la manera en la que SQL Server utiliza la memoria para almacenar objetos (procedimientos almacenados...).

  • Administrador de la memoria intermedia: permite vigilar la manera en la que SQL Server utiliza la memoria para almacenar páginas de datos.

  • Administrador de memoria: vigila la utilización global de la memoria.

  • Lector...

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.

images/107-memoptim.png

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:

  • Proceso\Franja de trabajo.

  • SQL Server: Administrador de la memoria intermedia\Tasa de acceso al caché de memoria. Indica un porcentaje que representa el número de veces que las páginas de datos solicitadas se encuentran...

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.

images/108-limitressources.png

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.

images/108-limitressources2.png

La instrucción RECONFIGURE permite tener en cuenta los nuevos valores de las opciones de configuración sin tener que reiniciar el 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.

No es posible visualizar el plan de ejecución de un trigger o de un procedimiento almacenado.

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

  • Mostrar el plan de ejecución estimado. El script Transact SQL no se ejecuta y el plan de ejecución mostrado procede del análisis de la consulta por el optimizador de la consulta.

  • Incluir...

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.

images/1010-querystorage.png

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.

images/1010-querystorage2.png

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 herramienta de línea de comandos sqlmaint se mantiene por razones de compatibilidad con versiones anteriores, pero se recomienda no usarla.

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.

images/1011-maintenanceplan.png

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.

Es posible solicitar la ejecución de esta herramienta en línea de comandos con dta.exe.

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:

  • Nombrar de manera única el análisis.

  • Hacer referencia a un archivo o una tabla que contenga una carga de trabajo.

  • Seleccionar la base o las bases que van a ser utilizadas por este análisis.

1. Inicialización del asistente de configuración

Toda la información relativa a la configuración va a ser almacenada en la base msdb. Para ello, durante la primera ejecución del asistente de configuración, se define una estructura...

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.

No es posible definir triggers DDL de tipo instead of.

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 integración de un proveedor de acceso para poder navegar en la arborescencia del servidor de la misma manera que se hace en un sistema de archivos, es decir, principalmente con las instrucciones cd y dir. Este proveedor permite conectarse a instancias de SQL Server 2016, 2014, 2012 o 2008.

  • La adición de applets de comando con el objetivo de poder integrar y ejecutar una acción SQL, fundamentalmente los scripts Transact SQL. Todos estos applets se apoyan en la tecnología SMO. De esta manera, PowerShell solo considera las instrucciones que toman en cuenta SMO. Estos applets de comando también se denominan cmdlet. Como para las otras instrucciones de PowerShell, no son sensibles a mayúsculas/minúsculas. Sin embargo, respetar las reglas de nomenclatura referentes a mayúsculas/minúsculas permite facilitar la lectura de las instrucciones.

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

images/1015-sqlps.png

Cuando arranca sqlps, se muestra un mensaje referente a la versión de SQL Server. Estas tres líneas de mensajes se pueden eliminar ejecutando sqlsp -nologo.

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:

  • La definición y gestión de las reglas.

  • El modo de ejecución de las reglas.

  • La administración directa.

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

1. Principios de funcionamiento

La copia en espejo de las bases de datos permite garantizar una solución de alta disponibilidad conservando una inversión material razonable. En este tipo de alta disponibilidad, una base se copia en espejo con otra instancia de SQL Server. La base de datos inicial tiene el calificativo de principal, mientras que la base de datos de destino se llama espejo.

El papel desempeñado por cada una de las bases de datos es específico de cada relación de copia en espejo. El simple hecho de que una instancia contenga bases de datos que desempeñen el papel de principal en una copia en espejo no significa que no pueda contener bases de datos espejo en otras relaciones de copia en espejo.

Esta solución de alta disponibilidad no permite obviar las operaciones de copia de seguridad. Es preferible tener una copia en espejo como solución complementaria para garantizar la mejor disponibilidad posible de los datos.

La copia en espejo de las bases se efectúa mediante el registro de transacciones. Todas las transacciones efectuadas en la base de datos principal se recogen en el registro de transacciones. Esta información se registra inicialmente en memoria y luego se registra lo antes posible en disco. Es en el momento de esta escritura en disco cuando la información se envía a sus espejos. La base espejo registra esta información en disco. Las transacciones se recrean posteriormente. Las dos bases (principal y espejo) contienen, de esta manera, la misma información.

La copia en espejo puede utilizarse también para garantizar una sólida protección de los datos frente a un funcionamiento incorrecto del servidor principal. La diferencia entre alta protección y alta disponibilidad es que, en el primer caso, será una operación manual del administrador la que permitirá promover la base de destino como base principal...