Rendimiento

13
Sep
2024

Transacciones Distribuidas y DTC

Cuando hablamos de sistemas de bases de datos, uno de los retos más grandes es garantizar la consistencia de los datos en entornos distribuidos. No suele ser lo común pero, a medida que las arquitecturas de aplicaciones se vuelven más complejas, surge la necesidad de coordinar múltiples transacciones que puedan involucrar diferentes bases de datos o incluso diferentes servidores. Aquí es donde entra en juego el concepto de transacciones distribuidas, y en SQL Server, el Distributed Transaction Coordinator (DTC) juega un papel crucial.

¿Qué son las Transacciones Distribuidas?

Una transacción distribuida es aquella que abarca más de un recurso de red, como bases de datos o sistemas de archivos ubicados en diferentes servidores. A diferencia de una transacción local que afecta a una sola base de datos, las transacciones distribuidas tienen la capacidad de coordinar cambios en varias bases de datos, asegurando que todos los participantes de la transacción lleguen a un estado de compromiso o vuelvan a un estado previo en caso de fallo.

El desafío en las transacciones distribuidas es garantizar que todos los nodos involucrados lleguen a un consenso sobre el resultado de la transacción. Esto es fundamental para mantener la integridad de los datos y evitar inconsistencias que podrían llevar a resultados inesperados o, en el peor de los casos, a la corrupción de los datos.

Distributed Transaction Coordinator (DTC)

En SQL Server, el Distributed Transaction Coordinator (DTC) es el componente encargado de gestionar las transacciones distribuidas. Su función principal es asegurar que todas las partes de una transacción distribuida, que pueden involucrar múltiples bases de datos y servidores, se comprometan correctamente o se deshagan en caso de error.

DTC utiliza el protocolo de dos fases (2PC, por sus siglas en inglés) para coordinar las transacciones. Este protocolo se divide en dos fases: la fase de preparación y la fase de compromiso. En la primera fase, DTC pregunta a todos los participantes si están listos para comprometer la transacción. Si todos responden afirmativamente, se procede a la segunda fase, donde se envía la orden de compromiso. Si alguno de los participantes no puede comprometerse, se inicia el proceso de deshacer la transacción en todos los participantes, asegurando que el sistema vuelva a un estado coherente.

Configuración y uso de Transacciones Distribuidas en SQL Server

Para aprovechar las transacciones distribuidas en SQL Server, primero necesitamos asegurarnos de que DTC esté configurado y funcionando correctamente en todos los servidores involucrados. Esto implica la configuración tanto a nivel de sistema operativo como en SQL Server.

Configurar DTC a nivel sistema operativo y red

En cuanto a la configuración del sistema operativo, es crucial que DTC esté habilitado y configurado para permitir transacciones remotas, ya que, por defecto, estas suelen estar desactivadas por razones de seguridad. Para habilitar DTC, desde el panel de control tendremos que acceder a “Agregar o Quitar Componentes de Windows” y activar la opción “Habilitar el acceso DTC de red”. Una vez hecho esto, y reiniciado el servidor si se nos requiere, el equipo estará listo para admitir transacciones distribuidas.

Sin embargo, esto no es todo, es importante asegurarse de que las reglas del firewall permitan la comunicación entre los servicios DTC de los diferentes servidores. DTC usa llamadas al procedimiento remoto RPC por lo que los puertos necesarios son, en primer lugar el puerto 135 TCP y UDP para establecer la comunicación y después un puerto TCP dinámico del rango 49152-65535. Este rango se puede configurar cambiando configuraciones del registro de windows si lo deseamos pero lo importante es que, nuestro firewall admita conexiones tanto por el puerto 135 como por todos los del rango dinámico seleccionado.

Usar DTC en SQL Server

Una vez que DTC esté operativo, podremos comenzar a utilizar transacciones distribuidas en SQL Server. Esto se hace a través de la instrucción BEGIN DISTRIBUTED TRANSACTION, que inicia una transacción distribuida que abarca múltiples servidores. Es importante tener en cuenta que, aunque la sintaxis es similar a la de una transacción local, el alcance y la complejidad son considerablemente mayores.

Un ejemplo sencillo podría involucrar dos servidores SQL Server diferentes. Comenzamos la transacción distribuida en el primer servidor y realizamos las operaciones necesarias. Luego, nos conectamos al segundo servidor y realizamos más operaciones dentro de la misma transacción. Finalmente, decidimos si se comprometen los cambios (commit) o si se deshacen (rollback).

Consideraciones en el Uso de Transacciones Distribuidas

Aunque el uso de transacciones distribuidas y DTC ofrece grandes ventajas en términos de consistencia y fiabilidad, también presenta una serie de retos que debemos considerar.

En primer lugar, las transacciones distribuidas suelen ser más lentas que las locales debido a la sobrecarga de la coordinación entre múltiples nodos. Esto puede afectar el rendimiento de las aplicaciones, especialmente en sistemas con alta concurrencia.

Además, la complejidad de la configuración y la gestión de DTC puede ser un obstáculo en muchas organizaciones donde, también será común involucrar a varias personas de varios departamentos para el cambio. Es vital asegurarse de que todos los servidores involucrados estén correctamente configurados y que la comunicación entre ellos sea fluida. Cualquier problema en la configuración de DTC puede resultar en errores difíciles de diagnosticar, que pueden ser muy costosos de resolver en producción.

Otro aspecto a tener en cuenta es la fiabilidad del sistema. Aunque DTC está diseñado para manejar fallos, es esencial contar con mecanismos adicionales de recuperación y monitorización para minimizar el impacto de posibles fallos de red o de los servidores.

Por último, es fundamental considerar la seguridad en la configuración de DTC. Dado que las transacciones distribuidas pueden involucrar la transferencia de datos sensibles entre servidores, es necesario implementar medidas de seguridad robustas para proteger esta información. Esto incluye el uso de comunicaciones seguras, así como la correcta configuración de permisos y autenticaciones.

Buenas prácticas para la gestión de Transacciones Distribuidas

Para gestionar eficazmente las transacciones distribuidas en SQL Server, es importante seguir una serie de buenas prácticas que nos permitirán minimizar riesgos y maximizar el rendimiento.

En primer lugar, debemos evitar utilizar transacciones distribuidas a menos que sean absolutamente necesarias. Si es posible, debemos buscar alternativas, como la replicación o el uso de servicios distribuidos que manejen la consistencia eventual. Las transacciones distribuidas deben reservarse para casos en los que la consistencia estricta sea un requisito ineludible. Si, el primer consejo es no lo hagas, es lo que hay.

Cuando sea necesario utilizar transacciones distribuidas, es fundamental optimizar el diseño de las mismas para reducir al mínimo el tiempo que la transacción está abierta. Esto incluye realizar todas las operaciones preparatorias fuera de la transacción y asegurarse de que el código dentro de la transacción sea lo más eficiente posible.

Además, es recomendable implementar una monitorización continua del rendimiento y de los posibles errores de DTC. Existen herramientas en SQL Server que nos permiten rastrear y analizar el rendimiento de las transacciones distribuidas, así como diagnosticar problemas en tiempo real. Por ejemplo, SQL Server Profiler, xEvents o DMVs.

Conclusión

Las transacciones distribuidas y el uso de DTC en SQL Server son herramientas poderosas que permiten garantizar la consistencia de los datos en entornos complejos y distribuidos. Sin embargo, su uso requiere una planificación cuidadosa y una gestión rigurosa para evitar problemas de rendimiento y fiabilidad.

Es importante recordar que no todas las aplicaciones necesitan transacciones distribuidas. En muchos casos, existen soluciones alternativas que pueden ofrecer la consistencia y fiabilidad necesarias sin la complejidad adicional. Cuando se opte por utilizar transacciones distribuidas, debemos asegurarnos de seguir las buenas prácticas y mantener una supervisión constante para garantizar el éxito a largo plazo.

Si abordamos las transacciones distribuidas con una comprensión clara de sus beneficios y limitaciones, y si estamos dispuestos a invertir en su correcta implementación, podemos lograr una gestión eficiente y segura de nuestros sistemas distribuidos 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
11
Sep
2024

Bloqueos Optimizados

Hoy vamos a hablar de una de las características nuevas que implementan las bases de datos de Azure para maximizar el rendimiento sin comprometer la integridad de los datos. Cuando diseñamos y gestionamos nuestras bases de datos, debemos considerar cómo se gestionan los bloqueos, especialmente en entornos con alta concurrencia. La gestión de bloqueos es crucial para garantizar que múltiples transacciones puedan ejecutarse en paralelo sin conflictos. En este artículo, exploraremos en profundidad los bloqueos optimizados en las Azure Databases, cómo funcionan y cómo pueden ser aprovechados para mejorar el rendimiento de nuestras aplicaciones.

¿Qué son los bloqueos optimizados en Azure SQL Database?

