Compartir a través de

Replicación de columnas de identidad

Al asignar una propiedad IDENTITY a una columna, Microsoft SQL Server genera automáticamente números secuenciales para las nuevas filas insertadas en la tabla que contiene la columna de identidad. Para obtener más información, vea IDENTITY (Property) (Transact-SQL). Dado que las columnas de identidad se pueden incluir como parte de la clave principal, es importante evitar valores duplicados en las columnas de identidad. Para usar columnas de identidad en una topología de replicación que tenga actualizaciones en más de un nodo, cada nodo de la topología de replicación debe usar un intervalo diferente de valores de identidad, de modo que no se produzcan duplicados.

Por ejemplo, el publicador podría asignarse el intervalo 1-100, el suscriptor A el intervalo 101-200 y el suscriptor B el intervalo 201-300. Si se inserta una fila en el publicador y el valor de identidad es, por ejemplo, 65, ese valor se replica en cada suscriptor. Cuando la replicación inserta datos en cada suscriptor, no incrementa el valor de la columna de identidad en la tabla suscriptor; en su lugar, se inserta el valor literal 65. Solo las inserciones de usuario, pero no las inserciones del agente de replicación hacen que se incremente el valor de la columna de identidad.

La replicación controla las columnas de identidad en todos los tipos de publicación y suscripción, lo que le permite administrar las columnas manualmente o hacer que la replicación las administre automáticamente.

Nota:

No se admite la adición de una columna de identidad a una tabla publicada, ya que puede dar lugar a una no convergencia cuando la columna se replica en el suscriptor. Los valores de la columna de identidad del publicador dependen del orden en que se almacenan físicamente las filas de la tabla afectada. Las filas se pueden almacenar de forma diferente en el suscriptor; por lo tanto, el valor de la columna de identidad puede ser diferente para las mismas filas.

Especificación de una opción de administración de intervalos de identidades

La replicación ofrece tres opciones de administración de intervalos de identidad:

  • Automático. Se usa para la replicación de combinación y replicación transaccional con actualizaciones realizadas por el suscriptor. Especifique intervalos de tamaño para el publicador y los suscriptores, y la replicación administra automáticamente la asignación de nuevos intervalos. La replicación establece la opción NOT FOR REPLICATION en la columna de identidad del Suscriptor, de modo que solo las inserciones de usuario hagan que el valor se incremente en el Suscriptor.

    Nota:

    Los suscriptores deben sincronizarse con el publicador para recibir nuevos intervalos. Dado que los suscriptores se asignan automáticamente intervalos de identidades, es posible que cualquier suscriptor agote toda la oferta de intervalos de identidades si solicita repetidamente nuevos intervalos.

  • Manual. Se usa para la replicación transaccional y de instantáneas sin actualizaciones en el suscriptor, la replicación transaccional punto a punto o si la aplicación debe controlar los intervalos de identidad mediante programación. Si especifica la administración manual, debe asegurarse de que los intervalos se asignan al publicador y a cada suscriptor y que se asignan nuevos intervalos si se usan los intervalos iniciales. La replicación establece la opción NOT FOR REPLICATION en la columna de identidad del suscriptor.

  • Ninguno. Esta opción se recomienda únicamente para la compatibilidad retroactiva con versiones más antiguas de SQL Server y está disponible exclusivamente a través de la interfaz de procedimiento almacenado para las publicaciones transaccionales.

Para especificar una opción de administración de intervalos de identidades, consulte Administrar columnas de identidad.

Asignación de rangos de identidad

La replicación de mezcla y la replicación transaccional usan diferentes métodos para asignar intervalos; estos métodos se describen en esta sección.

Hay dos tipos de intervalos que se deben tener en cuenta al replicar columnas de identidad: los intervalos asignados al publicador y los suscriptores, y el intervalo del tipo de datos de la columna. En la tabla siguiente se muestran los intervalos disponibles para los tipos de datos que se usan normalmente en columnas de identidad. El intervalo se usa en todos los nodos de una topología. Por ejemplo, si usa smallint a partir de 1 con un incremento de 1, el número máximo de inserciones es 32 767 para el publicador y todos los suscriptores. El número real de inserciones depende de si hay huecos en los valores usados y si se usa un valor de umbral. Para obtener más información sobre los umbrales, consulte las siguientes secciones: «Replicación de mezcla» y «Replicación transaccional con suscripciones de actualización en cola».

