Replicar columnas de identidadReplicate Identity Columns

SE APLICA A: síSQL Server síAzure SQL Database (solo Instancia administrada) noAzure Synapse Analytics (SQL DW) noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Cuando asigna una propiedad IDENTITY a una columna, MicrosoftMicrosoft SQL ServerSQL Server genera automáticamente números que se incrementan secuencialmente para las nuevas filas insertadas en la tabla que contiene la columna de identidad.When you assign an IDENTITY property to a column, MicrosoftMicrosoft SQL ServerSQL Server automatically generates sequential numbers for new rows inserted in the table containing the identity column. Para obtener más información, vea IDENTITY (propiedad ) (Transact-SQL).For more information, see IDENTITY (Property) (Transact-SQL). Debido a que las columnas de identidad pueden estar incluidas como parte de la clave principal, resulta importante evitar los valores duplicados en las columnas de identidad.Because identity columns might be included as a part of the primary key, it is important to avoid duplicate values in the identity columns. Para utilizar 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 diferentes valores en el intervalo de identidad con el fin de que no se produzcan duplicados.To use identity columns in a replication topology that has updates at more than one node, each node in the replication topology must use a different range of identity values, so that duplicates do not occur.

Por ejemplo, podría asignarse el rango 1-100 al Publicador, el rango 101-200 al Suscriptor A y el rango 201-300 al Suscriptor B.For example, the Publisher could be assigned the range 1-100, Subscriber A the range 101-200, and Subscriber B the range 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.If a row is inserted at the Publisher and the identity value is, for example, 65, that value is replicated to each Subscriber. Cuando la replicación inserta datos en cada suscriptor, no incrementa el valor de la columna de identidad en la tabla del suscriptor; en su lugar, se inserta el valor literal 65.When replication inserts data at each Subscriber, it does not increment the identity column value in the Subscriber table; instead, the literal value 65 is inserted. Son solo las inserciones del usuario y no las del agente de replicación las que hacen que se incremente el valor de la columna de identidad.Only user inserts, but not replication agent inserts cause the identity column value to be incremented.

La replicación controla las columnas de identidad en todos los tipos de publicaciones y suscripciones, permitiéndole a usted administrar las columnas manualmente o hacer que la replicación las administre automáticamente.Replication handles identity columns across all publication and subscription types, allowing you to manage the columns manually or have replication manage them automatically.

Nota

No se admite la posibilidad de agregar una columna de identidad a una tabla publicada porque puede dar como resultado la falta de convergencia cuando la columna se replica en el suscriptor.Adding an identity column to a published table is not supported, because it can result in non-convergence when the column is replicated to the Subscriber. Los valores de la columna de identidad en el publicador dependen del orden en que se almacenen físicamente las filas de la tabla afectada.The values in the identity column at the Publisher depend on the order in which the rows for the affected table are physically stored. Las filas se pueden almacenar de forma diferente en el suscriptor; por tanto, el valor de la columna de identidad puede ser diferente para las mismas filas.The rows might be stored differently at the Subscriber; therefore the value for the identity column can be different for the same rows.

Especificar una opción de administración del intervalo de identidadSpecifying an Identity Range Management Option

