Roberto Carrancio

Mi nombre es Roberto Carrancio y soy un DBA de SQL server con más de 10 años de experiencia en el sector. Soy el creador del blog soydba.es donde intento publicar varios artículos a la semana (de lunes a viernes que los fines de semana me gusta estar con mi gente y disfrutar de mi moto) Espero que disfrutes leyendo este blog tanto como yo disfruto escribiendo y que te sea de utilidad. Si tienes alguna sugerencia, pregunta o comentario, puedes dejarlo al final de cada entrada o enviarme un correo electrónico. Estaré encantado de leerte y responderte. ¡Gracias por tu visita! Mi principal interés es compartir mi conocimiento sobre bases de datos con todo el que quiera aprenderlo. Me parece un mundo tan apasionante como desconocido. Fuera de lo profesional me encanta la cocina, la moto y disfrutar de tomar una cervecita con amigos.
Mi nombre es Roberto Carrancio y soy un DBA de SQL server con más de 10 años de experiencia en el sector. Soy el creador del blog soydba.es donde intento publicar varios artículos a la semana (de lunes a viernes que los fines de semana me gusta estar con mi gente y disfrutar de mi moto) Espero que disfrutes leyendo este blog tanto como yo disfruto escribiendo y que te sea de utilidad. Si tienes alguna sugerencia, pregunta o comentario, puedes dejarlo al final de cada entrada o enviarme un correo electrónico. Estaré encantado de leerte y responderte. ¡Gracias por tu visita! Mi principal interés es compartir mi conocimiento sobre bases de datos con todo el que quiera aprenderlo. Me parece un mundo tan apasionante como desconocido. Fuera de lo profesional me encanta la cocina, la moto y disfrutar de tomar una cervecita con amigos.
09
Sep
2024

TRUNCATE vs DELETE a fondo

Cuando hablamos de bases de datos, uno de los temas que a menudo genera confusión es la diferencia entre las operaciones TRUNCATE y DELETE en SQL Server. Ambas sirven para eliminar datos, pero lo hacen de maneras fundamentalmente distintas, lo que las hace adecuadas para diferentes escenarios. Seguro que todos habéis oído hablar de que truncate es una operación que no se puede revertir, incluso habréis escuchado que no registra la operación en el log de transacciones. Bien pues eso, como suele pasar con todas estas cosas, tiene matices. Digamos que son verdades a medias que pueden servir para un usuario no tan avanzado pero que, si queremos ir más allá, tenemos que comprender a fondo.

Operaciones DDL y DML 

Para entender las diferencias entre TRUNCATE y DELETE, lo primero que debemos comprender es las categorías a las que pertenecen: TRUNCATE es una operación DDL (Data Definition Language), mientras que DELETE es una operación DML (Data Manipulation Language). Las operaciones DDL, como CREATE, ALTER o DROP, se utilizan para definir o modificar la estructura de las bases de datos y sus objetos. Por el contrario, las operaciones DML, como INSERT, UPDATE y DELETE, se utilizan para manipular los datos que residen dentro de esas estructuras.

La clasificación de TRUNCATE como una operación DDL implica que no solo afecta los datos, sino también la estructura de la tabla de una manera fundamental. Al eliminar datos con TRUNCATE, no se eliminan registros individuales, sino que se vacía la tabla por completo, lo que va a tener un impacto distinto en el rendimiento y el uso de recursos del sistema, entre otras cosas.

TRUNCATE en SQL Server

La operación TRUNCATE en SQL Server es rápida y eficiente cuando se necesita eliminar todos los datos de una tabla. La teoría dice que, a diferencia de DELETE, que borra registros fila por fila y puede generar una gran cantidad de entradas en el log de transacciones, TRUNCATE simplemente resetea las páginas de datos, liberando todo el espacio asociado a los registros en un solo paso. Entendamos esto. 

Si recordáis, cuando hablamos de las estructuras físicas de los datos, os conté que SQL Server almacena los registros en páginas de 8 Kbs. El tamaño de la página en sí no es importante aquí pero si tenemos que entender que estas páginas son exclusivas para una tabla, es decir una tabla puede tener sus datos en varias páginas pero cada página solo va a almacenar datos de una tabla.
Bien, pues con esto en mente, ya podemos entender lo que acabamos de decir, cuando ejecutamos un TRUNCATE, lo que hace el motor de base de datos es buscar todas las páginas de datos de esa tabla y, sin importar lo que haya dentro, eliminarlas directamente. También va a actuar sobre una serie de metadatos en tablas de sistema pero eso lo veremos más adelante, no nos compliquemos ahora. Este comportamiento no solo reduce el uso de log de transacciones, sino que también hace que la operación sea considerablemente más rápida.

Otras consideraciones de TRUNCATE en SQL Server

Otra característica clave de TRUNCATE es que no activa los triggers de la tabla, dado que no se considera una operación sobre registros individuales como ya acabamos de explicar. Sin embargo, hay una excepción importante a esto, aunque TRUNCATE elimina todos los registros, no puede ser utilizado si existen restricciones de integridad referencial, como claves foráneas (Foreign Keys). Para utilizar TRUNCATE en estos casos, primero se deben eliminar o deshabilitar las restricciones referenciales.