Los bloqueos optimizados son una característica avanzada de Azure SQL Database diseñada para reducir la contención (bloqueos) y, por tanto, mejorar el rendimiento de las transacciones en entornos con alta concurrencia. En esencia, esta característica permite al motor de bases de datos minimizar el tiempo durante el cual las transacciones mantienen bloqueos, reduciendo así la posibilidad de que otras transacciones tengan que esperar para acceder a los mismos recursos. En entornos con alta concurrencia, como los que a menudo manejamos en la nube, esta optimización puede marcar la diferencia entre una aplicación fluida y una plagada de cuellos de botella.

El principio básico detrás de los bloqueos optimizados es el uso eficiente de los recursos del sistema. Las bases de datos tradicionales suelen imponer bloqueos a nivel de fila, página o tabla, lo que puede llevar a que las transacciones se bloqueen entre sí si intentan acceder a los mismos datos. Con los bloqueos optimizados, Azure SQL Database ajusta dinámicamente el nivel de bloqueo, permitiendo que las transacciones adquieran solo los bloqueos necesarios y los liberen lo antes posible. De este modo, se mejora la eficiencia general del sistema.

¿Dónde puedo usar los bloqueos optimizados?

A día de hoy, los bloqueos optimizados son una característica exclusiva de las Azure SQL Databases, no vamos a encontrar esta funcionalidad ni en versiones de SQL Server ni en Azure Managed Instance. Si estamos trabajando con Azure SQL Databases (sea cual sea nuestro nivel de servicio) debemos saber que los bloqueos optimizados están habilitados por defecto y, por tanto, podremos esperar el comportamiento que veremos a continuación siempre y cuando no los deshabilitemos. También es importante mencionar que esta funcionalidad depende de la recuperación acelerada de base de datos (ADR) por lo que si en algún momento deshabilitamos ADR en nuestra base de datos perderemos la funcionalidad de los bloqueos optimizados.

Funcionamiento de los bloqueos optimizados

Para entender cómo los bloqueos optimizados logran mejorar el rendimiento, es imprescindible entender cómo funciona esta gestión a bajo nivel. Cuando una transacción se ejecuta en Azure SQL Database, el motor de la base de datos evalúa el impacto potencial de los bloqueos necesarios. Dependiendo de factores como la naturaleza de la consulta, el nivel de aislamiento de la transacción y la carga actual del sistema, el motor decide si aplicar un bloqueo exclusivo, compartido o, en algunos casos, ninguno en absoluto. De esto ya hemos hablado en nuestro artículo sobre los bloqueos y deadlocks.

Uno de los aspectos clave de esta optimización es la técnica conocida como «lock escalation» o escalado de bloqueos. En lugar de aplicar bloqueos a nivel de fila o página, que pueden ser demasiado restrictivos, el motor de Azure SQL Database puede optar por escalar el bloqueo a un nivel superior (como a nivel de tabla) o utilizar técnicas de versionado de filas (row versioning). Esto permite que múltiples transacciones accedan simultáneamente a diferentes partes de los datos sin interferir entre sí.

Además, los bloqueos optimizados se integran con otras características avanzadas de Azure SQL Database, como las transacciones de larga duración y el procesamiento de consultas en paralelo. El motor de la base de datos tiene la capacidad de ajustar dinámicamente la estrategia de bloqueo según la duración y complejidad de las transacciones, lo que minimiza el impacto en el rendimiento.

TID y LAQ: Las claves para entender esto

Si queremos profundizar en los bloqueos optimizados hay dos conceptos fundamentales que debemos dominar: el Transaction ID (TID) y el Lock Acquisition Queue (LAQ). Estos términos juegan un papel crucial en la forma en que el motor de base de datos gestiona y optimiza los bloqueos, especialmente en entornos con alta concurrencia.

Transaction ID (TID)

El Transaction ID (identificador de transacción) , conocido como TID, es un identificador único asignado por el motor de la base de datos a cada transacción que se inicia en Azure SQL Database. Este identificador es esencial para la gestión de bloqueos, ya que permite al sistema rastrear de manera precisa qué transacción está accediendo a qué recursos en un momento dado. Además, el TID facilita la implementación de estrategias de bloqueo como la escalada de bloqueos y el versionado de filas.

Cuando una transacción se ejecuta en Azure SQL Database, el TID se convierte en la referencia central para todas las operaciones que esa transacción realiza. Cada vez que la transacción intenta leer o modificar un registro, el motor de la base de datos utiliza el TID para determinar si es necesario adquirir un nuevo bloqueo, mantener un bloqueo existente o escalarlo. Esta capacidad de rastreo granular es lo que permite a Azure SQL Database aplicar bloqueos de manera eficiente y minimizar la contención entre transacciones.

El TID también juega un papel fundamental en la resolución de conflictos entre transacciones concurrentes. Si dos transacciones intentan acceder al mismo recurso al mismo tiempo, el motor de la base de datos utilizará los TIDs asociados para decidir cuál transacción obtendrá acceso al recurso y cuál tendrá que esperar o, en casos extremos, finalizará esa transacción y deberá ser reintentada. Este proceso es esencial para mantener la integridad de los datos y evitar condiciones de carrera, donde el resultado de una transacción podría depender del orden en que se completan otras transacciones.

TID en acción

Vamos a ver cómo aplica esto en la práctica con un ejemplo muy sencillo. Para ello partiremos de la siguiente consulta que, como veis, crea una tabla, inserta unos valores y en una transacción actualiza esos registros. Antes de cerrar la transacción consultamos los bloqueos e intentos de bloqueos generados para después cerrar la transacción y borrar la tabla.

CREATE TABLE foo
(foo1 int PRIMARY KEY not null
,foo2 int null);
INSERT INTO foo VALUES (1,10),(2,20),(3,30);
GO
BEGIN TRAN
UPDATE foo 
SET foo2=foo2+10;
SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID AND resource_type in ('PAGE','RID','KEY','XACT');
COMMIT
GO
DROP TABLE IF EXISTS foo;

Si ejecutamos esto en SQL Server o en una base de datos de una instancia administrada de Azure (Azure Managed Instance) veremos que se generan cuatro registros, tres bloqueos exclusivos a nivel de clave y un intento de bloqueo exclusivo a nivel de página. Os dejo un ejemplo:

No Bloqueos Optimizados TIP

Sin embargo, la misma consulta sobre una base de datos de Azure con bloqueos optimizados solo genera un bloqueo exclusivo a nivel de transacción:

Bloqueos Optimizados TIP

Lock Acquisition Queue (LAQ)

El Lock Acquisition Queue (bloqueo después de la calificación), o LAQ, es otro concepto clave en la gestión de bloqueos optimizados. La LAQ es esencialmente una cola en la que las transacciones esperan para adquirir un bloqueo sobre un recurso determinado. Cuando trabajamos en un entorno de base de datos concurrido, donde múltiples transacciones pueden intentar acceder al mismo recurso simultáneamente, la LAQ nos ayuda a gestionar y organizar estas solicitudes de bloqueo para minimizar el tiempo de espera y evitar conflictos.

Cuando una transacción intenta adquirir un bloqueo sobre un recurso que ya está bloqueado por otra transacción, se coloca en la LAQ correspondiente a ese recurso. A medida que los recursos se van liberando, las transacciones en la LAQ se procesan en orden, lo que garantiza que las transacciones que han estado esperando más tiempo tengan prioridad para acceder al recurso. Este enfoque ayuda a reducir la contención y asegura que las transacciones no se bloqueen indefinidamente, lo que nos podría causar tiempos de espera excesivos y degradación del rendimiento.

La LAQ no solo gestiona el orden en que las transacciones adquieren bloqueos, sino que también juega un papel crucial en la optimización de los bloqueos mismos. En lugar de simplemente otorgar un bloqueo cuando un recurso se libera, el motor de Azure SQL Database utiliza la información en la LAQ para decidir si es necesario escalar el bloqueo a un nivel superior, como a nivel de tabla, o si se puede mantener a un nivel más granular, como a nivel de fila. Esta flexibilidad es clave para maximizar la concurrencia y minimizar la sobrecarga de bloqueo.

LAQ en acción

Si recordáis cuando hablamos de los bloqueos, comentamos que las consultas se evalúan fila a fila para comprobar si se pueden realizar o creando primero un bloqueo compartido de actualización (U). En caso de no haber conflicto ese bloqueo escala a un bloqueo (X) antes de realizar la actualización. Este paradigma cambia cuando tenemos bloqueos optimizados y un nivel de aislamiento Read Committed Snapshot o RCSI (por defecto en las bases de datos de Azure) evaluando ahora las consultas contra la versión confirmada más reciente y en caso de no haber conflicto la transacción adquiere un bloqueo (X) y se completa.