La replicación ofrece tres opciones de administración del intervalo de identidad:Replication offers three identity range management options:

  • Automático.Automatic. Se utiliza para replicación de mezcla y replicación transaccional con actualizaciones en el suscriptor.Used for merge replication and transactional replication with updates at the Subscriber. Especifique intervalos de tamaño para el publicador y los suscriptores, y la replicación automáticamente administrará las asignaciones de los nuevos intervalos.Specify size ranges for the Publisher and Subscribers, and replication automatically manages the assignment of new ranges. La replicación establece la opción NOT FOR REPLICATION en la columna de identidad en el suscriptor, con el fin de que solo las inserciones del usuario hagan que el valor se incremente en el suscriptor.Replication sets the NOT FOR REPLICATION option on the identity column at the Subscriber, so that only user inserts cause the value to be incremented at the Subscriber.

    Nota

    Los suscriptores deben estar sincronizados con el publicador para recibir nuevos intervalos.Subscribers must synchronize with the Publisher to receive new ranges. Como a los suscriptores se les asignan intervalos de identidad automáticamente, es posible que cualquier suscriptor agote todo el suministro de intervalos de identidad si solicita repetidas veces nuevos intervalos.Because Subscribers are assigned identity ranges automatically, it is possible for any Subscriber to exhaust the entire supply of identity ranges if it repeatedly requests new ranges.

  • Manual.Manual. Se utiliza para replicación de instantáneas y transaccional sin actualizaciones en el suscriptor, replicación transaccional punto a punto o en los casos en los que la aplicación deba controlar los intervalos de identidad mediante programación.Used for snapshot and transactional replication without updates at the Subscriber, peer-to-peer transactional replication, or if your application must control identity ranges programmatically. Si especifica la administración manual, debe asegurarse de que los intervalos se asignan al publicador y a cada suscriptor y que los nuevos intervalos se asignan si se utilizan los intervalos iniciales.If you specify manual management, you must ensure that ranges are assigned to the Publisher and each Subscriber and that new ranges are assigned if the initial ranges are used. La replicación establece la opción NOT FOR REPLICATION en la columna de identidad en el suscriptor.Replication sets the NOT FOR REPLICATION option on the identity column at the Subscriber.

  • Ninguno.None. Esta opción solo se recomienda para lograr la compatibilidad con versiones anteriores de SQL ServerSQL Server y solo está disponible desde la interfaz de procedimientos almacenados de publicaciones transaccionales.This option is recommended only for backwards compatibility with earlier versions of SQL ServerSQL Server and is available only from the stored procedure interface for transactional publications.

Para especificar una opción de administración del intervalo de identidad, vea Administrar columnas de identidad.To specify an identity range management option, see Manage Identity Columns.

Asignar intervalos de identidadAssigning Identity Ranges

La replicación de mezcla y la replicación transaccional utilizan métodos diferentes para asignar intervalos. Estos métodos se describen en esta sección.Merge replication and transactional replication use different methods for assigning ranges; these methods are described in this section.

Existen dos tipos de intervalos que deben tenerse en cuenta a la hora de replicar columnas de identidad: los intervalos asignados al publicador y a los suscriptores, y el intervalo del tipo de datos de la columna.There are two types of ranges to take into account when replicating identity columns: the ranges assigned to the Publisher and Subscribers, and the range of the data type in the column. En la siguiente tabla se muestran los intervalos disponibles para los tipos de datos que se utilizan normalmente en las columnas de identidad.The following table shows the ranges available for the data types typically used in identity columns. El intervalo se utiliza en todos los nodos de una topología.The range is used across all nodes in a topology. Por ejemplo, si utiliza smallint comenzando por 1 con un incremento de 1, el número máximo de inserciones es de 32.767 para el publicador y todos los suscriptores.For example, if you use smallint starting at 1 with an increment of 1, the maximum number of inserts is 32,767 for the Publisher and all Subscribers. El número real de inserciones depende de si hay espacios en los valores utilizados y de si se ha utilizado un valor de umbral.The actual number of inserts depends on whether there are gaps in the values used and whether a threshold value is used. Para obtener más información acerca de los umbrales, vea las secciones "Replicación de mezcla" y "Replicación transaccional con suscripciones de actualización en cola" más adelante.For more information about thresholds, see the following sections "Merge Replication" and "Transactional Replication with Queued Updating Subscriptions".

Si el publicador agota su intervalo de identidad después de una inserción, puede asignar automáticamente un nuevo intervalo si la inserción la realizó un miembro del rol fijo de base de datos db_owner .If the Publisher exhausts its identity range after an insert, it can automatically assign a new range if the insert was performed by a member of the db_owner fixed database role. Si la inserción la realizó un usuario con otro rol, el Agente de registro del LOG, el Agente de mezcla o un usuario miembro del rol db_owner, deberá ejecutar sp_adjustpublisheridentityrange (Transact-SQL).If the insert was performed by a user not in that role, the Log Reader Agent, Merge Agent, or a user who is a member of the db_owner role must run sp_adjustpublisheridentityrange (Transact-SQL). Para las publicaciones transaccionales, el Agente de registro del LOG debe estar ejecutándose para asignar automáticamente un nuevo intervalo (la opción predeterminada es que el agente se ejecute continuamente).For transactional publications, the Log Reader Agent must be running to automatically allocate a new range (the default is for the agent to run continuously).

Advertencia

Durante una inserción por lotes grande, el desencadenador de replicación se desencadena una sola vez, no para cada fila de la inserción.During a large batch insert the replication trigger is fired only once, not for each row of the insert. Esto puede provocar un error en la instrucción de inserción si se agota un intervalo de identidad durante una inserción grande, como una instrucción INSERT INTO .This can lead to a failure of the insert statement if an identity range is exhausted during an large insert, such as an INSERT INTO statement.

