La replicación P2P (peer to peer) apareció en el ya ahora lejano SQL Server 2005 y fue refinada con funcionalidades importantes, como la detección de conflictos, en SQL Server 2008. Desde entonces hasta la actualidad prácticamente no ha tenido mejoras más allá de corrección de bugs y pequeños ajustes para mejorar su compatibilidad con otras características. La replicación en SQL Server hace muchos años que es el “patito feo” desde el punto de vista de marketing pero también por la existencia de herramientas de replicación de terceros más amigables y capaces.

La principal ventaja que aporta P2P respecto a otras soluciones es que cada nodo permite tanto escrituras como lecturas (multi master), aunque debemos tener claro que estas operaciones de escritura se ejecutan en el nodo local y luego, asíncronamente, se replican al resto de nodos. En sistemas de bases de datos distribuidas más modernas como CosmosDB este comportamiento puede ajustarse para cada transacción gestionando el nivel de consistencia que solicitemos al motor, desde Strong hasta Eventual, lo cual permite mucha más flexibilidad.

La adopción de replicación P2P ha sido muy escasa a lo largo de estos años por muchos motivos, pero uno de ellos es que se trata de una característica disponible únicamente en la versión Enterprise. Este coste también causa que software de replicación de terceros (que implica licenciamiento extra) también quede descartado en muchos escenarios. Si nos centramos solo en SQL Server ciertamente en muchas ocasiones la necesidad de replicación es entre dos nodos únicamente, no entre N, por lo que de forma manual, sin requerir Enterprise, podríamos configurar una replicación transaccional bidireccional cruzada entre los nodos. En el caso que necesitáramos más de 2 nodos deberíamos pasar necesariamente por una arquitectura de réplica tipo hub and spoke, de forma que todas las replicaciones bidireccionales sean cruzadas entre dos pares de nodos, con un nodo central común a todos.

En este tipo de replicaciones uno de los principales dolores de cabeza es la gestión de los conflictos, por lo que lo ideal es que contemos con un modelo de base de datos que disminuya al máximo la posibilidad de su ocurrencia. Por ejemplo, si tenemos algún tipo de clave basada en IDENTITY o secuencias, sería muy conveniente que tengamos rangos distintos entre los nodos para evitar colisiones simplemente al insertar nuevos registros al mismo tiempo. Si es previsible que por el funcionamiento de nuestra aplicación van a generarse una cantidad muy elevada de conflictos directamente no recomendaría el uso de este tipo de replicaciones ya que el coste de soporte podría ser absurdamente alto.

La replicación P2P nativamente nos detectará los siguientes conflictos:

  • Insert-insert

Ocurre cuando una fila con una misma clave se inserta en más de un nodo a la vez. Cuando nos referimos “a la vez” a lo que nos referimos es que los insert ocurren durante el periodo de tiempo que existe entre que se sincronizan los nodos entre sí, el cual suele ser de varios segundos. Otro caso menos habitual es que registros con distinta clave primaria sufran un conflicto insert-insert debido a, por ejemplo, alguna constraint UNIQUE extra de otras columnas, como un identificador de tipo documento nacional de identidad, pasaporte, etc.

  • Update-update

Ocurre cuando una misma fila se modifica a la vez en más de un nodo. Si este caso va a ser habitual recordemos que en la replicación de mezcla tendríamos mecanismos para poder mezclar los updates con facilidad y obtener una fila con los cambios aplicados de forma conjunta.

  • Delete-delete

Ocurre cuando una fila se borró en más de un nodo a la vez. Este caso suele ser sencillo de gestionar y puede ser ignorado habitualmente ya que el resultado deseado suele ser que las filas aparezcan borradas en ambos nodos.

  • Insert-update

Ocurre cuando una fila se modifica en un nodo pero la misma fila fue borrada y reinsertada en otro nodo.

  • Insert-delete

Ocurre cuando una fila se borra en un nodo, pero la misma fila fue borrada y reinsertada en otro nodo.

  • Update-delete

Ocurre cuando una fila se actualiza en un nodo y fue borrada en otro nodo.