Si el publicador agota su intervalo de identidades después de una inserción, puede asignar automáticamente un nuevo intervalo si un miembro del rol fijo de base de datos de db_owner ha realizado la inserción. Si la inserción fue realizada por un usuario que no está en ese rol, el Agente de lectura de registros, el Agente de combinación o un usuario que sea miembro del rol db_owner debe ejecutar sp_adjustpublisheridentityrange (Transact-SQL). En el caso de las publicaciones transaccionales, el Agente lector de registros debe estar en funcionamiento para asignar automáticamente un nuevo intervalo (el agente está configurado para funcionar continuamente por defecto).

Advertencia

Durante una inserción por lotes grande, el desencadenador de replicación se desencadena solo una vez, no para cada fila de la inserción. Esto puede provocar un error en la instrucción INSERT si se agota un intervalo de identidades durante una inserción grande, como una instrucción INSERT INTO.

Tipo de dato Intervalo
tinyint No es compatible con la gestión automática
smallint -2^15 (-32.768) a 2^15-1 (32.767)
int -2^31 (-2,147,483,648) a 2^31-1 (2,147,483,647)
bigint -2^63 (-9,223,372,036,854,775,808) a 2^63-1 (9,223,372,036,854,775,807)
decimal y numeric -10^38+1 a 10^38-1

Nota:

Para crear un número de incremento automático que se puede usar en varias tablas o que se puede llamar desde aplicaciones sin hacer referencia a ninguna tabla, consulte Números de secuencia.

Replicación de mezcla

El Publicador administra los intervalos de identidad y los propaga a los Suscriptores por el Agente de Mezcla (en una jerarquía de republicación, los intervalos se administran por el Publicador raíz y los republicadores). Los valores de identidad se asignan desde un grupo en el publicador. Al agregar un artículo con una columna de identidad a una publicación en el Asistente para nueva publicación o mediante sp_addmergearticle (Transact-SQL), especifique los valores para:

  • El parámetro @identity_range , que controla el tamaño del intervalo de identidad asignado inicialmente tanto al publicador como a los suscriptores con suscripciones de cliente.

    Nota:

    En el caso de los suscriptores que ejecutan versiones anteriores de SQL Server, este parámetro (en lugar del parámetro @pub_identity_range ) también controla el tamaño del intervalo de identidades en suscriptores de nueva publicación.

  • El parámetro @pub_identity_range , que controla el tamaño del intervalo de identidad para volver a publicar asignado a suscriptores con suscripciones de servidor (necesarios para volver a publicar datos). Todos los suscriptores con suscripciones de servidor reciben un margen para la republicación, incluso si no publican nuevamente los datos.

  • El parámetro @threshold , que se usa para determinar cuándo se requiere un nuevo intervalo de identidades para una suscripción a SQL Server Compact o una versión anterior de SQL Server.

Por ejemplo, podría especificar 10000 para @identity_range y 500000 para @pub_identity_range. El publicador y todos los suscriptores que ejecutan SQL Server 2005 o una versión posterior, incluido el suscriptor con la suscripción de servidor, se asignan un intervalo principal de 10000. Al suscriptor con la suscripción de servidor también se le asigna un intervalo principal de 500000, que pueden usar los suscriptores que se sincronicen con el suscriptor de republicación (también debe especificar @identity_range, @pub_identity_range y @threshold para los artículos de la publicación en el suscriptor de nueva publicación).

Cada suscriptor que ejecuta SQL Server 2005 o una versión posterior también recibe un intervalo de identidad secundario. El intervalo secundario es igual al tamaño del intervalo principal; cuando se agota el intervalo principal, se usa el intervalo secundario y el Agente de mezcla asigna un nuevo intervalo al suscriptor. El nuevo intervalo se convierte en el intervalo secundario y el proceso continúa a medida que el suscriptor usa valores de identidad.

Replicación transaccional con suscripciones de actualización en cola

Los intervalos de identidad son administrados por el distribuidor y propagados a los suscriptores mediante el agente de distribución. Los valores de identidad se asignan desde un grupo en el distribuidor. El tamaño del grupo se basa en el tamaño del tipo de datos y en el incremento usado para la columna de identidad. Al agregar un artículo con una columna de identidad a una publicación en el Asistente para nueva publicación o mediante sp_addarticle (Transact-SQL), especifique los valores para:

  • Parámetro @identity_range , que controla el tamaño del intervalo de identidad asignado inicialmente a todos los suscriptores.

  • Parámetro @pub_identity_range , que controla el tamaño del intervalo de identidad asignado al publicador.

  • Parámetro @threshold , que se usa para determinar cuándo se requiere un nuevo intervalo de identidades para una suscripción.