Tipo de datosData type IntervaloRange
tinyinttinyint No se admite para la administración automáticaNot supported for automatic management
smallintsmallint De -2^15 (-32.768) a 2^15-1 (32.767)-2^15 (-32,768) to 2^15-1 (32,767)
intint De -2^31 (-2.147.483.648) a 2^31-1 (2.147.483.647)-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
bigintbigint De -2^63 (-9.223.372.036.854.775.808) a 2^63-1 (9.223.372.036.854.775.807)-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
decimal y numericdecimal and numeric De -10^38+1 a 10^38-1-10^38+1 through 10^38-1

Nota

Para crear un número que se incremente automáticamente y que se pueda usar en varias tablas, o que se pueda llamar desde las aplicaciones sin hacer referencia a ninguna tabla, vea Números de secuencia.To create an automatically incrementing number that can be used in multiple tables or that can be called from applications without referencing any table, see Sequence Numbers.

Replicación de mezclaMerge Replication

Los intervalos de identidad los administra el publicador y se propagan a los suscriptores mediante el Agente de mezcla (en una jerarquía de republicación, los intervalos los administra el publicador raíz y los republicadores).Identity ranges are managed by the Publisher and propagated to Subscribers by the Merge Agent (in a republishing hierarchy, ranges are managed by the root Publisher and the republishers). Los valores de identidad se asignan a partir de un grupo en el publicador.The identity values are assigned from a pool at the Publisher. Cuando agregue 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 valores para:When you add an article with an identity column to a publication in the New Publication Wizard or by using sp_addmergearticle (Transact-SQL), you specify values for:

  • El parámetro @identity_range, que controla el tamaño del intervalo de identidad asignado inicialmente al publicador y a los suscriptores con suscripciones de cliente.The @identity_range parameter, which controls the identity range size initially allocated both to the Publisher and to Subscribers with client subscriptions.

    Nota

    En los suscriptores que se ejecuten en versiones anteriores de SQL ServerSQL Server, este parámetro (y no el parámetro @pub_identity_range) también controla el tamaño del intervalo de identidad en los suscriptores que se pueden volver a publicar.For Subscribers running previous versions of SQL ServerSQL Server, this parameter (rather than the @pub_identity_range parameter) also controls the identity range size at republishing Subscribers.

  • El parámetro @pub_identity_range, que controla el tamaño del intervalo de identidad para las republicaciones asignadas a los suscriptores con suscripciones de servidor (obligatorio para volver a publicar datos).The @pub_identity_range parameter, which controls the identity range size for republishing allocated to Subscribers with server subscriptions (required for republishing data). Todos los suscriptores con suscripciones de servidor reciben un intervalo para volver a publicar, incluso si no han vuelto a publicar ningún dato.All Subscribers with server subscriptions receive a range for republishing, even if they don't actually republish data.

  • 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 CompactSQL Server Compact o a una versión anterior de SQL ServerSQL Server.The @threshold parameter, which is used to determine when a new range of identities is required for a subscription to SQL Server CompactSQL Server Compact or a previous version of SQL ServerSQL Server.

Por ejemplo, se podría especificar 10000 para @identity_range y 500000 para @pub_identity_range.For example, you could specify 10000 for @identity_range and 500000 for @pub_identity_range. El publicador y todos los suscriptores que ejecutan SQL Server 2005 (9.x)SQL Server 2005 (9.x) o una versión posterior, incluido el suscriptor con la suscripción de servidor, están asignados un intervalo principal de 10000.The Publisher and all Subscribers running SQL Server 2005 (9.x)SQL Server 2005 (9.x) or a later version, including the Subscriber with the server subscription, are assigned a primary range of 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 sincronizan con el suscriptor de republicación (también se debe especificar @identity_range, @pub_identity_rangey @threshold para los artículos de la publicación en el suscriptor de republicación).The Subscriber with the server subscription is also assigned a primary range of 500000, which can be used by Subscribers that synchronize with the republishing Subscriber (you must also specify @identity_range, @pub_identity_range, and @threshold for the articles in the publication at the republishing Subscriber).