En el caso de una replicación bidireccional cuando nos encontremos con alguno de estos errores el comportamiento que tendremos será una parada de la replicación con un fallo del agente de distribución al intentar replicar el comando correspondiente. Normalmente cada uno de estos conflictos estará correlacionado con la generación por parte del agente de distribución de un único código de error (algunos casos pueden ser varios errores distintos). Esto nos permite de alguna forma poder gestionar estos conflictos en base a los códigos de error dando lugar a la posibilidad de generar un sistema de detección de conflictos. Este sistema sería menos potente y eficiente que el que tenemos en la replicación P2P (la cual se apoya en la columna oculta $sys_p2p_cd_id) pero a cambio nos permitiría usar una versión SQL Server Standard. Adicionalmente, si lo que deseamos es que nuestra replicación continúe en caso de errores, podremos utilizar el parámetro SkipErrors en el agente de distribución para que, ya sin respetar el comportamiento habitual de una réplica transaccional, podamos continuar replicando el resto de cambios posteriores al que causó el error.

Para clarificar un poco todo esto vamos a mostrar el funcionamiento con un ejemplo práctico. Vamos a configurar 2 bases de datos idénticas con distinto nombre sobre las cuales replicaremos los datos vía replicación transaccional bidireccional. Las bases de datos las vamos a crear sobre una misma instancia, pero el comportamiento sería exactamente el mismo si utilizáramos varias instancias. Sobre dichas bases de datos replicadas vamos a simular conflictos y vamos a analizar cómo podríamos gestionarlos/resolverlos de forma manual.

El primer paso será asegurarnos que en la instancia que vamos a utilizar tenemos el soporte de replicación instalado:

Si no hemos configurado ninguna publicación previamente en esta instancia tendremos que configurarle su distribuidor. Podremos utilizar un distribuidor existente o bien crear uno nuevo. Para este ejemplo crearemos un nuevo distribuidor local y asociaremos la instancia como un publicador de dicho distribuidor:

A continuación, crearemos un par de bases de datos que llamaremos replicaA y replicaB donde crearemos una tabla llamada replica en cada una de ellas:

CREATE TABLE replica (ID int primary key, data varchar(100), ID2 int unique)

El siguiente paso será configurar dos publicaciones y dos subscripciones “cruzadas”, una para replicar de replicaA a replicaB y otra para replicar de replicaB a replicaA. La configuración de las réplicas sería igual que con cualquier otra réplica transaccional con un par de salvedades a la hora de crear las subscripciones.

La primera es que debemos tener la opción @loopback_detection habilitada para evitar que hagamos “ping pong” con los cambios, replicando el mismo cambio de A a B, de B a A, de A a B de nuevo, etc.

La segunda es que normalmente, al igual que ocurre en la P2P, tiene más sentido que no inicialicemos mediante snapshots las subscripciones, sino que utilicemos un método alternativo para esta sincronización inicial. En nuestro caso como vamos a partir de tablas vacías, podemos simplemente indicarle que el subscriptor ya se encuentra inicializado correctamente utilizando el sync_type “replication support only”:

Teniendo en cuenta este par de particularidades hemos creado las publicaciones y subscripciones en nuestra instancia. Comprobaremos desde el monitor de replicación el estado inicial:

Conviene que nos aseguremos que el funcionamiento es correcto y que los cambios “fluyen” desde el publicador al distribuidor y del distribuidor al subscriptor sin latencias ni errores. Para ello podemos simplemente insertar algunos trace tokens desde el propio monitor de replicación:

legados a este punto ya podemos comenzar a probar nuestros escenarios de conflicto. Comenzaremos por el insert-insert para el que insertaremos una fila con la misma clave primaria a la vez en ambas bases de datos:

-- insert-insert

SET XACT_ABORT ON

BEGIN TRAN
INSERT INTO ReplicaA.dbo.replica (ID,data,ID2) values (1,'data',null)
INSERT INTO ReplicaB.dbo.replica (ID,data,ID2) values (1,'data',null)
COMMIT TRAN

Casi de forma inmediata obtenderemos un error que indica que algo ha ido mal en el batch de comandos que se han intentado ejecutar en el subscriptor:

Como un batch puede tener muchos comandos, la replicación intentará ejecutarlos individualmente antes de darse por vencida. Pasados unos segundos veremos ya el fallo por PK duplicada:

Llegados a este punto nuestras replicas quedarán detenidas y necesitarán de intervención manual para poder continuar. Si realmente la situación es que ambas filas del conflicto insert-insert son iguales, podríamos pensar que una posible solución “rápida” en este escenario sería borrar las filas en ambas bases de datos para que se inserten las filas pendientes en ambas subcripciones.

SET XACT_ABORT ON

BEGIN TRAN
DELETE FROM ReplicaA.dbo.replica WHERE ID=1
DELETE FROM ReplicaB.dbo.replica WHERE ID=1
COMMIT TRAN

Al hacer esto, veremos que la réplica puede continuar solucionándose el error:

Sin embargo, ocurre algo que inicialmente podríamos no tener en cuenta. Como hemos insertado un comando de delete en la réplica al realizar el borrado, lo que estamos acabando ejecutando en cada réplica es:

  • Replica A: Delete local + Insert replicado de B a A (el que falló) + Delete replicado de B a A
  • Replica B: Delete local + Insert replicado de A a B (el que falló) + Delete replicado de A a B

Por tanto, el resultado final tras la operación será que nos quedamos sin ninguna fila en ninguna de las réplicas:

-- Check tables

SELECT * FROM ReplicaA.dbo.replica
SELECT * FROM ReplicaB.dbo.replica

Para corregir la situación tendríamos que además de realizar los dos deletes, volver a reinsertar la fila “correcta” en únicamente uno de los nodos:

-- Insert only in one database

INSERT INTO ReplicaA.dbo.replica (ID,data,ID2) values (1,'data',null)
WAITFOR DELAY '00:00:10'

-- Check tables

SELECT * FROM ReplicaA.dbo.replica
SELECT * FROM ReplicaB.dbo.replica

Hemos visto cómo es importante que cuando vayamos a realizar cualquier tipo de “resolución de conflictos manual” tengamos presente que las nuevas operaciones correctivas también van a replicarse. Si quisiéramos intentar automatizar esta resolución de errores el primer paso sería modificar el parámetro SkipErrors en la configuración de nuestro agente de distribución. Para ello podríamos crearnos un perfil de agente personalizado donde indiquemos que queremos ignorar los comandos que generen un error 2627:

Sin embargo, existe ya un profile por defecto que nos ignora los casos más habituales, concretamente el 2627 (conflicto de PK duplicada), 2601 (conflicto por restricción UNIQUE) y 20598 (fila no encontrada):

De estos tres el último error puede ocurrir tanto si hemos borrado una fila e intentamos realizar un UPDATE sobre ella, como si intentamos realizar un DELETE sobre una fila que ha sido previamente eliminada. Vamos a utilizar este perfil y vamos a simular de nuevo el error anterior que nos genera un conflicto insert-insert. Es importante tener en cuenta que, dependiendo de la versión de SQL Server y del parámetro modificado, estos cambios de perfil de agente pueden entrar en efecto de forma automática o requerir que se pare y vuelva a arrancar el agente. En estos casos es siempre mejor ir sobre seguro y realizar el reinicio del agente para evitar que no coincida al 100% el perfil actual en ejecución con el configurado:

Lanzaremos de nuevo un insert-insert simultáneamente esta vez con el ID 2 en conflicto:

-- insert-insert

SET XACT_ABORT ON

BEGIN TRAN
INSERT INTO ReplicaA.dbo.replica (ID,data,ID2) values (2,'data',123)
INSERT INTO ReplicaB.dbo.replica (ID,data,ID2) values (2,'data',123)
COMMIT TRAN

Esta vez la réplica no quedará detenida, por lo que seguiríamos replicando otros cambios que puedan llegar, pero se nos registrará un error en el histórico avisándonos que se ha ignorado un error:

Salvo que tengamos muy controlada la situación en la que se pueda llegar a generar el conflicto, no deberíamos confiarnos con este tipo de “parches” ya que imaginemos por ejemplo que realizamos un insert con conflicto pero donde no todas las columnas de la fila sean idénticas. Por ejemplo:

-- insert-insert

SET XACT_ABORT ON

BEGIN TRAN
INSERT INTO ReplicaA.dbo.replica (ID,data,ID2) values (3,'dataReplicaA',12345)
INSERT INTO ReplicaB.dbo.replica (ID,data,ID2) values (3,'dataReplicaB',123456)
COMMIT TRAN

