Configuración de transacciones distribuidas para un grupo de disponibilidad Always On
Se aplica a:
SQL Server
SQL Server 2017 (14.x) admite todas las transacciones distribuidas, incluidas las bases de datos de un grupo de disponibilidad. En este artículo se explica cómo configurar un grupo de disponibilidad para las transacciones distribuidas.
A fin de garantizar que las transacciones distribuidas sucedan, el grupo de disponibilidad debe estar configurado para registrar las bases de datos como administradores de recursos de transacción distribuida.
Nota
SQL Server 2016 (13.x) Service Pack 2 y versiones posteriores proporcionan compatibilidad total con las transacciones distribuidas en los grupos de disponibilidad. En las versiones de SQL Server 2016 (13.x) anteriores al Service Pack 2, las transacciones distribuidas entre bases de datos (es decir, las transacciones que usan bases de datos en la misma instancia de SQL Server) que implican una base de datos en un grupo de disponibilidad no son compatibles. SQL Server 2017 (14.x) no presenta esta limitación.
En SQL Server 2016 (13.x), los pasos de configuración son los mismos que en SQL Server 2017 (14.x).
En una transacción distribuida, las aplicaciones cliente funcionan con Microsoft DTC (Coordinador de transacciones distribuidas) para mantener la coherencia de las transacciones en múltiples orígenes de datos. DTC es un servicio disponible en los sistemas operativos basados en Windows Server compatibles. En una transacción distribuida, DTC es el coordinador de transacciones. Una instancia de SQL Server suele ser el administrador de recursos. Cuando hay una base de datos en un grupo de disponibilidad, cada base de datos debe ser su propio administrador de recursos.
SQL Server no impide las transacciones distribuidas de bases de datos en un grupo de disponibilidad, aun cuando el grupo de disponibilidad no esté configurado para transacciones distribuidas. Pero cuando un grupo de disponibilidad no está configurado para transacciones distribuidas, hay ocasiones en las que la conmutación por error puede que no se efectúe correctamente. En concreto, es posible que la nueva instancia de SQL Server de réplica principal no pueda obtener el resultado de la transacción de DTC. Para que la instancia de SQL Server pueda obtener el resultado de las transacciones dudosas de DTC tras la conmutación por error, configure el grupo de disponibilidad para que admita transacciones distribuidas.
DTC no interviene en el procesamiento de grupos de disponibilidad a menos que una base de datos también sea miembro de un clúster de conmutación por error. Dentro de un grupo de disponibilidad, la coherencia entre las réplicas se mantiene mediante la lógica del grupo de disponibilidad: la principal no completará la confirmación y reconocerá la confirmación al autor de la llamada hasta que la secundaria haya confirmado que ha guardado las entradas del registro en el almacenamiento duradero. Solo entonces el servidor principal declarará que la transacción se completará. En el modo asincrónico, no se espera el reconocimiento por parte de la réplica secundaria, y hay una posibilidad explícita de que se pierda una pequeña cantidad de datos.
Requisitos previos
Antes de configurar un grupo de disponibilidad que admita transacciones distribuidas, debe cumplir los siguientes requisitos previos:
Todas las instancias de SQL Server que participan en la transacción distribuida deben pertenecer a la versión SQL Server 2016 (13.x) o posterior.
Los grupos de disponibilidad deben ejecutarse en Windows Server 2012 R2 o en una versión posterior. Para Windows Server 2012 R2, debe instalar la actualización de KB3090973 disponible en https://support.microsoft.com/kb/3090973.
Crear un grupo de disponibilidad que admita transacciones distribuidas
Configure un grupo de disponibilidad que admita transacciones distribuidas. Establezca el grupo de disponibilidad para permitir que cada base de datos se registre como un administrador de recursos. En este artículo se explica cómo configurar un grupo de disponibilidad de manera que cada base de datos sea un administrador de recursos en DTC.
Puede crear un grupo de disponibilidad que admita transacciones distribuidas en SQL Server 2016 (13.x) o una versión posterior. Para crear un grupo de disponibilidad que admita transacciones distribuidas, incluya DTC_SUPPORT = PER_DB en la definición de dicho grupo de disponibilidad. Con el siguiente script se crea un grupo de disponibilidad que admite transacciones distribuidas.
CREATE AVAILABILITY GROUP MyAG
WITH (
DTC_SUPPORT = PER_DB
)
FOR DATABASE DB1, DB2
REPLICA ON
'Server1' WITH (
ENDPOINT_URL = 'TCP://SERVER1.corp.com:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = AUTOMATIC
),
'Server2' WITH (
ENDPOINT_URL = 'TCP://SERVER2.corp.com:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = AUTOMATIC
)
Nota
El script anterior es un ejemplo sencillo de un grupo de disponibilidad y no está pensado para ningún entorno de producción en concreto.
Modificar un grupo de disponibilidad para que admita transacciones distribuidas
Puede modificar un grupo de disponibilidad para que admita transacciones distribuidas en SQL Server 2017 (14.x) o una versión posterior. Para modificar un grupo de disponibilidad para que admita transacciones distribuidas, incluya DTC_SUPPORT = PER_DB en el script ALTER AVAILABILITY GROUP. Con este script de ejemplo se cambia el grupo de disponibilidad de forma que admita transacciones distribuidas.
ALTER AVAILABILITY GROUP MyaAG
SET (
DTC_SUPPORT = PER_DB
);
Nota
A partir de SQL Server 2016 (13.x) Service Pack 2, puede modificar un grupo de disponibilidad para transacciones distribuidas. Para las versiones de SQL Server 2016 (13.x) anteriores al Service Pack 2, necesita descartar y recrear el grupo de disponibilidad con la configuración DTC_SUPPORT = PER_DB.
Para deshabilitar las transacciones distribuidas, utilice el siguiente comando de Transact-SQL:
ALTER AVAILABILITY GROUP MyaAG
SET (
DTC_SUPPORT = NONE
);
Transacciones distribuidas: conceptos técnicos
Una transacción distribuida abarca dos o más bases de datos. Como administrador de transacciones, DTC coordina la transacción entre las instancias de SQL Server y otros orígenes de datos. Cada instancia del motor de base de datos de SQL Server puede funcionar como un administrador de recursos. Cuando un grupo de disponibilidad se configura con DTC_SUPPORT = PER_DB, las bases de datos pueden actuar como administradores de recursos. Para obtener más información, consulte la documentación de MS DTC.
Una transacción con dos o más bases de datos en una única instancia del motor de base de datos es una transacción distribuida de facto. La instancia administra la transacción distribuida internamente; para el usuario funciona como una transacción local. SQL Server 2017 (14.x) promueve todas las transacciones entre bases de datos a DTC cuando las bases de datos están en un grupo de disponibilidad configurado con DTC_SUPPORT = PER_DB, incluso en una sola instancia de SQL Server.
En la aplicación, una transacción distribuida se administra de forma muy parecida a una transacción local. Al final de la transacción, la aplicación solicita que se confirme o se revierta la transacción. El administrador de transacciones debe administrar una confirmación distribuida de forma diferente para reducir al mínimo el riesgo de que, si se produce un error en la red, algunos administradores de recursos realicen confirmaciones mientras los demás revierten la transacción. Esto se consigue administrando el proceso de confirmación en dos fases (la fase de preparación y la fase de confirmación), lo que se conoce como confirmación en dos fases.
Fase de preparación
Cuando el administrador de transacciones recibe una solicitud de confirmación, envía un comando de preparación a todos los administradores de recursos implicados en la transacción. Cada administrador de recursos hace lo necesario para que la transacción sea duradera y todos los búferes que contienen imágenes del registro de la transacción se pasan a disco. A medida que cada administrador de recursos completa la fase de preparación, notifica si la preparación ha tenido éxito o no al administrador de transacciones.
Fase de confirmación
Si el administrador de transacciones recibe la notificación de que todas las preparaciones son correctas por parte de todos los administradores de recursos, envía comandos de confirmación a cada administrador de recursos. A continuación, los administradores de recursos pueden completar la confirmación. Si todos los administradores de recursos indican que la confirmación ha sido correcta, el administrador de transacciones envía una notificación de éxito a la aplicación. Si algún administrador de recursos informó de un error al realizar la preparación, el administrador de transacciones envía un comando para revertir la transacción a cada administrador de recursos e indica a la aplicación que se ha producido un error de confirmación.
Pasos detallados
Con los siguientes puntos se explica cómo funciona la aplicación con DTC para completar las transacciones distribuidas.
- La instancia de SQL Server se da de alta en la transacción de DTC. Esto puede ocurrir cuando hay más de un administrador de recursos en la transacción, o bien si el cliente solicita que una transacción se promueva al nivel de transacción de DTC.
- El cliente realiza algunas funciones en la instancia de SQL Server de la transacción de DTC.
- El cliente emite si la transacción de DTC se confirma o se anula.
- Si el cliente emite una anulación, la transacción se anula inmediatamente.
- Si el cliente emite una confirmación, DTC inicia el protocolo de confirmación en dos fases, para lo cual solicita a todos los administradores de recursos de la transacción que preparen la transacción.
- DTC indica a todos los administradores de recursos que confirmen la transacción después de que todos ellos hayan confirmado correctamente la fase de preparación. Si algo impide la confirmación correcta, DTC anula la transacción.
Consecuencias de configurar un grupo de disponibilidad para que admita transacciones distribuidas
Cada entidad que participa en una transacción distribuida se conoce como administrador de recursos. Ejemplos de administradores de recursos:
- Instancia de SQL Server.
- Una base de datos en un grupo de disponibilidad que se ha configurado para admitir transacciones distribuidas.
- Servicio DTC: también puede ser un administrador de transacciones.
- Otros orígenes de datos.
Para poder participar en las transacciones distribuidas, una instancia de SQL Server se da de alta en DTC. Normalmente, la instancia de SQL Server se da de alta en DTC en el servidor local. Cada instancia de SQL Server crea un administrador de recursos con un identificador de administrador de recursos (RMID) único y lo registra con DTC. En la configuración predeterminada, todas las bases de datos de una instancia de SQL Server usan el mismo RMID.
Cuando una base de datos está en un grupo de disponibilidad, la copia de lectura y escritura de la base de datos (o réplica principal) se puede trasladar a otra instancia de SQL Server. Para admitir transacciones distribuidas durante este traslado, cada base de datos debe actuar como un administrador de recursos independiente y tener un RMID único. Cuando un grupo de disponibilidad tiene DTC_SUPPORT = PER_DB, SQL Server crea un administrador de recursos para cada base de datos y se registra con DTC por medio de un RMID único. En esta configuración, la base de datos es un administrador de recursos en las transacciones de DTC.
Importante
Tenga en cuenta que DTC tiene un límite de 32 inscripciones por transacción distribuida. Dado que cada base de datos de un grupo de disponibilidad se inscribe con DTC por separado, si la transacción incluye más de 32 bases de datos, es posible que reciba el siguiente error cuando SQL Server intente inscribir la 33.ª base de datos:
Enlist operation failed: 0x8004d101(XACT_E_TOOMANY_ENLISTMENTS). SQL Server could not register with Microsoft Distributed Transaction Coordinator (MS DTC) as a resource manager for this transaction. The transaction may have been stopped by the client or the resource manager.
Para más información sobre las transacciones distribuidas en SQL Server, vea Transacciones distribuidas.
Administrar las transacciones sin resolver
Después de una conmutación por error no se puede recuperar el resultado de las transacciones activas que había durante el cambio de RMID. Esto se debe a que el RMID que SQL Server usó para dar de alta no es el mismo que el RMID que usó para recuperar el resultado. El cambio de RMID puede ocurrir en los siguientes casos:
- Cuando se cambia
DTC_SUPPORTen un grupo de disponibilidad. - Cuando se agrega o quita una base de datos de un grupo de disponibilidad.
- Cuando se elimina un grupo de disponibilidad.
En los casos anteriores, si la réplica principal se conmuta por error a una nueva instancia de SQL Server, dicha instancia intenta ponerse en contacto con DTC para identificar el resultado de la transacción. DTC no puede devolver el resultado, porque el RMID que la base de datos está usando para obtener el resultado de las transacciones dudosas durante la recuperación no está dado de alta. Por lo tanto, la base de datos pasa a un estado SOSPECHOSO.
El nuevo registro de errores de SQL Server contiene una entrada similar al siguiente ejemplo:
Microsoft Distributed Transaction Coordinator (MS DTC)
failed to reenlist citing that the database RMID does
not match the RMID [xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx]
associated with the transaction. Please manually resolve
the transaction.
SQL Server detected a DTC/KTM in-doubt transaction with UOW
{yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy}.Please resolve it
following the guideline for Troubleshooting DTC Transactions.
El ejemplo anterior refleja que DTC no podría volver a dar de alta la base de datos de la nueva réplica principal en la transacción que se creó después de la conmutación por error. La instancia de SQL Server no puede averiguar el resultado de la transacción distribuida, por lo que marca la base de datos como sospechosa. La transacción se marca como una unidad de trabajo (UOW) y se hace referencia a ella con un GUID. Para recuperar la base de datos, confirme o revierta la transacción manualmente.
Advertencia
Cuando una transacción se confirma o revierte manualmente, ello puede repercutir en una aplicación. Confirme que la acción de confirmación o reversión es coherente con los requisitos de la aplicación.
Ejecute solo uno de los siguientes scripts:
- Para confirmar la transacción, actualice y ejecute el siguiente script (sustituya
yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyypor la UOW de la transacción dudosa reflejada en el mensaje de error anterior) y ejecute lo siguiente:
KILL 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy' WITH COMMIT
- Para revertir la transacción, actualice y ejecute el siguiente script (sustituya
yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyypor la UOW de la transacción dudosa reflejada en el mensaje de error anterior) y ejecute lo siguiente:
KILL 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy' WITH ROLLBACK
Después de confirmar o revertir la transacción, puede usar ALTER DATABASE para establecer la base de datos en línea. Actualice y ejecute el siguiente script (indique el nombre de base de datos de la base de datos sospechosa):
ALTER DATABASE [DB1] SET ONLINE
Para más información sobre cómo resolver transacciones dudosas, vea Resolve Transactions Manually (Resolver transacciones manualmente).
Pasos siguientes
Grupos de disponibilidad AlwaysOn: interoperabilidad (SQL Server)
Transactions - Always On Availability Groups and Database Mirroring (Transacciones: grupos de disponibilidad AlwaysOn y creación de reflejo de la base de datos)
Supporting XA Transactions (Compatibilidad con las transacciones XA)
How It Works: Session/SPID (-2) for DTC Transactions (Funcionamiento de Session/SPID [-2] en las transacciones de DTC)
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de