¿Te ha sonado a chino todo esto? No te preocupes que te lo enseño con un ejemplo. Mira esta captura sobre una base de datos de Azure.

Bloqueos Optimizados LAQ 1

En el ejemplo anterior, he creado una tabla en la sesión de la izquierda, introducido tres registros y posteriormente he actualizado el primero dentro de una transacción que no he llegado a confirmar ni revertir la transacción. Mientras tanto, en la sesión de la derecha he actualizado otro de los registros de la tabla sin problema. Esto en SQL Server o en Azure Managed instance generaría un bloqueo y la transacción de la derecha no llegaría a completarse pues, aunque son registros diferentes, la tabla no tiene ningún índice y eso hace que el bloqueo (X) no se realice a nivel de fila. ¿No te lo crees? Te lo demuestro.

No Bloqueos Optimizados LAQ

Beneficios de los Bloqueos Optimizados en entornos de alta concurrencia

Los entornos de alta concurrencia, como en los que solemos trabajar cuando tenemos aplicaciones empresariales críticas, son los que más se benefician de los bloqueos optimizados. En estos escenarios, múltiples usuarios o aplicaciones pueden estar accediendo a la base de datos simultáneamente, realizando lecturas y escrituras en paralelo. Sin una gestión adecuada de los bloqueos, es fácil que se produzcan cuellos de botella, donde una transacción tiene que esperar a que otra libere un recurso.

Con los bloqueos optimizados, Azure SQL Database reduce significativamente la posibilidad de que esto ocurra. Al minimizar el tiempo de bloqueo y ajustar dinámicamente el nivel de bloqueo, nos permite que más transacciones se ejecuten en paralelo sin interferir entre sí. Esto no solo mejora el rendimiento de la base de datos, sino que también reduce el tiempo de respuesta de las aplicaciones que dependen de ella.

Por ejemplo, en una aplicación de comercio electrónico con alta concurrencia de usuarios durante una campaña de ventas, los bloqueos optimizados aseguran que las transacciones de actualización de inventario y procesamiento de pedidos no se bloqueen mutuamente, permitiendo una experiencia de usuario fluida y sin interrupciones.

Inconvenientes de los Bloqueos Optimizados

Aunque los bloqueos optimizados están habilitados por defecto en Azure SQL Database desde Marzo de 2024 y, ahora mismo, no hay manera de deshabilitarlos, es importante que sepamos cómo funciona esta característica para evitar sustos. La clave para ello es entender, tanto la nueva gestión de bloqueos optimizados como el perfil de carga de trabajo de nuestra base de datos. Si bien los bloqueos optimizados son efectivos en la mayoría de los escenarios, ciertos tipos de consultas o transacciones pueden requerir ajustes específicos en la aplicación.

Resultados inesperados 

Acabamos de ver un ejemplo de las bondades de los bloqueos optimizados en combinación con RCSI pero esto tiene más implicaciones. El hecho de que ahora las transacciones de escritura se evalúan contra la versión confirmada almacenada en el snapshot y no se bloqueen nos puede traer resultados inesperados. Por ejemplo suponed que tenemos una tabla con un campo ID de empleado y un campo sueldo. Pongamos que una transacción A quiere actualizar los datos del empleado con ID 1 de 1000 a 1100. Mientras esa transacción no ha terminado, tenemos otra transacción B que quiere actualizar los sueldos mayores que 1001 un 10%. 

En un entorno tradicional de SQL Server la transacción B esperaría al bloqueo de la transacción A y se ejecutaría tras esta, dando un resultado de un sueldo de 1210 para el empleado con ID 1 pues cuando la transacción B se ejecute el update de la transacción A habrá finalizado y el sueldo será de 1100 cumpliendo con la condición de sueldo mayor que 1001. Sin embargo, con los bloqueos optimizados y RCSI la transacción B se evaluaría contra la última versión confirmada (el snapshot de antes de iniciar la transacción A) y, por tanto, el empleado con ID 1 no cumpliría con la condición de la consulta.

Sin bloqueos Optimizados:

NO Bloqueos Optimizados LAQ Issues

Con bloqueos Optimizados:

Bloqueos Optimizados LAQ Issues

¿Podemos hacer algo?

¿Os había dicho que los bloqueos optimizados no se pueden deshabilitar? Vamos a matizarlo. Hemos visto ya que para que funcionen los bloqueos optimizados tenemos que tener habilitado ADR, pues bien esto es como no decir nada pues ADR está habilitado siempre en las bases de datos de Azure sin posibilidad de deshabilitarse. 

Entonces, si no puedo deshabilitar los bloqueos optimizados ni ADR, ¿qué opción tengo? Realmente pocas. Básicamente, nuestra única opción es jugar con los niveles de aislamiento. Por definición esta característica es incompatible con Serializable y con Repeteable Reads por lo que cambiar esta configuración sería nuestra única opción. Y seamos sinceros, esto es una broma de mal gusto, no es viable en una base de datos con alta concurrencia. Aunque, en algunos casos, un nivel de aislamiento más alto puede ser necesario para garantizar la integridad de los datos, esto también incrementa el tiempo de bloqueo, y mucho, haciendo inviable técnicamente esta solución teórica. 

Conclusión

En resumen, los bloqueos optimizados en Azure SQL Database representan un cambio importante en la gestión de concurrencia. Teóricamente para mejorar el rendimiento de nuestras aplicaciones pero, en ocasiones puede ser un problema. En este momento, se hace imprescindible conocer a fondo los conceptos de Transaction ID (TID) y Lock Acquisition Queue (LAQ) para que esta gestión de bloqueos no nos juegue una mala pasada. Podéis pensar que la solución pasa por migrar nuestras bases de datos de Azure a una instancia administrada pero, seamos sinceros ¿Cuanto creeis que tardarán en aplicar este cambio allí también? 

Esto nos genera un debate muy interesante sobre la pérdida de control que hemos sufrido en la nube, especialmente en la infraestructura SAAS. Este caso es especialmente delicado pues un cambio de configuración sin posibilidad de marcha atrás ha cambiado completamente el comportamiento de nuestras aplicaciones pudiendo generar resultados inesperados. Y tú, ¿qué opinas? Te leo en los comentarios.

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
06
Sep
2024

Kerberos SPN para SQL Server

En el pasado artículo os hablé de la autenticación en SQL Server y profundizamos en los protocolos NTLM y Kerberos que se usan para la autenticación con usuarios de Directorio Activo. Cuando hablamos de Kerberos, os comenté que pese a ser un sistema más rápido y seguro que NTLM necesita de más configuraciones. En concreto comentamos los Service Principal Names (SPN) que es en lo que vamos a profundizar hoy. Un SPN mal configurado puede desencadenar una serie de problemas, desde forzar el uso de NTLM hasta errores de autenticación. Por ello, comprender en detalle cómo funciona Kerberos en combinación con SQL Server, y más específicamente, cómo configurar adecuadamente los SPN, es fundamental para cualquier DBA. 

¿Qué es un SPN y por qué es importante en SQL Server?

Los Service Principal Names, son identificadores únicos que permiten a los ordenadores localizar un servicio específico dentro de una red. Si llevamos esto a SQL Server, el SPN actúa como un enlace entre una instancia de SQL Server y su identidad en la red, permitiendo a los clientes autenticarse correctamente mediante Kerberos. En escenarios de autenticación Kerberos, el SPN se registra en el Active Directory y se asocia a la cuenta de servicio que ejecuta SQL Server. Esta configuración asegura que Kerberos pueda autenticar correctamente a los usuarios que intentan conectarse a la base de datos. De lo contrario, los usuarios podrían enfrentar errores de autenticación que, en el mejor de los casos, causan inconvenientes, y en el peor, generan brechas de seguridad. Si el SPN no está configurado correctamente, el proceso de autenticación podría revertir al protocolo NTLM, que es menos seguro y menos eficiente que Kerberos.

Configuración de SPN

Configurar correctamente los Service Principal Names es esencial para garantizar la autenticación Kerberos en SQL Server. En primer lugar, es necesario identificar bajo qué cuenta se ejecuta el servicio SQL Server, ya que el SPN debe registrarse en el Active Directory bajo esa cuenta específica. 

Un detalle muy importante que no podemos pasar por alto es que un SPN debe ser único en todo el dominio. Si dos servicios diferentes compartieran el mismo SPN, tendríamos fallos en la autenticación que además, por experiencia propia os lo digo, son muy difíciles de diagnosticar y solucionar. Para evitar estos conflictos, se recomienda utilizar el comando setspn -L para listar los SPN actuales y verificar la ausencia de duplicados antes de crear un nuevo SPN.

Configurar SPN para SQL Server

Cuando vamos a registrar un servidor SQL Server es crucial que el SPN se registre tanto para el nombre del servidor como para su FQDN (Fully Qualified Domain Name). Esto asegura que, independientemente de cómo se conecten los clientes al servidor (mediante el nombre corto o el FQDN), la autenticación Kerberos se realice sin problemas. La sintaxis básica para registrar un SPN para SQL Server es la siguiente y nos servirá tanto para la instancia por defecto del servidor:

setspn -S MSSQLSvc/servidor:puerto dominio\cuentaServicio
setspn -S MSSQLSvc/servidor.dominio.com:puerto dominio\cuentaServicio

Podríamos usar el parámetro -A para crear los Service Principal Names pero yo personalmente prefiero usar -S en el comando setspn ya que verifica automáticamente si el SPN ya existe, previniendo registros duplicados.

Creación de SPN en Instancias Nombradas de SQL Server

Cuando trabajamos con instancias con nombre de SQL Server, la creación de SPN requiere un enfoque ligeramente diferente al de las instancias predeterminadas. Las instancias nombradas utilizan un puerto dinámico por defecto, lo que puede complicar la configuración del SPN. Para facilitar la gestión y evitar problemas de autenticación, deberemos asignar un puerto fijo a la instancia nombrada. Una vez hecho esto, para la configuración del SPN seguiremos un procedimiento similar al de una instancia predeterminada pero, registrando también los Service Principal Names con el nombre de la instancia.

Supongamos que tenemos una instancia con nombre de SQL Server llamada «SQLInstancia» que se ejecuta en el servidor «servidorSQL» con el puerto fijo 1436. El SPN se configuraría de la siguiente manera:

setspn -S MSSQLSvc/servidorSQL:1436 dominio\cuentaServicio
setspn -S MSSQLSvc/servidorSQL.dominio.com:1436 dominio\cuentaServicio
setspn -S MSSQLSvc/servidorSQL:SQLInstancia dominio\cuentaServicio
setspn -S MSSQLSvc/servidorSQL.dominio.com:SQLInstancia dominio\cuentaServicio

Realmente podríamos dejar el puerto dinámico por defecto y registrar el SPN solo por el nombre pero, no soy partidario de ello. Este método que os acabo de enseñar nos asegura que la autenticación Kerberos funciona correctamente independientemente de si la conexión se realiza por nombre de la instancia o por el puerto.

Eliminación de SPN

Eliminar un SPN incorrecto o redundante es una operación delicada, pero a veces necesaria para resolver conflictos o corregir configuraciones. Si detectamos que un SPN fue registrado incorrectamente, o si estamos migrando un servicio y necesitamos limpiar los registros antiguos, podemos utilizar el comando setspn -D para borrar los registros de SPN.

Por ejemplo, si necesitamos eliminar un SPN registrado para una instancia predeterminada en el servidor «servidorSQL», el comando sería:

setspn -D MSSQLSvc/servidorSQL:1433 dominio\cuentaServicio

O bien, para eliminar un SPN de una instancia con nombre:

setspn -D MSSQLSvc/servidorSQL\SQLInstancia dominio\cuentaServicio

Es importante tener precaución al usar el comando setspn -D, ya que eliminar un SPN incorrectamente puede llevar a problemas de autenticación, especialmente en entornos de producción. Antes de realizar la eliminación, es recomendable listar todos los Service Principal Names registrados con setspn -L cuentaServicio para confirmar que estamos eliminando el SPN correcto.

Además, cuando eliminemos un SPN debemos gestionarlo cuidadosamente, asegurándonos de que el servicio no se quede sin SPN registrado, lo que podría causar fallos en la autenticación de los usuarios y, por tanto, afectar a la disponibilidad del servicio.

SPN para Otros Servicios de SQL Server

Además de las instancias del motor de base de datos de SQL Server, otros servicios de SQL Server, como SQL Server Reporting Services (SSRS), SQL Server Analysis Services (SSAS) y SQL Server Integration Services (SSIS), también requieren la configuración de SPN para soportar la autenticación Kerberos. Aunque su uso está disminuyendo en favor de Power BI y otras herramientas nuevas en la nube como Fabric aún es común encontrarse con instalaciones locales de estos servicios que tendremos que administrar. Cada uno de estos servicios tiene sus propios requisitos y consideraciones para la correcta configuración de SPN. Vamos a ver cómo configurar los Service Principal Names para cada uno de estos servicios.

SQL Server Reporting Services (SSRS) y Power BI Report Server (PBIRS)

SQL Server Reporting Services (SSRS) es un servicio que se utiliza para generar, administrar y entregar informes a través de una interfaz web. De la misma manera y sobre esa base existe un servicio local de Power BI Server para publicar informes llamado Power BI Report Server (PBIRS). Para asegurar que las conexiones a SSRS y PBIRS se autentiquen correctamente mediante Kerberos, debemos registrar un SPN para el servicio HTTP que utilizan.

Supongamos que un SSRS o PBIRS está configurado en un servidor llamado «servidorSQL» con el nombre de instancia «Reportes» y está accesible a través de HTTP. Los Service Principal Names se configuran de la siguiente manera:

setspn -S HTTP/servidorSQL dominio\cuentaServicio
setspn -S HTTP/servidorSQL.dominio.com dominio\cuentaServicio

IMPORTANTE: Además del registro de Kerberos en SSRS deberemos habilitar esta conexión en el fichero de configuración «RsReportServer.config». Para ello nos aseguraremos de añadir (o que esté añadida) la palabra clave «<RSWindowsNegotiate>» como primera entrada en el apartado «<AuthenticationTypes>«.

Consideraciones Adicionales para HTTPS

Si SSRS o PBIRS están configurados para utilizar HTTPS, la configuración del SPN no cambia en cuanto a la necesidad de registrar los Service Principal Names para el servicio HTTP. Sin embargo, es crucial que el certificado SSL esté correctamente configurado y que el nombre del certificado coincida con el nombre de host utilizado para acceder al servidor. Esto asegura que la autenticación Kerberos funcione sin problemas en un entorno seguro.

Configuración para Entornos con Nombres de Alias o CNAME

En algunos escenarios, es posible que PBIRS esté configurado para acceder a través de un nombre de alias o un CNAME, lo cual es común en configuraciones de alta disponibilidad o cuando se utiliza un balanceador de carga. En estos casos, también debemos registrar un SPN para el alias o CNAME, de manera que las autenticaciones se gestionen correctamente:

setspn -S HTTP/aliasPBIRS dominio\cuentaServicio
setspn -S HTTP/aliasPBIRS.dominio.com dominio\cuentaServicio

Esto asegura que cualquier solicitud de autenticación Kerberos que se realice a través del alias sea manejada adecuadamente, previniendo errores de autenticación que podrían derivar en problemas de acceso a los informes.

SQL Server Analysis Services (SSAS)

SQL Server Analysis Services (SSAS) es un servicio que proporciona herramientas de bases de datos tabulares y de cubos para análisis y minería de datos. Para SSAS, el SPN debe registrarse para el servicio «MSOLAPSvc.3». Si, por ejemplo, tenemos una instancia de SSAS llamada «Analisis» en el servidor «servidorSQL», los Service Principal Names se configurarán de la siguiente manera: 

setspn -S MSOLAPSvc.3/servidorSQL:puerto dominio\cuentaServicio
setspn -S MSOLAPSvc.3/servidorSQL.dominio.com:puerto dominio\cuentaServicio

Si SSAS utiliza un puerto predeterminado o fijo, ese puerto debe incluirse en el SPN. Es importante verificar el puerto en uso antes de registrar el SPN para evitar errores en la configuración.

SQL Server Integration Services (SSIS)

SQL Server Integration Services (SSIS) no requiere típicamente la configuración de un SPN, ya que SSIS es más comúnmente utilizado en el contexto de ejecución local de paquetes. Sin embargo, si SSIS está configurado para ejecutar paquetes en un servidor remoto y queremos o necesitamos usar la autenticación Kerberos, necesitaremos registrar un SPN para el servicio de agente de SQL Server.

El SPN para SQL Server Agent, que es responsable de ejecutar trabajos que pueden incluir paquetes SSIS, se configuraría de la siguiente manera para una instancia predeterminada en «servidorSQL»:

setspn -S SQLServerAgent/servidorSQL dominio\cuentaServicio
setspn -S SQLServerAgent/servidorSQL.dominio.com dominio\cuentaServicio

SQL Server Browser Service

SQL Server Browser Service es el servicio responsable de enrutar las solicitudes de conexión a la instancia correcta de SQL Server en servidores que ejecutan múltiples instancias. Aunque no es común, si necesitamos configurar Kerberos para este servicio, el SPN se registraría así:

setspn -S MSOLAPDisco\servidorSQL dominio\cuentaServicio
setspn -S MSOLAPDisco\servidorSQL.dominio.com dominio\cuentaServicio

Este SPN permitiría que el SQL Server Browser Service maneje correctamente las conexiones basadas en Kerberos, asegurando la autenticación en entornos con múltiples instancias o nombres de alias.