DELETE en SQL Server

Por otro lado, la operación DELETE es más versátil, ya que permite eliminar registros de forma selectiva utilizando una cláusula WHERE. Esto la convierte en la mejor opción cuando necesitamos eliminar solo una parte de los datos de una tabla. Además, DELETE es una operación completamente registrada, lo que significa que cada eliminación se registra en el log de transacciones (fila a fila), permitiendo una recuperación granular de los datos si fuese necesario.

Sin embargo, esta granularidad tiene un coste en términos de rendimiento. Al registrar cada eliminación de forma individual, DELETE puede ser significativamente más lento que TRUNCATE cuando se trata de eliminar grandes volúmenes de datos. Además, DELETE activa triggers y respeta las restricciones de integridad referencial, lo que puede añadir complejidad adicional a la operación.

TRUNCATE y DELETE con Campos IDENTITY y Secuencias

Un aspecto crítico al utilizar TRUNCATE y DELETE es cómo cada una de estas operaciones afectan los campos autoincrementales y las secuencias.

Campos IDENTITY

En SQL Server, los campos autoincrementales, conocidos como campos IDENTITY, generan un valor único y creciente para cada nuevo registro. Cuando se utiliza DELETE para eliminar registros, el valor actual de IDENTITY no se ve afectado. Esto significa que, después de una operación de DELETE, el próximo registro insertado continuará con el siguiente valor de IDENTITY, sin importar cuántos registros hayan sido eliminados. Por ejemplo, si el último valor fue 100, el próximo registro será 101, incluso si todos los registros anteriores fueron eliminados.

En cambio, cuando se utiliza TRUNCATE, el comportamiento es diferente. TRUNCATE reinicia el valor de IDENTITY a su valor inicial, generalmente 1. Esto ocurre porque TRUNCATE no solo elimina todos los registros, sino que también resetea el estado interno de la tabla, incluyendo los contadores incrementales (una de esas operaciones de metadatos de las que hablábamos antes). Esta diferencia es crucial en escenarios donde el mantenimiento del orden de IDENTITY es importante.

Secuencias

Las secuencias en SQL Server, a diferencia de los campos IDENTITY, son objetos independientes que generan valores únicos secuenciales que pueden ser utilizados en múltiples tablas. Cuando se utiliza DELETE o TRUNCATE, las secuencias no se ven afectadas directamente, ya que el valor generado por una secuencia no está ligado al contenido de una tabla específica. Por esta razón, si queremos reiniciar una secuencia, esto debe hacerse explícitamente utilizando una instrucción ALTER SEQUENCE. 

Permisos Necesarios para TRUNCATE y DELETE

Otra diferencia fundamental entre TRUNCATE y DELETE es el nivel de permisos requerido para ejecutar cada operación.

Para ejecutar una operación DELETE, un usuario necesita permisos de DELETE en la tabla en cuestión. Estos permisos son relativamente comunes y se pueden otorgar a través de roles estándar como db_datawriter o directamente a nivel de tabla. Dado que DELETE es una operación DML, no requiere permisos adicionales sobre la estructura de la tabla ni afecta los metadatos de la base de datos.

Por otro lado, TRUNCATE es una operación DDL, lo que significa que requiere permisos más elevados. Para ejecutar TRUNCATE, el usuario necesita permisos de ALTER en la tabla, ya que la operación afecta tanto la estructura de la tabla como los datos. Además, si la tabla está involucrada en restricciones de integridad referencial, el usuario también necesitará permisos adicionales para manejar esas relaciones. Tenemos que tener muy en cuenta este requisito de permisos más elevados en entornos donde deseamos limitar el acceso a operaciones que pueden alterar significativamente la estructura de la base de datos.

Rollback en TRUNCATE y DELETE

Llegamos a lo que todos estabais esperando, la capacidad de hacer rollback y deshacer una transacción de borrado. Como hemos visto hasta ahora, DELETE va a registrar cada una de las filas borradas como una operación en el log de transacciones por lo que podemos revertir la operación, teóricamente incluso parcialmente. Pero, ¿y qué pasa con TRUNCATE? ¿Es cierto que no se puede hacer ROLLBACK? Seguramente creas que no se puede, porque eso es lo que te han contado. Pero esa afirmación viene de una inexactitud que quiero aclarar aquí y ahora. Para ello abre un poco tu mente que voy a contarte esos matices de los que hablaba en la introducción. Mira la siguiente captura:

TRUNCATE

¿Qué ha pasado aquí? Esto no era lo que te habían explicado. He creado una tabla e insertado tres registros. Luego en una transacción he hecho un TRUNCATE y la tabla se ha borrado pero, cuando he hecho un ROLLBACK, los datos seguían ahí. Lo primero que tenemos que entender en este punto es como SQL Server gestiona las transacciones, esto es algo que explicamos aquí, por lo que si no lo has leído y el próximo párrafo te suena a chino te recomiendo ir al enlace en este punto y aclarar esos conceptos. 

¿Cuándo no se puede hacer ROLLBACK de un TRUNCATE?