WAITFOR DELAY '00:00:10'

-- Check tables
SELECT * FROM ReplicaA.dbo.replica
SELECT * FROM ReplicaB.dbo.replica

En este caso lo que vamos a tener es un escenario en el cual las dos filas con ID 3 no tienen los mismos contenidos, es decir, que estarán desincronizadas. Esto se suele llamar un escenario de divergencia entre publicador y subscriptor. Para poder detectar este tipo de situaciones podemos utilizar un sistema de validación que viene incluido en la replicación. Básicamente debemos situarnos sobre cada una de las publicaciones e indicar que queremos validar sus subscriptores:

Este proceso de validación podrá ser más o menos pesado en función de las opciones de validación que usemos. Por ejemplo, para calcular el número de filas tenemos dos métodos, uno que ejecuta un count real y otro que se apoya en las tablas de metadatos para obtener el número de filas (que en condiciones normal debería ser correcto). Además, podemos indicar si queremos calcular checksums para verificar los datos de la fila:

Cuando ejecutamos la validación, normalmente esperaríamos que apareciera algún resultado, alguna ventana con las diferencias encontradas, etc. pero no es así. Tendremos que ir al historial del agente de distribución donde podremos encontrar (cuando termine, que en este caso es instantáneo al tratarse de una tabla muy pequeña) el resultado:

Es importante que validemos que realmente el fallo es real o no lo es. De hecho, el propio mensaje ya nos indica con el uso del “might be” que existen posibilidades de falsos positivos. La razón es que cuando comparamos los checksums y el número de filas entre las tablas si se trata de un sistema “vivo” con datos replicándose con frecuencia es bastante normal que los conteos y/o checksums puedan no coincidir temporalmente sin que sea un problema. Es recomendable lanzar estas validaciones en los periodos de menor actividad del sistema, de madrugada habitualmente, para minimizar la probabilidad de falsos positivos.

Desgraciadamente tampoco se provee de ningún método sencillo para poder solucionar estas diferencias por lo que debemos solucionarlas de forma manual, por ejemplo, realizando un update en uno de los nodos al valor que consideremos correcto. En nuestro caso vamos a preservar los valores de replicaA:

-- insert-insert manual resolution of divergence, update row on ReplicaB with values from ReplicaA

SET XACT_ABORT ON

BEGIN TRAN
UPDATE ReplicaB.dbo.replica SET data='dataReplicaA',ID2=12345 WHERE ID=3
COMMIT TRAN

WAITFOR DELAY '00:00:10'

-- Check tables

SELECT * FROM ReplicaA.dbo.replica
SELECT * FROM ReplicaB.dbo.replica

Una posible alternativa a este trabajo manual sería utilizar una herramienta de terceros para comparar y generar los comandos de sincronización necesarios. También podemos usar una herramienta poco conocida y que viene de serie en SQL Server desde 2005 (si no me falla la memoria) llamada tablediff. Esta herramienta nos permite especificar una tabla origen y una tabla destino y obtener el fichero con las diferencias y los comandos necesarios para sincronizar las tablas. Vamos a volver a crear una situación de divergencia y lanzaremos la herramienta para ver qué resultado obtenemos:

-- insert-insert manual resolution of divergence, update one row

SET XACT_ABORT ON

BEGIN TRAN
INSERT INTO ReplicaA.dbo.replica (ID,data,ID2) values (4,'dataReplicaA',1234567)
INSERT INTO ReplicaB.dbo.replica (ID,data,ID2) values (4,'dataReplicaB',12345678)
COMMIT TRAN

WAITFOR DELAY '00:00:10'

-- Check tables

SELECT * FROM ReplicaA.dbo.replica
SELECT * FROM ReplicaB.dbo.replica

Una vez desincronizados, lanzamos la herramienta indicando el servidor, base de datos y tabla tanto origen como destino:

El resultado es el siguiente script que deberemos lanzar contra replicaB:

-- Host: .\SQL2019
-- Database: [ReplicaB]
-- Table: [dbo].[replica]

UPDATE [dbo].[replica] SET [data]=N'dataReplicaA',[ID2]=1234567 WHERE [ID] = 4

