SQL Server

26
Jul
2024

Diferencias Instancia, BBDD y Esquema en SQL Server

Cuando hablamos de bases de datos, términos como instancia, base de datos (BBDD) y esquema son fundamentales, pero no en todos los sistemas tienen los mismos significados, lo que genera confusión entre los desarrolladores y administradores de bases de datos principiantes. En este artículo, voy a explicarte lo que significan en SQL Server así como las diferencias y relaciones entre estos conceptos tan clave que son el pilar de nuestro RDBMS. 

Cuando trabajamos con SQL Server o Azure SQL, es crucial entender cómo están organizados los datos y las estructuras que los contienen. A menudo, los términos «instancia», «base de datos» y «esquema» se utilizan indistintamente, pero cada uno tiene un significado específico y una función distinta. Veamoslo.

¿Qué es una Instancia en SQL Server?

Una instancia de SQL Server es una “copia” del motor de base de datos que se ejecuta como un servicio del sistema operativo. Es la aplicación del servidor SQL instalada en nuestro equipo y podemos tener una o varias instaladas y ejecutándose a la vez. Cada instancia puede contener varias bases de datos y tiene su propia gestión de la memoria, los procesos y su propia configuración.

Tipos de Instancias

Existen dos tipos principales de instancias en SQL Server:

  • Instancia predeterminada: Por defecto es la primera instancia instalada en el servidor y no tiene nombre específico. Se accede a ella utilizando sólo el nombre del servidor. 
  • Instancia con nombre: Se pueden instalar múltiples instancias con nombre en un mismo servidor, y se accede a ellas utilizando el formato Nombre_Servidor\Nombre_Instancia.

Esto no es siempre así, puedes poner un nombre a todas tus instancias o instalar primero una instancia con nombre y después la predeterminada. Lo que sí es importante es que solo puede haber una instancia predeterminada en el servidor y eso se configura al momento de la instalación.

Ventajas y desventajas de Múltiples Instancias

Las múltiples instancias tienen ciertas ventajas, por ejemplo, permiten un mayor aislamiento ya que se pueden separar las cargas de trabajo críticas en diferentes instancias. Esto nos permite una configuración independiente donde cada instancia puede tener su propia configuración de seguridad, memoria y opciones de rendimiento. Además es más sencillo gestionar actualizaciones y parches sin afectar a todas las bases de datos.

Esto no quiere decir que tengamos que tener un solo servidor de SQL con todas las instancias, aunque es una opción válida, todo lo que hemos visto como ventajas es aún mayor cuando tenemos servidores independientes cada uno con una instancia, aunque sean servidores virtuales. Aunque esta solución tiene un mayor coste, a mi es la que más me gusta.

Base de Datos (BBDD)

Una base de datos (BBDD) es una colección organizada de datos que se puede acceder, gestionar y actualizar fácilmente. En SQL Server, cada base de datos es una entidad autónoma que contiene sus propios conjuntos de archivos.Cada base de datos en SQL Server consta de tres tipos principales de archivos: Los archivos de datos primarios (.mdf) que contienen los datos de la base de datos y su estructura, los archivos de datos secundarios (.ndf) utilizados para distribuir datos en diferentes discos y mejorar el rendimiento y los archivos de registro o log de transacciones (.ldf) que almacenan todas las transacciones y cambios a la base de datos, cruciales para la recuperación ante fallos.

Esquemas en SQL Server

Un esquema es un contenedor lógico dentro de una base de datos que agrupa objetos como tablas, vistas, procedimientos almacenados, etc. Ayuda a organizar los objetos y facilita la gestión de permisos y la segmentación de datos.

Los esquemas son extremadamente útiles para la segregación de datos permitiéndonos separar datos y objetos por departamentos o aplicaciones y mejorando así la gestión de la seguridad al aplicar permisos a nivel de esquema en lugar de a nivel de objeto. También son un gran aliado para la organización ya que nos va a permitir mantener un orden lógico y estructurado dentro de una base de datos.

Diferencias Clave entre Instancia, BBDD y Esquema en SQL Server

Para recapitular, y a modo resumen, vamos a destacar las diferencias esenciales entre estos tres conceptos:

  • Instancia: Es el entorno de ejecución del motor de base de datos que puede contener múltiples bases de datos. Proporciona aislamiento y configuración independiente.
  • Base de Datos (BBDD): Es una colección autónoma de datos y objetos dentro de una instancia. Cada base de datos tiene su propio conjunto de archivos de datos y registro.
  • Esquema: Es un contenedor lógico dentro de una base de datos que organiza y agrupa objetos. Facilita la gestión de permisos y la segregación de datos.

Conclusión

Entender las diferencias y relaciones entre instancia, base de datos y esquema es fundamental para cualquier profesional que trabaje con SQL Server o Azure SQL. Desde la gestión de múltiples instancias hasta la organización de datos en esquemas, cada componente tiene su lugar y función específica. Al aprovechar estas herramientas de manera efectiva, podemos garantizar que nuestras bases de datos sean robustas, seguras y escalables, tanto en entornos locales como en la nube. 

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

No te vayas aun. Hemos creado una página donde estamos recopilando todos estos artículos que dan respuesta a estas preguntas frecuentes de SQL Server. Pásate por aquí a echar un vistazo.

Publicado por Roberto Carrancio en Cloud, SQL Server, 0 comentarios
24
Jul
2024

Auditoría en SQL Server: ¿Cómo configurarlas?

En nuestro anterior post hablamos sobre SQL Server Profiler y sus trazas y comentábamos que uno de sus usos puede ser el de auditoría ya que registra todos los eventos sobre nuestros servidores SQL Server. Sin embargo su elevado consumo de recursos no lo hace la solución más ideal para esta función. Y es que, SQL Server implementa una auditoría nativa mucho más potente con menor coste de recursos que la hacen la herramienta ideal. Configurar auditorías en SQL Server no solo nos va a ayudar a supervisar la actividad y los cambios, sino que también es un proceso clave en nuestra estrategia de protección de datos y cumplimiento normativo. En este artículo, voy a tratar de explicarte cómo puedes configurar eficazmente estas auditorías, optimizando cada aspecto para obtener el máximo beneficio.

¿Qué es la Auditoría de SQL Server?

La auditoría de SQL Server es un mecanismo que permite registrar y rastrear actividades y eventos dentro de nuestras instancias de SQL Server. La auditoría puede ser configurada para capturar una variedad de eventos, desde inicios de sesión hasta cambios en la configuración del servidor. Con estos registros, podemos realizar un seguimiento detallado de quién hizo qué y cuándo, lo que es vital para la seguridad y el cumplimiento normativo.

Tipos de Auditoría de SQL Server

SQL Server proporciona dos enfoques principales para la auditoría: la auditoría de instancia y la auditoría de base de datos. Cada uno tiene sus características específicas y se aplica en diferentes contextos según los requisitos de seguridad y cumplimiento por lo que no son excluyentes.

La auditoría de instancia se enfoca en eventos que afectan a toda la instancia de SQL Server, no solo a bases de datos individuales. Es ideal para capturar eventos que tienen un impacto global en el servidor y para mantener una vista general sobre la actividad de toda la instancia. Su uso principal es registrar el cumplimiento de políticas de seguridad como la gestión de accesos y el control de cambios en la configuración del servidor o de la instancia. Para ello, captura eventos que afectan a toda la instancia, como inicios de sesión, cambios en la configuración del servidor, y operaciones de mantenimiento. También permite registrar eventos de alto nivel que impactan el funcionamiento de la instancia.