TRUNCATE, aunque no registra fila a fila los borrados, sí que deja rastro en el log, mínimo pero deja rastro. Concretamente registra las páginas que han sido borradas. Incluso las bloquea hasta que la transacción se confirma. Esto quiere decir que si se puede hacer ROLLBACK de un TRUNCATE siempre que la transacción no esté confirmada, es decir, dentro de una transacción implícita o explícita, antes de hacer COMMIT. Gracias a registrar y bloquear las páginas borradas (impedir su borrado o sobrescritura) mientras no haya un COMMIT es posible volver a restaurarlas en caso de ROLLBACK.

Si ahora estás pensando que nada tiene sentido y que te han engañado, tranquilo, no es del todo así. Realmente, en parte, sí que es cierta la afirmación de que no se puede hacer ROLLBACK de un TRUNCATE. Lo que pasa es que solo aplica a deshacer una transacción desde el fichero de log, es decir a restaurar la base de datos a un punto en el tiempo previo a una transacción.

TRUNCATE en Diferentes Sistemas Gestores de Bases de Datos 

Ahora que hemos visto cómo funcionan TRUNCATE y DELETE en SQL Server, es interesante analizar cómo TRUNCATE se comporta en otros sistemas gestores de bases de datos.

Truncate en Oracle

En Oracle, la operación TRUNCATE funciona de manera muy similar a SQL Server. Es una operación DDL que vacía una tabla de forma rápida y eficiente, sin generar una cantidad significativa de entradas en el log de transacciones. Como en SQL Server, TRUNCATE no puede ser utilizado si la tabla tiene claves foráneas activas, lo que exige deshabilitar estas restricciones antes de ejecutar la operación. Sin embargo, Oracle permite la opción de TRUNCATE … CASCADE, que automáticamente trunca las tablas relacionadas que dependan de la tabla principal.

Truncate en MySQL/MariaDB

En MySQL y MariaDB, TRUNCATE también se considera una operación DDL, aunque internamente se ejecuta como un DROP TABLE seguido de un CREATE TABLE para recrear la estructura de la tabla. Este enfoque significa que el rendimiento es similar al de otros sistemas, con la ventaja añadida de que libera de inmediato el espacio de almacenamiento utilizado por la tabla. Por el contrario, este planteamiento también significa que es una operación 100% irreversible, no como lo que acabamos de ver en SQL Server. Además, a diferencia de SQL Server y Oracle, MySQL no permite truncar una tabla si existen tablas que dependen de ella a través de claves foráneas, incluso si esas claves están deshabilitadas.

Truncate en PostgreSQL

En PostgreSQL, TRUNCATE sigue siendo una operación DDL, pero con algunas características adicionales. Además de ser rápido y eficiente, PostgreSQL permite truncar múltiples tablas en una sola operación, lo que puede ser útil en escenarios donde se necesita vaciar varias tablas relacionadas de una sola vez. También incluye la opción CASCADE, que automáticamente trunca todas las tablas relacionadas. Al igual que en otros sistemas, TRUNCATE no activa triggers, lo que lo hace muy útil para operaciones de mantenimiento de bases de datos.

Truncate en Microsoft Fabric

En el caso de Fabric Datawarehouse, un sistema optimizado para grandes volúmenes de datos y cargas de trabajo analíticas, os iba a decir que TRUNCATE no es una operación permitida. Sin embargo, en los últimos días esto ha cambiado y ahora sí que es posible hacerlo. Aunque es una característica realmente nueva de la que aún hay poca información, TRUNCATE en los warehouse de Fabric se comporta de manera similar a otros sistemas, pero con algunas consideraciones adicionales.

Dado que Fabric Datawarehouse está diseñado para gestionar grandes cantidades de datos de manera eficiente, TRUNCATE es especialmente útil para operaciones de limpieza y reinicialización de tablas. Sin embargo, no pierde la esencia de un almacén de datos por lo que, lo que TRUNCATE, es este caso, es una operación sólo de metadatos. Escribe un nuevo registro delta eliminando todos los archivos parquet existentes. De esta manera es mucho más rápido que DELETE, pero preserva la historia de la tabla, a diferencia de CTAS/DROP/RENAME.

 

Conclusión

Aunque TRUNCATE registra menos información en el log de transacciones que DELETE, registra lo suficiente para permitirnos un rollback completo dentro de una transacción implícita o explícita. Este equilibrio entre un registro mínimo y la capacidad de revertir la operación hace de TRUNCATE una opción muy interesante para eliminar rápidamente grandes volúmenes de datos. Sin embargo, al usarlo debemos extremar las precauciones, ya que la reversión es todo o nada, y siempre debemos tener en cuenta que los permisos necesarios son más elevados.

En resumen, TRUNCATE ofrece una opción rápida y efectiva para la limpieza de tablas, siempre que comprendamos sus implicaciones y limitaciones en el contexto de transacciones y logs. Si la prioridad es el rendimiento y se necesita vaciar una tabla por completo sin preocuparse por las restricciones de integridad referencial, TRUNCATE es la opción más adecuada. En cambio, si se requiere eliminar registros selectivos o si existen dependencias de claves foráneas que no pueden ser deshabilitadas, DELETE será la operación preferida.

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