Mantenimiento de SPN

El mantenimiento continuo de los Service Principal Names es tan importante como su configuración inicial. Debemos estar atentos a cualquier cambio en la infraestructura, como la migración de SQL Server a un nuevo servidor, el cambio de cuentas de servicio o la actualización del nombre del dominio, ya que estos eventos pueden requerir una actualización de los SPN correspondientes.

Es recomendable establecer procedimientos regulares de monitorización para asegurar que los SPN siguen registrados correctamente. Si detectamos un problema de autenticación que sugiere un fallo en Kerberos, el primer paso que debemos realizar es verificar el estado de los Service Principal Names.

Herramientas como klist o el ya mencionado comando setspn pueden ser útiles para diagnosticar problemas de tickets Kerberos. Del mismo modo, el visor de eventos de Windows puede proporcionarnos detalles adicionales sobre fallos de autenticación que nos ayudarán a diagnosticar los problemas.

En caso de encontrarnos con un SPN duplicado o incorrecto, la solución pasa por eliminar el registro incorrecto utilizando el comando setspn -D, como ya hemos visto, y, acto seguido, registrar de nuevo el o los SPN correctos. Esta intervención debe realizarse con cuidado, ya que eliminar un SPN sin registrar el nuevo puede resultar en una pérdida de conectividad con el servicio SQL Server.

SPN y Always On

En entornos de alta disponibilidad, como los clusters de failover de SQL Server o las configuraciones Always On, la gestión de los Service Principal Names se vuelve aún más compleja. En estos escenarios, los SPN deben configurarse no sólo para la instancia de SQL Server, sino también para el nombre del recurso del clúster o el listener de Always On.

Por ejemplo, en un entorno Always On, los SPN deben registrarse tanto para cada réplica como para el listener. Esto garantiza que, en caso de failover, los clientes puedan seguir conectándose al servicio SQL Server utilizando Kerberos, sin experimentar interrupciones. El manejo incorrecto de los Service Principal Names en estos entornos puede provocar fallos en la autenticación y en la conmutación por error, por lo que es esencial prestar mucha atención en estos casos.

Conclusión

Los SPN de Kerberos son un componente crítico en la infraestructura de autenticación de SQL Server. Su correcta configuración y mantenimiento nos aseguran que los entornos de base de datos funcionan de manera segura y eficiente. A medida que la complejidad de nuestras infraestructuras crezca, es fundamental que prestemos atención a los detalles de configuración de SPN, especialmente en entornos de alta disponibilidad.

Un SPN mal configurado no solo puede comprometer la seguridad, sino que también puede afectar negativamente el rendimiento y la disponibilidad de nuestros servicios. Por lo tanto, debemos abordar la gestión de SPN con el mismo rigor y precisión que aplicamos a otros aspectos de la administración de bases de datos. Al hacerlo, garantizaremos que SQL Server siga siendo un pilar confiable en nuestras infraestructuras críticas.

 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 SQL Server, 0 comentarios
28
Ago
2024

Conociendo los Linked Server

Una de esas características interesantes de SQL Server que a menudo pasan desapercibidas es la capacidad de conectarse y consultar datos de diversas fuentes de datos ajenas a nuestra instancia. Esto se consigue gracias a los Linked Servers (Servidores Vinculados) que facilitan la integración de datos distribuidos, permitiéndonos interactuar con otras bases de datos como si fueran parte de nuestra instancia local de SQL Server. Ya sea porque necesitemos acceder a datos de otro servidor SQL, de Oracle, archivos Excel o incluso consultas LDAP los Linked Server son una herramienta imprescindible.

¿Qué es un Linked Server en SQL Server?

Un linked server, o servidor vinculado, es una opción de SQL Server que nos permite establecer una conexión con otra fuente de datos, que puede ser otra instancia de SQL Server, un servidor Oracle, un servidor MySQL, una hoja de cálculo y muchas más opciones. Esta herramienta está pensada para escenarios donde necesitemos acceder a datos almacenados en distintos sistemas para realizar análisis, reportes o integraciones de datos.

La principal ventaja de los linked servers es que nos permiten ejecutar consultas a datos remotos utilizando la sintaxis de cuatro partes: NombreDelLinkedServer.BaseDeDatos.Esquema.Tabla. Este enfoque simplifica la integración de datos, eliminando la necesidad de replicar físicamente los datos en un solo lugar.

Configuración y uso de Servidores Vinculados en SQL Server

Configurar un Linked Server es un proceso relativamente sencillo que se puede realizar tanto mediante SQL Server Management Studio (SSMS) como con comandos T-SQL.  Para crear un linked server a través de SSMS nos dirigiremos a la carpeta Objetos de Servidor y haremos clic derecho sobre Servidores Vinculados para a continuación seleccionar Nuevo Servidor Vinculado. Se nos abrirá una ventana donde rellenaremos los datos relativos al origen de los datos y a la seguridad (usuario y contraseña remotos). Para crear un Linked Server utilizando T-SQL, el comando es el siguiente:

EXEC sp_addlinkedserver
    @server = 'NombreDelLinkedServer',
    @srvproduct = '',
    @provider = 'NombreDelProveedorOLEDB',
    @datasrc = 'NombreOIPDelServidorRemoto';

Para gestionar la autenticación, que puede ser mediante Windows o especificando credenciales propias, utilizamos:

EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'NombreDelLinkedServer',
    @useself = 'False',
    @locallogin = NULL,
    @rmtuser = 'UsuarioRemoto',
    @rmtpassword = 'ContraseñaRemota';

Una vez configurado, podemos realizar consultas distribuidas usando la sintaxis de cuatro partes:

SELECT * FROM NombreDelLinkedServer.BaseDeDatos.Esquema.Tabla;

Uso de Linked Servers con servidores no SQL Server

La sintaxis de cuatro partes es muy útil, pero solo es aplicable cuando el linked server apunta a otra instancia de SQL Server. Cuando necesitamos conectarnos a servidores que no sean SQL Server, como por ejemplo Oracle, MySQL o sistemas de archivos como Excel, esta sintaxis no es compatible. En estos casos, debemos utilizar métodos alternativos para ejecutar nuestras consultas.

¿Por qué no se puede usar la sintaxis de cuatro partes?

La sintaxis de cuatro partes (NombreDelLinkedServer.BaseDeDatos.Esquema.Tabla) depende de la estructura jerárquica de SQL Server, que organiza los objetos en bases de datos, esquemas y tablas de una manera específica. Otros sistemas de bases de datos, como Oracle, tienen una organización interna diferente que no se adapta a esta estructura. Por ejemplo, en Oracle, las bases de datos y esquemas no se organizan de la misma manera, lo que hace que esta sintaxis no sea aplicable y genere errores si la intentamos utilizar.

Alternativa: Uso de OPENQUERY para Consultas con Linked Servers

Para trabajar con linked servers que apuntan a servidores no SQL Server, OPENQUERY es la herramienta adecuada. Esta función permite enviar una consulta SQL completa al servidor remoto, ejecutarla allí y devolver los resultados a SQL Server. 

También podemos usar OPENQUERY para servidores SQL Server en vez de la sintaxis de 4 partes y, aunque pueda parecer más complejo al escribir la consulta, es especialmente útil para mejorar el rendimiento en consultas distribuidas, ya que permite que el servidor remoto procese la consulta completa y solo devuelva los resultados.

La sintaxis de OPENQUERY es la siguiente:

SELECT * 
FROM OPENQUERY(
 NombreDelLinkedServer,
  'SELECT Columna1, Columna2 FROM TablaRemota WHERE Condicion'
);

Esta forma de proceder nos permite aprovechar la sintaxis y capacidades nativas del servidor remoto, como Oracle o MySQL, optimizando la ejecución de las consultas y minimizando la transferencia de datos.

Seguridad en el Uso de Linked Servers

La seguridad es un factor crucial al utilizar linked servers, no olvidemos que estamos accediendo a datos remotos y eso siempre es delicado. Dado que estamos extendiendo nuestras consultas a otros servidores, es fundamental asegurarnos de que las conexiones sean seguras y que las credenciales estén adecuadamente protegidas.

Por ello, es recomendable utilizar la autenticación de Windows siempre que sea posible, ya que nos permite aprovechar las políticas de seguridad de Active Directory. Sin embargo, también es probable que para ello tengamos que configurar la autenticación por Kerberos y registrar SPNs para no tener problemas de inicio de sesión. 

La alternativa sería usar un login de SQL del servidor remoto. En este caso, si necesitamos utilizar autenticación SQL, es importante que las credenciales tengan los mínimos privilegios necesarios en el servidor remoto para realizar las tareas requeridas.

Además, debemos ser muy cuidadosos con las opciones de seguridad como «no delegation» y «mapped logins» para evitar la elevación de privilegios y controlar quién tiene acceso al linked server. Las auditorías regulares de los linked servers y sus usuarios configurados, para mi, son esenciales para mantener un entorno seguro.