La auditoría de base de datos se centra en eventos que ocurren dentro de una base de datos específica. Es ideal para capturar eventos relacionados con las operaciones de datos y la estructura de la base de datos, proporcionando un nivel de detalle más granular. Como hemos dicho, captura eventos a nivel de base de datos, como modificaciones en los datos, cambios en los objetos, y accesos a datos y nos permite definir qué operaciones se auditan en tablas, esquemas o procedimientos almacenados específicos. Se usa principalmente para registrar operaciones como inserciones, actualizaciones y eliminaciones, es decir, control de cambios y para monitorizar y registrar el acceso a datos sensibles o críticos dentro de una base de datos.

Auditoría de Instancia en SQL Server

Ya hemos visto que la auditoría a nivel de instancia nos permite capturar eventos que afectan a toda la instancia de SQL Server, independientemente de las bases de datos individuales. Este enfoque es útil para registrar eventos que ocurren a nivel de servidor, como cambios en la configuración del servidor o inicios de sesión.

Pasos para Configurar una Auditoría de Instancia

Primero, debemos definir una auditoría que especificará qué eventos se registrarán y cómo se almacenarán los resultados. Esto se hace mediante el SQL Server Management Studio (SSMS) o mediante Transact-SQL (T-SQL).

Usando SSMS:

En SSMS, navegamos a Seguridad > Auditorías.

Hacemos clic derecho en Auditorías y seleccionamos Nueva Auditoría.

En la ventana de propiedades, configuramos la ubicación del archivo de auditoría, que puede ser un archivo de registro, un archivo de eventos o un registro de la aplicación.

Establecemos las opciones necesarias, como el tamaño máximo del archivo y la política de retención.

Auditoria-1

Usando T-SQL:

USE [master]
GO
CREATE SERVER AUDIT [Audit-20240720-104102]
TO FILE 
( FILEPATH = N'C:\AuditLogs\'
 ,MAXSIZE = 1 GB
 ,MAX_FILES = 10
 ,RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)
GO

Una vez creada la auditoría, debemos definir qué eventos específicos deseamos capturar. Esto se hace mediante la creación de especificaciones de auditoría.

Usando SSMS:

Navegamos a Seguridad > Especificaciones de Auditoría.

Hacemos clic derecho en Especificaciones de Auditoría y seleccionamos Nueva Especificación de Auditoría.

Seleccionamos la auditoría previamente creada y definimos los eventos que deseamos capturar, como Inicios de sesión o Cambios en la configuración.

Auditoria-2

Usando T-SQL:

USE [master]
GO
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20240720-104510]
FOR SERVER AUDIT [Audit-20240720-104102]
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (FAILED_LOGIN_GROUP)
GO

Finalmente, habilitamos tanto la auditoría como las especificaciones para comenzar a capturar los eventos configurados desde SSMS con clic derecho del ratón sobre los objetos y habilitar.

Usando T-SQL:

ALTER SERVER AUDIT [Audit-20240720-104102] WITH (STATE = ON)
GO
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20240720-104510] WITH (STATE = ON)

Auditoría de Base de Datos en SQL Server

A diferencia de la anterior, la auditoría a nivel de base de datos se centra en registrar eventos que ocurren dentro de una base de datos específica. Este nivel de detalle es fundamental para monitorear actividades relacionadas con el contenido y los objetos de la base de datos.

Pasos para Configurar una Auditoría de Base de Datos

Al igual que con la auditoría a nivel de instancia, primero debemos crear una auditoría que especifique dónde se almacenarán los registros. Esta auditoría se crea a nivel de instancia y podemos usar la misma que teníamos antes, en la base de datos solo vamos a crear las especificaciones.

Usando SSMS:

Navegamos a la base de datos en Seguridad > Especificaciones de Auditoría.

Hacemos clic derecho en Especificaciones de Auditoría y seleccionamos Nueva Especificación de Auditoría.

Configuramos los eventos específicos, como operaciones de datos o cambios en los objetos.

Auditoria-3

Usando T-SQL:

USE [AdventureWorks2022]
GO
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20240720-105338]
FOR SERVER AUDIT [Audit-20240720-104102]
ADD (UPDATE ON DATABASE::[AdventureWorks2022] BY [public]),
ADD (INSERT ON DATABASE::[AdventureWorks2022] BY [public]),
ADD (DELETE ON DATABASE::[AdventureWorks2022] BY [public])
GO

Igual que antes, activamos la auditoría y las especificaciones para comenzar a registrar los eventos.

Usando T-SQL:

ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20240720-105338] WITH (STATE = ON);

Auditoría vs SQL Server Profiler

Como vimos en el pasado post, SQL Server Profiler es otra herramienta que, aunque no se usa para auditorías a largo plazo, sigue siendo relevante para capturar eventos en tiempo real y para el análisis detallado de sesiones y transacciones. Vamos a comparar esta herramienta con las auditorías de SQL Server en términos de capacidades y usos.

Alcance y propósito

SQL Server Profiler captura eventos en tiempo real para el análisis detallado de sesiones y transacciones. Es ideal para depurar problemas y monitorear el rendimiento en tiempo real ya que proporciona una vista instantánea de la actividad de la base de datos. Por el contrario, las auditorías de SQL Server registran eventos a lo largo del tiempo, permitiendo un seguimiento extensivo y cumplimiento normativo. Son más adecuadas para el cumplimiento de regulaciones y para proporcionar informes detallados sobre eventos históricos ya que capturan eventos críticos y permiten su almacenamiento en archivos o registros para un análisis posterior.

Persistencia

Los datos capturados por Profiler son temporales y se almacenan en memoria mientras se realiza el seguimiento. Aunque se puede salvar un archivo de traza, este no está diseñado para almacenamiento a largo plazo o para el cumplimiento normativo. Las auditorías de SQL Server sin embargo si almacenan los eventos en archivos o registros, lo que permite un almacenamiento prolongado y una revisión a largo plazo. Además facilitan la conservación de datos históricos necesarios para el cumplimiento regulatorio.

Rendimiento

Como ya vimos también, SQL Server Profiler puede impactar el rendimiento del servidor durante la captura de eventos debido a la sobrecarga de recursos. lo que no lo hace la herramienta ideal para sesiones largas y poco específicas donde el rendimiento es crítico. En este sentido, las auditorías de SQL Server tienen un menor impacto en el rendimiento, especialmente cuando se configuran para capturar solo eventos esenciales. Además nos permiten ajustar la granularidad de la auditoría para minimizar la sobrecarga.

Usabilidad

SQL Server Profiler nos ofrece una interfaz gráfica para configurar y visualizar eventos en tiempo real pero requiere de una comprensión avanzada para interpretar los eventos capturados. Las auditorías de SQL Server que configuramos a través de SSMS o T-SQL, proporcionan una forma estructurada y más amigable de registrar eventos para que puedan ser consumidos por auditores y técnicos de ciberseguridad. Las especificaciones de auditoría permiten un control preciso sobre qué eventos se registran y cómo se almacenan.

Conclusión

Configurar auditorías en SQL Server, tanto a nivel de instancia como de base de datos, es fundamental para mantener un control exhaustivo sobre nuestras bases de datos y garantizar la seguridad y el cumplimiento. A través de la correcta configuración de auditorías y especificaciones, podemos registrar eventos críticos y analizar el acceso y las modificaciones a nuestros datos. Aunque el proceso puede parecer complejo, seguir estos pasos nos permite implementar una estrategia de auditoría efectiva que proporciona una visión detallada y precisa de la actividad en nuestras instancias y bases de datos. Al final, una auditoría bien configurada es una herramienta poderosa que fortalece nuestra postura de seguridad y facilita el cumplimiento normativo.

  Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta 

Publicado por Roberto Carrancio en Cloud, SQL Server, 0 comentarios
22
Jul
2024

SQL Server Profiler