Autenticación en SQL Server

Hace unos días hablábamos sobre la diferencia entre los inicios de sesión y los usuarios en SQL Server y Azure SQL y pudimos ver cómo teníamos a nuestro alcance varios métodos de autenticación para iniciar sesión. Como vimos los logins podían ser usuarios nativos de SQL Server o heredados del dominio de Windows o de Azure (lo que se conoce como Entra ID y antes como Azure Active Directory). Esta autenticación es un aspecto crítico para los sistemas de datos, la seguridad de nuestros datos depende en gran medida de cómo controlamos el acceso a ellos. 

En este artículo, vamos a explorar en profundidad los distintos modos de autenticación disponibles en SQL Server y Azure SQL. Por un lado, tendremos los métodos tradicionales como la autenticación de SQL Server y Windows, y por otro lado, la integración con Azure Entra ID (anteriormente conocido como Azure Active Directory). También quiero entrar en los protocolos subyacentes, Kerberos y NTLM, sobre los que se basan estos métodos de autenticación.

Modos de Autenticación en SQL Server y Azure SQL

SQL Server y Azure SQL ofrecen varias opciones para autenticar usuarios. Cada una de estas opciones está diseñada para escenarios específicos, y la elección de un método sobre otro puede tener implicaciones significativas en la seguridad y la facilidad de administración.

Autenticación de SQL Server

La autenticación de SQL Server es probablemente el método más tradicional. Se basa en un sistema interno de gestión de inicios de sesión y contraseñas dentro de SQL Server. Este método no depende del sistema operativo ni de ningún servicio externo para validar las credenciales, lo que significa que los usuarios y las contraseñas se gestionan directamente en el propio motor de bases de datos. Este tipo de autenticación es ideal en escenarios donde necesitamos un control granular sobre el acceso de usuarios que no están en nuestro dominio de Windows o cuando estamos trabajando en entornos mixtos.

Autenticación de Windows

La autenticación de Windows permite a los usuarios autenticarse usando sus credenciales de dominio de Windows. Este método es mi preferido en entornos donde se utiliza Active Directory para gestionar usuarios y grupos. La principal ventaja de la autenticación de Windows es que podemos integrar sin problemas SQL Server en un entorno de seguridad ya establecido. Además nos permite hacer uso de los grupos de Directorio Activo, lo que nos puede simplificar enormemente la tarea y aumentar la seguridad. Por último, otra ventaja es que no solo simplifica la gestión de credenciales, sino que también permite aprovechar protocolos de seguridad avanzados como Kerberos, lo que añade una capa adicional de protección como veremos más adelante.

Autenticación con Azure Entra ID

Con la aparición de servicios en la nube, la autenticación a través de Azure Entra ID se ha convertido en una opción cada vez más importante, especialmente para entornos de Azure SQL. Este método permite a los usuarios autenticarse mediante sus credenciales de Azure Entra ID, lo que facilita una integración más fluida con otros servicios de Azure. Además, permite implementar las características de seguridad avanzadas de Azure como la autenticación Multifactor (MFA) y las políticas de acceso condicional, que no son posibles con los métodos tradicionales de autenticación.

Protocolos de Autenticación: Kerberos y NTLM

Al hablar de autenticación en entornos Windows, es fundamental entender los protocolos que operan en segundo plano. Kerberos y NTLM son los dos principales protocolos de autenticación utilizados, cada uno con características y aplicaciones distintas. A pesar de que ambos sirven para el mismo propósito, sus diferencias son significativas, especialmente en términos de seguridad y rendimiento. Para tomar decisiones informadas sobre cuál utilizar, es esencial comprender cómo funcionan y en qué se distinguen.

NTLM

NTLM (NT Lan Manager) es un protocolo de autenticación desarrollado por Microsoft que ha estado en uso desde la era de Windows NT. A pesar de su antigüedad y las limitaciones de seguridad que presenta, NTLM sigue siendo utilizado en escenarios donde Kerberos no está disponible o no puede ser implementado, como en redes que no están unidas a un dominio.

El proceso de autenticación con NTLM es relativamente sencillo pero menos seguro que Kerberos. NTLM se basa en un desafío-respuesta (challenge-response), donde el cliente primero establece una conexión con el servidor y envía el nombre de usuario. El servidor genera un valor de desafío (un número aleatorio) que se envía al cliente. El cliente, a su vez, cifra este desafío utilizando un hash de la contraseña del usuario y lo envía de vuelta al servidor. El servidor compara este resultado con lo que esperaba y, si coinciden, se concede el acceso.

Autenticacion-NTLM

Una de las principales limitaciones de NTLM es la falta de autenticación mutua. Es decir, mientras que el servidor autentica al cliente, el cliente no autentica al servidor, lo que deja abierta la posibilidad de ataques como el «man-in-the-middle». Además, NTLM es vulnerable al ataque de «pass-the-hash», donde un atacante puede reutilizar el hash de la contraseña para acceder a recursos sin conocer la contraseña original.

Autenticación NTLM en SQL Server