Aunque configuremos que la réplica “ignore” ciertos errores y continúe replicando el resto, esto no significa que este error no quede registrado. Vamos a provocar un insert-insert distinto a los anteriores, por un conflicto con la columna ID2 que tiene una restricción UNIQUE:

-- insert-insert

SET XACT_ABORT ON

BEGIN TRAN
INSERT INTO ReplicaA.dbo.replica (ID,data,ID2) values (6,'dataReplicaA',11)
INSERT INTO ReplicaB.dbo.replica (ID,data,ID2) values (7,'dataReplicaB',11)
COMMIT TRAN

WAITFOR DELAY '00:00:10'

-- Check tables

SELECT * FROM ReplicaA.dbo.replica
SELECT * FROM ReplicaB.dbo.replica

En este caso acabamos con dos filas con distinta PK pero que únicamente una de ellas está presente en cada nodo de la réplica.  En la tabla MSrepl_errors de la base de datos de distribución podemos encontrar los errores registrados:

Violation of UNIQUE KEY constraint ‘UQ__replica__C49703DDC15EC0A2’. Cannot insert duplicate key in object ‘dbo.replica’. The duplicate key value is (11).

Violation of PRIMARY KEY constraint ‘PK__replica__3214EC27F4D1F7F1’. Cannot insert duplicate key in object ‘dbo.replica’. The duplicate key value is (5).

Si por el contrario lanzamos un UPDATE en una de las filas que no están aun replicadas, forzaremos el código de error 20598, que en este caso estaría producido como “efecto secundario” de un conflicto previo, lo cual es algo que tendremos que tener en cuenta cuando analicemos el estado de la sincronización de nuestros datos.

-- Conflict due to missing row update

UPDATE ReplicaA.dbo.replica SET ID2=111 where ID=6

WAITFOR DELAY '00:00:10'

-- Check tables

SELECT * FROM ReplicaA.dbo.replica
SELECT * FROM ReplicaB.dbo.replica

Este proceso en el que se explica cómo se ha llegado al conflicto es lo que suelo denominar buscar la “historia” del conflicto ya que muchas veces no es tan obvia como pensábamos en un principio. Podemos tener una situación que sea fruto de una desincronización previa anterior no detectada. O bien causada por un error asimétrico, como por ejemplo una dependencia con otra tabla no replicada, o con otra base de datos, o con un linked server, o con un trigger que modifica otra tabla indirectamente, etc. Si hiciéramos el ejercicio de analizar uno a uno el resto de conflictos de distinta naturaleza al insert-insert veríamos que se pueden afrontar de forma similar. Por ejemplo, en el caso del update-update deberíamos tener en cuenta si queremos preservar los cambios introducidos en ambos nodos (hacer un merge de los registros) o bien mantener la fila completa de uno de los nodos lanzando un nuevo update con los valores a preservar.

Teniendo en cuenta todo lo anterior, aunque no tengamos detección automática de conflictos como en la P2P, es posible utilizar replicación transaccional bidireccional para un entorno multi master si añadimos un proceso que detecte que la réplica está “saltando errores” y disparar medidas automatizadas de detección/resolución de conflictos en caso necesario. No olvidemos que en el caso de la replicación P2P tenemos detección de conflictos automática pero la resolución es extremadamente básica, ya que solo nos permite “machacar” las filas en conflicto asumiendo que el cambio procedente del nodo con mayor prioridad (ID mayor) es el que queremos preservar. Si esto no es así, tendremos que intervenir de forma manual igualmente. Lo mismo ocurre en cualquier situación en la que queramos realizar algún tipo de “merge” de cambios entre los dos nodos, que tampoco nos ofrecerá la posibilidad por lo que en ambos casos tendremos trabajo extra a realizar por nuestra parte si nuestra base de datos no tiene un diseño 100% a prueba de conflictos (algo no habitual).