Hoy os quiero hablar de SQL Server Profiler. Aunque su soporte está discontinuado y en un futuro será eliminado de las características de SQL Server en favor de otras herramientas como xEvents, sigue siendo una de las herramientas más poderosas y versátiles a nuestra disposición cuando estamos depurando un problema con consultas SQL.

Durante las próximas líneas, veremos en profundidad el uso del SQL Server Profiler, sus funcionalidades avanzadas, su impacto en los recursos del sistema y cómo puede ayudarnos a mantener nuestros sistemas SQL Server funcionando de manera eficiente.

¿Qué es el SQL Server Profiler?

El SQL Server Profiler es una herramienta nativa de SQL Server que nos permite crear trazas para capturar y analizar eventos que ocurren principalmente en instancias de SQL Server. Aunque su uso principal es para capturar eventos de SQL Server, también es posible conectarlo a una instancia de SQL Server Analysis Services y, por tanto, a un modelo de Power BI. Los eventos que vamos a poder capturar pueden ser consultas T-SQL, procedimientos almacenados, transacciones y mucho más, dependerá de a qué tipo de servidor estemos conectados. Como habrás imaginado ya, poder monitorizar y registrar estos eventos es crucial para ayudarnos en la resolución de problemas, la optimización del rendimiento y la auditoría de actividades en nuestras bases de datos.

Principales Características del SQL Server Profiler

SQL Server Profiler, como hemos visto, nos puede ser de gran utilidad para descubrir qué es lo que está pasando en nuestro SQL Server. Esto lo hace una herramienta ideal para cualquier DBA que persiga un problema de rendimiento o para un desarrollador que necesite depurar una aplicación. Para ello, podemos destacar estas características principales de la herramienta:

  • Monitorización en Tiempo Real y más: SQL Server Profiler nos permite observar la actividad sobre la base de datos a medida que ocurre, lo cual es esencial para identificar y solucionar problemas de rendimiento de manera inmediata. Pero no solo eso, también nos permite guardar esos archivos para analizarlos en detalle más adelante.
  • Filtrado de Eventos: Como el resultado de registrar toda la actividad puede ser un fichero tan grande que sea casi imposible de consumir, SQL Server Profiler implementa una serie de filtros específicos para centrarnos en eventos particulares, evitando así la sobrecarga de información y facilitando la identificación de problemas concretos.
  • Reproducción de Trazas: Como ya os comentaba antes, podemos guardar los archivos de trazas pero, no solo para su visualización. SQL Server Profiler nos da la capacidad de abrir esas trazas conectados en un entorno de prueba, por ejemplo, y reproducir paso a paso, todos los eventos de la traza. Esto es increíblemente útil para el análisis de problemas y depuración de código sin afectar el sistema de producción.

Configuración y Uso del SQL Server Profiler

Cuando queremos usar SQL Server Profiler primero debemos iniciar una nueva sesión de traza. Al hacerlo, se nos presentará una serie de opciones de configuración que nos permitirán personalizar la traza según nuestras necesidades. Veamos los pasos.

Pasos Básicos para Iniciar una Nueva Trazabilidad

  1. Abrir SQL Server Profiler: Lo encontramos en el menú de herramientas de SQL Server Management Studio (SSMS) o como aplicación instalada en tu sistema operativo.
  2. Crear Nueva Traza: Seleccionamos «File» > «New Trace» y nos conectamos a la instancia que queremos monitorizar. Recuerda que puede ser SQL Server (on premise o Azure), SSAS o un modelo de Power BI.
  3. Seleccionar Plantilla: Podremos elegir una de las plantillas predefinidas, si es que se ajustan a nuestras necesidades, o configurar una nueva.
  4. Configurar Eventos y Columnas: En esta pestaña podremos personalizar los eventos y columnas que queremos capturar y mostrar. Abajo a la derecha encontrarás unos checks para mostrar todos los eventos y columnas, recuerda activarlos para ver todas las opciones disponibles.
  5. Aplicar Filtros: Podremos configurar filtros específicos para reducir la cantidad de datos capturados y enfocarnos en eventos relevantes. Es interesante por ejemplo el filtro por base de datos o por usuario si es que tenemos claro donde está más o menos localizado el problema.
  6. Iniciar la Traza: Una vez configurada, iniciamos la traza y comenzamos a monitorizar los eventos en tiempo real.
  7. Una vez con la traza iniciada la podremos pausar y reanudar a nuestro gusto. También detener definitivamente la captura de eventos. Una vez finalizado podremos guardar el fichero para analizarlo o reproducirlo más tarde.

Análisis de Datos Capturados

Una vez que hemos capturado una traza, el siguiente paso es analizar los datos para identificar posibles problemas de rendimiento o errores en la aplicación.

Uno de los usos más comunes del SQL Server Profiler es identificar consultas lentas que pueden estar afectando el rendimiento de la base de datos. Podemos buscar eventos específicos como «SQL» o «RPC» y revisar las métricas de tiempo de ejecución, CPU y lecturas/escrituras de disco para cada consulta.

Los bloqueos y deadlocks también pueden ser un gran problema que queramos analizar, sobre todo en sistemas con alta concurrencia (y sin un nivel de aislamiento Read Committed Snapshot). Utilizando eventos como «Lock» y «Lock Chain», podemos identificar las transacciones involucradas en deadlocks y tomar medidas para resolverlos, ya sea optimizando las consultas o ajustando la configuración de bloqueo.

Impacto de SQL Server Profiler en el rendimiento

Como puedes imaginar todo esto tiene una gran contrapartida y es su impacto en el rendimiento. El SQL Server Profiler puede generar una cantidad significativa de datos, especialmente en sistemas con alta actividad. Cada evento capturado se guarda en un archivo de traza, lo que puede resultar en un uso considerable del espacio en disco. Si no tenemos cuidado podemos llegar a llenar el disco. 

Para evitar este problema de consumo excesivo de disco debemos tomar precauciones y aplicar filtros para capturar solo los eventos necesarios, de esta manera reduciremos la cantidad de datos registrados. También es importante configurar límites de tamaño para los archivos de traza y habilitar la opción de sobrescribir los archivos más antiguos. Por último debemos asegurarnos de que las trazas se almacenan en volúmenes con suficiente capacidad para evitar problemas de espacio en disco.

Otro de los problemas principales que podemos encontrar durante el proceso de captura y registro de eventos por parte del SQL Server Profiler es que puede afectar el rendimiento del servidor. El impacto varía según la cantidad de eventos capturados y la configuración de la traza. A mayor frecuencia de eventos capturados, mayor será el impacto en el rendimiento. Capturar una gran cantidad de eventos y columnas innecesarias aumenta la carga en el sistema, como es obvio.

Trata de acotar tus trazas en el tiempo y de capturar los eventos mínimos imprescindibles para evitar saturar la CPU y RAM de tu servidor, utilizar plantillas predefinidas optimizadas para tareas específicas puede ayudarte en este sentido. Es vital en este sentido programar la captura de trazas durante periodos de baja actividad del sistema o solo durante el tiempo que tengas localizado que se produce el error que tratas de depurar en lugar de mantenerlas activas por tiempo prolongado.

Casos de Uso Avanzados del SQL Server Profiler

Como has podido ver en lo que llevamos de artículo, SQL Server Profiler es una herramienta muy útil para depurar código o localizar problemas de rendimiento. Pero, no solo nos va a servir para eso, a continuación os presento otros escenarios en los que nos puede ser de utilidad.

Auditoría de Seguridad