En SQL Server, NTLM se utiliza principalmente cuando Kerberos no está configurado correctamente o cuando la conexión se realiza en un entorno de trabajo que no soporta Kerberos, como un grupo de trabajo (WORKGROUP) en lugar de un dominio. También, cuando accedamos desde el propio servidor local y no por la red, SIEMPRE se va a usar NTLM. Por último, es común ver NTLM en escenarios legacy, donde las aplicaciones antiguas no son compatibles con Kerberos.

Kerberos

Kerberos es un protocolo de autenticación mucho más avanzado que NTLM, introducido en Windows 2000. Basado en un sistema de «tickets», Kerberos no solo ofrece mayor seguridad, sino también un rendimiento mejorado en comparación con NTLM.

Kerberos opera utilizando un tercero de confianza, conocido como el Key Distribution Center (KDC), que emite tickets de autenticación. El proceso comienza cuando un usuario solicita acceso a un servicio. El cliente primero se autentica ante el KDC, que le proporciona un Ticket Granting Ticket (TGT). Este TGT permite al usuario solicitar tickets de servicio (Service Tickets) para acceder a diferentes recursos en la red. Cada ticket de servicio se presenta al servidor para establecer la autenticación, y dado que estos tickets están cifrados, Kerberos ofrece una mayor protección contra ataques.

Autenticacion-Kerberos

Una de las características más importantes de Kerberos es la autenticación mutua, donde tanto el cliente como el servidor validan las identidades del otro, reduciendo significativamente el riesgo de ataques de suplantación de identidad. Además, Kerberos es más eficiente en términos de red y recursos, ya que no requiere múltiples rondas de comunicación para completar la autenticación como en NTLM.

Autenticación Kerberos en SQL Server

En SQL Server, Kerberos es el protocolo que se usa preferentemente siempre y cuando esté disponible, es decir, configurado correctamente en un entorno de dominio de Active Directory. Esto no solo mejora la seguridad, sino que también nos permite el uso de características avanzadas como la delegación de autenticación, que es esencial para aplicaciones que requieren pasar las credenciales de usuario a través de múltiples capas de servicios. Sin esta delegación de autenticación, por ejemplo, es imposible “saltar” desde tu ordenador local a un servidor vinculado configurado en tu servidor SQL con el inicio de sesión de Active Directory.

Comparación: NTLM vs Kerberos

Cuando comparamos NTLM y Kerberos en entornos SQL Server, es evidente que Kerberos ofrece numerosas ventajas, tanto en términos de seguridad como de eficiencia. Sin embargo, la elección entre uno y otro puede depender de varios factores, incluidos los requisitos del entorno y la compatibilidad de las aplicaciones. 

Seguridad

Kerberos es claramente superior en términos de seguridad. La autenticación mutua y el uso de tickets cifrados hacen que Kerberos sea menos susceptible a los ataques de red comunes. Por el contrario, NTLM, con su enfoque de desafío-respuesta y su vulnerabilidad al ataque pass-the-hash, es menos seguro y, por lo tanto, menos adecuado para entornos donde la seguridad es una preocupación primordial.

Rendimiento

En términos de rendimiento, Kerberos también tiene la ventaja. Al usar tickets, Kerberos reduce la carga de comunicación y mejora la eficiencia de la autenticación, especialmente en redes grandes con muchos usuarios y servicios. NTLM, aunque funcional, puede generar una carga adicional en la red debido a la necesidad de múltiples rondas de autenticación.

Compatibilidad y Configuración

A pesar de sus ventajas, Kerberos requiere una configuración más compleja y solo funciona en entornos de dominio de Active Directory. NTLM, aunque menos seguro, es más sencillo de implementar y funciona en una gama más amplia de escenarios, incluidos aquellos que no están dentro de un dominio.También deberemos tener en cuenta la complejidad extra de configuración de un entorno compatible con Kerberos donde tendremos que registrar correctamente los SPN (Service Principal Names) cuando usemos una cuenta de servicio que no sea la por defecto en una instancia por defecto y siempre que usemos instancias con nombre.

Conclusión

La elección del modo de autenticación en SQL Server o Azure SQL no es trivial y debe basarse en las necesidades específicas de seguridad y administración de cada entorno. La autenticación de SQL Server proporciona flexibilidad en escenarios específicos, mientras que la autenticación de Windows y Azure Entra ID ofrecen ventajas significativas en términos de seguridad y facilidad de gestión. Además, el entendimiento de los protocolos subyacentes como Kerberos y NTLM nos permite tomar decisiones más informadas sobre cómo proteger nuestras bases de datos contra amenazas externas. Al final, lo más importante es elegir el método de autenticación que no solo se alinee con las políticas de seguridad de la organización, sino que también se adapte a las características y necesidades del entorno en el que operamos.

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, 1 comentario
02
Sep
2024

Fin de soporte de SQL Server 2019, ¿estamos preparados?

Empieza septiembre, se acaban las vacaciones de verano y, con la vuelta a la oficina, es momento de empezar a prepararnos para un hito importante para todos los que trabajamos con bases de datos: el fin del ciclo de vida de SQL Server 2019. Será el 28 de febrero de 2025 y, aunque parece una fecha aún lejana, lo cierto es que el tiempo para planificar y llevar a cabo una migración es elevado y, teniendo en cuenta que en muchos casos hablamos de varias instancias para migrar, el tiempo es más bien justo.