Rendimiento al Usar Linked Servers y OPENQUERY

El rendimiento es un aspecto que no debemos pasar por alto al trabajar con linked servers. La latencia de la red y el rendimiento del servidor remoto son factores que pueden afectar considerablemente nuestras consultas distribuidas. Para mitigar estos problemas, debemos optimizar nuestras consultas para que solo traigan los datos necesarios.

Además, no debemos olvidar que, al trabajar con datos de un servidor remoto, nuestro motor de base de datos no va a ser capaz de estimar la cardinalidad de los datos, es decir, no va a saber de antemano cuantos registros le vienen y cuántos recursos asignar para la resolución de esa consulta. En este contexto, el uso de OPENQUERY puede ser una gran ventaja en términos de rendimiento. Al permitir que el servidor remoto procese la consulta completa, reducimos la cantidad de datos que se transfieren y optimizamos la carga en nuestra instancia local de SQL Server.

No obstante, es importante evitar las consultas donde tengamos que trabajar con datos remotos y locales a la vez, normalmente es más eficiente cargar los datos remotos en plano (con las transformaciones que hayamos podido hacer enteramente en el servidor remoto) y luego ya operarlo en combinación con los datos locales. De todas formas, como cada caso es un mundo, lo mejor es probar y ajustar las consultas para asegurarnos de que estamos obteniendo el máximo beneficio de esta técnica. 

En este sentido, también debemos considerar el impacto de las transacciones distribuidas. Cuando nuestras operaciones involucran cambios en múltiples servidores, debemos asegurarnos de que todas las transacciones se manejan correctamente para evitar inconsistencias. Esto puede requerir el uso de coordinadores de transacciones distribuidas (DTC), lo que añade una capa de complejidad y potencial impacto en el rendimiento.

Conclusión

Los Linked Server en SQL Server nos ofrecen una forma versátil y eficiente de interactuar con datos distribuidos en múltiples fuentes. Sin embargo, su uso no está exento de riesgos en cuanto a la seguridad y el rendimiento por lo que debemos ir con cuidado.

Cuando trabajamos con servidores SQL Server, la sintaxis de cuatro partes es una opción sencilla y directa. Sin embargo, cuando nos conectamos a servidores que no son SQL Server, OPENQUERY se convierte en la herramienta clave para ejecutar consultas distribuidas de manera eficiente. Entender las capacidades y limitaciones de cada método nos permitirá aprovechar al máximo los linked servers, garantizando que nuestras aplicaciones funcionen de manera segura y eficiente en entornos distribuidos y heterogéneos.

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 SQL Server, 0 comentarios
26
Ago
2024

Bus Dimensional

El modelado dimensional es una técnica fundamental en la construcción de sistemas de Business Intelligence (BI), que nos va a permitir analizar nuestros datos de forma eficiente y con un alto grado de flexibilidad. Dentro de este marco, el concepto de «Bus dimensional» se posiciona como un componente clave para garantizar la cohesión y la escalabilidad de nuestros modelos de datos. Este enfoque fue popularizado por Ralph Kimball, uno de los pioneros del modelado dimensional, quien estableció las bases de esta metodología en sus numerosos escritos y libros. En este artículo, exploraremos en profundidad el concepto de Bus dimensional, su aplicación en modelos de BI y por qué es esencial para el éxito de cualquier proyecto de análisis de datos.

El Bus Dimensional en modelos de BI según Kimball

Cuando diseñamos un modelo dimensional, nuestro objetivo principal es organizar los datos de forma que facilite tanto su análisis como su consulta. Ralph Kimball, en su obra «The Data Warehouse Toolkit», introdujo la idea del Bus Dimensional como una estrategia para unificar y estandarizar los modelos de datos en toda la organización. Esta técnica nos va a permitir que las diferentes áreas de negocio interactúen y compartan información de forma coherente, garantizando que los datos se interpreten de forma única y consistente a lo largo de diferentes procesos y departamentos.

El Bus dimensional no es solo una herramienta técnica, sino una estrategia de diseño que busca asegurar que todos los modelos compartan una misma estructura y lenguaje, al menos así lo propuso Kimball. De este modo, las distintas áreas de negocio podrán interactuar y comparar datos de forma coherente y sin ambigüedades. Este enfoque es particularmente útil en organizaciones grandes, donde múltiples equipos de BI trabajan en paralelo y, por tanto, es vital que los modelos de datos sean consistentes y escalables.

Diseño del Bus Dimensional en modelos de BI siguiendo a Kimball

Para entender cómo implementamos un Bus Dimensional, es necesario primero comprender su arquitectura básica según la metodología de Kimball. El Bus Dimensional se fundamenta en dos componentes esenciales que os sonarán seguro: las dimensiones conformadas y los hechos compartidos. Las dimensiones conformadas son aquellas que, por diseño, se utilizan en múltiples áreas de negocio y mantienen la misma estructura y contenido. Por ejemplo, una dimensión de tiempo o de clientes puede ser utilizada en distintos modelos para representar el mismo conjunto de datos, asegurando la coherencia entre informes y análisis.

Kimball destacó que la clave del éxito en la implementación de un Bus Dimensional radica en la consistencia de estas dimensiones conformadas. Este enfoque facilita que los hechos compartidos, que se refieren a los eventos o transacciones relevantes para diferentes áreas de negocio, sean representados de forma uniforme en los modelos. Así, se asegura que al consultar los datos, se puedan comparar y analizar los mismos eventos desde diferentes perspectivas sin perder la integridad de la información.

Implementar un Bus Dimensional en nuestros modelos de BI siguiendo las recomendaciones de Kimball requiere un análisis exhaustivo de las necesidades de negocio, así como una colaboración estrecha entre los distintos equipos de desarrollo. No se trata solo de diseñar un modelo técnico eficiente, sino de asegurar que todos los stakeholders involucrados en el proyecto compartan una visión común de los datos y su estructura.

Beneficios de implementar un Bus Dimensional según Kimball

Implementar un Bus Dimensional, como lo concibió Kimball, nos va a aportar múltiples beneficios que pueden impactar positivamente tanto en la eficiencia operativa como en la calidad del análisis de datos. Uno de los beneficios más destacados es la reducción de la redundancia en el modelado de datos. Al utilizar dimensiones conformadas y hechos compartidos, evitamos la creación de múltiples versiones de las mismas entidades, lo que a su vez disminuye la complejidad del mantenimiento del modelo y mejora la consistencia de los datos.

Otro aspecto crucial, resaltado por Kimball, es la escalabilidad. A medida que la organización crece y se incorporan nuevas áreas de negocio o se amplían las ya existentes, el Bus Dimensional permite que estos cambios se integren de forma armoniosa en el modelo de datos existente. Esto no solo reduce el tiempo y los costes asociados con la ampliación del modelo, sino que también asegura que los datos de las nuevas áreas sean compatibles con los modelos ya existentes.

Además, el Bus Dimensional facilita la creación de informes y análisis comparativos entre diferentes áreas de negocio. Al tener un modelo de datos estandarizado, podemos comparar datos de ventas, finanzas, operaciones, entre otros, sin la necesidad de realizar complejas transformaciones o ajustes manuales. Esto mejora la rapidez y la precisión en la toma de decisiones, ya que todos los stakeholders acceden a la misma información y pueden confiar en la coherencia de los datos, un punto esencial en la metodología de Kimball.

Consideraciones para la Implementación según Kimball

A pesar de sus numerosos beneficios, la implementación de un Bus Dimensional según las directrices de Kimball no está exenta de dificultades que deberemos afrontar. Uno de los principales retos, como ya habrás podido imaginar, es alinear las distintas áreas de negocio y que todos estén de acuerdo en cuanto a la definición de dimensiones conformadas y hechos compartidos. Es común que diferentes departamentos tengan visiones y necesidades particulares sobre cómo deben estructurarse los datos, lo que puede generar conflictos a la hora de definir un modelo común.

Para esto, Kimball enfatiza la importancia del liderazgo en estos casos. Es fundamental contar con un liderazgo fuerte y una clara visión de los objetivos del proyecto. Los equipos de BI deben trabajar de forma colaborativa, estableciendo estándares y prácticas que aseguren la alineación de todas las partes involucradas. Esto puede implicar negociaciones y concesiones, pero al final, el resultado será un modelo de datos más robusto y flexible.

Otro aspecto a considerar, que Kimball también aborda, es la complejidad técnica que puede implicar la implementación de un Bus Dimensional en organizaciones con sistemas heredados (legacy) o con una infraestructura de datos fragmentada. En estos casos, puede ser necesario realizar importantes transformaciones en los sistemas existentes para alinearlos con la nueva arquitectura de datos, lo que puede requerir tiempo y recursos significativos. Sin embargo, los beneficios a largo plazo, en términos de eficiencia y escalabilidad, suelen justificar estos esfuerzos.