Aunque no es lo ideal, SQL Server Profiler también puede ser utilizado para auditorías de seguridad. Podemos capturar eventos relacionados con el acceso a datos sensibles, cambios en la configuración del servidor y actividades de inicio de sesión. Esto nos permite mantener un registro detallado de las acciones realizadas en la base de datos, lo cual es esencial para cumplir con normativas de seguridad y auditoría. Sin embargo, sus especificaciones y consumo de recursos hacen que no sea la herramienta ideal. En este sentido podremos usar mejor los eventos extendidos o la auditoría nativa de SQL Server.

Optimización de Procedimientos Almacenados

La optimización de procedimientos almacenados es una tarea crítica. Muchas aplicaciones usan estos objetos para sus procesos en lugar de ejecutar código Ad Hoc. Utilizando el Profiler, podemos capturar la ejecución de procedimientos y analizar su rendimiento. Esto nos va a permitir identificar procedimientos que requieren optimización, ya sea mediante la revisión del código T-SQL, la reestructuración de índices y estadísticas o la modificación de la lógica de las consultas.

Diagnóstico de Problemas de Red

En entornos distribuidos, los problemas de red pueden afectar significativamente al rendimiento de SQL Server. El Profiler nos permite capturar eventos relacionados con conexiones y desconexiones, así como latencias en la comunicación. Con esta información, podemos trabajar junto con los administradores de red para resolver problemas de conectividad y mejorar el rendimiento general del sistema.

Uso del SQL Server Profiler para Monitorizar Power BI

Aunque es un proceso no documentado, podemos usar el Profiler para capturar las consultas DAX de Power BI. Yo esto lo he visto funcionando en una charla de Francisco Gutierrez en los Power BI Days de Bilbao y me dejó sorprendido. No esperaba que una herramienta discontinuada por Microsoft tuviera la capacidad de interactuar con Power BI. 

Conclusión

El SQL Server Profiler es una herramienta muy potente para cualquier DBA que busque mantener el rendimiento de sus bases de datos SQL Server. Desde la identificación de consultas lentas hasta la auditoría de actividades y la resolución de problemas de red, el Profiler nos proporciona una visión detallada y en tiempo real del comportamiento de nuestro sistema. Su capacidad para personalizar la captura de eventos y analizar datos detallados lo convierte en una herramienta versátil y poderosa. Sin embargo, es importante ser conscientes del impacto que el Profiler puede tener en los recursos del sistema y aplicar estrategias adecuadas para minimizar su uso de disco y su impacto en el rendimiento. Además, siempre que podamos, deberemos usar los eventos extendidos y familiarizarnos con ellos ya que al SQL Server Profiler no le queda mucho entre nosotros.

 Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

Publicado por Roberto Carrancio en Cloud, Rendimiento, SQL Server, 2 comentarios
19
Jul
2024

DATETRUNC: Una novedad de SQL Server 2022

Aunque ya ha pasado un tiempo desde la llegada de SQL Server 2022 no es raro encontrarse con gente que está empezando a adoptarlo o, con gente que aun llevando tiempo trabajando en esta versión no conoce todas sus novedades. Entre estas nuevas funcionalidades, una de las más destacadas es la función DATETRUNC. Esta nueva función integrada de fecha y hora simplifica enormemente el trabajo con fechas, permitiendo a los desarrolladores y administradores de bases de datos realizar tareas comunes de manera más eficiente y con menos código.

Hasta ahora, manipular fechas en SQL Server requería el uso de múltiples funciones y a menudo resultaba en código complicado y difícil de leer. Aunque si has visto nuestro video sobre el tema ya serás un experto. Con la llegada de DATETRUNC, estas tareas se simplifican significativamente. La función DATETRUNC nos permite truncar una fecha a una parte específica, como año, mes, día, hora, minuto, etc., eliminando la necesidad de combinaciones complejas de funciones de fecha.

¿Qué es DATETRUNC?

La función DATETRUNC recibe como parámetro una fecha que le pasemos y la trunca al nivel de precisión que hayamos especificado. Esto es especialmente útil cuando necesitamos agrupar datos por períodos específicos, como meses o trimestres, o simplemente cuando queremos estandarizar las fechas a un nivel común para comparaciones más precisas.

Sintaxis de DATETRUNC

La sintaxis de la función DATETRUNC es sencilla y directa, solo tendremos que pasar el nivel de precisión al que queremos truncar y un valor tipo fecha o fecha y hora:

DATETRUNC ( datepart, date )
  1. datepart: Especifica la parte de la fecha a la que queremos truncar. Puede ser año, trimestre, mes, día, hora, minuto, segundo, etc.
  2. date: La fecha que queremos truncar.

Ejemplos Prácticos de DATETRUNC

Por si han quedado dudas, y como a caminar se aprende caminando vamos con algunos ejemplos prácticos:

Truncar a Año

Imaginemos que tenemos una fecha y queremos truncarla al inicio del año:

SELECT DATETRUNC(year, '2023-07-15 14:30:00') AS TruncatedDate;

Este código devolverá: 2023-01-01 00:00:00.000. Hemos truncado la fecha al primer día del año 2023.

DATETRUNC-1

Truncar a Mes

Si queremos truncar una fecha al inicio del mes seguiremos la misma lógica:

SELECT DATETRUNC(month, '2023-07-15 14:30:00') AS TruncatedDate;

El resultado será: 2023-07-01 00:00:00.000. La fecha se trunca al primer día del mes de julio de 2023.

DATETRUNC-2

Truncar a Día

Para truncar una fecha al inicio del día:

SELECT DATETRUNC(day, '2023-07-15 14:30:00') AS TruncatedDate;

El resultado será: 2023-07-15 00:00:00.000. En este caso, la hora, los minutos y los segundos se eliminan, dejando solo la fecha.

DATETRUNC-3

Aplicaciones Prácticas en el Mundo Real

Todo esto es muy bonito pero, lo importante es cómo usarlo y cuando. Ahora te voy a contar algunos ejemplos útiles que se me ocurren para DATETRUNC. Seguro que a nuestros amigos de BI que siempre están jugando con dimensiones de fechas se le ocurren un montón más de ejemplos.

Agrupación de Datos

Una de las aplicaciones más comunes de DATETRUNC es en la agrupación de datos. Por ejemplo, podemos querer agrupar ventas por mes:

SELECT
    DATETRUNC(month, OrderDate) AS SaleMonth,
    SUM(SalesAmount) AS TotalSales
FROM FactInternetSales
GROUP BY DATETRUNC(month, OrderDate);

Este ejemplo agrupa las ventas por mes, facilitando la generación de reportes mensuales.

DATETRUNC-4

Comparaciones de Fechas

Otra aplicación que nos va a ser muy útil es en las comparaciones de fechas. Si queremos comparar sólo la parte de la fecha sin considerar la hora, podemos utilizar DATETRUNC:

SELECT *
FROM Events
WHERE DATETRUNC(day, EventDate) = '2023-07-15';

Aquí, estamos comparando solo la parte de la fecha de EventDate con el 15 de julio de 2023, ignorando la hora.

Ventajas de Usar DATETRUNC

Como podrás imaginar, si le he dedicado un tiempo a este artículo es porque creo que DATETRUNC tiene alguna ventaja para ti. Como ya hemos comentado una de sus grandes bazas es la sencillez del código, que simplifica su escritura y comprensión, pero no es solo eso, también vamos a notar mejoras en el rendimiento.

Código Más Limpio y Legible

Antes de la introducción de DATETRUNC, truncar fechas requería el uso de funciones como DATEADD y DATEDIFF en combinaciones complejas. Con DATETRUNC, el código es mucho más limpio y fácil de entender.

-- Sin DATETRUNC
SELECT DATEADD(month, DATEDIFF(month, 0, '2023-07-15 14:30:00'), 0) AS TruncatedDate;
-- Con DATETRUNC
SELECT DATETRUNC(month, '2023-07-15 14:30:00') AS TruncatedDate;

¿Mejor Rendimiento?