Como decía, esta fecha pone fin al soporte técnico y a las actualizaciones que no sean de seguridad para esta versión de SQL Server, lo que implica una necesidad urgente de evaluar nuestras estrategias de migración y considerar futuras implementaciones. Este evento no solo nos afecta desde un punto de vista operativo, sino que también abre la puerta a nuevas oportunidades y desafíos en el mundo de la gestión de datos.

Qué significa el fin del ciclo de vida de SQL Server 2019

Cuando hablamos del fin del ciclo de vida de un producto, en este caso SQL Server 2019, nos referimos al momento en el que Microsoft deja de ofrecer soporte técnico, actualizaciones de corrección de errores y mejoras. A partir de esa fecha, cualquier fallo que se descubra en SQL Server 2019 no será corregido por Microsoft, lo que nos deja expuestos a posibles riesgos. Además, sin soporte oficial, la resolución de problemas técnicos puede volverse mucho más complicada y costosa. Por suerte aún nos queda un plazo llamado soporte extendido en el que Microsoft sigue proporcionando actualizaciones de seguridad pero, no deberíamos recurrir a esto por los riesgos antes mencionados.

El fin del ciclo de vida de SQL Server 2019 no debería sorprendernos, ya que forma parte del ciclo natural de cualquier software. Sin embargo, la realidad es que muchos entornos productivos aún están basados en versiones que están próximas a quedar obsoletas, lo que nos obliga a actuar con rapidez para asegurar la continuidad de nuestros sistemas.

Planificación para la migración: ¿Hacia dónde debemos movernos?

La migración de SQL Server 2019 a versiones más recientes o a otros sistemas no es una tarea trivial. Requiere de una planificación meticulosa, que debe incluir una evaluación detallada de las aplicaciones y bases de datos que dependen de SQL Server 2019, así como una comprensión clara de las mejoras y cambios que ofrecen las versiones más nuevas. Ya dedicamos un artículo completo a planificar una migración que os recomiendo leer.

El paso lógico sería migrar a SQL Server 2022 que, no solo ofrece nuevas características de seguridad y rendimiento, sino que también está diseñada para integrarse más eficazmente con los servicios en la nube de Azure. Además, SQL Server 2022 mejora la integración con servicios de análisis avanzados y ofrece un mejor soporte para grandes volúmenes de datos.

También podríamos migrar nuestras bases de datos a la nube de Azure, ya sea a bases de datos de Azure SQL o a una instancia administrada de SQL en Azure. Cualquiera de estas dos soluciones SAAS son una buena opción y la elección entre una y otra deberá ser en base a las características y limitaciones de cada una de ellas. Podéis ampliar información sobre este tema en este otro artículo.

Para aquellos que buscáis alternativas más allá del ecosistema de Microsoft, también es un buen momento para considerar otras opciones de bases de datos, tanto relacionales como no relacionales. Las bases de datos en la nube, como Amazon RDS o Google Cloud SQL, ofrecen opciones robustas y escalables que pueden ser atractivas para ciertos entornos empresariales. También se puede considerar la transición a bases de datos open-source como PostgreSQL o MySQL, que han ganado popularidad en los últimos años gracias a su flexibilidad y bajo coste.

El futuro de los clústeres de Big Data en SQL Server

Uno de los aspectos más significativos que desaparece con el fin del ciclo de vida de SQL Server 2019 es el soporte para los clústeres de Big Data, una característica que se introdujo con esta versión. Estos clústeres permitían a las organizaciones gestionar grandes volúmenes de datos utilizando una combinación de tecnologías SQL y NoSQL, incluyendo HDFS (Hadoop Distributed File System) y Spark. Aunque los clústeres de Big Data en SQL Server ofrecían una solución integrada para la gestión de grandes volúmenes de datos, la realidad es que su adopción ha sido limitada.

Con el fin del soporte, es crucial que consideremos qué alternativas existen para gestionar grandes volúmenes de datos en el futuro. Aquí es donde entran en juego otras tecnologías, como Azure Synapse Analytics, que combina la integración de datos, el análisis de grandes volúmenes de datos y la inteligencia artificial en una única solución. Azure Synapse ofrece un entorno mucho más flexible y escalable que los clústeres de Big Data de SQL Server, y es capaz de manejar no solo datos estructurados, sino también semiestructurados y no estructurados.

Otra opción a considerar es la adopción de arquitecturas de datos modernas basadas en el concepto de data lakehouse, que combina lo mejor de los data lakes y los data warehouses. Esta arquitectura es cada vez más popular, ya que permite almacenar grandes volúmenes de datos sin necesidad de preocuparse por la estructura de estos datos desde el principio. Además, soluciones como Databricks y Snowflake están ganando terreno como alternativas viables para la gestión y análisis de grandes volúmenes de datos.

Retos y oportunidades al abandonar SQL Server 2019