Conclusión

En resumen, el Bus Dimensional, tal como lo propuso Ralph Kimball, es una estrategia poderosa para garantizar la cohesión, consistencia y escalabilidad en modelos de BI. Al estandarizar las dimensiones y hechos que se utilizan en diferentes áreas de negocio, facilitamos la comparación y el análisis de datos, reducimos la redundancia y mejoramos la escalabilidad del sistema. Si bien su implementación puede presentar desafíos, especialmente en organizaciones grandes o con infraestructuras complejas, los beneficios a largo plazo en términos de eficiencia operativa y calidad del análisis de datos hacen que el esfuerzo valga la pena.

Adoptar un enfoque de Bus Dimensional no solo mejora la gestión de datos en el presente, sino que prepara a la organización para un futuro en el que la capacidad de analizar y utilizar datos de forma eficaz será cada vez más crucial. En última instancia, seguir las directrices de Kimball en el diseño de modelos de BI nos permite construir sistemas más sólidos, coherentes y preparados para los desafíos del mañana.

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 Power BI, 0 comentarios
09
Ago
2024

Mantenimiento imprescindible en SQL Server

El mantenimiento de bases de datos en SQL Server, como en todos los sistemas de bases de datos, es una actividad crítica para garantizar el rendimiento y la estabilidad de las aplicaciones que dependen de ellas. Un enfoque proactivo en la gestión de índices, estadísticas, integridad y copias de seguridad nos permite a los administradores evitar problemas potenciales antes de que se conviertan en fallos costosos. En este artículo, profundizaremos en cada uno de estos aspectos, proporcionando una visión detallada y práctica para mantener nuestras bases de datos en condiciones óptimas.

Mantenimiento de índices para mejorar el rendimiento

Los índices son componentes esenciales en nuestras bases de datos, ya que nos permiten un acceso rápido a los datos y mejoran significativamente el rendimiento de nuestras consultas. Prueba de ello es la cantidad de artículos que les hemos dedicado en el blog. Sin embargo, la eficiencia de los índices puede deteriorarse con el tiempo debido a la fragmentación, que ocurre cuando las páginas de datos se desorganizan. Este fenómeno afecta negativamente la rapidez de las búsquedas y actualizaciones y tenemos que prevenirlo y ponerle solución.

Para mantener un rendimiento óptimo, es crucial que monitoricemos y gestionemos los índices de forma periódica. No vamos a entrar en muchos más detalles pues ya le dedicamos un artículo completo a este tema. Simplemente vamos a ver que tenemos dos opciones de mantenimiento, la reorganización y la reconstrucción. La reorganización de índices es una técnica por la cual el motor de base de datos ajusta los índices sin necesidad de reconstruirlos completamente, lo que es en sí un proceso menos intensivo en recursos. Cuando la fragmentación es alta, la reorganización ya no es tan efectiva y debemos recurrir a la reconstrucción de índices, aunque sea un proceso más costoso en términos de tiempo y recursos. Deberemos planificar estas actividades de mantenimiento durante periodos sin uso o de baja actividad para minimizar el impacto a los usuarios.

Mantenimiento de estadísticas

Las estadísticas en SQL Server proporcionan al optimizador de consultas la información necesaria para decidir cuál es el mejor plan de ejecución posible para nuestra consulta, es decir, cómo ejecutar una consulta de la manera más eficiente posible. Sin datos actualizados, el optimizador puede hacer estimaciones inexactas, lo que lleva a un mal rendimiento.

La actualización regular de las estadísticas es una práctica recomendada para mantener el rendimiento del sistema. SQL Server ofrece opciones de actualización automática, pero en entornos con alta carga de trabajo o donde el rendimiento es crítico, puede ser más efectivo realizar actualizaciones manuales programadas. Esto asegura que las estadísticas reflejen con precisión la distribución actual de los datos, lo que es esencial para que el optimizador pueda generar planes de ejecución óptimos. Además no olvidéis que una actualización de estadísticas fuerza la recopilación de los planes de ejecución por lo que tenemos que buscar el equilibrio para mantenerlas actualizadas sin provocar un exceso de recompilaciones de los planes en caché.

Chequeo de integridad de las bases de datos

La integridad de los datos es un aspecto fundamental en la administración de bases de datos. Gracias a las herramientas de SQL Server, como DBCC CHECKDB, vamos a poder verificar la consistencia física de las bases de datos y de sus datos internos. Esta verificación es crucial para identificar y corregir errores en las estructuras de datos, que pueden surgir por diversas razones, desde fallos del hardware de almacenamiento hasta errores humanos o de consistencia en los datos.

Realizar verificaciones de integridad de manera regular ayuda a detectar problemas antes de que afecten la disponibilidad del sistema o provoquen pérdidas de datos. En caso de encontrarnos con corrupción, deberemos tomar acciones inmediatamente para reparar los daños antes de que puedan ir a más. Para esto intentaremos usar las herramientas disponibles como DBCC CHECKDB y, si no es posible restauraremos los datos desde una copia de seguridad anterior al incidente. 

Una vez resuelta la corrupción tendremos que buscar el origen del problema para evitar que se vuelva a repetir. Las causas más comunes de corrupción son fallos en el almacenamiento o interrupciones inesperadas del servicio (como fallos de alimentación). Es común encontrarse con problemas de corrupción también en instalaciones en sistemas operativos de escritorio que, para ahorrar energía, desconectan la alimentación de los discos duros. Esta configuración debe ser deshabilitada si se instala SQL Server en un sistema operativo de escritorio.

Copias de seguridad como medida de protección esencial

Las copias de seguridad deberían ser la piedra angular de cualquier estrategia de administración de bases de datos. Gracias a estas copias vamos a poder recuperar datos en caso de fallos o desastres, asegurando la continuidad del negocio. Es crucial diseñar un plan de copias de seguridad que considere la frecuencia de los respaldos, la retención y el almacenamiento seguro de los mismos.

Como ya vimos en el artículo que les dedicamos, existen diferentes tipos de copias de seguridad, como completas, diferenciales y de log de transacciones, cada una con su propia función y aplicabilidad. También es vital que realicemos pruebas de restauración periódicas para asegurarnos de que los procedimientos de recuperación funcionarán correctamente cuando sean necesarios. Además, es aconsejable que almacenemos las copias de seguridad en ubicaciones seguras y, si es posible, en lugares distintos geográficamente para protegernos contra desastres locales.

Soluciones de mantenimiento para bases de datos en SQL Server

Para que podamos gestionar eficazmente el mantenimiento de bases de datos en SQL Server podemos contar con herramientas y soluciones que automatizan y optimizan estas tareas. SQL Server ofrece varias herramientas nativas, mientras que otros desarrolladores han creado soluciones adicionales que pueden complementar o incluso superar las capacidades integradas. 

Herramientas nativas de SQL Server para el mantenimiento

SQL Server incluye varias características y herramientas integradas que facilitan la gestión del mantenimiento de bases de datos. Entre ellas, el Mantenimiento de Bases de Datos (Database Maintenance) y el Asistente para Mantenimiento (Maintenance Plan Wizard) son particularmente útiles para automatizar tareas como la reorganización y reconstrucción de índices, la actualización de estadísticas, la verificación de integridad y la realización de copias de seguridad.

Estas herramientas nos permiten a los DBAs configurar trabajos programados (jobs) de manera sencilla, utilizando una interfaz gráfica o scripts Transact-SQL. Sin embargo, aunque son bastante flexibles y suficientes para escenarios no muy complejos, es común encontrarnos con que estas herramientas pueden estar limitadas en cuanto a personalización y control exhaustivo sobre nuestras tareas de mantenimiento.

Soluciones avanzadas de Ola Hallengren

Una de las soluciones más reconocidas y ampliamente utilizadas en la comunidad SQL Server es el conjunto de scripts de mantenimiento desarrollado por Ola Hallengren. Estos scripts son muy configurables y nos proporcionan una solución completa para el mantenimiento de índices, copias de seguridad y verificación de integridad. De esta manera, nos permiten adaptar nuestras tareas de mantenimiento a las necesidades específicas de cada entorno.

Yo siempre recomiendo los scripts de Ola Hallengren ya que destacan por su eficiencia frente a las soluciones nativas. Además tienen una integración con el agente de SQL Server que nos facilita la programación y monitorización de las tareas de mantenimiento. Por último, pero no menos importante, estos scripts son gratuitos y se actualizan regularmente, lo que los convierte en una opción robusta y confiable.

Conclusión