Todos los suscriptores que se ejecutan en SQL Server 2005 (9.x)SQL Server 2005 (9.x) o una versión posterior también reciben un intervalo de identidad secundario.Each Subscriber running SQL Server 2005 (9.x)SQL Server 2005 (9.x) or a later version also receives a secondary identity range. El intervalo secundario tiene el mismo tamaño que el intervalo principal. Cuando se agota el intervalo principal, se utiliza el intervalo secundario y el Agente de mezcla asigna un nuevo intervalo en el suscriptor.The secondary range is equal in size to the primary range; when the primary range is exhausted, the secondary range is used, and the Merge Agent assigns a new range to the Subscriber. El nuevo intervalo se convierte en el intervalo secundario y el proceso continúa a medida que el suscriptor utiliza valores de identidad.The new range becomes the secondary range, and the process continues as the Subscriber uses identity values.

Replicación transaccional con suscripciones de actualización en colaTransactional Replication with Queued Updating Subscriptions

El distribuidor administra los intervalos de identidad y el Agente de distribución los propaga a los suscriptores.Identity ranges are managed by the Distributor and propagated to Subscribers by the Distribution Agent. Los valores de identidad se asignan a partir de un grupo en el distribuidor.The identity values are assigned from a pool at the Distributor. El tamaño del grupo se basa en el tamaño del tipo de datos y en el incremento utilizado para la columna de identidad.The pool size is based on the size of the data type and the increment used for the identity column. Cuando agregue 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 valores para:When you add an article with an identity column to a publication in the New Publication Wizard or by using sp_addarticle (Transact-SQL), you specify values for:

  • El parámetro @identity_range, que controla el tamaño del intervalo de identidad inicialmente asignado a todos los suscriptores.The @identity_range parameter, which controls the identity range size initially allocated to all Subscribers.

  • El parámetro @pub_identity_range, que controla el tamaño del intervalo de identidad asignado al publicador.The @pub_identity_range parameter, which controls the identity range size allocated to the Publisher.

  • El parámetro @threshold, que se usa para determinar cuándo se requiere un nuevo intervalo de identidades para una suscripción.The @threshold parameter, which is used to determine when a new range of identities is required for a subscription.

Por ejemplo, se podría especificar 10000 para @pub_identity_range, 1000 para @identity_range (suponiendo menos actualizaciones en el suscriptor), y 80 % para @threshold.For example, you could specify 10000 for @pub_identity_range, 1000 for @identity_range (assuming fewer updates at the Subscriber), and 80 percent for @threshold. Después de 800 inserciones en el suscriptor (80 por ciento de 1000), al suscriptor se le asigna un nuevo intervalo.After 800 inserts at a Subscriber (80 percent of 1000), a Subscriber is assigned a new range. Después de 8000 inserciones en el publicador, al publicador se le asigna un nuevo intervalo.After 8000 inserts at the Publisher, the Publisher is assigned a new range. Cuando se asigne un nuevo intervalo, habrá un espacio en los valores del intervalo de identidad de la tabla.When a new range is assigned, there will be a gap in the identity range values in the table. Si se especifica un umbral mayor, se obtienen menos espacios pero el sistema es menos tolerante a los errores: si el Agente de distribución no se puede ejecutar por alguna razón, es más fácil que un suscriptor se quede sin identidades.Specifying a higher threshold results in smaller gaps, but the system is less fault-tolerant: if the Distribution Agent cannot run for some reason, a Subscriber could more easily run out of identities.

Asignar intervalos para la administración manual del intervalo de identidadAssigning ranges for manual identity range management

Si especifica la administración manual del intervalo de identidad, debe asegurarse de que el publicador y todos los suscriptores utilizan intervalos de identidad diferentes.If you specify manual identity range management, you must ensure that the Publisher and each Subscriber use different identity ranges. Por ejemplo, imagine 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.For example, consider a table at the Publisher with an identity column defined as IDENTITY(1,1): the identity column starts at 1 and is incremented by 1 each time a row is inserted. Si la tabla del publicador tiene 5.000 filas y prevé que aumentará durante la vida de la aplicación, el publicador podría utilizar el rango 1-10.000.If the table at the Publisher has 5,000 rows, and you expect some growth in the table over the life of the application, the Publisher could use the range 1-10,000. Si hay dos suscriptores, el Suscriptor A podría usar el rango 10.001-20.000 y el Suscriptor B, el rango 20.001-30.000.Given two Subscribers, Subscriber A could use 10,001-20,000, and Subscriber B could use 20,001-30,000.