La transición hacia nuevas versiones de SQL Server o la adopción de nuevas tecnologías de gestión de datos no está exenta de desafíos. Uno de los principales retos que enfrentaremos será la necesidad de garantizar la compatibilidad de las aplicaciones existentes con las nuevas plataformas. Muchas aplicaciones empresariales están diseñadas y optimizadas para versiones específicas de SQL Server, lo que puede hacer que la migración requiera no solo la migración de SQL Server por nuestra parte sino que los equipos de desarrollo lleven a cabo una reescritura significativa del código o incluso una reingeniería completa de las aplicaciones.

Otro reto importante es la gestión de los datos históricos. Las organizaciones que han acumulado grandes volúmenes de datos a lo largo de los años deberán planificar cómo migrar estos datos de manera eficiente y sin pérdida de información. La migración de grandes volúmenes de datos puede ser un proceso largo y costoso, que debemos planificar y ejecutar de forma cuidadosa.

Sin embargo, junto con estos retos, también se presentan nuevas oportunidades. La adopción de nuevas tecnologías de bases de datos y análisis de datos nos permite aprovechar las últimas innovaciones en el campo de la inteligencia artificial y el machine learning. Estas tecnologías nos ofrecen la capacidad de analizar datos en tiempo real, identificar patrones y tomar decisiones informadas de manera más rápida y precisa.

Conclusión

El fin del ciclo de vida de SQL Server 2019 es un recordatorio claro de la naturaleza en constante evolución de la tecnología. Aunque la transición a nuevas versiones o tecnologías puede parecer desafiante, es también una oportunidad para mejorar nuestras capacidades de gestión de datos, optimizar nuestros sistemas y estar mejor preparados para enfrentar los desafíos del futuro.

Es crucial que no dejemos esta planificación para el último momento. Identificar nuestras necesidades, evaluar nuestras opciones y comenzar a implementar las soluciones más adecuadas garantizará que nuestras operaciones continúen sin interrupciones. En este entorno tan dinámico, estar un paso adelante no solo es una ventaja, sino una necesidad para seguir siendo competitivos en la gestión de datos.

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, 1 comentario
30
Ago
2024

Log de errores de SQL Server

Si hay una herramienta imprescindible para un administrador de bases de datos esa es, sin duda, el log de errores. Este archivo nos proporciona un registro detallado de los eventos que ocurren en el sistema, permitiéndonos identificar problemas, realizar diagnósticos precisos y, en definitiva, mantener la estabilidad y el rendimiento de nuestras instancias de SQL Server. Sin embargo, para aprovechar al máximo esta herramienta, es fundamental comprender cómo configurarla adecuadamente, cómo interpretar la información que nos ofrece y qué hacer cuando necesitamos reiniciarla. En este artículo, profundizaremos en estos aspectos para que podamos sacarle todo el partido posible al log de errores.

¿Qué es el log de errores de SQL Server?

El log de errores de SQL Server es un archivo que recoge información relevante sobre los eventos que ocurren en la instancia de SQL Server. Este log incluye desde mensajes informativos y advertencias hasta errores críticos que pueden afectar el rendimiento o la disponibilidad del servidor. Es un recurso de gran valor para nosotros, ya que nos proporciona un historial detallado de la actividad del servidor, incluyendo fallos de autenticación, problemas de conectividad, errores de bases de datos y cualquier otra incidencia relevante que se produzca durante la operativa normal de SQL Server.

Configuración del log de errores de SQL Server

El log de errores de SQL Server se configura automáticamente durante la instalación, pero esto no significa que no podamos ajustar sus parámetros para adaptarlos a nuestras necesidades. Uno de los primeros aspectos que debemos considerar es el número de archivos de log que SQL Server retiene. Por defecto, se guardan 6 archivos de log, pero este número puede modificarse según lo que consideremos más adecuado para nuestra operación. Si necesitamos mantener un historial más largo de errores, podemos aumentar este número hasta un máximo de 99. También podemos ajustar el tamaño máximo de los ficheros de log para mantener un control más exhaustivo. Para hacerlo, en SSMS, nos posicionamos sobre la carpeta “Administración”, hacemos clic derecho en la carpeta “Log de SQL Server” y ahí en “Configurar”.

SQL Server Log Configuration

Estos simples ajustes nos permitirán mantener un registro más extenso de la actividad del servidor, lo cual es especialmente útil en entornos con alta criticidad donde los errores pasados pueden ser relevantes para la resolución de incidentes futuros.

Leer el log de errores

Una vez configurado el log, el siguiente paso es saber cómo leerlo e interpretarlo correctamente. SQL Server ofrece varias formas de acceder al contenido del log de errores, siendo la más común a través de SQL Server Management Studio (SSMS). Desde SSMS, podemos encontrar el log de errores en la carpeta de «Administración» y seleccionando «Logs de SQL Server». Aquí podremos ver una lista de los archivos de log disponibles, y al hacer doble clic en uno de ellos, podremos explorar los eventos registrados.

Cada entrada del log está compuesta por una fecha y hora, un nivel de gravedad y un mensaje. La fecha y hora nos indican cuándo ocurrió el evento, mientras que el nivel de severidad nos da una idea de la gravedad del problema. Los mensajes pueden variar en detalle, pero es importante estar atentos a ciertos patrones o palabras clave como «Error», «Failed» o «Severe», que suelen indicar problemas críticos que requieren atención inmediata.