Un mantenimiento adecuado y constante de las bases de datos en SQL Server es esencial para garantizar su rendimiento, disponibilidad y seguridad. La gestión eficiente de índices, la actualización estadísticas, las verificaciones regulares de integridad y una estrategia eficaz de copias de seguridad son pilares fundamentales en esta tarea. Adoptar un enfoque proactivo y planificado nos permite evitar problemas antes de que se conviertan en crisis, asegurando que nuestras bases de datos funcionen de manera óptima y que los datos estén siempre disponibles y protegidos.

 

Publicado por Roberto Carrancio en Cloud, Rendimiento, SQL Server, 0 comentarios
07
Ago
2024

Reducir bases de datos (shrink)

En nuestro pasado artículo hablamos del crecimiento de las bases de datos. Entre otras cosas, os expliqué las diferencias entre espacio utilizado y espacio total del fichero y a que eran debidas. También vimos los errores más comunes que nos pueden llevar a que nuestras bases de datos crezcan más de lo necesario. En el artículo de hoy vamos a ver las técnicas de reducción de espacio de una base de datos con la operación shrink. El objetivo es que entendamos cuándo y por qué usar esta herramienta y si tienes que usarla, al final del artículo te diré cómo.

Espacio ocupado y utilizado

Como explicamos en el pasado artículo, aunque muy por encima, el espacio que ocupan nuestras bases de datos SQL Server en disco no es el tamaño real que tienen ocupado los datos. Por temas de rendimiento, el espacio que se asigna al fichero es mayor, o debería serlo, al que realmente ocupan los datos. Esto es así porque los tiempos de las operaciones de escritura podemos dividirlos en dos, por un lado reservar el espacio necesario en disco para los datos y por último escribir los datos. Aunque con técnicas como la inicialización instantánea de ficheros podemos agilizar mucho el proceso de crecimiento de los ficheros, si el espacio ya está previamente reservado, es decir, ocupado por el fichero pero no utilizado, será mucho más rápido el proceso de escritura. 

Reducir una base de datos

Cuando hablamos de reducir una base de datos, lo único que vamos a poder hacer sin eliminar datos es liberar ese espacio que tenía el fichero reservado pero no en uso. Es decir, reducir el tamaño del fichero hasta como mínimo el tamaño máximo de los datos. En el pasado artículo ya hablamos de las técnicas para que los datos ocupen menos y esto nos puede servir para reducir su tamaño, sin embargo, ninguna de estas técnicas va a reducir por sí misma el tamaño de los ficheros, para eso tenemos que recurrir a una reducción de ficheros o de base de datos también conocida como shrink.

¿Qué es el shrink?

El shrink es un proceso mediante el cual se reduce el tamaño físico de un archivo de base de datos, ya sea de datos o de log. Este comando libera espacio no utilizado y lo devuelve al sistema operativo. Aunque a primera vista puede parecer una herramienta útil para gestionar el espacio en disco, es fundamental entender cómo funciona realmente y los efectos que puede tener en el rendimiento de la base de datos. Empecemos por el principio, podemos hacer un shrink de toda la base de datos o de solo unos ficheros (ya sean de datos o de log), esta última opción es la más recomendable ya que nos permite un mayor control sobre la operación. 

Tipos de Shrink

Por otro lado, existen tres tipos de shrink, debido a que solo se puede liberar el espacio libre al final del fichero nos encontramos con un shrink truncateonly donde solo se libera ese espacio libre al final del fichero pero el espacio que hay entre los datos (fragmentación) sigue ocupado, este tipo de shrink es el más rápido y menos invasivo. 

El tipo más efectivo para liberar espacio es cuando definimos un tamaño que será el tamaño total que ocupe el fichero tras el proceso, en este caso, para conseguir liberar todo el espacio, SQL Server moverá físicamente los datos dentro del fichero eliminando los espacios libres y dejándolos al final para así poder liberar todo ese espacio. Esta operación en ficheros de datos es complicada, no solo por lenta, sino porque desfragmenta los índices. Al mover los datos de sitio los índices ya no apuntan al sitio correcto y hay que reconstruirlos después o no serán usables. El problema con esto, es que al reconstruir los índices, SQL Server crea un nuevo índice y al terminar borra el antiguo y si, durante este proceso vuelve a consumir espacio del fichero de datos. En concreto el espacio que ocupa el índice. 

Por último, cuando tenemos varios ficheros de base de datos del mismo filegroup podemos hacer un shrink en uno de ellos que lo vacíe por completo moviendo los datos al resto de ficheros. Esto también nos va a generar los problemas de fragmentación comentados anteriormente.

Ventajas del Uso del Shrink

Una de las ventajas más obvias del shrink es la liberación de espacio en disco. Esto nos será especialmente útil en situaciones donde el almacenamiento sea limitado o costoso. Además, el shrink puede ser una solución rápida para situaciones de emergencia en las que se necesita liberar espacio inmediatamente. Podemos ejecutar un shrink de forma fácil, incluso podemos programarlo en jobs para que se ejecute automáticamente. Incluso podemos configurar nuestras bases de datos para que liberen el espacio libre por defecto de manera automática.

Otra ventaja es que el shrink puede ayudar en la gestión de archivos de log de transacciones, que a menudo crecen considerablemente en bases de datos con muchas transacciones. En este contexto, el shrink puede reducir el tamaño de estos archivos después de una copia de seguridad del log, evitando así que ocupen espacio innecesario. En este mismo contexto, podemos hacer un shrink sobre la base de datos tempdb cuando haya crecido más de la cuenta debido a una transacción grande.

Inconvenientes y Riesgos del Shrink

A pesar de sus ventajas aparentes, como ya hemos comentado, nos podemos encontrar con muchos inconvenientes si ejecutamos un shrink sin ser cuidadosos. Uno de los principales problemas es la fragmentación de los índices. El proceso de shrink puede reorganizar los datos de manera que los índices queden fragmentados, lo que puede degradar significativamente el rendimiento de las consultas. La fragmentación aumenta el tiempo de respuesta de las consultas y la carga sobre el sistema, lo cual es contraproducente en entornos de alta demanda.

Además, el shrink es un proceso que consume recursos, muchos recursos. Durante su ejecución, puede aumentar la carga de trabajo del servidor, afectando a otras operaciones. Tendremos que tener un especial cuidado en bases de datos grandes o en sistemas con recursos limitados. También es importante mencionar que el espacio liberado por el shrink no se puede recuperar sin expandir el archivo nuevamente, lo cual podría ser necesario si la base de datos vuelve a crecer rápidamente.

Prácticas Recomendadas para el Uso del Shrink

Dado que el shrink puede tener efectos adversos en el rendimiento, es crucial seguir ciertas prácticas recomendadas para minimizar sus desventajas. Primero, no debemos usar el shrink como una solución de gestión de espacio a largo plazo. Normalmente si ya hemos utilizado un espacio vamos a necesitarlo en un futuro. Por tanto, es mejor emplearlo en situaciones específicas, como después de la eliminación masiva de datos o en la gestión de archivos de log de transacciones. Como ya habrás adivinado, cuando hacemos un shrink del log de transacciones o de la tempdb no vamos a vernos afectados por la fragmentación de índices.

También es recomendable realizar el shrink en momentos de baja actividad para minimizar el impacto en el rendimiento del sistema. Posteriormente, deberemos realizar una reconstrucción de índices para solucionar la fragmentación causada por el proceso de shrink. Esto ayuda a restaurar el rendimiento óptimo de las consultas.

Por último, debemos tener una previsión del espacio que van a necesitar nuestros ficheros de bases de datos y reducirlos siempre dejando el suficiente espacio libre para unas escrituras óptimas.

Alternativas al Shrink

El shrink más eficiente es el que no se hace. En este sentido, existen alternativas que pueden ser más adecuadas en ciertas circunstancias. La gestión proactiva del espacio, como la eliminación de datos obsoletos o el archivo de datos antiguos, puede reducir la necesidad de realizar un shrink. Además, la implementación de prácticas de mantenimiento regulares, como el reindexado y la actualización de estadísticas, puede ayudar a mantener la base de datos en buen estado sin los efectos adversos del shrink.

Conclusión

El shrink en SQL Server es una herramienta que debe ser utilizada con precaución y entendimiento. Aunque puede ofrecer una solución rápida para liberar espacio en disco, sus efectos secundarios, como la fragmentación de índices y el consumo de recursos, deben ser cuidadosamente gestionados. Es fundamental considerar el shrink como una herramienta de última instancia y explorar alternativas y prácticas de mantenimiento regulares para mantener una base de datos saludable y eficiente. La clave está en el balance y en la planificación proactiva, asegurando que el rendimiento y la integridad de los datos no se vean comprometidos.

Si has llegado hasta aquí esperando ver cómo hacer un shrink te diré que este no era el objetivo de este artículo, tienes eso en un vídeo aquí. Hoy quería contarte todo lo que rodea a esta operación, para mi, es más importante saber cuándo y por que hacer un shrink que saber hacerlo.

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, Rendimiento, SQL Server, 0 comentarios