Después de inicializar un suscriptor mediante una instantánea u otro medio, ejecute DBCC CHECKIDENT para asignarle al suscriptor un punto de partida para este intervalo de identidad.After a Subscriber is initialized with a snapshot or through another means, execute DBCC CHECKIDENT to assign the Subscriber a starting point for its identity range. Por ejemplo, en el suscriptor A, ejecutaría DBCC CHECKIDENT('<TableName>','reseed',10001).For example, at Subscriber A, you would execute DBCC CHECKIDENT('<TableName>','reseed',10001). En el suscriptor B, ejecutaría CHECKIDENT('<TableName>','reseed',20001).At Subscriber B, you would execute CHECKIDENT('<TableName>','reseed',20001).

Para asignar nuevos intervalos en el publicador o en los suscriptores, ejecute DBCC CHECKIDENT y especifique un valor nuevo para regenerar la tabla.To assign new ranges to the Publisher or Subscribers, execute DBCC CHECKIDENT and specify a new value to reseed the table. Debería encontrar una manera de determinar cuándo se debe asignar un intervalo nuevo.You should have some way to determine when a new range must be assigned. Por ejemplo, su aplicación podría tener un mecanismo que detectara si un nodo va a consumir su intervalo y asignarle uno nuevo utilizando DBCC CHECKIDENT.For example, your application could have a mechanism that detects when a node is about to use up its range and assign a new range using DBCC CHECKIDENT. También puede agregar una restricción CHECK para asegurarse de que no se pueda agregar una fila si va a ocasionar el uso de un valor fuera del intervalo de identidad.You can also add a check constraint to ensure that a row cannot be added if it would cause an out of range identity value to be used.

Controlar los intervalos de identidad tras la restauración de una base de datosHandling Identity Ranges after a Database Restore

Si está utilizando la administración automática del intervalo de identidad, cuando un suscriptor se restaura de una copia de seguridad, automáticamente solicita un nuevo intervalo de valores de identidad.If you are using automatic identity range management, when a Subscriber is restored from a backup, it automatically requests a new range of identity values. Si un publicador se restaura de una copia de seguridad, debe asegurarse de que al publicador se le ha asignado un intervalo adecuado.If a Publisher is restored from a backup, you must ensure that the Publisher is assigned an appropriate range. Para la replicación de mezcla, asigne un nuevo intervalo con sp_restoremergeidentityrange (Transact-SQL).For merge replication, assign a new range using sp_restoremergeidentityrange (Transact-SQL). En la replicación transaccional, determine el valor más alto que se ha utilizado y después establezca el punto de partida para nuevos intervalos.For transactional replication, determine the highest value that has been used and then set the starting point for new ranges. Utilice el siguiente procedimiento después de la restauración de la base de datos de publicaciones:Use the following procedure after the publication database has been restored:

  1. Detenga todas las actividades en todos los suscriptores.Stop all activity on all Subscribers.

  2. Por cada tabla publicada que incluya una columna de identidad:For each published table that includes an identity column:

    1. En la base de datos de suscripciones en cada suscriptor, ejecute IDENT_CURRENT('<TableName>').In the subscription database at each Subscriber, execute IDENT_CURRENT('<TableName>').

    2. Registre el valor más alto encontrado en todos los suscriptores.Record the highest value found across all Subscribers.

    3. En la base de datos de publicaciones en el publicador, ejecute DBCC CHECKIDENT(<TableName>','reseed',<HighestValueFound+1>.In the publication database at the Publisher, execute DBCC CHECKIDENT(<TableName>','reseed',<HighestValueFound+1>).

    4. En la base de datos de publicaciones en el publicador, ejecute sp_adjustpublisheridentityrange <PublicationName>, <TableName>.In the publication database at the Publisher, execute sp_adjustpublisheridentityrange <PublicationName>, <TableName>.

    Nota

    Si el valor de la columna de identidad está establecido para que se reduzca y no para que se incremente, registre el valor más bajo encontrado y regenere con ese valor.If the value in the identity column is set to decrement rather than increment, record the lowest value found, and then reseed with that value.

Consulte tambiénSee Also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
DBCC CHECKIDENT (Transact-SQL) DBCC CHECKIDENT (Transact-SQL)
IDENT_CURRENT (Transact-SQL) IDENT_CURRENT (Transact-SQL)
IDENTITY (propiedad) (Transact-SQL) IDENTITY (Property) (Transact-SQL)
sp_adjustpublisheridentityrange (Transact-SQL)sp_adjustpublisheridentityrange (Transact-SQL)