Además de SSMS, también podemos utilizar T-SQL para consultar el contenido del log de errores. Para ello usaremos el procedimiento almacenado xp_readerrorlog. Por ejemplo, el siguiente comando nos muestra los errores más recientes:

EXEC xp_readerrorlog 0, 1, N'Error';

Este comando filtra las entradas del log, devolviendo sólo aquellos registros que contienen la palabra «Error». Es una forma rápida de identificar problemas graves sin necesidad de revisar manualmente cada línea.

Lectura del log en texto plano y acceso desde el sistema de archivos

Si necesitamos acceder al log de errores en texto plano o el servicio de SQL Server no está arrancado, podemos localizar el archivo directamente en el sistema de archivos del servidor.

Esto es especialmente útil cuando nos enfrentamos a una instancia que no arranca y no sabemos por qué ya que SQL Server mientras arranca va dejando registro en el log y ahí es donde podemos encontrar el problema. Incluso no encontrar log del intento de arranque nos va a dar una pista, en concreto que el servicio ni se puede empezar a iniciar lo que, normalmente, es debido a un fallo con la cuenta de servicio.  El archivo se encuentra en la carpeta de instalación de SQL Server, dentro del directorio LOG. La ruta por defecto es de esta carpeta es C:\Program Files\Microsoft SQL Server\MSSQL{NumeroDeVersion}.{NombreInstancia}\MSSQL\Log\. 

Si no lo tenemos claro, podemos buscar la ruta de logs en el servicio de SQL Server. En concreto, si abrimos las propiedades del servicio de SQL Server en el administrador de configuración de SQL Server y nos vamos a los parámetros de inicio del servicio vamos a poder ver un parámetro -E con la ruta del log de errores.

SQL Server Log Path

En este directorio, encontraremos el archivo ERRORLOG, que es el log de errores actual, junto con archivos numerados que representan los logs anteriores (ERRORLOG.1, ERRORLOG.2, etc.). Estos archivos pueden abrirse con cualquier editor de texto, como el Bloc de notas, permitiéndonos revisar los eventos registrados incluso si SQL Server no está en ejecución.

Aspectos críticos a tener en cuenta 

A lo largo de mi experiencia, he aprendido que ciertos eventos en el log de errores requieren una atención especial. Por ejemplo, los errores relacionados con la memoria o el almacenamiento pueden tener un impacto inmediato en el rendimiento del sistema, mientras que los fallos en los trabajos de mantenimiento pueden afectar la integridad de los datos a largo plazo. Por ello, es crucial revisar periódicamente el log en busca de indicios de problemas potenciales, incluso si el sistema parece estar funcionando correctamente.

Otro punto que debería merecer nuestra atención es la repetición de ciertos errores. Un error aislado puede no ser motivo de preocupación, pero si observamos que un mismo mensaje aparece repetidamente, es probable que estemos ante un problema subyacente que requiere investigación y resolución. La repetición de errores de autenticación, por ejemplo, podría indicar intentos fallidos de acceso no autorizado o problemas con la configuración de seguridad.

Mi recomendación en este apartado es que configuréis alertas para los errores más críticos como os expliqué en este otro artículo.

Cómo reiniciar el log de errores de SQL Server

Llega un momento en el que el log de errores puede haberse llenado tanto de información que ya no es relevante, o bien necesitamos limpiar el registro para facilitar el análisis de nuevos eventos. En estos casos, reiniciar el log de errores es una práctica recomendada. Reiniciar el log no elimina los archivos existentes, sino que crea un archivo nuevo, lo que nos permite empezar a registrar eventos desde cero mientras mantenemos un historial accesible. El proceso de reinicio es sencillo y se puede realizar mediante el siguiente comando T-SQL: 

EXEC sp_cycle_errorlog;

Este comando cierra el log de errores actual y crea un nuevo archivo. Es una operación segura que no afecta el rendimiento del servidor, pero debe ser utilizada con precaución, especialmente si estamos en medio de una investigación de errores, ya que el nuevo archivo comenzará a registrar sólo los eventos que ocurran después de la ejecución del comando. Mi recomendación en este sentido es programar este comando en un job que se ejecute de manera mensual o semanal en función del número de eventos que se generen normalmente en nuestro sistema. Esta práctica, junto con una configuración de retención de ficheros acorde a nuestras necesidades, nos va a facilitar mucho la lectura del log en caso de problema.

Conclusión

El log de errores de SQL Server es una herramienta fundamental para los administradores de bases de datos, y su correcta configuración y uso pueden marcar la diferencia entre la detección temprana de un problema y una crisis mayor. Configurar adecuadamente el número de archivos de log, saber cómo leer e interpretar la información, y estar atentos a eventos críticos son prácticas esenciales que no debemos subestimar. Asimismo, el reinicio del log nos permite mantener un registro ordenado y manejable, facilitando la identificación de nuevos eventos. En resumen, dominar el manejo del log de errores de SQL Server es una habilidad indispensable que nos ayudará a mantener la estabilidad y seguridad de nuestras instancias, asegurando un rendimiento óptimo y una operación sin contratiempos.

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