La función DATETRUNC, en teoría, está optimizada para el rendimiento, reduciendo la sobrecarga de cómputo comparado con las versiones anteriores que implicaban múltiples ejecuciones de funciónes. Aunque como siempre, sigue siendo mejor no usar funciones.

DATETRUNC-5

En este ejemplo vemos como con la función DATETRUNC el coste de la consulta es un 1% menor pero no se acerca en absoluto a los resultados cuando no usamos funciones de fecha. Es curioso porque Microsoft asegura que ha optimizado el rendimiento pero el comportamiento sigue siendo el mismo. 

El motor de SQL dice: “No tengo ni idea de cuál es la primera fecha que produciría YEAR(2017) y es imposible que pueda adivinarlo por mi mismo. Mejor leeré todas las fechas desde los inicios de los tiempos hasta ahora.” Esto es absurdo, lo sé, pero es así, y es una de las razones por las que siempre se dice que evitemos usar funciones en la cláusula WHERE. Como ves con DATETRUNC en SQL Server 2022 no es diferente.

Conclusión

SQL Server 2022 introdujo innovaciones y mejoras en nuestra capacidad para gestionar y manipular datos de manera eficiente. La función DATETRUNC es una adición bienvenida, simplificando la manipulación de fechas y mejorando la legibilidad y el rendimiento del código. Al empezar a usar esta nueva función, podremos escribir consultas más claras y un poco más eficientes, facilitando nuestro trabajo diario. Sin embargo no es oro todo lo que reluce y, como toda función, debemos evitar su uso en los filtros. 

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

Publicado por Roberto Carrancio en Cloud, Rendimiento, SQL Server, 0 comentarios
17
Jul
2024

Propiedades Extendidas en SQL Server

Hace unos días, en nuestro canal de telegram surgió un tema interesante sobre documentación de tablas y sus columnas en SQL Server. Una de las formas más sencillas para documentar nuestros objetos en SQL Server de forma nativa (sin recurrir a aplicaciones de terceros) es el uso de las propiedades extendidas. Personalmente pienso que, si no tienes una herramienta de documentación externa,  las propiedades extendidas son una alternativa indispensable para cualquier DBA o trabajador de datos. En este artículo, veremos qué son las propiedades extendidas, sus usos principales, y cómo podemos mejorar la gestión y documentación de nuestros entornos de SQL Server.

¿Qué son las Propiedades Extendidas en SQL Server?

En resumidas cuentas, las propiedades extendidas son metadatos adicionales que podemos asociar a objetos dentro de SQL Server. Estas propiedades, presentes en SQL Server desde SQL 2008, son unas etiquetas clave-valor que nos van a permitir almacenar información extra, como descripciones, anotaciones y cualquier dato relevante que consideremos útil para la administración y documentación de nuestra base de datos. No te creas que esto es todo, tú en tu escenario le vas a poder dar cualquier uso que te imagines y que sea compatible con una etiqueta clave-valor.

Usos Comunes de las Propiedades Extendidas

Como ya hemos adelantado, las propiedades extendidas tienen tantos usos como tu creatividad logre imaginar. Os voy a contar los principales con los que yo me he encontrado a lo largo de estos años.

  • Documentación de Objetos: Podemos usar las propiedades extendidas para la documentación de tablas, columnas, procedimientos almacenados, funciones y otros objetos de manera que esta tarea se vuelva más manejable y, sobre todo, la información se almacene en la propia base de datos.
  • Auditoría y Cumplimiento: De la misma manera, se pueden utilizar las propiedades extendidas para almacenar información de auditoría y cumplir con regulaciones específicas como etiquetado de datos confidenciales o sensibles.
  • Metadatos Personalizados: Más de lo mismo, podemos usar propiedades extendidas para agregar metadatos específicos de nuestra organización y que de otra manera no estarían cubiertos por los atributos estándar de SQL Server.

Documentando objetos en SQL Server con Propiedades Extendidas

Como hemos visto, uno de los usos más comunes y útiles de las propiedades extendidas es la documentación de objetos. Esta capacidad no solo nos facilita el mantenimiento, sino que también mejora la colaboración entre equipos de desarrollo y los administradores de las bases de datos. Al tener documentado quien creó una tabla o que proceso o persona se encarga de alimentar los datos, por ejemplo, los DBAs vamos a tener más fácil encontrar a las personas afectadas ante una incidencia. O, si sabemos quien desarrolló ese procedimiento que va lento, vamos a poder tomar medidas correctivas (siempre respetando los derechos humanos, recordad).

Documentación de Tablas y Columnas

Podemos agregar descripciones detalladas a nuestras tablas y columnas para aclarar su propósito y uso. Por ejemplo, podemos describir la función de una columna específica, lo que es particularmente útil en bases de datos grandes y complejas.

Ejemplo Práctico

Supongamos que tenemos una tabla Clientes y queremos documentar su columna EmailAlternativo. Podemos hacerlo con el siguiente script:

EXEC sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'Correo electrónico del cliente para contactar en caso de que el principal devuelva un error', 
    @level0type = N'SCHEMA', @level0name = 'dbo', 
    @level1type = N'TABLE',  @level1name = 'Clientes', 
    @level2type = N'COLUMN', @level2name = 'EmailAlternativo';

Con este script, hemos agregado una propiedad extendida a la columna EmailAlternativo de la tabla Clientes en el esquema dbo, describiendo que almacena el correo electrónico del cliente alternativo. Como siempre pasa, cuanto más descriptivas sean las descripciones más fácil será entenderlas.

Documentación de Vistas, Procedimientos Almacenados y Funciones

Las vistas, procedimientos almacenados y funciones también pueden beneficiarse de las propiedades extendidas. Podemos documentar su propósito, parámetros y cualquier otra información relevante que facilite su uso y mantenimiento.

Ejemplo Práctico

Consideremos un procedimiento almacenado llamado usp_ObtenerClientesActivos. Podemos documentar su propósito con el siguiente script:

EXEC sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'Obtiene una lista de todos los clientes activos en el sistema', 
    @level0type = N'SCHEMA', @level0name = 'dbo', 
    @level1type = N'PROCEDURE', @level1name = 'usp_ObtenerClientesActivos';
EXEC sp_addextendedproperty 
    @name = N'MS_Author', 
    @value = N'Ataulfo Contreras’, 
    @level0type = N'SCHEMA', @level0name = 'dbo', 
    @level1type = N'PROCEDURE', @level1name = 'usp_ObtenerClientesActivos';
En este caso, el script añade una descripción clara y concisa sobre el propósito del procedimiento usp_ObtenerClientesActivos. Después, un segundo script, añade el autor del procedimiento a otra propiedad extendida.

Gestión de Propiedades Extendidas

La forma sencilla de gestionar las propiedades extendidas es a través del entorno gráfico, con un clic derecho sobre el objeto al que deseemos añadir una propiedad extendida podremos entrar en la ventana de propiedades y ahí encontraremos el apartado para las propiedades extendidas. Sin embargo esto no es rápido cuando tienes que ir columna a columna de una tabla documentando sus atributos. Para ello podremos usar los procedimientos almacenados de sistema.

Crear Propiedades Extendidas

Para añadir una propiedad extendida, utilizaremos el procedimiento almacenado sp_addextendedproperty. Este procedimiento nos permite especificar el nombre y el valor de la propiedad, así como el objeto al que se va a asociar.

Modificación de Propiedades Extendidas

Si necesitamos actualizar una propiedad extendida existente, utilizaremos el procedimiento sp_updateextendedproperty. Este procedimiento nos permite cambiar el valor de una propiedad extendida sin necesidad de eliminarla y volver a crearla.

Eliminación de Propiedades Extendidas