Por ejemplo, podría especificar 10000 para @pub_identity_range, 1000 para @identity_range (suponiendo menos actualizaciones en el suscriptor) y un 80 % para @threshold. Después de 800 inserciones en un suscriptor (80 por ciento de 1000), se asigna un nuevo rango al suscriptor. Después de 8000 inserciones en el publicador, al publicador se le asigna un nuevo intervalo. Cuando se asigna un nuevo intervalo, habrá un hueco en los valores del rango de identidades de la tabla. Si se especifica un umbral mayor, se producen brechas más pequeñas, pero el sistema es menos tolerante a fallos: si el Agente de distribución no se puede ejecutar por algún motivo, un suscriptor podría quedarse más fácilmente sin identidades.

Asignación de rangos para la gestión manual de identidades

Si especifica la administración manual del intervalo de identidades, debe asegurarse de que el publicador y cada suscriptor usen intervalos de identidad diferentes. Por ejemplo, considere una tabla en el publicador con una columna de identidad definida como IDENTITY(1,1): la columna de identidad comienza en 1 y se incrementa en 1 cada vez que se inserta una fila. Si la tabla del publicador tiene 5000 filas y espera un crecimiento en la tabla durante la vida útil de la aplicación, el publicador podría usar el intervalo de 1 a 10 000. Dados dos suscriptores, el suscriptor A podría usar 10 001-20 000 y el suscriptor B podría usar 20 001-30 000.

Después de que un suscriptor se haya inicializado con una instantánea o a través de otro medio, ejecute DBCC CHECKIDENT para asignar al suscriptor un punto de inicio para su rango de identidades. Por ejemplo, en el suscriptor A, ejecutaría DBCC CHECKIDENT('<TableName>','reseed',10001). En el suscriptor B, ejecutaría CHECKIDENT('<TableName>','reseed',20001).

Para asignar nuevos intervalos al publicador o suscriptores, ejecute DBCC CHECKIDENT y especifique un nuevo valor para volver a usar la tabla. Debe tener alguna manera de determinar cuándo se debe asignar un nuevo intervalo. Por ejemplo, la aplicación podría tener un mecanismo que detecte cuándo un nodo está a punto de usar su intervalo y asignar un nuevo intervalo mediante DBCC CHECKIDENT. También puede agregar una restricción de comprobación para asegurarse de que no se puede agregar una fila si provocaría que se usara un valor de identidad fuera del intervalo.

Gestión de intervalos de identidad tras una restauración de base de datos

Si usa la administración automática de intervalos de identidades, cuando se restaura un suscriptor desde una copia de seguridad, solicita automáticamente un nuevo intervalo de valores de identidad. Si se restaura un publicador a partir de una copia de seguridad, debe asegurarse de que al publicador se le asigna un intervalo adecuado. Para la replicación de mezcla, asigne un nuevo intervalo utilizando sp_restoremergeidentityrange (Transact-SQL). Para la replicación transaccional, determine el valor más alto que se ha usado y, a continuación, establezca el punto de partida para los nuevos intervalos. Use el procedimiento siguiente después de restaurar la base de datos de publicación:

  1. Detenga toda la actividad en todos los suscriptores.

  2. Para cada tabla publicada que incluya una columna de identidad:

    1. En la base de datos de suscripciones de cada suscriptor, ejecute IDENT_CURRENT('<TableName>').

    2. Registre el valor más alto encontrado entre todos los suscriptores.

    3. En la base de datos de publicación del publicador, ejecute DBCC CHECKIDENT(<TableName>','reseed',<HighestValueFound+1>).

    4. En la base de datos de publicación del publicador, ejecute sp_adjustpublisheridentityrange <PublicationName>, <TableName>.

    Nota:

    Si el valor de la columna de identidad se establece en decremento en lugar de incrementar, registre el valor más bajo encontrado y, a continuación, vuelva a usar ese valor.

Véase también

BACKUP (Transact-SQL)
DBCC CHECKIDENT (Transact-SQL)
IDENT_CURRENT (Transact-SQL)
IDENTITY (Propiedad) (Transact-SQL)
sp_adjustpublisheridentityrange (Transact-SQL)

  • Last updated on