En general el proceso a implementar sería dependiente de cada entorno, no hay algo genérico que vaya a funcionar en todos, pero en líneas generales podría ser algo como esto:

  • Verificar periódicamente la tabla MSrepl_errors para detectar presencia de nuevos errores.
  • Detectar los códigos de error existentes y extraer (parsear) del mensaje de error la tabla y el ID afectado.
  • Obtener los datos de ambos nodos y analizar si tenemos un conflicto de tipo insert-insertupdate-update, etc.
  • Si es un caso habitual/conocido de nuestro entorno y donde la resolución de conflictos esperamos que sea automática y sea siempre la misma acción a tomar (como machacar siempre con los datos del nodo A) podríamos:
    • Auditar/respaldar los valores existentes en las tablas con dichos IDs.
    • Generar script que con los IDs realice las acciones necesarias para sincronizar teniendo en cuenta los efectos de la replicación (updatesinsertsdeletes, etc.).
    • Guardar en un log de “scripts de resolución de conflictos” el script a lanzar para tenerlo como referencia en caso de fallos.
    • Ejecutar el script.
    • Verificar pasados unos segundos que los datos están sincronizados para esos IDs.
  • En un caso no conocido/habitual podríamos:
    • Notificar mediante el envío de un mensaje de correo o similar al DBA.
    • Tomar acciones correctivas manuales.
    • Si nos ocurre múltiples veces, considerar que nos encontramos ante un nuevo caso habitual y plantearnos cómo abordaríamos su resolución automatizada.

Adicionalmente a este automatismo, sería conveniente que periódicamente validáramos que los subscriptores permanecen sincronizados lanzando una validación de la subscripción AtoB y de la BtoA. Esto nos permitiría detectar situaciones que no podríamos detectar de otras formas. Tenemos que tener claro que en cuanto entre un proceso de replicación en nuestro entorno, y más si es bidireccional, con posibilidad de conflictos, etc. estamos añadiendo una complejidad a nuestro entorno normalmente mayor a la que esperamos inicialmente. Dicho de otra forma, se suele infraestimar el impacto que puede llegar a tener desde el punto de vista de soporte así como los problemas causados indirectamente por la información desincronizada.

Además, el tamaño del problema causado por un conflicto o tener los datos desincronizados empeora con el tiempo. Un ejemplo que es muy claro es si nos planteamos el alta de un nuevo cliente en nuestro ERP. Si por algún problema tenemos un mismo cliente dado de alta con un ID distinto en cada nodo, si es un cliente que se dio de alta hoy, seguramente solucionarlo hoy es poco costoso. Si ese conflicto/desincronización se mantiene durante días o semanas ese cliente tendrá comerciales asociados, tendrá pedidos con sus líneas, sus facturas asociadas, etc. todos asociados a distintos IDclientes en cada nodo. Esto complicara mucho más el realizar la sincronización ya que no podremos simplemente eliminar un IDcliente ya que desearemos reasociar sus pedidos (eliminando duplicidades quizás) ajustar la contabilidad de los dos clientes en únicamente uno, etc. Es posible que incluso para ciertas operaciones críticas de datos maestros como el alta de clientes, la modificación de sus datos, etc. podamos decidir utilizar únicamente uno de los dos nodos, no funcionando en modo multi-master, para así no correr con el riesgo de sufrir estos problemas.

En conclusión, las arquitecturas que incluyen replicación de datos con múltiples nodos de multi-master pueden ser una solución a ciertos problemas, como aportar escalabilidad horizontal de la carga de escritura, pero a cambio añaden complejidades respecto a la consistencia de los datos. En otros modelos no relacionales muchas veces esta consistencia se desprecia o se deja de lado de forma que podemos escalar, pero a cambio podemos acabar con datos duplicados, registros hijos huérfanos apuntando a padres que ya no existen, etc. En algunos casos tras analizar las necesidades de replicación multi-master es posible que una replicación transaccional P2P sea una alternativa viable técnicamente pero no viable por coste (SQL Server Enterprise Edition). En estos casos hemos es posible utilizar replicación transaccional bidireccional a cambio de una mayor complejidad a la hora de detectar y resolver los futuros conflictos por lo que es crítico que éstos sean estadísticamente poco probables y sea su resolución mediante operaciones complementarias una opción viable.

X Edición Executive Máster en BI & Advanced Analytics con Tecnologías Microsoft. Conviértete en un año en un experto en BI con un seguimiento personalizado de los mentores y MVPs de SolidQ y con el nuevo temario del máster en BI & Advanced Analytics , introduciendo Modern Data Warehouse, analítica y visualización avanzada.

¡Empezamos en octubre! Inscríbete ahora y aprovecha el descuento que hay disponible hasta finales de julio o completar inscripciones. Toda la información aquí.

 

Rubén Garrigós