Para eliminar una propiedad extendida, utilizaremos el procedimiento sp_dropextendedproperty. Este procedimiento elimina completamente la propiedad especificada del objeto.

Consultar las Propiedades Extendidas existentes

La finalidad de las propiedades extendidas siempre va a ser poder consultarlas, si no para qué íbamos a estar haciendo todo este trabajo, ¿verdad?. En este sentido Microsoft pone a nuestra disposición la función sys.fn_listextendedproperty pero su uso está muy limitado y a mi no me gusta. Existen otras herramientas externas capaces de usar las propiedades extendidas para la generación automática de documentación, como SQL Server Data Tools (SSDT) y otros sistemas de documentación de bases de datos. Estas herramientas pueden extraer las propiedades extendidas y generar documentación completa y detallada de la base de datos. Sin embargo, como el objetivo de este post es no salirnos de SQL y cómo sabéis que yo soy partidario de usar las tablas y vistas de sistema, en este caso no iba a ser menos. Os dejo el script que yo uso:

SELECT
CASE 
   WHEN indexes.name IS NOT NULL AND Child.type_desc = 'USER_TABLE' THEN 'TABLE_INDEX'
   WHEN indexes.name IS NOT NULL AND Child.type_desc = 'VIEW' THEN 'VIEW_INDEX'
   WHEN extended_properties.minor_id <> 0 AND Child.type_desc = 'USER_TABLE' THEN 'TABLE_COLUMN'
   WHEN extended_properties.minor_id <> 0 AND Child.type_desc = 'VIEW' THEN 'VIEW_COLUMN'
   ELSE Child.type_desc 
END AS Object_Type,
extended_properties.name AS Extended_Property_Name,
CAST(extended_properties.value AS NVARCHAR(MAX)) AS Extended_Property_Value,
schemas.name AS Schema_Name,
Child.name AS Object_Name,
Parent.name AS Parent_Object_Name,
columns.name AS Parent_Column_Name,
indexes.name AS Index_Name
FROM sys.extended_properties
INNER JOIN sys.objects Child
   ON extended_properties.major_id = Child.object_id
INNER JOIN sys.schemas
   ON schemas.schema_id = Child.schema_id
LEFT JOIN sys.objects Parent
   ON Parent.object_id = Child.parent_object_id
LEFT JOIN sys.columns
   ON Child.object_id = columns.object_id
   AND extended_properties.minor_id = columns.column_id
   AND extended_properties.class_desc = 'OBJECT_OR_COLUMN'
   AND extended_properties.minor_id <> 0
LEFT JOIN sys.indexes
   ON Child.object_id = indexes.object_id
   AND extended_properties.minor_id = indexes.index_id
   AND extended_properties.class_desc = 'INDEX'
ORDER BY Child.type_desc ASC;

Conclusión

Las propiedades extendidas en SQL Server son una opción muy útil y válida para la documentación de nuestras bases de datos. Nos permiten añadir metadatos personalizados a nuestros objetos, mejorando la claridad, la colaboración y el cumplimiento normativo. Ya sea que estemos documentando tablas, columnas, procedimientos almacenados, funciones o cualquier otro objeto, las propiedades extendidas nos facilitan una gestión más eficiente y eficaz de nuestros datos en SQL Server. Además, la capacidad de automatizar la adición y gestión de propiedades extendidas mediante scripts de SQL amplía aún más sus aplicaciones, permitiéndonos mantener nuestras bases de datos bien documentadas y organizadas sin un esfuerzo manual excesivo. Aprovechando estas características, podemos transformar nuestras bases de datos en repositorios de información clara y útil, accesibles para todos los miembros de nuestro equipo y para las aplicaciones que interactúan con ellas.

En resumen, no solo conocer sino dominar el uso de las propiedades extendidas es una habilidad importante para cualquiera que busque optimizar la gestión y documentación de sus bases de datos en SQL Server. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

Publicado por Roberto Carrancio en Cloud, SQL Server, 0 comentarios
15
Jul
2024

DBA vs IA: Fight! La prueba de si la IA te va a quitar el trabajo

Los avances en inteligencia artificial son imparables, eso está claro. Además está claro que son la tendencia en el mercado, parece que términos como big data, cloud o blockchain son cosa del pasado y ahora lo moderno es tener IA hasta en el inodoro. Todas las empresas compiten por añadir IA a sus productos a la par que apuestan por integrar asistentes como ChatGPT o Copilot a sus procesos para facilitar la vida de los trabajadores. Y ojo que a mi me parece muy bien el avance de la IA pero no debemos olvidarnos de nuestra propia inteligencia natural. Por lo menos hasta que esto avance un poco más y vivamos en un mundo donde trabajen robots con IA y nosotros podamos dedicarnos a la holganza más absoluta. No me enrollo más, que lo que os vengo a contar hoy es muy interesante, una prueba más de cómo está ahora la IA en el ámbito de la administración de base de datos. Que cada uno saque sus conclusiones de si puede o no abandonarse a la vida contemplativa gracias a que una máquina complete sus tareas.

Definiendo la prueba 

Os pongo en situación, me encontraba yo desempeñando mis labores diarias, por las que me pagan no estas tareas del blog que lo hago por amor al arte. En ese momento estaba empezando a tomar el control de una nueva instancia de SQL, en concreto una instancia administrada en Azure. Se trata de una instancia compartida por varios microservicios donde cada uno de ellos tiene su base de datos, en principio, totalmente independiente a las demás. Por lo que he podido ver en el resumen de consumo de recursos del portal web de Azure, el uso de CPU es elevado en esta instancia y, por desgracia, ahí se queda todo lo que puedes ver en esta ventana. Igual que cuando miras el activity monitor de un SQL en local la verdad.

Como es un servidor compartido y hay varias bases de datos debo priorizar por lo que lo lógico es empezar por la que mayor consumo hace de los recursos. Partimos de una gran ventaja, al ser Azure Query Store está habilitado por defecto en todas las bases de datos recolectando datos sin que yo haya tenido que decírselo pero, esta es una solución que muestra datos a nivel base de datos y no de instancia. No me sirve de primeras. Necesito otra solución y me parece una buena oportunidad de poner a prueba a la IA antes de tirar de mi navaja suiza de scripts. Como estamos hablando de Azure vamos a hacer que todo quede en casa y preguntar a Copilot.

Será Copilot un buen DBA, pongámoslo a prueba

Os dejo con lo que estáis todos esperando, la conversación íntegra con Copilot. Luego al final os daré mis conclusiones.

Round 1

Roberto:

Tengo una instancia administrada de sql en azure con una docena de bases de datos. Necesito saber cual de las bases de datos tiene un mayor consumo de recursos. ¿Cómo puedo hacerlo?

Copilot:

Para monitorear el consumo de recursos de las bases de datos en una instancia administrada de SQL en Azure, puedes utilizar el servicio de Azure SQL Analytics en Azure Monitor. Aquí te dejo los pasos generales para hacerlo:
Habilita la supervisión de diagnóstico para tu instancia administrada de SQL Server en Azure. Esto se hace en la sección de “Diagnóstico” en la configuración de tu instancia en el portal de Azure.

  1. Configura la exportación de registros a un espacio de trabajo de Log Analytics. Durante la configuración del diagnóstico, puedes elegir enviar los datos a un espacio de trabajo de Log Analytics existente o crear uno nuevo.
  2. Instala la solución de Azure SQL Analytics desde la galería de soluciones en tu espacio de trabajo de Log Analytics.
  3. Una vez que la solución esté instalada y los datos comiencen a fluir, puedes ver los paneles de Azure SQL Analytics para obtener detalles sobre el consumo de recursos. Hay un panel específico llamado “SQL Managed Instance Resource Usage” que proporciona detalles sobre el uso de recursos a nivel de base de datos.

Recuerda que puede llevar algún tiempo hasta que los datos estén disponibles en los paneles después de habilitar la supervisión de diagnóstico.

Espero que esto te ayude a identificar la base de datos que consume más recursos. Si tienes más preguntas, no dudes en preguntar. 😊

Round 2

Roberto:

¿Hay manera de hacerlo desde las herramientas nativas de SQL en SSMS?

Copilot

Sí, puedes usar las herramientas nativas de SQL Server Management Studio (SSMS) para monitorear el consumo de recursos de las bases de datos en una instancia administrada de SQL en Azure. Aquí te dejo los pasos generales para hacerlo:

  1. Conéctate a tu instancia administrada de SQL Server en Azure usando SSMS.
  2. Abre una nueva consulta en la base de datos que deseas monitorear.
  3. Ejecuta una consulta de DMV (Dynamic Management View) para obtener información sobre el consumo de recursos. Por ejemplo, puedes usar la vista sys.dm_db_resource_stats para obtener estadísticas de uso de recursos a nivel de base de datos. Aquí tienes un ejemplo de cómo podría ser esta consulta:

SELECT 

    database_name, 

    avg_cpu_percent, 

    avg_data_io_percent, 

    avg_log_write_percent

FROM 

    sys.dm_db_resource_stats

ORDER BY 

    avg_cpu_percent DESC;

Esta consulta te dará el porcentaje promedio de CPU, I/O de datos y escritura de log utilizados por cada base de datos, ordenados por el uso de CPU.

Por favor, ten en cuenta que estas vistas de gestión dinámica solo mantienen datos de las últimas 2 horas, por lo que si necesitas un seguimiento a largo plazo, te recomendaría utilizar Azure SQL Analytics como mencioné anteriormente.

Espero que esto te ayude a identificar la base de datos que consume más recursos. Si tienes más preguntas, no dudes en preguntar. 😊

Round 3

Roberto:

Esa DMV no tiene database_name

Copilot:

Tienes razón, lamento el error. La vista de gestión dinámica sys.dm_db_resource_stats no incluye el nombre de la base de datos. En su lugar, puedes usar la vista sys.resource_stats para obtener estadísticas de uso de recursos a nivel de base de datos en una instancia administrada de SQL Server en Azure. Aquí tienes un ejemplo de cómo podría ser esta consulta:

SELECT 

    database_name, 

    avg_cpu_percent, 

    avg_data_io_percent, 

    avg_log_write_percent

FROM 

    sys.resource_stats

ORDER BY 

    avg_cpu_percent DESC;

Esta consulta te dará el porcentaje promedio de CPU, I/O de datos y escritura de log utilizados por cada base de datos, ordenados por el uso de CPU.

Por favor, ten en cuenta que sys.resource_stats solo mantiene datos de las últimas 14 días.

Espero que esto te ayude a identificar la base de datos que consume más recursos. Si tienes más preguntas, no dudes en preguntar. 😊

Round 4

Roberto:

El objeto sys.resource_stats al que haces referencia no existe ni en Azure MI ni en SQL Server

Copilot:

Mis disculpas por el error anterior. En las instancias administradas de Azure SQL, no se dispone directamente de una vista de gestión dinámica (DMV) que proporcione el uso de recursos a nivel de base de datos.

Sin embargo, puedes obtener información sobre el uso de recursos a nivel de instancia utilizando la DMV sys.dm_instance_resource_governor_workloads. Aunque esta DMV no desglosa el uso de recursos por base de datos, puede darte una idea del uso total de recursos de tu instancia administrada.

Si necesitas un desglose del uso de recursos a nivel de base de datos, te recomendaría considerar el uso de Azure SQL Insights, que es una característica de Azure Monitor. SQL Insights proporciona una visión más detallada del rendimiento y el uso de recursos a nivel de base de datos.

Lamento cualquier confusión causada y agradezco tu paciencia. Si tienes más preguntas o necesitas más ayuda, no dudes en preguntar. 😊

Round 5: KO

Roberto:

Yo creo que sí que se puede hacer usando esta consulta, ¿es cierto?

SELECT 

        [Database],

        CONVERT (DECIMAL (6, 3), [CPUTimeInMiliSeconds] * 1.0 / 

        SUM ([CPUTimeInMiliSeconds]) OVER () * 100.0) AS [CPUTimeAsPercentage]

     FROM 

      (SELECT 

          dm_execplanattr.DatabaseID,

          DB_Name(dm_execplanattr.DatabaseID) AS [Database],

          SUM (dm_execquerystats.total_worker_time) AS CPUTimeInMiliSeconds

       FROM sys.dm_exec_query_stats dm_execquerystats

       CROSS APPLY 

        (SELECT 

            CONVERT (INT, value) AS [DatabaseID]

         FROM sys.dm_exec_plan_attributes(dm_execquerystats.plan_handle)

         WHERE attribute = N’dbid’

        ) dm_execplanattr

       GROUP BY dm_execplanattr.DatabaseID

      ) AS CPUPerDb

Copilot:

Sí, tienes razón. La consulta que has proporcionado utiliza las vistas de gestión dinámica sys.dm_exec_query_stats y sys.dm_exec_plan_attributes para obtener el tiempo total de CPU utilizado por cada consulta, agrupado por base de datos.

La parte interna de la consulta suma el tiempo de CPU para cada base de datos. La parte externa de la consulta convierte ese tiempo en un porcentaje del tiempo total de CPU.

Por lo tanto, esta consulta te dará una idea de qué bases de datos están consumiendo más tiempo de CPU en tu instancia administrada de SQL Server en Azure.

Gracias por compartir esta consulta, es un buen ejemplo de cómo se pueden utilizar las vistas de gestión dinámica para obtener información detallada sobre el uso de recursos. 😊

Conclusiones de esta prueba

Tontuna natural 1 – Inteligencia Artificial 0. 

Y digo tontuna natural porque he perdido mi tiempo hablando con una máquina cuando ya tenía la respuesta antes de empezar. Y peor aún, he hecho el intento de decirle como lo tiene que hacer como si estos modelos se alimentasen del feedback de los usuarios. Pero bueno, quedémonos con que por lo menos, la prueba me ha servido para llenar un artículo y entreteneros con esta lectura ligera.

Analizando los resultados lo que saco en claro es que Copilot me ha dado más caritas sonrientes que respuestas correctas y, al menos esta es mi impresión, esta es la tónica habitual en cuanto las preguntas se especializan y requieren de experiencia profesional. Y no solo lo digo yo, hablando con más gente el otro día todos comentaban como en fórmulas DAX por ejemplo, Copilot falla más que acierta cuando pasas de preguntas básicas y le exiges un poco. La IA es un gran aliado en tareas repetitivas o sencillas y yo la uso mucho pero siempre hay que tener claras sus limitaciones y supervisar todo lo que nos dice. No estamos aún en situación de poner en producción código generado por IA sin mirarlo y retocarlo.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

Publicado por Roberto Carrancio en Cloud, SQL Server, 0 comentarios
12
Jul
2024
Nueva actualización crítica SQL Server

Nueva actualización crítica SQL Server

Una versión anterior de este artículo fue publica el pasado mes de abril. El artículo de hoy es muy importante ya que Microsoft ha publicado ayer mismo una actualización de seguridad crítico para SQL Server. Esto me ha llevado a escribir esto y “colarlo” por delante de lo que ya estaba programado en el blog. Vamos a aprovechar esta oportunidad para descubrir todo lo que rodea a las actualizaciones de SQL y que debemos saber y por último ya descubriremos por qué es tan importante esto que os estoy compartiendo justo hoy. 

Como ya hemos dicho en muchas ocasiones, en todo lo relacionado con el mundo de la tecnología, el cambio es la única constante. Como profesionales de bases de datos, sabemos que mantener nuestros sistemas actualizados es crucial para garantizar su rendimiento, seguridad y eficiencia y por tanto para desempeñar correctamente nuestro trabajo. En este sentido SQL Server no se queda atrás y se renueva y mejora continuamente con actualizaciones. Recuerda que en el blog tenemos un artículo dedicado a la actualización segura de Always On.

¿Qué son las Actualizaciones de SQL Server?

Empecemos por el principio, ¿qué son las actualizaciones de SQL Server? Las actualizaciones de SQL Server son mejoras y correcciones que Microsoft lanza periódicamente para su sistema de gestión de bases de datos, SQL Server. Estas actualizaciones pueden incluir desde parches de seguridad hasta nuevas funcionalidades, pasando por mejoras de rendimiento y solución de incidencias.

Actualizar SQL Server trae consigo una serie de ventajas como ya hemos visto, no solo tendremos una mayor seguridad seguridad sino que se habilitarán nuevas funcionalidades, se mejorará el rendimiento y se solucionarán los errores.

Tipos de Actualización de SQL Server

Las actualizaciones de SQL Server se pueden clasificar en dos grandes grupos, y dentro de estos encontraremos varias categorías principales. Como grandes tipos podemos diferenciar las actualizaciones mayores, que implican un cambio de versión y las menores que no implican cambio de versión. Dentro de este último grupo tenemos actualizaciones de Service Packs, Acumulativas y de seguridad. 

Actualización Mayor de SQL Server

Las actualizaciones mayores de SQL Server son lanzamientos completos de nuevas versiones del sistema de gestión de bases de datos. Estas actualizaciones suelen incluir una gran cantidad de nuevas funcionalidades, mejoras de rendimiento y seguridad, y a veces cambios en la arquitectura del sistema.

Por ejemplo, SQL Server 2022, que se lanzó en noviembre de 2022, es la versión más reciente hasta la fecha. Esta versión continúa con las mejoras en seguridad y rendimiento, proporcionando una plataforma de datos moderna para escenarios híbridos.

Las actualizaciones mayores también pueden incluir cambios en la compatibilidad con versiones anteriores, por lo que es importante revisar cuidadosamente las notas de la versión antes de actualizar a una nueva versión mayor.

Actualización menores de SQL Server

Además de las actualizaciones mayores Microsoft proporciona un soporte continuo a las versiones de SQL Server que aún están dentro de los plazos de mantenimiento incluyendo las versiones generales de distribución (GDRs), los paquetes de servicio (SPs), y las actualizaciones acumulativas (CUs). Esto es lo que se conoce como actualizaciones menores y también es importante mantenernos al día con ellas.

  • Service Packs (SPs): Son colecciones de actualizaciones y correcciones de errores que se lanzan periódicamente. Los SPs suelen incluir todas las actualizaciones acumulativas y parches de seguridad lanzados hasta la fecha de su publicación. Este tipo de actualizaciones no se han vuelto a publicar desde el Service Pack 3 para SQL Server 2016, ninguna de las últimas versiones de SQL Server ha tenido más Service Pack. 
  • Actualizaciones acumulativas (CUs): Son conjuntos de actualizaciones y correcciones de errores que se lanzan más frecuentemente que los SPs. Las CUs incluyen todas las actualizaciones desde la última CU o SP. 
  • Parches de seguridad: Son actualizaciones críticas que se lanzan para corregir vulnerabilidades específicas de seguridad detectadas en SQL Server. Son las más importantes y como tal se actualizarán automáticamente desde Windows Update si tenemos marcada la opción de actualizar otros productos de Microsoft. Esto es un arma de doble filo pues la actualización requiere parada del servicio y personalmente no lo recomiendo. Yo prefiero actualizar manualmente los servidores de manera controlada, empezando por entornos de desarrollo y pruebas y terminando por los más críticos de producción.

Configuración de Base de Datos: Query_Hotfixes

La configuración de alcance de base de datos Query_Hotfixes es una característica introducida en SQL Server 2016. Esta configuración permite habilitar o deshabilitar las correcciones del optimizador de consultas a nivel de base de datos.

Las correcciones del optimizador de consultas son mejoras o cambios en el optimizador de consultas que se introducen en las actualizaciones de SQL Server CU o SP. Antes de SQL Server 2016, para aprovechar estas mejoras, era necesario habilitar la traza 41992. Sin embargo, a partir de SQL Server 2016, estas mejoras se habilitan en la configuración de la base de datos. Para habilitar las correcciones del optimizador de consultas, puedes usar el siguiente comando:

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;

Este comando configura la base de datos para utilizar todas las correcciones del optimizador de consultas. Por esto, es importante recordar que cualquier cambio en esta configuración sólo afectará a la base de datos en la que se ejecuta el comando y que por defecto esta característica viene siempre deshabilitada.

El Último parche GDR: 09/07/2024

El pasado 9 de julio de 2024, Microsoft lanzó una actualización de seguridad para SQL Server 21016, 2017, 2019 y 2022. Esta actualización resuelve varias vulnerabilidades críticas, en particular en los controladores ODBC y OLE DB Nativos de Microsoft para SQL Server. Antes de esta actualización o si aún no la hemos instalado, estas vulnerabilidades podrían permitir la ejecución remota de código. Esto significa que un atacante podría tomar el control de nuestros sistemas, afectando gravemente a la integridad y confidencialidad de la información. Al instalar esta actualización, se protegen los sistemas contra estas amenazas, reforzando la seguridad de nuestras bases de datos. Es importante destacar que para aplicar esta actualización, debes tener instalado SQL Server 2016, 2017, 2019 o 2022. Se han publicado los parches para las versiones sin ninguna CU o para los sistemas actualizados a la última CU de cada versión.

Vulnerabilidades corregidas en esta actualización

Esta es la lista de CVEs corregidos en esta actualización:

Vulnerabilidades de ejecución remota de código en el controlador ODBC de Microsoft para SQL Server:

  • CVE-2024-28929
  • CVE-2024-28930
  • CVE-2024-28931
  • CVE-2024-28932
  • CVE-2024-28933
  • CVE-2024-28934
  • CVE-2024-28935
  • CVE-2024-28936
  • CVE-2024-28937
  • CVE-2024-28938
  • CVE-2024-28941
  • CVE-2024-28943
  • CVE-2024-29043

Vulnerabilidades del controlador OLE DB de Microsoft para la ejecución de código remoto de SQL Server:

  • CVE-2024-28939
  • CVE-2024-28940
  • CVE-2024-28942
  • CVE-2024-28944
  • CVE-2024-28945
  • CVE-2024-28927
  • CVE-2024-28909
  • CVE-2024-29044
  • CVE-2024-28906
  • CVE-2024-29045
  • CVE-2024-28908
  • CVE-2024-29046
  • CVE-2024-28926
  • CVE-2024-28909
  • CVE-2024-29047
  • CVE-2024-28911
  • CVE-2024-28912
  • CVE-2024-28914
  • CVE-2024-28913
  • CVE-2024-29048
  • CVE-2024-29982
  • CVE-2024-29983
  • CVE-2024-29984
  • CVE-2024-29985
  • CVE-2024-28915

Conclusión

Mantener SQL Server actualizado es una tarea esencial para cualquier profesional de bases de datos. No solo nos ayuda a mantener nuestras bases de datos seguras, sino que también nos permite aprovechar las últimas mejoras y funcionalidades. Así que, recordemos siempre mantener un ojo en las últimas actualizaciones de SQL Server aquí. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de LinkedIn y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

Publicado por Roberto Carrancio en Cloud, SQL Server, 0 comentarios